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

Подписаться 2 тыс.

Просмотров 2,6 тыс.

50% 1

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:
info@cloudchallengers.com
Follow us on
Instagram : cloudchallengers
Facebook : cloudchallengers
LinkedIn : linkedin.com/company/cloudchallengers

Опубликовано:

25 июн 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 15

@ritammoharana4219 4 дня назад

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 7 дней назад

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

@asadahmad8047 6 дней назад

This question is asked to how much experienced person

@CloudChallengers 2 дня назад

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

@mohanprasanthmanickam8292 14 дней назад

Thanks for the video.

@CloudChallengers 13 дней назад

@mohanprasanthmanickam8292, Thanks for your comments.

@nr_creations9734 14 дней назад

Very useful

@CloudChallengers 13 дней назад

@nr_creations9734, Thanks for the encouragement.

@maheshnagisetty4485 9 дней назад

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

@CloudChallengers 9 дней назад

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

@chandanpatra1053 14 дней назад

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 13 дней назад

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

@vijaygupta7059 13 дней назад

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 13 дней назад

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