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 🙌

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

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

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

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

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

[@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 🙌❤️🙌

[@dhruvsharma8839]

Amazing video, more videos like this ❤️

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

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

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

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

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

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

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

[@Priyanka-us8rw]

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

[@shashankemani1609]

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

[@shikharsaxena8984]

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

[@nithish837]

Really nice video. We need a playlist of all such advanced SQL 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

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

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

[@vimalradadiya5929]

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

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

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

[@ankitbanerjee7745]

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

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

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

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

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

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

[@SANJAYYADAV-hm2bs]

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

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

[@pushkarratnaparkhi2205]

Thanks for great content. Happy Teacher's Day.

[@atulk9122]

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

[@phanikumar2753]

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

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

[@vansaber]

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

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

[@SwayamRath22]

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

[@TKJ]

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

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

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

[@sravankumar1767]

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

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

[@chalapathyreddyv8770]

Never imagined this type of solutions is possible

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

[@ShivamSharma-mn1wm]

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

[@TheExploringMonk]

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

[@adharshsunny2709]

Amazing man

[@anil5335]

Nice one for brain exercise.

[@amolpardeshi2610]

Nice Sir, thanks

[@kuchcheejeeaesehiii1138]

Very informative bro

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

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

[@mritunzaysingh8978]

Please make a video on windows function

[@omkarm7865]

Very helpful

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

[@giridhararaomaddirala2472]

Thank you bro

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

[@bhaskar9781]

Please do it by pivoting

[@ruthrantech]

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

[@shivamsrivastava950]

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

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

[@anshulkatara568]

I usually use decode in such scenario instead of case

[@archanadevi4539]

We can achieve this by using decode

[@Jedi1976]

Is their any platform for practicing SQL questions like DSA

[@shaikumair6161]

Can you explain how to unpivot result?

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

[@chessforevery1]

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

[@tanayburreja225]

how to approach when there are multiple payment modes ?

[@debashisjena6836]

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

[@veer400]

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

[@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");

[@jaykumarnilkanth2354]

how to get this questions on our sql server?

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

[@Daily_Dose_010]

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

[@shashank_mishra]

Thanks for pointing that out, corrected it 😊

[@akashnampalli7617]

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

[@lakshitsharma5533]

Please share data set

[@CORRCTME]

From where I can learn SQL

[@debashisjena6836]

Please like karo video ko

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

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

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