Recursive CTE | Leetcode Hard SQL Problem 5 | Complex SQL 12

In the first part of this video we are going to discuss how recursive CTE works. In second part we will discuss a leet code SQL hard problem where we will be using recursive CTE concept.
Playlist for complex SQL questions:

create and insert script for this problem. Do try yourself without using CTE.

create table sales (
product_id int,
period_start date,
period_end date,
average_daily_sales int
);

insert into sales values(1,'2019-01-25','2019-02-28',100),(2,'2018-12-01','2020-01-01',10),(3,'2019-12-01','2020-01-31',1);

[shahinurrahman]

awesome!

I took all the parameters in the recursive CTE to avoid the joins.

with recursive dt_cte as (
select period_start, period_end, product_id, average_daily_sales from sales
union all
select adddate(period_start, 1), period_end, product_id, average_daily_sales
from dt_cte
where period_start<period_end
)

select product_id, date_format(period_start, '%Y') as yr, sum(average_daily_sales) as tot_sale
from dt_cte group by date_format(period_start, '%Y'), product_id
order by product_id, yr, tot_sale;

[simardeepsingh]

These videos are extremely helpful. I have searched a lot in YouTube but none solves these many hard problems. Kudos to you :)

[arpiteshsrivastava]

Thanks Ankit! ... and below is the query for Mysql workbench (If any one wants).
Note: Addon/modified keywords/parameters are ---> recursive, date_add(dates, interval 1 day)

with recursive t_sales as
(select min(period_start) as dates, max(period_end) maxdate from sales
union all
select date_add(dates, interval 1 day) as dates, maxdate from t_sales
where dates < maxdate)
select product_id, year(dates) as report_year, sum(average_daily_sales) as total_amount
from t_sales inner join sales
on dates between period_start and period_end
group by product_id, year(dates)
order by product_id;

[chaitanyaprasad]

This logic is just Too good solution. Even that inner join is a brilliant idea.

[asmitadasgupta]

Hi Ankit, can you please post some more questions on this topic? Also, how to understand when to use CTE/Recursive CTE?

[vandanaK-mhzo]

zindagi me pehli baar recursive CTE smjh me aya hai

[PalakShah-fxmd]

Excellent explanation on recursive CTE.

[shekharagarwal]

keep rocking Ankit . Excellent explanation!

[anishchhabra]

Great way to explain a concept, thank you bro.

[mandardange]

Thank you taking this up. really helpful to figure out the approach to solve any problem.

[thousifkhan]

I tried in the postgres in little different way which could be interesting
1.I have assumed that data is large.
2.If data is large then generating data for each date will consume more space and time
3.So I computed the number of days month wise for corresponding year and computed by (days*sales)
4.Finally aggregated by product_id wise and year wise



with recursive r_cte as
(
select min(period_start) as dates, max(period_end) as max_date from sales
union all
select (dates+interval '1 month')::date, max_date from r_cte
--where dates < max_date
where dates < date_trunc('month', max_date)
)
, dates_cte as
(
select min(dates) as date_start
, least(date_trunc('month', dates) + interval '1 month - 1 day', max(max_date))::date as date_end
from r_cte
group by dates order by dates
)
, days_cte as (
select *, (date_end-date_start)+1 as monthly_days from dates_cte
)select
b.product_id, extract('year' from a.date_start) as year, as total_sales
from days_cte a
join sales b on a.date_start between b.period_start and b.period_end
group by b.product_id, extract('year' from a.date_start)
order by b.product_id
;

[shubhamagrawal]

I have also used recursive CTE but in a different way. In the last, I haven't use any JOIN clause to solve this problem.
Here is my code (very short & simple code I have written) : -

with recursive cte as(
select product_id, period_start, period_end, average_daily_sales from sales
union
select product_id, date_add(period_start, interval 1 day) period_start, period_end, average_daily_sales
from cte where period_start < period_end)

select product_id, year(period_start) report_year, sum(average_daily_sales)
from cte group by 1, 2

[partharora]

Learnt Something New today . Thanks for the video ... But question is how to remember what approach we need to take ? mostly seeing such type of questions all that comes in mind is to use case statement or any analytical functioin ..

[ramakumarguntamadugu]

🔥this is very useful, thanks for sharing..

[ideaexecute]

Awesome, I learnt recusive CTE very well

[aadil]

Ankit what will be the order of execution in case of recursive CTE?

[shubhambhargava]

Today I was asked the same question. I thought of using CASE for getting the years. Couldn't answer it. Immediately after the interview, I search it on leetcode followed by youtube mentioning "Total Sales by Amount Ankit Bansal" :) Thank you!

[abhayakumar]

From Oracle/SQl...
with d_dates (dates, max_dates)
as
(
select min(period_start) as dates, max(period_end) as max_dates from sales
union all
select dates+1 as dates, max_dates from d_dates
where dates<max_dates
) --select * from d_dates;
select s.product_id, extract(year from dates) report_year, sum(s.average_daily_sales) as tot_amt
from d_dates dd
inner join sales s
on dates between s.period_start and s.period_end
group by s.product_id, extract(year from dates)
order by s.product_id, extract(year from dates);

[siddhantgupta]

I have a doubts, like hackerrank does not support recursive function, so how to do similar types of problem on it

[guptaashok]

i think we should also take productid in CTE and use it in join so that if there are overlapping date range across product will not create issue.