TCS SQL Interview Question - Find product wise total amount, including products with no sales

One of the SQL questions recently asked in TCS interview.
Given us products and transactions table, We need to Find product wise total amount, including products with no sales.
Let us create table and insert data
create table products (pid int, pname varchar(50), price int)
insert into products values (1, 'A', 1000),(2, 'B', 400),(3, 'C', 500);
create table transcations (pid int, sold_date DATE, qty int, amount int)
insert into transcations values (1, '2024-02-01', 2, 2000),(1, '2024-03-01', 4, 4000),
(1, '2024-03-15', 2, 2000),(3, '2024-04-24', 3, 1500),(3, '2024-05-16', 5, 2500);
For more SQL interview questions. Check out our playlist.
• SQL Interview Questions
Contact us:
[e-mail]
Follow us on
Instagram : cloudchallengers
Facebook : cloudchallengers
LinkedIn : linkedin.com/company/cloudchallengers

[@ritammoharana4219]

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.

[@biswajitpradhan6121]

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

[@mohanprasanthmanickam8292]

Thanks for the video.

[@CloudChallengers]

@mohanprasanthmanickam8292, Thanks for your comments.

[@asadahmad8047]

This question is asked to how much experienced person

[@CloudChallengers]

@asadahmad8047, this question is asked for experienced candidate with 4+ years of experience.

[@nr_creations9734]

Very useful

[@CloudChallengers]

@nr_creations9734, Thanks for the encouragement.

[@maheshnagisetty4485]

Nice Video Bro,but i have a doubt if in case year will select dynamically(not in hardcore) how we will do that?

[@CloudChallengers]

@maheshnagisetty4485, Thanks for the feedback.
You can declare year.
DECLARE @Year INT = 2024;
SELECT @Year AS Year, ........................

[@chandanpatra1053]

is that question asked to an data engineer/data analyst or is that question asked for a sql developer role.can you please clarify?

[@CloudChallengers]

@chandanpatra1053, this question is asked for data engineer role.

[@vijaygupta7059]

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

[@CloudChallengers]

@vijaygupta7059, Thanks for posting the alternative approach. Keep posting different approaches for upcoming videos as well.

[@vlog.444]

Thanks for the video