Find the Subtasks That Did Not Execute Leetcode

 with recursive CTE as

(

SELECT task_id,subtasks_count,1 as result FROM Tasks

UNION ALL

SELECT CTE.task_id,subtasks_count,result+1 FROM CTE where result<subtasks_count

)

SELECT 

CTE.task_id,CTE.result FROM CTE LEFT JOIN Executed e

on e.task_id=CTE.task_id and e.subtask_id= CTE.result

where e.subtask_id is null

order by task_id,result

Median of numbers

create Table numbers (number int,frequency int);

insert into numbers values (1,2);

insert into numbers values (4,1);

insert into numbers values (6,2);

insert into numbers values (9,1); 

with cte2 as(

select n.*,

sum(frequency) over (order by number) as acc_sum,

sum(n.frequency) over() as sum_freq from numbers n

)

select avg(c2.number) from cte2 c2

where c2.acc_sum between sum_freq/2 and (sum_freq/2)+c2.frequency;

Median Employee Salary

 Create table If Not Exists Employee (Id int, Company varchar(255), Salary int);

Truncate table Employee;

insert into Employee (Id, Company, Salary) values ('1', 'A', '2341');

insert into Employee (Id, Company, Salary) values ('2', 'A', '341');

insert into Employee (Id, Company, Salary) values ('3', 'A', '15');

insert into Employee (Id, Company, Salary) values ('4', 'A', '15314');

insert into Employee (Id, Company, Salary) values ('5', 'A', '451');

insert into Employee (Id, Company, Salary) values ('6', 'A', '513');

insert into Employee (Id, Company, Salary) values ('7', 'B', '15');

insert into Employee (Id, Company, Salary) values ('8', 'B', '13');

insert into Employee (Id, Company, Salary) values ('9', 'B', '1154');

insert into Employee (Id, Company, Salary) values ('10', 'B', '1345');

insert into Employee (Id, Company, Salary) values ('11', 'B', '1221');

insert into Employee (Id, Company, Salary) values ('12', 'B', '234');

insert into Employee (Id, Company, Salary) values ('13', 'C', '2345');

insert into Employee (Id, Company, Salary) values ('14', 'C', '2645');

insert into Employee (Id, Company, Salary) values ('15', 'C', '2645');

insert into Employee (Id, Company, Salary) values ('16', 'C', '2652');

insert into Employee (Id, Company, Salary) values ('17', 'C', '65');

with cte as(

select Id,Company,salary 

,ROW_NUMBER() over(partition by Company order by salary) as rnk

,count(*) over(partition by Company) as cnt

from Employee

)select Id,Company,salary,rnk,cnt,CEILING(cnt/2.0),cnt/2+1 from cte

where rnk in (CEILING(cnt/2.0),cnt/2+1)

MySQL Generate Numbers

 WITH recursive numbers AS (

    select 0 as Date
   union all
   select Date + 1
   from numbers
   where Date < 10)
select * from numbers;

Create Table SampleTable
(
Start_Range Bigint,
End_Range Bigint
);
Insert into SampleTable Values (1,4);
Insert into SampleTable Values (6,6);
Insert into SampleTable Values (8,9);
Insert into SampleTable Values (11,13);
Insert into SampleTable Values (15,15);


with recursive CTE as 
(
SELECT MIN(Start_Range) as id from SampleTable
UNION ALL
SELECT id+1 from CTE
where id < (select max(End_Range) from SampleTable)
)
SELECT id FROM CTE ,SampleTable where id>=Start_Range and id<=End_Range order by id;




Powershell Fastest and Efficient way to read huge csv file

$row_count = 0 
Get-Content '.\big_file_name_with_path.csv' -ReadCount 2000 | foreach { $row_count += $_.count }
$row_count