SQL Interview — 5

Learning Zone

1 min readMar 5, 2019

HackerRank Questions

Projects | HackerRank

Write a query to output the start and end dates of projects listed by the number of days it took to complete the…

www.hackerrank.com

SQL Örnekleri → http://ayanangshudas.xyz/SQL/

CTE ( Common Table Expression) nedir?

Get Learning Zone’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

http://www.ahmetkaymaz.com/2010/01/18/sql-server-onceki-satirlarin-toplami/ → oku.

declare @Projects Table (Task_Id Int, Start_Date DateTime, End_Date DateTime)--Same Data for the probleminsert into @Projects Values (1,'2015-10-01','2015-10-02'), (2,'2015-10-02','2015-10-03'), (3,'2015-10-03','2015-10-04'), (4,'2015-10-13','2015-10-14'), (5,'2015-10-14','2015-10-15'), (6,'2015-10-28','2015-10-29'), (7,'2015-10-30','2015-10-31')
;With ProjectsCte (Start_Date, End_Date)
As(
select Start_Date, End_Date from @Projects where Start_Date not in (Select End_Date from @Projects)
union All
select pc.Start_Date, p.End_Date from @Projects p inner join ProjectsCte pc on p.Start_Date = pc.End_Date
)select Start_Date, Max(End_Date) as End_Date from ProjectsCte group by Start_Date
order by Count(End_Date), Start_Date