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

[@Ujjwalmishra-t1w]

Hello everyone welcome back to the channel

[@CloudChallengers]

😄

[@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

[@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.

[@seshulatha]

I didn’t get the defining the months part …. Plz would u please explain why should we do that..

[@mohanprasanthmanickam8292]

Thanks for the video.

[@CloudChallengers]

@mohanprasanthmanickam8292, Thanks for your comments.

[@nr_creations9734]

Very useful

[@CloudChallengers]

@nr_creations9734, Thanks for the encouragement.

[@seshulatha]

Cross apply mean cross join??

[@asadahmad8047]

This question is asked to how much experienced person

[@CloudChallengers]

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

[@MubarakAli-qs9qq]

But this was a very easy one

[@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.

[@Harish0402]

how we can achieve this scenario in oracle

[@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