SQL Interview Questions and Answers Series | HackerRank | SQL Projects Planning ( MEDIUM )

[@pranavkhatri30]

Great solution, I have added one thing in MySQL to order by datediff of end_date and start_date and then if the difference is same then start_date

[@rohit_vora]

in postgresql: with cte as (select * ,(start_date - lag(start_date,1, start_date-1) over(order by task_id)) flag from project), flags as (select * ,(sum(flag) over(order by task_id) - task_id) grp from cte), final as (select distinct min(start_date) over(partition by grp ) start_date, max(end_date) over(partition by grp ) end_date, (max(end_date) over(partition by grp ) -min(start_date) over(partition by grp ) ) date_diff from flags) select start_date, end_date from final order by date_diff

[@AbhishekPanwar-j9j]

Just a suggestion, add your final query in the description so that we can copy from there

[@iqraanwar3656]

practice it, ab itna to ham log kr hi skte wo itni mhnt se videos bna rhi smjha rhi taky hm bhuddiman bne

[@simranbhamra2163]

Really appreciate your efforts, way of explanation and approach-10/10. 🤌🤌

[@badrilalnagar9232]

बड़प्पन अमीरी में नहीं, सज्जनता और शालीनता में सन्निहित है।

[@omkarghodake2834]

MYSQL solution to the same question , with cte1 as ( select start_date, row_number()over(order by start_date) start_date_rank from projects where start_date not in (select end_date from projects ) ), cte2 as ( select end_date, row_number()over(order by end_date) end_date_rank from projects where end_date not in (select start_date from projects ) ) select start_date, end_date from ( select cte1.start_date, cte2.end_date, datediff(end_date,start_date) as date_diff from cte1, cte2 where start_date_rank = end_date_rank order by date_diff , start_date ) as c

[@arpitsaini084csc9]

you wrote this query in mysql server instead of mysql what is the difference in these and i wrote the same query in mysql but this showed a error in datediff() function can you explain plz.

[@Data_learn]

it is ms sql which is product of microsoft

[@mononahmed8125]

please explain the 26th line

[@AVINASH-p3b]

You did Mtech in Upgrad?

[@datasciencewithnish]

@@AVINASH-p3b No

[@AVINASH-p3b]

@@datasciencewithnish masters?

[@HARSHRAJ-gp6ve]

WITH cte AS ( SELECT Start_Date, End_Date, DAY(End_Date) AS end_date1, ROW_NUMBER() OVER (ORDER BY Start_Date) AS x1 FROM tasks ),cte1 as( select Start_Date,End_Date,(end_date1-x1) as x2 FROM cte ),cte2 as( select x2,MIN(Start_Date) as min1,MAX(End_Date) as max1 FROM cte1 GROUP BY x2 ) select min1,max1 FROM cte2 ORDER BY DATEDIFF(max1,min1),min1;

[@eu_dz8684]

Could you send me a link to this challenge

[@datasciencewithnish]

The link is attached in the description box. You can access it from there. Thanks :)

[@puneetdhir2009]

WITH start_date as ( SELECT start_date , row_number() over(order by start_date) as start_date_rank FROM projects WHERE start_date NOT IN (SELECT end_date FROM projects ) ), end_date as ( SELECT end_date , row_number() over(order by end_date) as end_date_rank FROM projects WHERE end_date NOT IN (SELECT start_date FROM projects) ) SELECT s.start_date , e.end_date FROM start_date s INNER JOIN end_date e ON s.start_date_rank = e.end_date_rank ORDER BY datediff(day, s.start_date, e.end_date), s.start_date