AMAZON DATA ENGINEER Interview Question | SQL Intermediate Question 10

Question - Find total transactions through Cash and Online mode.
The question has been solved using aggregate functions and case condition.
#amazon #netflix #facebook #google #microsoft #flipkart #sqldeveloper #dataanalytics #dataengineering #sqlserver #placement #placements #interviewquestions #ml #ai

[@nareshgiduthuri7847]

Thanks for your content.
Can you please add below in description which would help
create table merchant(merchant_id varchar(20) , amount int, payment_mode varchar(20));
insert into merchant values ('m1',200,'cash'),('m2',520,'online'),('m1',700,'online'),('m3',1400,'online'),('m2',50,'cash'),('m1',300,'cash');
select * from merchant;

[@king-hc6vi]

Can we do partition on Payment mode and seperate the online and cash mode with row number as 1 for cash and row number as 2 for online....
And then case statement in which sum of R1 gives the value and puts 0 for online.
Similarly another case statement in which sum of R2 gives the value and puts 0 for cash.
Please let me know if this approach is correct or not.

[@vijaygupta7059]

same solution as you in MSSQL DB
Select merchant_id
,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 payments
group by merchant_id
order by sum(case when payment_mode = 'cash' then amount else 0 end) desc

[@ushasworld5223]

Could u please provide data set so that we can practice along with u

[@Code-Con]

Will definitely provide in the upcoming videos.

[@apurvasaraf5828]

data?

[@Code-Con]

Will definitely provide in the upcoming videos.

[@SKAMRANPASHA]

Group by merchant_id , cash_mode