Posted by Sylvia, April 6, 2022, 8:59 p.m.
#54 Coding preference
My answer is slightly different from the solution. Is there any efficiency consideration that drives the solution to be formulated this way?
My answer:
SELECT CASE WHEN revenue >= 150 THEN 'high'
WHEN revenue >= 100 AND revenue < 150 THEN 'medium'
ELSE 'low' END AS customer_group,
COUNT(*) as count
FROM
(
SELECT a.customer_id, SUM(b.amount) as revenue
FROM customer a
LEFT JOIN payment b
ON a.customer_id = b.customer_id
GROUP BY 1
) X
GROUP BY 1
Solution:
SELECT customer_group, COUNT(*)
FROM (
SELECT
C.customer_id,
CASE WHEN SUM(P.amount) >= 150 THEN 'high'
WHEN SUM(P.amount) >= 100 THEN 'medium'
ELSE 'low' END customer_group
FROM customer C
LEFT JOIN payment P
ON P.customer_id = C.customer_id
GROUP BY C.customer_id
) X
GROUP BY customer_group
;