KPMG SQL Interview Question | BIG 4 | Best Delivery Partner | Deepankar Pathak

[@krishnenduchakraborty750]

Awesome explanation! 🙏

[@deepankarpathak983]

Thanks 👍

[@Abhi4upskill]

Hey Deep,
Here is my approach
with cte1 as (
select brand_1 as brand_name,Winner from KPMG_Delievry_Partner
union all
select brand_2 as brand_name,Winner from KPMG_Delievry_Partner
union all
select brand_3 as brand_name,Winner from KPMG_Delievry_Partner
)
select brand_name,count(brand_name)as no_of_rides,
count(case when brand_name = Winner then 1 end) as Win_rides,
count(case when brand_name Winner then 1 end) as Loss_rides
from cte1
group by brand_name

[@devrajpatidar5927]

hey nice video here is my approach :-
with cte as (
select Brand_1 as brand_name,Winner from Delievry_Partner
union all
select Brand_2 as brand_name,Winner from Delievry_Partner
union all
select Brand_3 as brand_name,Winner from Delievry_Partner
)
select brand_name,count(*) as no_of_rides,count(case when brand_name = winner then Winner end) as win_rides,
count(*) - count(case when brand_name = winner then Winner end) as losses
from cte
group by brand_name
;

[@deepankarpathak983]

Thanks for posting the answer.

[@rajm5349]

Hi deepankar thanks for videos, can u explain any healthcare related projects or queires

[@deepankarpathak983]

Will upload soon

[@resonance.439]

HI Deepankar I love what you are doing, and I have a question,
can anyone get Remote job as data analyst, what's its scope of getting remote job compare to other tech jobs.
i know you may be busy but plz answer my this question, your reply means a lot. pls

[@deepankarpathak983]

Hi, Thanks for the valuable feedback.
Before searching for remote job, we should focus on improving the skills. As for Data Analytics you should be good with Power BI, SQL & Python (Just for beginner's).
If you are good with that, then solve some problem. Add some good project in your portfolio.
After that search for Job, Remote job is not that easy, but if you are highly skilled it will take some time. But I am dam sure you will get it.

[@vijaygupta7059]

with cte as
(
Select Brand_1 as Brand , case when Brand_1=winner then 1 else 0 end as winner_flg from Delievry_Partner
union all
Select Brand_2 as Brand, case when Brand_2=winner then 1 else 0 end as winner_flg from Delievry_Partner
union all
Select Brand_3 as Brand, case when Brand_3=winner then 1 else 0 end as winner_flg from Delievry_Partner
)
Select Brand, count(Brand) as no_of_rides , sum(winner_flg) as win_rides , count(Brand) - sum(winner_flg) as los_rides
from cte
group by Brand

[@deepankarpathak983]

That's great, Keep Practicing.

[@code4js]

hey plz tell me which sql should i start with like i want to become bussiness analyst , many says mysql or plsql or any other, brother plz help and reply plz .

[@code4js]

plz reply sir plz , i am very confused

[@deepankarpathak983]

It totally depends upon you, you can start with any of them but you need to solve the problem as much as you can. It will improve your problem solving skills.
Just pick one of them, learn some basis and start solving problems.

[@code4js]

@@deepankarpathak983thanks for the reply but if you want to suggest any one for the role specific to business analyst, what would you suggest?

[@deepankarpathak983]

Sure, for business analysts.
Points to be focused on :-
1) Master SQL
2) Do some practice on SQL
3) Learn any BI tools.
4) Then start applying for Business analysis.
5) If you time then do some python stuff.
But be good in one thing, cover a single thing at a time.

[@KapilKumar-hk9xk]

with cte as
(select brand_1 as brand, winner from Delievry_Partner union all
select brand_2 as brand, winner from Delievry_Partner union all
select brand_3 as brand, winner from Delievry_Partner)
,aggregated_agents as
(select brand, sum(case when brand = winner then 1 else 0 end) as no_of_win, count(case when brand = winner then 1 else 0 end) as total_played from cte group by brand)
select *, (total_played - no_of_win) as total_loss from aggregated_agents
;

[@user-gq6cg3ls7f]

My approach
with cte as(
select Brand_1 from Delievry_Partner
union all
select Brand_2 from Delievry_Partner
union all
select Brand_3 from Delievry_Partner
), cte2 as(
select Brand_1, count(*) no_of_rides from cte
group by Brand_1
)
select c2.Brand_1 as brand_name,no_of_rides,
case when c2.Brand_1=Winner then 1 else 0 end win_rides,
no_of_rides - case when c2.Brand_1=Winner then 1 else 0 end losses
from cte2 c2 full join Delievry_Partner dp
on c2.Brand_1=dp.Winner

[@deepankarpathak983]

Thanks for sharing