Scenario Based SQL Question | Solving Using SCD Type 2 Concept | SQL Interview Question

In this video we will discuss a scenario based SQL problem. I will be solving it using Analytical function. You will learn how to use Lead analytical function with partition by clause and how to deal with data ranges in SQL.
Here is the ready script:
create table billings
(
emp_name varchar(10),
bill_date date,
bill_rate int
);
delete from billings;
insert into billings values
('Sachin','01-JAN-1990',25)
,('Sehwag' ,'01-JAN-1989', 15)
,('Dhoni' ,'01-JAN-1989', 20)
,('Sachin' ,'05-Feb-1991', 30)
;
create table HoursWorked
(
emp_name varchar(20),
work_date date,
bill_hrs int
);
insert into HoursWorked values
('Sachin', '01-JUL-1990' ,3)
,('Sachin', '01-AUG-1990', 5)
,('Sehwag','01-JUL-1990', 2)
,('Sachin','01-JUL-1991', 4)
#sql #scenariobased #interview

[@vandanaK-mh9zo]

Interesting !!! These problems look tedious initially, but then your step-by-step approach is amazing. Wonderful Work!!!

[@sharu164]

Thanks Ankit! You nailed it by connecting concepts with real world problems !

[@Datapassenger_prashant]

learned a new terminology today.. "SCD TYPE 2" and an amazing problem statement on how to handle end date in place of null. I was able to take care of that -1 in date but wasn't sure how to deal with if there is no change in bill date.. thank you once again for this super amazing concept.

[@tunguyenanh3071]

Really smart. I think a lot of ways to solve this problem but it doesn't work and you give an excellent solution amazing! Thank you for your channel. Hope you make more advanced SQL excercise

[@ankitbansal6]

Thank you 😊

[@AnandaHariharaShivamurthy]

Great videos! Here is my solution before watching complete video for solution with billing_dimension as ( select emp_name, bill_date as effective_start_date, lead(bill_date, 1, '9999-12-31') over(partition by emp_name order by bill_date) as effective_end_date, bill_rate from billings ) select h.emp_name, sum(bd.bill_rate * h.bill_hrs) as total_charges from HoursWorked h inner join billing_dimension bd on h.emp_name = bd.emp_name and h.work_date >= effective_start_date and h.work_date < effective_end_date group by h.emp_name;

[@murwathmehtar861]

Below is select query from postgresql, select a.emp_name, sum( a.bill_hrs * (select b.bill_rate from billings b where a.emp_name=b.emp_name and a.work_date > b.bill_date order by b.bill_date desc limit 1) ) as calc from HoursWorked a group by a.emp_name;

[@tushardeepsingh4976]

Ankit bhai can you please also explain various practical techniques for delta/incremental load.

[@ankitbansal6]

Sure. You mean Delete insert Upsert Etc Or you mean different scd types ?

[@RohitKumar-zm3nw]

With cte as ( Select b.emp_name,b.bill_date, lead(b.bill_date,1) Over(Partition by b.emp_name order by b.bill_date) AS bill_date2, b.bill_rate,h.work_date,h.bill_hrs from billings b JOIN HoursWorked h ON b.emp_name=h.emp_name and b.bill_datebill_date AND work_date>bill_date2 AND bill_date!=bill_date2 then NULL else work_date end AS work_date from cte), cte3 as ( Select emp_name,work_date,bill_hrs*bill_rate as Total from cte2 where work_date is not null) select emp_name,SUM(Total) from cte3 group by emp_name

[@saibhargav7610]

#Using lead & Between with cte as (select *,lead(bill_date,1,'9999-12-30') over(partition by emp_name order by bill_date) as lead_res from billings ) select cte.emp_name,sum(cte.bill_rate * hw.bill_hrs) as totalcharges from cte inner join hoursworked hw on cte.emp_name = hw.emp_name and hw.work_date between cte.bill_date and cte.lead_res group by cte.emp_name order by 2 desc

[@amrutaborse2163]

select enm, sum(case when work_date>= mn1 and work_date

[@khushisrivastava6519]

Hi Ankit, I have been following up your videos and find them quite insightful. Thank you for this wonderful work. I have one request to make. Please also explain the generic usage and functionality of these SQL analytical functions (like lead() in this video). This will help in understanding what other scenarios can these be used.

[@ankitbansal6]

I have created a separate video on lead lag. Please check that out.

[@RohitKumar-zm3nw]

@@ankitbansal6 With cte as ( Select b.emp_name,b.bill_date, lead(b.bill_date,1) Over(Partition by b.emp_name order by b.bill_date) AS bill_date2, b.bill_rate,h.work_date,h.bill_hrs from billings b JOIN HoursWorked h ON b.emp_name=h.emp_name and b.bill_datebill_date AND work_date>bill_date2 AND bill_date!=bill_date2 then NULL else work_date end AS work_date from cte), cte3 as ( Select emp_name,work_date,bill_hrs*bill_rate as Total from cte2 where work_date is not null) select emp_name,SUM(Total) from cte3 group by emp_name

[@shekharagarwal1004]

Thanks Ankit for your hard work. Really you made it enjoyable

[@omsingh06]

I follow all of your videos, it's really amazing. This all queries are helpful to crack SQL interview

[@ankitbansal6]

Glad to hear that

[@vinodpaluvuri54]

with cte as ( select hw.emp_name,work_date,bill_hrs,b.bill_date,b.bill_rate,row_number() over(partition by hw.emp_name,work_date,bill_hrs order by bill_date desc) as rn from HoursWorked hw left join billings b on hw.emp_name = b.emp_name where hw.work_date > b.bill_date) select emp_name,sum(bill_hrs*bill_rate) from cte where rn = 1 group by emp_name

[@ashutoshverma1418]

using union- with temp as( select *, count(*) over(partition by username) as total_act, rank() over(partition by username order by startDate desc) as rn from useractivity) select * from temp where total_act>1 and rn=2 union select * from temp where total_act=1 ;

[@vishnukumarreddykonatcsl9535]

Hi Ankit , SELECT LEAD(dateadd(day,-1,bill_date),1,DATE('28-SEP-1999')) OVER (partition by emp_name order by bill_date asc) as bill_date_end FROM billings; getting this error in Redshift ERROR: 42601: Default parameter not be supported for window function lead

[@ankitbansal6]

I think redshift does not support 3rd arg. you can modify query: SELECT nvl(LEAD(dateadd(day,-1,bill_date),1) OVER (partition by emp_name order by bill_date asc),DATE('28-SEP-1999')) as bill_date_end FROM billings;

[@vikhyatjalota2213]

with cte_1 as ( select *, lead(date_sub(bill_date, interval 1 day),1,'9999-01-30') over(partition by emp_name order by bill_date) as date_range from billings order by 1 ) select c.emp_name, sum(bill_rate * bill_hrs) as total_cost from cte_1 c join HoursWorked h on c.emp_name = h.emp_name and work_date between bill_date and date_range group by 1

[@RIKKY988]

microphone seems good now bro!

[@SudhirKumar-rl4wt]

Thanks for the question and solution.

[@Buzzingfact]

Thanks for making these videos

[@SuperMohit95]

Amazing question!!

[@Goku-br7yt]

Wow this approach is awsm!! 👍

[@ankitbansal6]

🙏

[@Chathur732]

with cte as ( SELECT *, LEAD(date_sub(bill_date,interval 1 day), 1, '9999-12-31') OVER (PARTITION BY emp_name ORDER BY bill_date) AS bill_date_end FROM billings) select cte.emp_name, sum(bill_rate*bill_hrs) as Total_amount from cte join hoursworked on cte.emp_name = hoursworked.emp_name and hoursworked.work_date between cte.bill_date and bill_date_end group by cte.emp_name

[@guptaashok121]

with bill AS (select emp_name,bill_rate,bill_date, lead(bill_date,1,'9999-12-31') over(partition by emp_name order by bill_date) as enddate from billings ) select a.emp_name,sum(bill_hrs*bill_rate) from HoursWorked a join bill b on a.emp_name=b.emp_name and a.work_date between b.bill_date and b.enddate group by a.emp_name

[@ankitbansal6]

Perfect. Thanks for posting 👏

[@mohit231]

Great video

[@arpanscreations6954]

My solution before watching the full video:: with cte as ( select emp_name, bill_date as bill_start, dateadd(day, -1, lead(bill_date, 1, GETDATE()) over(partition by emp_name order by bill_date)) as bill_end, bill_rate from billings ) select cte.emp_name, sum(cte.bill_rate * hw.bill_hrs) as total_charge from cte inner join HoursWorked hw on cte.emp_name = hw.emp_name where hw.work_date between cte.bill_start and cte.bill_end group by cte.emp_name

[@PriyanshuGupta-ec5fv]

Pls make a video implementing all SCD types

[@ankitbansal6]

Yes planning for it.

[@sreejitchakraborty6575]

My Sol: with cte as( select emp_name,bill_date as start_date,'01-JAN-9999' as end_date,bill_rate from billings where emp_name in ( select emp_name from billings b group by emp_name having count(*)=1 ) ) ,cte2 as( select * from billings where emp_name in ( select emp_name from billings b group by emp_name having count(*)>1 ) ),c as( select c1.emp_name,c1.bill_date as start_date,coalesce(lead(bill_date) over(partition by emp_name order by bill_date),'01-JAN-9999') end_date,c1.bill_rate from cte2 c1 ),final as( select * from c union all select * from cte ) select emp_name,sum(r) from ( select hw.emp_name,bill_hrs*bill_rate r from final f inner join hoursworked hw on(f.emp_name=hw.emp_name) and(hw.work_date>f.start_date) and(hw.work_date

[@anirvansen5024]

MY SQL Solution, only trick was to get the SCD type 2 table for billings tables with base as (select emp_name,bill_date as start_date, coalesce(date_sub(lead(bill_date) over(partition by emp_name order by bill_date),interval 1 day),'9999-12-31') as end_date, bill_rate from billings order by emp_name ) select t1.emp_name, sum(t1.bill_hrs * t2.bill_rate) as totalcharges from HoursWorked t1 join base t2 on t1.emp_name = t2.emp_name and t1.work_date between t2.start_date and t2.end_date group by t1.emp_name

[@sidharthadaggubati438]

This is exactly what’s shown in the video

[@Mysingh9767]

Thanks...

[@LearnYouAndMe]

below is my solution with billRateDateRng as ( select t.emp_name,t.bill_date as FromDate, t.bill_rate, isnull(LEAD(t.bill_date) over(partition by t.emp_name order by t.bill_date),cast(GETDATE() as date)) as ToDate from billings as t ) select t.emp_name, sum(t.bill_hrs*jt1.bill_Rate) as Amount from HoursWorked as t left join billRateDateRng as jt1 on jt1.emp_name=t.emp_name and (t.work_date>=jt1.FromDate and t.work_date

[@vivekupadhyay6663]

Hi Sir, Is the below solution acceptable? WITH CTE AS (SELECT emp_name, bill_date AS bill_start_date, MAX(bill_date) OVER(PARTITION BY emp_name ORDER BY emp_name) AS bill_end_date, bill_rate FROM billings) SELECT A.emp_name, SUM(A.bill_rate*B.bill_hrs) AS Total_Charges FROM CTE AS A INNER JOIN HoursWorked AS B ON A.emp_name=B.emp_name WHERE (B.work_date>=A.bill_start_date AND B.work_date=A.bill_start_date AND A.bill_start_date=A.bill_end_date) GROUP BY A.emp_name Thanks!

[@shekharagarwal1004]

Hello Vivek - Max function will give same value if there is one more entry for Sachin in billing table and will normalize the records producing wrong output . Please check below emp_name bill_date bill_rate Sachin 1990-01-01 25 Sehwag 1989-01-01 15 Dhoni 1989-01-01 20 Sachin 1991-02-05 30 Sachin 1991-08-01 50---------> This is new entry =============== emp_name work_date bill_hrs Sachin 1990-07-01 3 Sachin 1990-08-01 5 Sehwag 1990-07-01 2 Sachin 1991-07-01 4 Sachin 1991-08-05 10------> This is new entry

[@vivekupadhyay6663]

@@shekharagarwal1004 Thanks for pointing out my mistake. I understand my mistake. Will do better next time. Thanks again!

[@florincopaci6821]

THANK YOU!!!!

[@RohitKumar-zm3nw]

aisa koi Question nahi bana jo mein nahi solve kar sak With cte as ( Select b.emp_name,b.bill_date, lead(b.bill_date,1) Over(Partition by b.emp_name order by b.bill_date) AS bill_date2, b.bill_rate,h.work_date,h.bill_hrs from billings b JOIN HoursWorked h ON b.emp_name=h.emp_name and b.bill_datebill_date AND work_date>bill_date2 AND bill_date!=bill_date2 then NULL else work_date end AS work_date from cte), cte3 as ( Select emp_name,work_date,bill_hrs*bill_rate as Total from cte2 where work_date is not null) select emp_name,SUM(Total) from cte3 group by emp_name

[@uhajasthi9680]

with cte as( select username,activity,startdate,enddate,rank()over(partition by username order by startdate)as rn from UserActivity ) select username,activity,startdate,enddate from cte where rn=2 union select username,activity,startdate,enddate from UserActivity where username not in(select username from cte where rn=2)

[@ayushgupta-gz1zr]

with cte1 as(select * , lead(bill_date,1,'9999-12-12') over(partition by emp_name order by bill_date)-INTERVAL '1 day' as end_date from billings ), cte2 as( select cte1.bill_date,cte1.bill_rate,cte1.end_date, h.* from cte1 join HoursWorked h on h.work_date between cte1.bill_date and cte1.end_date and h.emp_name=cte1.emp_name ), cte3 as( select emp_name, sum(bill_rate*bill_hrs) from cte2 group by emp_name ) select * from cte3;

[@apurvasaraf5828]

with cte as (select emp_name,bill_date,bill_rate,lead(bill_date,1,'2000-9-9') over(partition by emp_name order by bill_date) as l from billings) select h.emp_name,sum((h.bill_hrs)*(c.bill_rate)) as s from HoursWorked h join cte c on work_date between bill_date and l and h.emp_name=c.emp_name group by h.emp_name

[@saimahendrapatnaik2847]

with cte as ( SELECT *,coalesce(lead(bill_date)over (partition by emp_name order by bill_date),"9999-12-01") as new_date from billings), cte1 as (select A.Player,(A.bill_rate * A.bill_hrs) as total_amount from (select c.emp_name as Player,c.bill_rate,h.bill_hrs from cte c join hoursworked h on h.emp_name=c.emp_name and h.work_date between c.bill_date and c.new_date)A) select Player,sum(total_amount) as total_anount from cte1 group by Player;

[@subhojitchatterjee6312]

MYSQL script for table creation: create table billings ( emp_name varchar(10), bill_date date, bill_rate int ); insert into billings values ('Sachin',STR_TO_DATE('01-01-1990','%d-%m-%Y'),25) ,('Sehwag' ,STR_TO_DATE('01-01-1989','%d-%m-%Y'), 15) ,('Dhoni' ,STR_TO_DATE('01-01-1989','%d-%m-%Y'), 20) ,('Sachin' ,STR_TO_DATE('05-01-1991','%d-%m-%Y'), 30) ; create table HoursWorked ( emp_name varchar(20), work_date date, bill_hrs int ); insert into HoursWorked values ('Sachin', STR_TO_DATE('01-06-1990','%d-%m-%Y'),3) ,('Sachin', STR_TO_DATE('01-08-1990','%d-%m-%Y'), 5) ,('Sehwag',STR_TO_DATE('01-07-1990','%d-%m-%Y'), 2) ,('Sachin',STR_TO_DATE('01-07-1991','%d-%m-%Y'), 4); A different take : WITH CTE AS (SELECT B1.EMP_NAME, B1.BILL_DATE,B1.BILL_RATE,HW.WORK_DATE,HW.BILL_HRS FROM BILLINGS B1 JOIN HoursWorked HW ON B1.EMP_NAME=HW.EMP_NAME), CTE_2 AS (SELECT *,LEAD(BILL_RATE,1,BILL_RATE) OVER(PARTITION BY EMP_NAME ORDER BY BILL_DATE) AS REVISED_RATE FROM CTE WHERE WORK_DATE>=BILL_DATE) SELECT EMP_NAME, SUM(BILL_HRS*REVISED_RATE) AS TOTAL_CHARGES FROM CTE_2 WHERE BILL_RATE=REVISED_RATE GROUP BY 1; This works for the given test case, can anyone tell me if this query will work fine for the other test cases as well??

[@exanode]

My Oracle Solution : WITH emp_rates AS ( SELECT EMP_NAME, BILL_DATE, NVL(LEAD(BILL_DATE - 1) OVER (PARTITION BY EMP_NAME ORDER BY BILL_DATE), SYSDATE) AS end_date, BILL_RATE FROM billings ) SELECT e.emp_name, SUM(bill_rate * bill_hrs) AS Total FROM emp_rates e JOIN hoursworked h ON e.emp_name = h.emp_name AND h.work_date BETWEEN e.bill_date AND e.end_date GROUP BY e.emp_name;

[@sirimaddala8661]

WITH CTE AS( SELECT HoursWorked.emp_name, work_date, bill_hrs, bill_date, bill_rate FROM HoursWorked LEFT JOIN billings ON HoursWorked.emp_name=billings.emp_name AND HoursWorked.work_date>=billings.bill_date), CTE2 AS (SELECT *, RANK() OVER(PARTITION BY emp_name, work_date ORDER BY bill_date DESC) AS R FROM CTE) SELECT emp_name,SUM(bill_hrs*bill_rate) FROM CTE2 WHERE R=1 GROUP BY emp_name