for dynamic solution : with cte1(pid , pname , _year , _month) as ( select A.pid ,A.pname , datepart(year,B.sold_date) as 'year' , 1 as 'month' from products A , transcations B group by A.pid , A.pname , datepart(year,B.sold_date) union all select pid , pname , _year , _month + 1 from cte1 where _month + 1
with cte as ( select generate_series(1,12) as month,2024 as year) select p.pid,p.pname,c.year,c.month,COALESCE(sum(t.amount),0) as total_sales from cte as c cross join productp1 as p left join transcations as t on c.month = date_part('month',t.sold_date) and p.pid = t.pid group by p.pid,p.pname,c.year,c.month order by p.pid,c.month; this query is for postgresql.
I have done using MSSQL DB : with r_cte as ( Select distinct p.pid, pname , case when sold_date is not null then year(sold_date) else '2024' end as years , 1 as month from products as p left join transcations as t on p.pid = t.pid union all Select pid, pname, years , (month+1) as month from r_cte where month
I didn’t get the defining the months part …. Plz would u please explain why should we do that..
for dynamic solution :
with cte1(pid , pname , _year , _month) as
(
select A.pid ,A.pname , datepart(year,B.sold_date) as 'year' , 1 as 'month' from products A , transcations B
group by A.pid , A.pname , datepart(year,B.sold_date)
union all
select pid , pname , _year , _month + 1 from cte1
where _month + 1
with cte as (
select generate_series(1,12) as month,2024 as year)
select p.pid,p.pname,c.year,c.month,COALESCE(sum(t.amount),0) as total_sales
from cte as c cross join productp1 as p
left join transcations as t
on c.month = date_part('month',t.sold_date) and p.pid = t.pid
group by p.pid,p.pname,c.year,c.month
order by p.pid,c.month;
this query is for postgresql.
Nice Video Bro,but i have a doubt if in case year will select dynamically(not in hardcore) how we will do that?
@maheshnagisetty4485, Thanks for the feedback.
You can declare year.
DECLARE @Year INT = 2024;
SELECT @Year AS Year, ........................
Hello everyone welcome back to the channel
😄
how we can achieve
this scenario in oracle
Thanks for the video.
@mohanprasanthmanickam8292, Thanks for your comments.
Cross apply mean cross join??
One thing one total sales must sales * qty
@peterparker4358, great catch. Thanks for highlighting.
Very useful
@nr_creations9734, Thanks for the encouragement.
This question is asked to how much experienced person
@asadahmad8047, this question is asked for experienced candidate with 4+ years of experience.
But this was a very easy one
is that question asked to an data engineer/data analyst or is that question asked for a sql developer role.can you please clarify?
@chandanpatra1053, this question is asked for data engineer role.
I have done using MSSQL DB :
with r_cte as
(
Select distinct p.pid, pname
, case when sold_date is not null then year(sold_date) else '2024' end as years
, 1 as month from products as p left join transcations as t
on p.pid = t.pid
union all
Select pid, pname, years , (month+1) as month from r_cte
where month
@vijaygupta7059, Thanks for posting the alternative approach. Keep posting different approaches for upcoming videos as well.
Thanks for the video