Forum

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
;

Answers

Your solution looks good, either way looks fine for me.

Thank you so much, Leon!

Sylvia, April 7, 2022, 1:24 p.m.

Thank you so much, Leon!

Sylvia, April 7, 2022, 1:35 p.m.
SQLPad user avatar

Leon (949)

April 7, 2022, 12:42 p.m.