Menu
CoddyTech

Add the Latest Channel

Part of the Connected Data section of Coddy's PostgreSQL journey. Lesson 23 of 25.

challenge icon

Challenge

Medium

Keep every customer and all four columns from the previous step. Add latest_paid_channel from that customer most recent paid order by date, then id descending. Use none when no paid order exists. Preserve customer_id display order.

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

WITH revenue AS (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) SELECT customer_id, name, paid_total, DENSE_RANK() OVER (ORDER BY paid_total DESC) AS revenue_rank FROM revenue ORDER BY customer_id;

All lessons in Connected Data

Practice on your own: PostgreSQL playground