Rank Customer Revenue
Part of the Connected Data section of Coddy's PostgreSQL journey. Lesson 22 of 25.
Challenge
MediumKeep every customer and the customer_id, name and paid_total columns from the previous report. Add revenue_rank using dense rank from highest paid_total to lowest; tied totals share rank. Display by customer_id.
The input tables are shown in the test panel. Each run starts from fresh data. Return the named columns in the stated order; do not hard-code the displayed rows.
Try it yourself
SELECT c.id AS customer_id, c.name, COALESCE(SUM(o.amount), 0) AS paid_total FROM customers AS c LEFT JOIN orders AS o ON o.customer_id = c.id AND o.status = 'paid' GROUP BY c.id, c.name ORDER BY c.id;All lessons in Connected Data
Practice on your own: PostgreSQL playground