Skip to content

2051. The Category of Each Member in the Store 👍

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
SELECT
  Members.member_id,
  Members.name,
  CASE
    WHEN COUNT(Visits.visit_id) = 0 THEN 'Bronze'
    WHEN COUNT(Purchases.visit_id) / COUNT(Visits.visit_id) >= 0.8 THEN 'Diamond'
    WHEN COUNT(Purchases.visit_id) / COUNT(Visits.visit_id) >= 0.5 THEN 'Gold'
    ELSE 'Silver'
  END AS category
FROM Members
LEFT JOIN Visits
  USING (member_id)
LEFT JOIN Purchases
  USING (visit_id)
GROUP BY 1;