Amazon Data Engineer SQL Interview Problem | Leetcode Hard SQL 2494 | Recursive CTE
ฝัง
- เผยแพร่เมื่อ 3 ธ.ค. 2024
- In this video we are going to discuss leetcode hard SQL problem 2494. It was asked in Amazon for Data Engineer position as well.
Master the ART of SQL :
www.namastesql...
Ready script:
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');
Zero to hero(Advance) SQL Aggregation:
• All About SQL Aggregat...
Most Asked Join Based Interview Question:
• Most Asked SQL JOIN ba...
Solving 4 Trick SQL problems:
• Solving 4 Tricky SQL P...
Data Analyst Spotify Case Study:
• Data Analyst Spotify C...
Top 10 SQL interview Questions:
• Top 10 SQL interview Q...
Interview Question based on FULL OUTER JOIN:
• SQL Interview Question...
Playlist to master SQL :
• Complex SQL Questions ...
Rank, Dense_Rank and Row_Number:
• RANK, DENSE_RANK, ROW_...
#sql #dataengineer
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
I used the similar approach for solving this question!
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;
I found this approach is very simple. Thank you
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')
@@neerajnaik5161 right..
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
you are master in recursive CTE quey... Awesome... continue to enlighten us in SQL.
Thank you, I will
Was a bit confusing but understood it
After watching in 2 or 3 times..
Please hit the like button to increase the reach of the video 🙏
Could you share the video where you are referring to getting started with recursive SQL?
Wonderful solution sir!
Great Concept Sir
Thanks very much
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)
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..
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
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
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
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?
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;
This is a very good question.
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.
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.
@@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.
@@andreih1167 What was your approach that doesn't compromise the performance? Could you share it ?
@@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
@@ankitbansal6 do make a video of solving the same using windows functions
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;
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
with cte as(
select *,case when lead(start_date,1) over (partition by hall_id order by start_date)
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
Hi Ankit ,
select hall_id,min(start_date),max(end_date) from (select *,case when prev_end_date is null or start_date
Thank you so much sir, I got the job with the help of your videos.
Thanks again 🙏
Great job 🎉
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)
Logic right on point 💯
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,🙏
Awesome 🎉🎉 Congratulations 👏
Your just Awsum in your teaching 🙌🙌🙌 and this Problem statement is great to understand bit confusing too
Thank you! 😃
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
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
You just nailed it bro 👌👌👌
Thank you 😊
Hi Ankit, that's really appreciable.
Thank you 🙏for the great content. Please also mention the performance comparison with different approaches.
Sure 👍
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
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
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;
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
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
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
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;
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🙏
In this case third row is a separate interval...why would you consider previous enddate to overlap?
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
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.
Hi Ankit,
SKI Assignment video is hidden. Kindly make it unhidden. I want to watch it again.
Thank you
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
;
Looks like more of hard coded solution
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)
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
How would you write a query to list all the pairs of overlapping records and then calculate an actual overlap for each pair ?
COuld you also start a series on python series that is necessary for data analyst job, like for scripting and automation
@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)
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
Hi Ankit, Thanks for the video. When are you planning the next SQL training batch?
May/june
Bro function and procedure bhi btao yar kisi video mein
finished watching
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
thank you bro 🥰🥰🥰🥰
Welcome 😊
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 .
Hi Ankit , in your SQL course have you covered data modeling , some advance topic like tuning
Tuning is covered. Not much about data modelling..
This query doesnt work in mysql database.
i have tried to add 'recursive' in from of r_cte, still doesnt work.
Can anyone explain
Which type of SQL questions asked in interview for freshers could you suggest some examples
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
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
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;
@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?
It will work
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
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
Yes or condition is not required.
For overlap case this solution will work.
@@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
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.
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.
@@ankitbansal6 thanks sir
Can you please make a video on advanced topics in SQL like PIVOT, Indexing, etc. Also , will this be included in your course?
Yes it's covered
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
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
Sir I have a query regarding procedure can u plz solve that query.
Send problem on sql.namaste@gmail.com
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
Mujhhe please..................🙏🙏 Aapse SQL ka session lena ya nahi lete h to please sir start session please make in Hindi
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;
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
hi all,
i have a real time issue using store procedure. if any interested to help me please reply me. thanks i. advance
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 ?
Join the 2 tables on student id and put the where conditions that you have written
@@ankitbansal6I'm little confused 😕
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
yr smajh nahi aya itna
- 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
This was unnecessarily made complicated. We could have achieved result with lag function only.
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
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
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);