Amazon Data Engineer SQL Interview Problem | Leetcode Hard SQL 2494 | Recursive CTE

[@deepakkumargupta5698]

my approch with cte as( select *, lag(end_date,1,start_date) over(partition by hall_id order by start_date) as prev_end_date, case when lag(end_date,1,start_date) over(partition by hall_id order by start_date) >= start_date then 0 else 1 end as flag from hall_events ) select hall_id,min(start_date),max(end_date) from cte group by hall_id,flag

[@theraizadatalks14]

I used the similar approach for solving this question!

[@aaravkumarsingh4018]

My approach:- with cte as( select hall_id,start_date,end_date,lag(end_date) over(partition by hall_id order by start_date) as prev_end_date from hall_events ) select hall_id,min(start_date) as start_date,max(end_date) as end_date from cte where prev_end_date is null or start_date < prev_end_date group by hall_id union select hall_id,start_date,end_date from cte where start_date>prev_end_date order by hall_id,start_date;

[@PranayDutta18]

I found this approach is very simple. Thank you

[@neerajnaik5161]

It holds good for above sample of data. But insert 1 additional row as below and your solution will fail insert into hall_events values (1,'2023-01-20','2023-01-26')

[@Datapassenger_prashant]

@@neerajnaik5161 right..

[@florincopaci6821]

Thank you for another usefull video. Starting from your solution i just replace the recursion with a case statement but my case here is the death of performace: ;with flo as (select a.*, row_number()over(order by start_date, hall_id) as rownum from hall_events a), flo1 as (select a.* , sum(case when exists(select * from flo where hall_id=a.hall_id and (a.start_date between start_date and end_date or start_date between a.start_date and a.end_date) and rownum

[@Quicktimesgaming]

you are master in recursive CTE quey... Awesome... continue to enlighten us in SQL.

[@ankitbansal6]

Thank you, I will

[@AnandKumar-dc2bf]

Was a bit confusing but understood it After watching in 2 or 3 times..

[@Tusharchitrakar]

This is how I did it in mysql and i think it is similar to your solution: (basic idea is to create a new group if the the current start date in the iteration is lesser than or equal to the previous end_date) (I used a view to create an initial starting point containing row_numbers because in mysql I cannot create a cte before a recursive cte that can hold the same information) create view starting_point as ( select *, row_number() over(partition by hall_id order by start_date) as rn from hall_events); with recursive cte as ( select *, rn as new_rn from starting_point where rn=1 UNION ALL select t1.hall_id, t1.start_date, t1.end_date, t1.rn, if(datediff(t1.start_date,t2.end_date)

[@Datapassenger_prashant]

awesome awesome problem statement,, I wanted to learn more about cte and its usages and this one is on top of the list... lot to learn from this... its true that we get to work on recursive cte a lot less in comparison to other cases.. but this one will add extra bonus on study which went around learning recursive cte.. as soon as you run r_cte.. and that group was created it made all sense... why recursive is used and why not case statement... as I tried with case statement first.. but lacked..and failed with solution..

[@andreih1167]

Thanks for your videos, Ankit. I think you are doing a great job educating people. However I wanted to notice that solution suggested in this video is extremely slow and not scalable. It's probably ok to use recursive CTEs on small datasets that you are sure will not grow in future or for educational purposes, otherwise, if you care about scalability, recursive CTEs should be avoided. I have done a little bit of performance testing of different solutions for this problem. I used local postgresql 15.1 instance, generated 5 million rows for this table (1000 ids X 5000 random intervals), no indexes. Even for that modest size of the table the recursive version suggested in the video is taking forever (waited for 25 minutes for query completion and then cancelled). For comparison, a more efficient solution which uses a couple of CTEs and window aggregation functions took just ~8 seconds.

[@ankitbansal6]

Thank you for putting efforts into measuring the performance. I agree for large data this query will be slow and a solution using window functions will perform far better. My intention was to use recursive cte because there are not many questions on the channel using the concept. So I just wanted to show some application of it. Going forward I will make sure to show another approach so that people don't apply recursive cte just for sake of applying.

[@andreih1167]

@@ankitbansal6 Makes sense, thanks for your reply. Just a suggestion - when appropriate, maybe consider mentioning performance related considerations, for example when teaching recursive queries say that there is a serious performance penalty associated with those; when talking about self joins say that it usually implies roughly quadratic time complexity as compared to linear time complexity when using window functions instead, etc.

[@harishkumar4663]

@@andreih1167 What was your approach that doesn't compromise the performance? Could you share it ?

[@andreih1167]

@@harishkumar4663 this is quite a known sql problem called packing intervals. I recommend a book by Itzik Ben-Gan called "T-SQL window functions for data analysis and beyond", it has this and many other interesting sql problems. For my testing the solution using window function from that book was the quickest. WITH C1 AS ( SELECT *, CASE WHEN start_date

[@omkargurme20]

​@@ankitbansal6 do make a video of solving the same using windows functions

[@ankitbansal6]

Please hit the like button to increase the reach of the video 🙏

[@sravan1802]

Could you share the video where you are referring to getting started with recursive SQL?

[@kumarashirwadmishra7414]

Wonderful solution sir!

[@sahilummat8555]

Hello Ankit Sir Another solution from my side. with cte as ( select *, lag(start_date,1,start_date)over(partition by hall_id order by start_date) as prev_start_date ,lag(end_date,1,end_date)over(partition by hall_id order by start_date) as prev_end_date, count(1)over(partition by hall_id) as cnt from hall_events) ,overlapping as ( select hall_id,MIN(start_date) as start_date,max(end_date)as end_date from cte where start_date between prev_start_date and prev_end_date and cnt>1 group by hall_id) ,not_overlapping as ( select hall_id,MIN(start_date) as start_date,max(end_date) as end_date from cte where start_date not between prev_start_date and prev_end_date or cnt=1 group by hall_id) select * from overlapping union all select * from not_overlapping order by hall_id

[@kuldeepgupta8526]

Hi Ankit, Appreciate your effort, i tried this with below approach select hall_id, min(start_date), max(end_date) from ( select hall_id,start_date,end_date, prior_start_date,prior_end_date,following_start_date,following_end_date , case when start_date between PRIOR_START_DATE and PRIOR_END_DATE then 1 when start_date between FOLLoWING_START_DATE and FOLLoWING_END_DATE then 1 when start_date between PRIOR_START_DATE and FOLLoWING_END_DATE then 1 when end_date between PRIOR_START_DATE and PRIOR_END_DATE then 1 when end_date between FOLLOWING_START_DATE and FOLLOWING_END_DATE then 1 when end_date between PRIOR_START_DATE and FOLLOWING_END_DATE then 1 when PRIOR_START_DATE between START_DATE and END_DATE then 1 when PRIOR_END_DATE between START_DATE and END_DATE then 1 when FOLLOWING_START_DATE between START_DATE and END_DATE then 1 when FOLLOWING_END_DATE between START_DATE and END_DATE then 1 else 0 end overlap_flag from ( select hall_id, start_date, end_date, lag(start_date) over( partition by hall_id order by start_date) prior_start_date , lag(end_date) over( partition by hall_id order by end_date) prior_end_date , lead(start_date) over( partition by hall_id order by start_date) following_start_date , lead(end_date) over( partition by hall_id order by end_date) following_end_date from hall_events ) ) group by hall_id, overlap_flag

[@Esinha12]

Pretty long but here's my solution 1. Get next start date 2. Get last end date 3. Check if next start date is equal or less than end date 4. Check if last end date is more or equal to end date 5. If either 3 or 5 is true then group then ________________________________________________________________ WITH cte1 AS ( SELECT a.* ,LAG(end_date,1,'01-01-2000') OVER(PARTITION BY hall_id ORDER BY start_date ASC) AS end_prev ,LEAD(start_date,1,'01-01-2099') OVER(PARTITION BY hall_id ORDER BY start_date ASC) AS next_start FROM hall_events AS a ) ,cte2 AS ( SELECT a.* ,CASE WHEN end_prev>=end_date THEN 1 ELSE 0 END AS flag_1, ,CASE WHEN next_start

[@Manish-lk2oq]

minor correction below in cte2 for flag1 - end_date should be replaced with start_date CASE WHEN end_prev>=end_date THEN 1 ELSE 0 END AS flag_1 let me know what yor say?

[@Thehackerpro1]

is there any problem in my approach ?🤔 with cte as( select *, case when lag(end_date,1,start_date) over(partition by hall_id order by start_date) >= start_date then 0 else 1 end as flag from hall_events ) select hall_id,min(start_date),max(end_date) from cte group by hall_id,flag;

[@vipvedprakash89]

Hei Ankit I would like to say big thank you for your SQL practical questions just because of that I cracked Infosys interviews. Thanks once again,🙏

[@ankitbansal6]

Awesome 🎉🎉 Congratulations 👏

[@nipunshetty9640]

Your just Awsum in your teaching 🙌🙌🙌 and this Problem statement is great to understand bit confusing too

[@ankitbansal6]

Thank you! 😃

[@aadarshchoudhary9648]

Thank you so much sir, I got the job with the help of your videos. Thanks again 🙏

[@ankitbansal6]

Great job 🎉

[@shahakunal1]

Hi Ankit , select hall_id,min(start_date),max(end_date) from (select *,case when prev_end_date is null or start_date

[@arpanscreations6954]

My approach: with cte as ( select * ,lag(end_date,1) over(partition by hall_id order by start_date) as prev_end_date, case when start_date

[@booktuber4875]

Great Concept Sir Thanks very much

[@shivambansal3560]

Hi Ankit, that's really appreciable. Thank you 🙏for the great content. Please also mention the performance comparison with different approaches.

[@ankitbansal6]

Sure 👍

[@vinaykumar_DE]

This is a very good question.

[@ching-tsungderontsai2750]

Correct me if I'm wrong, but I believe there is a bug for this solution. If a new row doesn’t overlap directly with the previous row but overlaps with earlier rows in the same "flag" group, the, the case when condition will still add 1 and results in incorrect grouping. For example, | hall_id | start_day | end_day | | 2 | 2022-12-08 | 2023-01-26 | | 2 | 2022-12-13 | 2023-01-04 | | 2 | 2023-01-20 | 2023-01-22 | -> The flag in this case will be (1, 1, 2), but they should all have the same flag.

[@redwannabil8031]

with cte as( select *,case when lead(start_date,1) over (partition by hall_id order by start_date)

[@rubyshorts281]

my simple solution with cte as ( select *, case when start_date > lag_end_dt then 1 else 0 end as flg from (select *, lag(end_date,1, start_date) over (partition by hall_id ) lag_end_dt from hall_events he)t ), cte2 as ( select * , sum(flg) over(partition by hall_id rows between unbounded preceding and current row) grp from cte ) select hall_id , grp, min(start_date) as start_dt , max(end_date) as end_dt from cte2 group by hall_id , grp

[@sagarvajsharwa]

You just nailed it bro 👌👌👌

[@ankitbansal6]

Thank you 😊

[@sankhadeeppramanik1226]

with cte as ( select hall_id, start_date, end_date,lead(start_date) over(partition by hall_id order by start_date) as next_start_date, lag(end_date) over(partition by hall_id order by start_date) as prev_end_date, case when lead(start_date) over (partition by hall_id order by start_date) =start_date then 1 else 0 end as flag from cte ) select hall_id, min(start_date) as start_date, max(end_date) as end_date from flag_cte where flag=1 group by hall_id, flag union select hall_id,start_date,end_date from flag_cte where flag=0 order by hall_id, start_date;

[@avi8016]

Logic right on point 💯

[@naveennaravala1707]

Hi Ankit, Thanks for the video. When are you planning the next SQL training batch?

[@ankitbansal6]

May/june

[@shubhamagrawal7068]

Hi Ankit, SKI Assignment video is hidden. Kindly make it unhidden. I want to watch it again. Thank you

[@anshagarwal_7]

Hi Ankit, As per code above, the row 3(below data sample) startdate will be compared with enddate of row 2 only, which will show it as a separate non-overlapping interval, although all the 3 intervals are overlapping hall id startDate endate 1 2023-01-01 2023-01-31 1 2023-01-05 2023-01-10 1 2023-01-12 2023-01-18 Thank you for the awesome question series🙏

[@redwannabil8031]

In this case third row is a separate interval...why would you consider previous enddate to overlap?

[@mz2888]

the dates in the second and third row lie between 2023-01-01 and 2023-01-31 which is the first row . He has got a good question.@@redwannabil8031

[@ashutoshgoyal3383]

with cte as(select * ,lag(end_date,1,end_date)over(partition by hall_id order by end_date) as le from hall_events),cte2 as( select *,case when le between start_date and end_date then 1 else 0 end as flag from cte),cte3 as( select *,sum(flag) over (partition by hall_id order by end_date rows between unbounded preceding and current row)-row_number() over (partition by hall_id order by end_date) dis from cte2) select hall_id,min(start_date),max(end_date) from cte3 group by hall_id,dis order by hall_id,min(start_date)

[@sahilummat8555]

Hi Ankit What is the difference in seeing your videos and joining the course . Like what additional advantage will we get ? BTW i love your content .

[@xpro4073]

With cte as( Select *, Case when lag(end_date,1,start_date) over(partition by hall_id order by start_date) >= start_date Then 0 else 1 end as flag From hall_events) Select hall_id,min(start_date),max(end_date) From cte Group by hall_id,flag Order by hall_id

[@yashc2141]

Hello @Ankit, How about this? with cte as( select t1.hall_id ,t1.start_date ,t1.end_date ,case when t1.start_date>lag(t1.end_date) over(partition by t1.hall_id order by t1.start_date) then 1 else 0 end cs from hall_events t1 left join hall_events t2 on t1.hall_id=t2.hall_id and t2.start_date between t1.start_date and t1.end_date ) select t1.hall_id ,min(t1.start_date) start_date ,max(t1.end_date) end_date from cte t1 where cs1 group by t1.hall_id union all select hall_id ,start_date ,end_date from cte where cs=1 order by 1 asc ;

[@bittusingh3729]

Hi Ankit , in your SQL course have you covered data modeling , some advance topic like tuning

[@ankitbansal6]

Tuning is covered. Not much about data modelling..

[@awaise92]

Hi Ankit, Is there any way to do it without recursive CTE? like some combination of lead or lag functions.. This solution is great, however seems little complicated to understand. Thanks

[@sai_sh]

with cte as( select *, coalesce(lag(end_date) over(partition by hall_id order by start_date asc),end_date) as rnk from hall_event ), cte1 as( select *, coalesce(case when rnk >= start_date then 1 else 0 end,1) as compare from cte ) select hall_id,min(start_date) , max(end_date) from cte1 group by hall_id,compare;

[@RohitKumar-l7t3g]

My Approach : with cte as (select * , lag(end_date) over(partition by hall_id order by start_date) as lag_date, case when start_date

[@gautambhandare8164]

simple solution - with cte as (select *, lead(start_date,1) over(partition by hall_id order by start_date) as lg from hall_events order by hall_id), cte1 as (select * , case when lg between start_date and end_date then 1 end as dat_cal from cte) select hall_id, start_date, end_date from cte1 where dat_cal is null

[@shubhammishra8686]

Thanks for this... I have one scenario...how to do a fuzzy match in SQL considering that fuzzy to be applied on one column only and how to get the similarity score

[@manishmr2116]

Simple solution WITHOUT RECURSSIVE CTE 🔥🔥🔥🔥🔥 with hall_cte as( select *,row_number() over(partition by hall_id order by end_date) as rn from hall_events), final_hall_cte as( select a.hall_id,a.start_date,case when a.end_date between b.start_date and b.end_date then b.end_date else a.end_date end as new_end_date from hall_cte a left join hall_cte b on a.hall_id=b.hall_id and a.rn+1=b.rn) select hall_id,min(start_date) as start_date,new_end_date as end_date from final_hall_cte group by hall_id,new_end_date

[@gazart8557]

COuld you also start a series on python series that is necessary for data analyst job, like for scripting and automation

[@dineshpanda5804]

Can you please make a video on advanced topics in SQL like PIVOT, Indexing, etc. Also , will this be included in your course?

[@ankitbansal6]

Yes it's covered

[@javedabdool1187]

with cte as ( select * ,case when (start_date = lead(start_date)over(order by hall_id,start_date )) or (start_date = lead(end_date)over(order by hall_id,start_date )) or (end_date = lead(start_date)over(order by hall_id,start_date )) or (end_date = lead(end_date)over(order by hall_id,start_date )) or (start_date = lag(start_date)over(order by hall_id,start_date )) or (start_date = lag(end_date)over(order by hall_id,start_date )) or (end_date = lag(start_date)over(order by hall_id,start_date )) or (end_date = lag(end_date)over(order by hall_id,start_date )) then 1 else 0 end as x from hall_events ) select hall_id,min(start_date)start_date,max(end_date)end_date from cte group by hall_id,x order by 1,2

[@skkholiya]

with cte_dates as( select *,lag(start_date) over(partition by hall_id order by start_date) pre_start_date, lag(end_date) over(partition by hall_id order by start_date) pre_end_date from hall_events ), is_overlapping as( select *, case when start_date between pre_start_date and pre_end_date then 1 when end_date between pre_start_date and pre_end_date then 1 when pre_end_date is null and pre_start_date is null then 1 else 0 end is_overlap from cte_dates) select hall_id, min(start_date) start_date,max(end_date) end_date from is_overlapping group by hall_id,is_overlap;

[@rishabhshar]

This query doesnt work in mysql database. i have tried to add 'recursive' in from of r_cte, still doesnt work. Can anyone explain

[@100sabh]

Solution with simple inner joins with CTE as ( select a.hall_id, a.START_DATE , a.END_DATE, case when (a.START_DATE between b.START_DATE and b.END_DATE) or (b.START_DATE between a.START_DATE and a.END_DATE) then 1 else 0 end as flg from hall_events a left outer join hall_events b on a.HALL_ID = b.HALL_ID and a.START_DATE b.START_DATE --and a.END_DATE b.END_DATE order by 1,2 ), cte2 as (select hall_id, START_DATE , END_DATE , Max(flg) flg from cte group by hall_id, START_DATE , END_DATE ) select hall_id , min(START_DATE) START_DATE , Max(END_DATE) END_DATE from cte2 group by hall_id , flg;

[@mattmatt245]

How would you write a query to list all the pairs of overlapping records and then calculate an actual overlap for each pair ?

[@fog2640]

finished watching

[@gamingwithlegend3183]

Ankit sir mujhe ek laptop purchase karna hai for sql and python kya aap mujhe koi laptop recommend kar sakte ho with configuration and second thing SQL or python pe kaam karne ke liye ram kitni chahiye.

[@ankitbansal6]

Koi theek thaak le lo laptop. You don't need very high configuration for SQL and python. My laptop has 8gb RAM , i5 and I don't face any issues.

[@gamingwithlegend3183]

@@ankitbansal6 thanks sir

[@siddharthsinghh]

In this problem why we are searching in reverse order also i mean its already if sorted by first cte then we only need need to look for next row

[@sonupatel-rc8ms]

select a.hall_id, min(a.start_date), max(a.end_date) from ( select *, case when lead(start_date) over(partition by hall_id order by start_date) between start_date and end_date then 1 when start_date between lag(start_date) over(partition by hall_id order by start_date) and lag(end_date) over(partition by hall_id order by start_date) then 1 else 0 end as flag from hall_events) a group by a.hall_id, a.flag order by a.hall_id

[@MovieBuzz-uu8kp]

select hall_id,min(start_date) as start_date,max(end_date) as end_date from ( select *, case when end_date >lg and start_date

[@anirvansen2941]

MYSQL solution with base as ( select *,coalesce (lag(end_date) over(partition by hall_id order by end_date asc) ,end_date)as prev_time from hall_events ), base_rank as ( select *, case when prev_time between start_date and end_date then 1 else 0 end as flag from base order by hall_id,end_date asc ) select hall_id,min(start_date) as start_date,max(end_date) as end_date from base_rank group by hall_id,flag

[@Nick-du9ss]

Which type of SQL questions asked in interview for freshers could you suggest some examples

[@xpro4073]

With cte as ( SELECT *, CASE WHEN start_date = LEAD(start_date) OVER (PARTITION BY hall_id ORDER BY start_date) THEN 0 ELSE 1 END AS rn FROM hall_events) Select hall_id, min(start_date), max(end_date) From cte Group by hall_id, rn Order by hall_id

[@mradulgupta]

select hall_id,start_date,end_date from (select hall_id,flag,min(start_date) as start_date,max(end_date) as end_date from (select *,case when start_date>lag_date then 1 else 0 end as flag from (select *,lag(end_date) over (partition by hall_id order by start_date) lag_date from hall_events) a) b group by hall_id,flag)

[@shankrukulkarni3234]

thank you bro 🥰🥰🥰🥰

[@ankitbansal6]

Welcome 😊

[@nevergiveup3475]

Bro function and procedure bhi btao yar kisi video mein

[@narender438]

@Ankit Bansal Plz Consider What If the For Hall ID = 1 Data Like 1.Row Like => 11 to 25 2. Row Like=> 13 to 17 3. Row Like => 19 to 21 In that Case the above VD Solution will Fails or not?

[@ankitbansal6]

It will work

[@aniketghodinde3041]

Is this correct solution without recursive cte select hall_id,min(start_date) as start_date,max(end_date) as end_date from ( SELECT *,CASE WHEN overlapp_flag = 1 THEN 1 WHEN overlapp1 = 1 THEN 1 ELSE 0 END AS final_overlapp from ( SELECT *,lag(overlapp_flag) over (Partition by hall_id order by start_date) as overlapp1 from ( Select *,CASE WHEN lead_start between start_date AND end_date THEN 1 ELSE 0 END as overlapp_flag from ( select *,lead(start_date) over (Partition by hall_id order by start_date) as lead_start from hall_events ) j ) p ) k ) h group by hall_id,final_overlapp order by hall_id,start_date

[@amalkumar5201]

Mujhhe please..................🙏🙏 Aapse SQL ka session lena ya nahi lete h to please sir start session please make in Hindi

[@sravan1802]

What if the rows have partial overlap.. i mean one row say has start date as 11th and end date as 15th and second row is 12th to 17th and third row is 19th to 25th. This use case may break the logic. And also when you have already sorted the data while generating the event id i dont think the OR condition was necessary, it appears to be redundant. Share your thoughts on the above example case how you may solve this

[@ankitbansal6]

Yes or condition is not required. For overlap case this solution will work.

[@narender438]

@@ankitbansal6 What If the For Hall_ID = 1 Data Like 1.Row Like => 11 to 25 2. Row Like=> 13 to 17 3. Row Like => 19 to 21 I think that Case the above Solution will Fail

[@namratakandalkar]

create table #hall_events ( hall_id integer, start_date date, end_date date ); delete from #hall_events insert into #hall_events values (1,'2023-01-13','2023-01-14') ,(1,'2023-01-14','2023-01-17') ,(1,'2023-01-15','2023-01-17') ,(1,'2023-01-18','2023-01-25') ,(2,'2022-12-09','2022-12-23') ,(2,'2022-12-13','2022-12-17') ,(3,'2022-12-01','2023-01-30'); ;with cte as ( select *,case when isnull(rnk,end_date) between start_date and end_date then 1 else 0 end as overlapping from ( select *,LAG(end_date,1) over (partition by hall_id order by end_date) as rnk from #hall_events )a ) select hall_id, MIN(start_date) as start_date , max(end_date) as end_date from cte where overlapping=1 group by hall_id union select hall_id,start_date,end_date from cte where overlapping=0 drop table #hall_events

[@gamingwithlegend3183]

Sir I have a query regarding procedure can u plz solve that query.

[@ankitbansal6]

Send problem on [e-mail]

[@prashantmhatre9225]

Video is good , leant a lot of you video and every new video we consider a new challahs for us. But I always disappointed when you revealed how you are going to solve this video. I would like if you first explain the problem and then tell the way you are going to solve it.thanks

[@abinayagobinath3898]

Hi ankit i am using posgres sql with cte as( select hall_id,start_date,end_date, row_number()over(order by hall_id,start_date) as event_id from (with r_cte as (select hall_id,start_date,end_date,event_id, 1 as flag from cte where event_id=1 union all select cte.hall_id,cte.start_date,cte.end_date,cte.event_id, case when cte.hall_id=r_cte.hall_id and (cte.start_date between r_cte.start_date and r_cte.end_date or r_cte.start_date between cte.start_date and cte.end_date) then 0 else 1 end +flag as flag from r_cte inner join cte on r_cte.event_id+1=cte.event_id ) select hall_id,min(start_date),max(end_date),event_id,flag from cte group by hall_id,flag)x) i am getting error as syntax error at end of input. pls anyone give solution

[@neerajnaik5161]

Here is easier solution without recursive CTE with cte as ( select hall_id, start_date, end_date, lag(end_date,1,end_date) over(partition by hall_id order by end_date) as prev_end_date from hall_events ), cte1 as ( select hall_id,start_date, end_date, sum(case when prev_end_date=start_date then 0 else 1 end) over (partition by hall_id order by end_date) as group_key from cte ) select hall_id, min(start_date) as start_date, max(end_date),group_key as end_date from cte1 group by hall_id, group_key order by hall_id, start_date

[@ethyria7685]

WITH cte AS (SELECT *, CASE WHEN DATEDIFF(end_date, cast( lead(start_date)OVER(PARTITION BY hall_id ORDER BY start_date) as date) ) < 0 then 1 WHEN end_date < Max(end_date)over(PARTITION BY hall_id) and lead(start_date)OVER(PARTITION BY hall_id ORDER BY start_date) is NOT NULL then 1 else 0 end as flag FROM hall_events) SELECT hall_id, min(start_date), max(end_date) FROM cte GROUP BY hall_id, flag

[@deveshpadgelwar8895]

Table A (Student ID, Student Name, Student Age) table B (Student ID, Student Class, Student Section) Select Student Name, Student Class, Student Section where Student Age between 16 to 18 and Student Section is 'D' How to find out sir ?

[@ankitbansal6]

Join the 2 tables on student id and put the where conditions that you have written

[@deveshpadgelwar8895]

@@ankitbansal6I'm little confused 😕

[@arjundev4908]

WITH CTE AS(SELECT *, LEAD(END_DATE,1,START_DATE)OVER(PARTITION BY HALL_ID) AS NXT_DATE FROM HALL_EVENTS),DUMP AS( SELECT *, SUM(CASE WHEN START_DATE = NXT_DATE THEN 1 ELSE 0 END)OVER(PARTITION BY HALL_ID ORDER BY END_DATE)AS STATUS FROM CTE GROUP BY HALL_ID,START_DATE,END_DATE) SELECT HALL_ID,MIN(START_DATE)AS START_DATE,MAX(END_DATE)AS END_DATE FROM DUMP GROUP BY HALL_ID,STATUS;

[@venkatchinta3105]

hi all, i have a real time issue using store procedure. if any interested to help me please reply me. thanks i. advance

[@ArunKumar-oi3tq]

WITH CTE1 AS ( SELECT HALL_ID,DAY(START_DATE) StartDate , DAY(END_DATE) EndDate FROM hall_events) , CTE2 AS (SELECT *, LAG(ENDDATE,1,STARTDATE) OVER (PARTITION BY HALL_ID ORDER BY STARTDATE) [DATE] ,CASE WHEN LAG(ENDDATE,1,STARTDATE) OVER (PARTITION BY HALL_ID ORDER BY STARTDATE) >= STARTDATE THEN 0 ELSE 1 END AS FLAGS FROM CTE1 ) SELECT Hall_ID, MIN(STARTDATE) ,MAX(ENDDATE) FROM CTE2 GROUP BY HALL_ID ,FLAGS ORDER BY HALL_I

[@PawankumarManpuri]

@ankitbansal6 Please find the solution below without using join and union - with start_hint_cte as ( select hall_id, start_date, end_date, case when lead(start_date, 1) over (partition by hall_id order by start_date)

[@edumail1016]

This was unnecessarily made complicated. We could have achieved result with lag function only.

[@akshayjain7937]

yr smajh nahi aya itna

[@vijaysinghrajput8526]

- without brute force (i.e., without lead /lag) - without recursive query with cte as ( select hall_id, min(start_date) start_date, end_date from ( select h.hall_id as hall_id, h.start_date as start_date, coalesce(e.end_date, h.end_date ) as end_date from hall_events h left join hall_events e on h.hall_id = e.hall_id and h.end_date = e.start_date ) a group by hall_id, end_date ) select c.hall_id, c.start_date, c.end_date from cte c inner join cte d on c.hall_id = d.hall_id and !(c.start_date between d.start_date and d.end_date) union select hall_id, start_date, end_date from ( select *, count(hall_id) over (partition by hall_id)as cnt from hall_events ) x where x.cnt =1

[@edumail1016]

SELECT hall_id, MIN(start_date), MAX(end_date) FROM ( SELECT *, LAG(end_date,1,start_date) OVER (PARTITION BY hall_id ORDER BY start_date) AS prev_end_date, CASE WHEN LAG(end_date,1,start_date) OVER (PARTITION BY hall_id ORDER BY start_date) >= start_date THEN 0 ELSE 1 END AS grp_flag FROM hall_events ) a GROUP BY hall_id, grp_flag ORDER BY hall_id

[@lakshaykhanna2462]

MySQL solution, no join or recursion: WITH cte AS ( SELECT *, IF(DATEDIFF(start_date, LAG(end_date) OVER(PARTITION BY hall_id ORDER BY start_date)) > 0, 1,0) new_series_flag FROM hall_events ), cte_2 AS ( SELECT *, SUM(new_series_flag) OVER(PARTITION BY hall_id ORDER BY start_date) AS grp FROM cte ) SELECT hall_id, MIN(start_date) AS start_date, MAX(end_date) AS end_date FROM cte_2 GROUP BY hall_id, grp; -------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------- 2nd Solution(Using Variable): No join, No recursion, Just 1 cte This one is not compatible with mssql at all SET @stepper = 1; WITH cte AS ( SELECT *, IF(DATEDIFF(start_date, LAG(end_date) OVER(PARTITION BY hall_id ORDER BY start_date)) > 0, 1,0) new_series_flag, LAG(hall_id) OVER(ORDER BY hall_id, start_date) AS prev_hall FROM hall_events ) SELECT MAX(hall_id) AS hall_id, MIN(start_date) AS start_date, MAX(end_date) AS end_date FROM cte GROUP BY IF(new_series_flag = 0 AND hall_id = prev_hall, @stepper, @stepper := @stepper + 1);