SQL Question in AMAZON Interview🔥Practice it Before Your Interviews😎 DE, Data Scientist, Analyst❤️

[@shashank_mishra]

Try this question by your own first, post the solution in comments ... then match it with my solution ✌️
And if you are enjoying these kind of videos and it looks helpful to you, then complete 1000 likes ❤️❤️

[@Energybooster111]

Sir please make video on Pivot function in Oracle...

[@thelearningtree7037]

create table SALE (merchant STRING, amount int, pay_mode STRING);
insert into SALE values('merchant_1',150,'cash');
insert into SALE values('merchant_1',500,'online');
insert into SALE values('merchant_2',450,'online');
insert into SALE values('merchant_1',100,'cash');
insert into SALE values('merchant_3',600,'cash');
insert into SALE values('merchant_5',200,'online');
insert into SALE values('merchant_2',100,'online');

[@anshusharaf2019]

Doing grt job man

[@ankandatta4352]

Thank you Brother

[@forniteguruji9409]

Hey Shashank,
Just a piece of advice, it would be great if you also include the CREATE table statements in the description box of every interview question video. It would be helpful for us to practice.

[@anil5335]

Don't be lazy you can write your own create statement data are available in video 😎

[@manishsinghpatel9128]

Please add more SQL Hackerrank rank or leetcode problems (Solution + Approach + Common mistakes )asked in companies . THIS IS very helpful 🙌

[@dhruvsharma8839]

Amazing video, more videos like this ❤️

[@mrwho.7163]

GREAT VIDEO MAN !!! can u do a fang joining road map for 2 yr experienced software engineer ? ps : tier 3 collage , tier 2 collage

[@shashank_mishra]

Noted

[@soumyaranjandash3597]

I also want for Freshers

[@mahesh.khatai93]

Hey Shashank ,
Thanks for the video . As you suggested for trying once before the solution . I tried to solve the problem statement using CTE approach .
1. 2 CTE - Online_data & Cash_data which adds the amount .
2. Full Outer Join CTE's on merchant_name and do NVL handling .
WITH online_data AS
(
SELECT merchant,payment_mode,SUM(amount) as online_total_amount from payment_data
WHERE payment_mode = 'Online'
GROUP BY merchant,payment_mode
),
cash_data AS
(
SELECT merchant,payment_mode,SUM(amount) as cash_total_amount from payment_data
WHERE payment_mode = 'Cash'
GROUP BY merchant,payment_mode
)
SELECT coalesce(a.merchant,b.merchant) as merchant,
NVL(CAST(a.online_total_amount AS STRING),'0') as online_amount,
NVL(CAST(b.cash_total_amount AS STRING),'0') as cash_amount
FROM online_data a FULL OUTER JOIN cash_data b
ON a.merchant = b.merchant
ORDER by merchant

[@shashankemani1609]

amazing. thanks so much for bringing such problem!
please make more of such content

[@rajannaRK2002]

whenever i watch ur videos i thought that here is hopes to get better path🤩...ur all podcasts and interviews r fruitful for me.. Hat's Off 🙌❤️🙌

[@Priyanka-us8rw]

It would be great if you create playlist for such sql interview questions for different companies.

[@nithish837]

Really nice video. We need a playlist of all such advanced SQL questions.

[@vimalradadiya5929]

Thank you sir 🙏 for this video and excited for this kind of videos in future

[@shikharsaxena8984]

These type of good SQL questions r not easily available, well explained, you are doing an awesome work 👏👏🙂

[@ankitbanerjee7745]

Earlier I used the pivot concept in SQL to solve this but your solution was so crisp and efficient.

[@phanikumar2753]

Great work brother....please continue the same....like from my side

[@atulk9122]

I made the mistake by summing at individual level but you catch it and explain why we need overall case statement in sum

[@rohitsheelvant6609]

Hey this way of learning SQL QUERIES is Super cool. Do bring us such content very often to us ✨.
Keep up the good work, cheers

[@SANJAYYADAV-hm2bs]

good question.
please make a separate playlist with all important questions for product companies.

[@sravankumar1767]

Nice explanation bro, keep on posting new videos 📹 👍 👌

[@limotto9452]

Thank you very much for this video and the lesson. It definitely caught me off guard when I jumped into conclusion as soon as I finished reading the questions and written the 1st statement with the groupby like you have mentioned, then quickly realized from the output column indeed I haven't been paying enough attention.

[@niksethi500]

Hahaha, I work in a product based startup and whenever i am making dashs using sql, I am often asked for exact same view for data visualisation. And it is nice to see that you are focusing on the optimised way of writing the query.

[@chalapathyreddyv8770]

Never imagined this type of solutions is possible

[@xploredatawithdurgesh_pragya]

This is my solution with Pivot
select merchant,isnull(cash,0) as Cash_Amount ,isnull(online,0) as Online_Amount
from (select merchant,amount,payment_mode from PAYMENTS_DATA)s
pivot(sum(amount) for payment_mode in ([cash],[online])) table2

[@SwayamRath22]

I made the mistake of using the aggregate SUM function inside the WHEN clause.

[@adharshsunny2709]

Amazing man

[@pushkarratnaparkhi2205]

Thanks for great content.
Happy Teacher's Day.

[@vansaber]

I got a question with a similar approach in my previous SQL interview. I wish I had seen this video before the interview!

[@sriharsha398]

Nice explanation and well summarized
Thank you brother

[@AravindKumar-vq1yh]

Hey Shashank I am an upcoming data engineer placed in a startup ,just a fresher and your videos are too good man ,they are motivating me a Lott 💗 I hope to meet you some time in the future and let's discuss more about data engineering 🔥

[@gagansingh3481]

select merchant,
isnull(Sum(case when payment_mode = 'Cash' then Amount else null end ),0)as cash_amount,
isnull(sum(case when payment_mode = 'Online' then Amount else null end ),0)as online_amount
from payments_data
group by merchant

[@kuchcheejeeaesehiii1138]

Very informative bro

[@subhajitroy5850]

Really a good one . I paused the video n thought of a sub query on top of the base data/query which you showed in the first iteration , but liked your approach too , you made it w/o even using a sub query.
I would suggest you to create a separate play list for the same which should have atleast one from each category like this one, some different window functions and so on ... covering the breadth (different types) and then add complexity on each type slowly 👍

[@thecerealcoder]

There could be many approaches. You'll need to show the most optimal one at interviews.
If you can avoid joins then the query will perform better.
The more you can do with less is usually the best approach in SQL.
Keeping in mind readability.

[@hartonosusilo1577]

using postgresql:
select merchant,
sum(case
when payment_mode = 'cash' then amount else 0
end) as cash_amount,
sum(case
when payment_mode = 'online' then amount else 0
end) as online_amount
from payments_data
group by merchant
order by merchant
😁😁

[@anil5335]

Nice one for brain exercise.

[@ramanreddy7276]

with cte as (
select mname,pmode,sum(amount) as amt from march group by mname,pmode order by mname )
select mname,max(case when pmode = 'cash' then amt else 0 end )as cash,
max(case when pmode = 'online' then amt else 0 end ) as "online"
from cte group by mname;

[@amitkarmakar6418]

Hello Sir, can this problem be solved with window function? i tried it with window function but not getting output in correct format?
my approach - select merchant,sum(amount) over(partition by merchant,payment_mode),payment_mode from leetcode.payments_data order by merchant;

[@shashank_mishra]

You have to do the group by in order to produce 1 record for each merchant

[@ShivamSharma-mn1wm]

Easy Peasy bro :), thnx for this video it helps boosting our confidence.

[@TKJ]

2 days back, I used pivot and I write very big code.
With this method, it will be a short code

[@mayankgupta4363]

Select merchant , cash_mode, online_mode , case cash_mode>=0 or online_mode>=0 then cash_mode+online_mode else 0 as total from table group by merchant

[@TheExploringMonk]

Without case you can do it with a mix of with clause and union all to achieve it.

[@amolpardeshi2610]

Nice Sir, thanks

[@navneetsingh6245]

I did this question in MS SQL Server using 2 Approaches.
Approach 1 Using Case Statement :-
SELECT merchant, SUM(CASE WHEN payment_mode = 'CASH' THEN amount ELSE 0 END) as cash_amount,
SUM(CASE WHEN payment_mode = 'ONLINE' THEN amount ELSE 0 END) as online_amount
FROM payments_data
GROUP BY merchant
Approach 2 Using Pivot Function :-
SELECT merchant, SUM(CASE WHEN CASH is NULL THEN 0 ELSE CASH END) as cash_amount, SUM(CASE WHEN ONLINE is NULL THEN 0 ELSE ONLINE END) as online_amount
FROM payments_data
PIVOT(
MAX(amount)
FOR payment_mode in
(CASH,ONLINE)
) pivotdata
GROUP BY merchant

[@ramakrishnan2927]

For beginners in SQL , you can try with outer join queries as building blocks and then move to better way of writing.
SELECT DISTINCT
C.MERCHANT,
COALESCE(A.TOTAL,0) AS CASH_TOTAL ,
COALESCE(B.TOTAL,0) AS ONLINE_TOTAL
FROM
C16 C LEFT OUTER JOIN
(SELECT merchant,SUM(AMOUNT) TOTAL FROM C16 WHERE P_MODE = 'CASH' GROUP BY merchant) A
ON C.MERCHANT = A. MERCHANT
LEFT OUTER JOIN
(SELECT merchant,SUM(AMOUNT) TOTAL FROM C16 WHERE P_MODE = 'ONLINE' GROUP BY merchant) B
ON C.MERCHANT = B. MERCHANT

[@ekanshmishra4517]

I have read about case when but i have never thought it would be such a handy operation😳. I would like to say buddy love the way you explained♥️. Also I have a doubt how, where, and which course to opt to practise these types of sql queries so that🙏 I can prepare it

[@shivamsrivastava950]

If I use pivot instead of case when statement. Then which query will be best in the term of performance? And why?

[@omkarm7865]

Very helpful

[@dheemanjain8205]

with cte1 as
(select merchant,payment,sum(amount) as total_amount from amazon group by merchant,payment)
select merchant,max(case when payment='cash' then total_amount end) offline,max(case when payment='online' then total_amount end) online from cte1 group by merchant

[@amazingriknow5986]

with x as (select payment.merchant,case when payment_mode ='cash' then amount else 0 end as cash_amount
,case when payment_mode ='online'
then amount else 0 end as online_amount
from payment)
select distinct merchant,sum(cash_amount)over(partition by merchant) as cash_amount,sum(online_amount)
over(partition by merchant) as online_amount from x;

[@anushkasingh6758]

Heyy I am looking for a video on Namma Yatri challenge by Justpay on Unstop. I really want to participate! Can you please make a video on that?

[@tanayburreja225]

how to approach when there are multiple payment modes ?

[@ragnarlodhbrok1684]

select merchant, cash_amount, online_amount from payments_data pivot(sum(amount) for (payment_mode) in ('Cash' as Cash_amount, 'Online' as Online_amount)) as pivottable;
is this correct,,, I'm new to pivot function so correct me if I am wrong

[@Jedi1976]

Is their any platform for practicing SQL questions like DSA

[@giridhararaomaddirala2472]

Thank you bro

[@vaibhavverma1340]

select merchant, sum(case when payment_mode = 'cash' then amount else 0 end)as cash_amount
,sum(case when payment_mode = 'online' then amount else 0 end)as online_amount from payments_data
group by merchant

[@saikatde6343]

I think my solution is lengthy. But still I gave a try.
with cte as
(
select
a.merchant ,
b.payment_mode
from
(select distinct merchant from payments_data) as a ,
(select distinct payment_mode from payments_data) as b
)
select merchant , [cash] as cash_amount , [online] as online_amount from
(
select a.merchant ,
a.payment_mode ,
coalesce(b.amount, 0) as amount
from
cte as a
left join
payments_data as b
on a.merchant = b.merchant and a.payment_mode = b.payment_mode
) as a
pivot
(
sum(amount)
for payment_mode in ([cash] , [online])
) as pivot_table;

[@anshulkatara568]

I usually use decode in such scenario instead of case

[@shaikumair6161]

Can you explain how to unpivot result?

[@pranshumishra406]

My first thought was to use SELF JOIN, lol. For some unknown reason, CASE WHEN isn't something that comes to mind intuitively while using SQL. I guess we can solve it using self join but it would probably be very inefficient.
I probably need to step up my SQL game, specially now that I'm working on ML at FAANG.

[@Karmihir]

Which one and what experience you join can you share the process?

[@chessforevery1]

You use (end ) in sql quey what is it meaning..?

[@abhimanyutiwari100]

Hi, can you share code to create table?

[@subhajitroy5850]

@shashank : same request from my side as well. If you provide the script n the data (insert statements with the same data you have in the video), people can first make the table ready n then practice /exp in their local

[@sayandatta4633]

==================
TABLE CREATION
==================
CREATE TABLE amazon (
merchant VARCHAR (50),
amount INT,
payment_mode VARCHAR(20)
);
INSERT INTO amazon (merchant,amount, payment_mode)
VALUES("mer_1",150,"CASH"),
("mer_1",500,"ONLINE"),
("mer_2",450,"ONLINE"),
("mer_1",100,"CASH"),
("mer_3",600,"CASH"),
("mer_5",200,"ONLINE"),
("mer_2",100,"ONLINE");

[@dhanavenkatachennakeswarar944]

select merchant,sum(case when payment_mode = 'cash' then amount else 0 end) cash_amt,
sum(case when payment_mode = 'online' then amount else 0 end) online_amt -- sum(amount)
from merchant
group by merchant

[@mritunzaysingh8978]

Please make a video on windows function

[@akashnampalli7617]

if it is a leetcode problem can you please tell me the problem number.

[@chitralekhbarve7513]

my solutions would be:
select merchant,
sum (case when payment_mode = 'Cash' then amount else 0 end ) as Cash_payment,
sum (case when payment_mode = 'Online' then amount else 0 end ) as Online_payment
from transactions
Group by merchant

[@arpitagarwal8485]

Nice question.
Here is my solution.
select merchant,
sum(
CASE WHEN payment_mode="CASH" THEN amount
ELSE 0
END) as "Cash_amount",
sum(
CASE WHEN payment_mode="ONLINE" THEN amount
ELSE 0
END) as "online_amount"
from payments_date group by merchant;

[@krishnachaitanyareddy2781]

even i think same

[@rishabhkapoor5105]

An alternate solution:
with online_table as (select Merchant, sum(Amount) as Online_Amount from mechant where Payment_mode = 'Online'
group by Merchant)
,cash_table as (select Merchant, sum(Amount) as Cash_Amount from mechant
where Payment_mode = 'Cash'
group by Merchant)
select ot.Merchant,ot.Online_Amount,ct.Cash_Amount from online_table ot left join cash_table ct on ot.Merchant = ct.Merchant;

[@ranjithshanmugam43]

Wow this Works, awesome

[@jaykumarnilkanth2354]

how to get this questions on our sql server?

[@SwetankSingh-vq2tz]

select Merchant,Coalesce(sum(case when PaymentMode = 'Online' then Amount end),0) as "Online",coalesce(sum(case when PaymentMode = 'Cash' then Amount end),0) as "Cash"
from Merchant
group by merchant;

[@veer400]

On which platform we can practice such type of questions ?? 🙄🙄

[@anushakrishnaa]

with cte as(
select merchant,SUM(case when pay_mode='cash' then amount else 0 end)cash,SUM(case when pay_mode='online' then amount else 0 end)online from SALE group by merchant,pay_mode)
select merchant,MAX(cash)cash_max,MAX(online)online_max from cte group by merchant

[@debashisjena6836]

Sir how can I speak english like u sir please replay me sir .

[@lakshitsharma5533]

Please share data set

[@bhaskar9781]

Please do it by pivoting

[@Daily_Dose_010]

Great content sir..! One typo in Bio Practice*

[@shashank_mishra]

Thanks for pointing that out, corrected it 😊

[@CORRCTME]

From where I can learn SQL

[@Desi_Data_Guy]

Select merchant,
SUM(Case
When payment_mode = ‘CASH’ then amount
Else 0
END) As Cash_amount,
SUM(Case
When payment_mode = ‘ONLINE’ then amount
Else 0
END) As online_amount,
From payments_data
Group by merchant;

[@ranjithshanmugam43]

Is there any other way we can achieve the solution using the cursor?

[@shashank_mishra]

You can try it out if it works .. share the solution here

[@coding3438]

Why use complex concepts when you use a simple case when. Also, we have to resort to case when because theres no sumif in ansi sql. If for example youre on big query you can simply use sumif(amount, mode =cash) with a group by.

[@arupchandra8602]

My solution :
select marchant,
sum(case when payment_mode='CASH' then amount else 0 end) Cash_amount,
sum(case when payment_mode='ONLINE' then amount else 0 end) Online_amount
from payments_data
group by marchant
order by marchant

[@rohitchakravarthi94]

SELECT
merchant,
sum(CASE WHEN payment_mode = 'CASH' THEN amount end) AS cash_amount,
sum(CASE WHEN payment_mode = 'ONLINE' THEN amount end)AS online_amount
FROM payments_data
--WHERE
GROUP BY merchant

[@fitneurons]

Select merchant,
Case
When payment_mode = 'Online' Then Sum(amount) Else 0 End as Online_amount,
When payment_mode = 'Cash' Then Sum(amount) Else 0 End as Cash_amount
From payments
Group By merchant, payment_mode

[@sureshpathak2008]

select merchant,sum(cash_amount) as cash_amount,sum(online_amount) as online_amount from(
select merchant,
CASE WHEN (payment_mode = "CASH") THEN SUM(amount)
ELSE 0 END as cash_amount,
CASE WHEN (payment_mode = 'ONLINE') THEN SUM(amount)
ELSE 0 END as online_amount
from payments_data group by merchant,payment_mode) pd group by pd.merchant;

[@archanadevi4539]

We can achieve this by using decode

[@coding3438]

This was not very hard! I suggest maybe you come up with some pretty complex queries for you viewers as a competetion for them, and then maybe you can solve that a week later live on video!!

[@debashisjena6836]

Please like karo video ko

[@ruthrantech]

DECODE (payment_mode, 'CASH', amount,0) as cash_amount, DECODE (payment_mode, 'ONLINE', amount,0) as online_amount in Oracle

[@manilalkasera4238]

with i_table as (
select merchant, sum(amount) as total_amount, payment_mode
from sales
group by merchant, payment_mode
)
select
merchant, sum(cash_amount) as cash_amount, sum(online_amount) as online_amount
FROM (
select
merchant,
CASE WHEN payment_mode = "CASH" THEN total_amount ELSE 0 END as cash_amount,
CASE WHEN payment_mode = "ONLINE" THEN total_amount ELSE 0 END as online_amount
From i_table
) x
group by x.merchant

[@Chinese_Burger]

select merchant,isnull(sum([CASH]),0),isnull(sum([ONLINE]),0) from tr1
pivot(sum(amount) for payment_mode in ([CASH],[ONLINE])) as pvt
group by merchant

[@forniteguruji9409]

-------------------------------------------------------------
Postgres Solution with CTE - Bad way
---------------------------------------------------------------
with cte_sum as (select merchant, sum(amount) as SS, payment_mode,
(case when payment_mode='CASH' then 1 else 0 end) as PM
from amazon group by merchant, payment_mode
order by merchant)
select merchant,
MAX(case when pm=1 then SS else 0 end) as CASH,
MAX(case when pm=0 then SS else 0 end) as ONLINE
from cte_sum
group by merchant
----------------------------------------------------
Postgres Solution - smart way of doing
-----------------------------------------------------
select merchant,
SUM(case when payment_mode='CASH' then amount else 0 end) as CASH,
SUM(case when payment_mode='ONLINE' then amount else 0 end) as ONLINE
from amazon
group by merchant

[@poojakamble005]

PIVOT
spreding column -> Payment_mode
aggregating col -> sum(amount)
group by column -> Merchants
select
Merchant
ifnull(CASH,0) as cash_amount,
ifnull(ONLINE,0) online_amount
from
(select Payment_mode, amount, Merchant from pay_data)DQ
PIVOT
( sum(amount) for Payment_mode IN ([CASH), [ONLINE]) )PQ