添加最新渠道
本课属于 Coddy PostgreSQL 学习旅程的“关联数据”单元。第 23 课,共 25 课。
挑战
中等保留每位客户和上一步中的全部四列。添加该客户最新已付款订单的 latest_paid_channel,按日期降序排列,然后按 id 降序排列。如果没有已付款订单,则使用 none。保留 customer_id 的显示顺序。
输入表显示在测试面板中。每次运行都会从全新数据开始。按指定顺序返回命名列,不要对显示的行进行硬编码。
亲手试试
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;关联数据中的所有课
自己练习: PostgreSQL 在线编辑器