LeetCode Medium 2051 'Category of Each Member in the Store' Interview SQL Question with Explanation

In this video I solve and explain a medium difficulty leetcode SQL question using MySQL query. This question has been asked in Apple, Facebook, Amazon, Google, Adobe, Microsoft, Adobe interviews or what we popularly call FAANG interviews.
I explain the related concept as well. This question includes points to keep in mind to develop SQL queries.

LeetCode is the best platform to help you enhance your skills, expand your knowledge and prepare for technical interviews.

If you found this helpful, Like and Subscribe to the channel for more content.

#LeetCodeSQL #FAANG #SQLinterviewQuestions

[florincopaci]

Thank you! i love you way of solving SQL problems! All the best!

[datazerohero]

Great videos, but it is better to show the output to understand better how we need to query to get the desired result

[meenayegan]

Great Logic!

A tiny tweak here:
;with cte as
(select m.member_id, m.name,
(count(charged_amount)
/cast(count(m.member_id) as float) *100)
as cus_percentage
from members m
left join
visits v
on m.member_id = v.member_id
left join
purchases p
on p.cvisit_id = v.visit_id
group by m.member_id, m.name
)
select member_id, name,
case

when cus_percentage = 0
then 'bronze'

when cus_percentage > = 80
then 'diamond'

when cus_percentage >= 50
then 'gold'
when cus_percentage < 50
then 'silver'

end
as category
from cte

[sachigs]

Thank you for the amazing videos. This has been super helpful.
Do we have to group by Name as well Or member id is enough?

[muddassirnazar]

Optimum Solution:select m.member_id, m.name,
case when count(v.member_id)=0 then 'Bronze'
when then 'Silver'
when
and then 'Gold'
else 'Diamond' end as category
from members m left join visits v
on m.member_id=v.member_id
left join purchases p on v.visit_id=p.visit_id
group by m.member_id, m.name

[vbmudalige]

Thanks for the video. Here is my solution;
with cte as (
select
V.visit_id,
V.member_id,
count(V.visit_id) over (
partition by
member_id
) as visit_count,
sum(
case
when charged_amount <> 0 then 1
else 0
end
) over (
partition by
member_id
) as purchase_count
from
Visits V
left join
Purchases P
on V.visit_id = P.visit_id
)
select
M.member_id,
M.name,
case
when C.visit_id is null then 'Bronze'
when 100 * C.purchase_count / C.visit_count < 50 then 'Silver'
when 100 * C.purchase_count / C.visit_count >= 80 then 'Diamond'
else 'Gold'
end as category
from
Members M
left join
cte C
on M.member_id = C.member_id
group by
1, 2, 3
order by
null