Thank you so so much for creating such a great question series.

with CTE_Bench_days as( select EMPLOYEE_ID, (to_date(end_dates,'dd/mm/yyyy hh24:mi:ss')-to_date(start_dates,'dd/mm/yyyy hh24:mi:ss'))+1 working_days from Staffing s join Consulting_engagements c on(s.JOB_ID=c.JOB_ID) where IS_CONSULTANT=1) select EMPLOYEE_ID,365- sum(working_days) from CTE_Bench_days group by EMPLOYEE_ID;

select e.*,first_value(sales)over(partition by product order by id) as fv,sum(sales)over (partition by product order by id) as rt from sales1 e;

with missing_cte as ( select min(id) as min_id from Missing_ID_Table union all select min_id +1 as id from missing_cte where min_id <20) select * from missing_cte except select * from Missing_ID_Table

select SOURCE_PHONE_NBR, case when FIRST_CALL=LAST_CALL then 'Y' when FIRST_CALL<>LAST_CALL then 'N' end as "Is_Match" from( select distinct SOURCE_PHONE_NBR, first_value(DESTINATION_PHONE_NBR)over(partition by SOURCE_PHONE_NBR order by CALL_START_DATETIME) as First_call, last_value(DESTINATION_PHONE_NBR)over (partition by SOURCE_PHONE_NBR order by CALL_START_DATETIME rows between unbounded preceding and unbounded following) as last_call from Phone_Log)

select * from BalanceTbl; with balance_cte as( select BALANCE,DATES,lag (BALANCE) over (order by DATES)as lag1, case when lag (BALANCE) over (order by DATES)=BALANCE then 0 else 1 end as tmp_seq from BalanceTbl), sequence_cte as(select BALANCE,DATES,sum(tmp_seq) over (order by dates) as seq --running tot from balance_cte) select BALANCE,min(DATES)as start_date, max(DATES) as end_date from sequence_cte group by BALANCE,seq order by seq;

select e.* ,sum(QUANTITY)over (partition by PRODUCTCODE order by INVENTORYDATE) running_total from Inventory e ;

--tot number of matched played by each team WITH cte_match_played AS ( SELECT team,SUM(tot) AS match_palyed FROM ( SELECT team_1 AS team,COUNT(*) AS tot FROM match_result GROUP BY team_1 UNION ALL SELECT team_2,COUNT(*) AS tot FROM match_result GROUP BY team_2 )GROUP BY team), --tot number of matches won by each team cte_match_won AS ( SELECT result,COUNT(*) AS match_won FROM match_result WHERE result IS NOT NULL GROUP BY result), --tot number of matches tie if any cte_match_tie AS ( SELECT team_1,tie_cnt FROM ( SELECT team_1,COUNT(*) AS tie_cnt FROM match_result WHERE result IS NULL GROUP BY team_1 UNION ALL SELECT team_2,COUNT(*) AS tie_cnt FROM match_result WHERE result IS NULL GROUP BY team_2 ) t ) SELECT a.team, a.match_palyed, b.match_won, nvl(c.tie_cnt, 0) AS match_tie, --tot num of match lost by each team ( a.match_palyed - b.match_won - nvl(c.tie_cnt, 0) ) AS match_lost FROM cte_match_played a JOIN cte_match_won b ON ( a.team = b.result ) LEFT JOIN cte_match_tie c ON ( a.team = c.team_1 );

Here is the my approach in mysql : with cte as (select *,lag(sequence+1,1,sequence) over(partition by grp ) as prev_sequence, sequence-lag(sequence+1,1,sequence) over(partition by grp) as grp_flag from emp_sq),cte_2 as (select *,sum(grp_flag) over(partition by grp rows between unbounded preceding and current row) as groupp from cte) select Grp,min(sequence) as min_seq,max(sequence) as max_seq from cte_2 group by Grp,groupp;

with cte_temp as( select *, lag(amount,1,0)over(partition by brand order by years) as prev from brands), cte_temp2 as( select *,case when amount>prev then 0 else 1 end as flag from cte_temp ), cte_answer as( select brand from cte_temp2 group by brand having sum(flag)=0 ) select years, brand, amount from cte_temp2 where brand in ( select * from cte_answer)

with cte as( select start_range,end_range from #sampletable union all select start_range+1,end_range from cte where start_range+1<=end_range) select start_range as id from cte order by start_range

with cte as (select *,lead(id) over(order by id) as ld from sample_table union all select id+1,ld from cte where id<ld) select * from(select id from cte group by id )subq except select * from sample_table

with cte as (select product_id,spend,years, lag(spend) over(partition by product_id order by years) as last_year_spend from(select product_id,sum(spend) as spend,year(transaction_date) as years from user_transaction group by product_id,year(transaction_date) ) as sbq) select *,round(coalesce(((spend-last_year_spend)/last_year_spend)*100,0),2) as YoY_growth from cte ---to replace null values also

Hi sir I have 1 doubt the join you mentioned like a.custid=b. custid it means for cte you have done group by and also max value you have taken then we have seen 1001 and 1002 you have joined with normally I am not understanding that in inner join should be 1 *4 it would be 4 times like 1001 1001 for both 1001 and 1002 but it is showing normal count and you please elaborate and add the comment

Great question.. even though by looking at it initially, I understood this will be done by Recursive CTE , but it took me some time to set this logic up

What about the window functions?

Select distinct UserId from ( Select UserId, Case WHEN DATEDIFF(day,LAG(CreatedAt) over(partition by UserId order by CreatedAt), CreatedAt) <=7 THEN 1 ELSE 0 END as Ind from Transactions_Amazon ) sub where Ind = 1

30sec straight forward to the point , thank you so much

As always, great question from Sunil. My approach without using the inbuilt percentile_cont function:- Declare @total int; Set @total = (Select sum(num_users) from search_frequency) ; with cte_rec_median as ( Select searches, num_users, 1 as step from search_frequency union all select searches, num_users, 1+step from cte_rec_median where step < num_users ) Select CASE WHEN @total%2 = 0 THEN CAST(sum(CAST(searches as Decimal(38,1))/2) as Decimal(38,1)) ELSE sum(searches) END as median from ( Select searches, CASE WHEN @total%2 = 0 THEN CASE WHEN rn = @total/2 or rn = @total/2+1 THEN 1 ELSE 0 END ELSE CASE WHEN rn = @total/2+1 THEN 1 ELSE 0 END END as Indicator from ( Select searches, num_users, step, row_number() over(order by searches, num_users) as rn from cte_rec_median )sub )sub2 where Indicator = 1

thanks a lot for clear understanding sir

Awesome ❤🎉🎉

Sir please teach Vlookup and hlookup

My Approach: with cte as( select Device_id, count(*) no_of_signals from Device group by Device_id ), cte2 as( select Locations, Device_id, count(*) cnt, count(locations) over (partition by Device_id) no_of_locations from Device group by Device_id, Locations ) select c.Device_id, c2.no_of_locations, c2.Locations as max_signal_location, c.no_of_signals from cte c inner join cte2 c2 on c.Device_id=c2.Device_id where cnt in (3,4)

Here's my solution without using LEAD or LAG function. ; with cte as ( Select S1.id as S1Id, S1.Visit_date as S1V, S1.No_of_people as S1P, S2.id as S2Id, S2.Visit_date as S2V, S2.No_of_people as S2P, S3.id as S3id, S3.Visit_date as S3V, S3.No_of_people as S3P from Stadium S1, Stadium S2, Stadium S3 where S1.id+1 = S2.id and S2.id+1 = s3.id and S1.No_of_people>=100 and S2.No_of_people>=100 and S3.No_of_people>=100 ) Select distinct * from ( Select S1id as id, S1V as Visit_date, S1P as No_of_people from cte UNION ALL Select S2id, S2V, S2P from cte UNION ALL Select S3id, S3V, S3P from cte ) sub

with cte as( select * ,case when Amount > lag(Amount, 1, 0) over(Partition by brand order by Years) then 1 else 0 end flag from brands ) select Years, Brand, Amount from ( select *, sum(flag) over(Partition by brand) sum_flag, count(*) over(Partition by brand) total_count from cte ) a where sum_flag = total_count

select transaction_date, count(distinct users_id) no_of_users ,count(distinct product_id) no_of_products from ( select *, rank() over(partition by users_id order by transaction_Date desc) rnk from user_transactions ) a where rnk = 1 group by transaction_date

C

In hp laptop same is not applicable pls tell me

--------------------CAN BE ACHEIVED EASILY BY ROW_NUMBER() with cte as(select *,row_number() over(partition by deptno order by salary)as rn from Employee_2), cte2 as( select *,row_number() over(partition by deptno order by salary desc)as rn2 from Employee_2) select empname,deptname,deptno,salary from cte where rn=1 union all select empname,deptname,deptno,salary from cte2 where rn2=1

-------1st SOLUTION select id,product,min(sales) over(partition by product)as sales_new from sales1 ------------------------2ND SOLUTION select id,product,SUM(sales) over(partition by product order by sales rows between unbounded preceding and current row)as cumulative_sales from sales1

with cte as(select *, first_value(destination_phone_nbr) over(partition by source_phone_nbr)as f_value, last_value(destination_phone_nbr) over(partition by source_phone_nbr)as l_value from Phone_Log), final_cte as( select source_phone_nbr, case when f_value=l_value then 'Y' else 'N' end as is_match,row_number() over(partition by source_phone_nbr)as rn from cte) select source_phone_nbr,is_match from final_cte where rn=1

WE MAY ACHEIVE THIS USING RECURSIVE CTE----- with recursive cte as(select min(id)as bs from sample_table union all select bs+1 from cte where bs+1<=(select max(id)from sample_table)) select * from cte where bs not in (select id from sample_table)

resolving the same question, and tried a little different approach.. luckily it works :) sol: with cte as ( select users_id,max(transaction_date) as dates,ROW_NUMBER() over(PARTITION by max(transaction_date) order by max(transaction_date)) as rn FROM user_transactions group by users_id ), cte2 as ( select transaction_date,count(transaction_date) as no_of_products from user_transactions group by transaction_date ) select cte.dates,max(cte.rn) as no_of_users,cte2.no_of_products from cte join cte2 on cte.dates=cte2.transaction_date group by cte.dates,cte2.no_of_products

Many Many thanks for this video ❤

the answer can achieved by this method also---------------- with cte as(select *,max(tranamt) over(partition by custid)as max_sal_per_cust from transaction_tbl) select *,round((tranamt/max_sal_per_cust),2)as ratio from cte

Thank you sir

very good explanation.

without CTE SELECT From_User,COUNt(*) cnt,row_number() OVER (ORDER BY COUNT(*) DESC, From_User) rnk FROM google_gmail_emails GROUP BY From_User ORDER BY COUNT(*) DESC

Why are we using [ ] brackets in the query? what is it's purpose?

You are really great. These videos helps lot❤

This SQL interview playlist really helped me and learned a lot. thank you great work!!

Option 3

with cte as (select len('interview') as rnk union all select rnk-1 from cte where rnk-1>0 ) select SUBSTRING('interview',1,rnk) from cte

select a.AccountNumber,b.Transaction_id, b.balance, a.trans_time from transaction_table b join( select accountNumber, max(transaction_time) as trans_time from transaction_table group by accountNumber ) as a on a.accountNumber= b.accountNumber and b.Transaction_time= a.trans_time order by Transaction_ID

Select Sales_Date,sum(Total) as Net from ( select Main.*,ROW_NUMBER () over (partition by Main.Sales_Date,Source_Currency order by Main.Effective_Start_Date desc) as RN from ( select Sales_Date,Effective_Start_Date,Source_Currency,Target_Currency,Sales_Amount,Exchange_Rate,(Sales_Amount*Exchange_Rate) as Total from T1, T2 where T1.Sales_Date >= T2.Effective_Start_Date and T1.Currency = T2.Source_Currency ) as Main) as sub where RN = 1 group by Sales_Date

Kindly check with this approach WITH t1 AS (SELECT ID, StudentName, CASE WHEN ID%2 =0 THEN ID/2 ELSE ID/2 + ID%2 END AS r FROM SeatArrangement), t2 AS (SELECT *,row_number() OVER (PARTITION BY r ORDER BY ID DESC) rn FROM t1) SELECT StudentName,row_number() OVER (ORDER BY 1) ID FROM t2

with cte as ( select *, row_number() over(partition by grp order by sequence) as rnk, sequence-row_number() over(partition by grp order by sequence) diff from emp) select distinct(grp) , min(sequence) over(partition by diff ) as minseq, max(sequence) over(partition by diff) as maxseq from cte order by grp;