Answers and explanations · Compare rows with window functions

Exercise 09a

Ben has 1890 and Chen 1270. ROW_NUMBER produces exactly two display positions; DENSE_RANK would express a different tie policy.

WITH line_totals AS (
 SELECT order_id,SUM(quantity*unit_price-line_discount) AS line_sales
 FROM aabha_sql.order_items GROUP BY order_id
), refund_totals AS (
 SELECT order_id,SUM(amount) AS refunded
 FROM aabha_sql.refunds WHERE refund_date < DATE '2025-04-01' GROUP BY order_id
), order_net AS (
 SELECT o.order_id,o.customer_id,o.order_date,o.channel,
 l.line_sales,COALESCE(r.refunded,0) AS refunded,
 l.line_sales-COALESCE(r.refunded,0) AS net_sales
 FROM aabha_sql.orders AS o
 JOIN line_totals AS l ON l.order_id=o.order_id
 LEFT JOIN refund_totals AS r ON r.order_id=o.order_id
 WHERE o.status='completed' AND o.order_date >= DATE '2025-01-01'
   AND o.order_date < DATE '2025-04-01'
), customer_sales AS (
 SELECT customer_id,SUM(net_sales) AS net_sales FROM order_net GROUP BY customer_id
), ranked AS (
 SELECT customer_id,net_sales,ROW_NUMBER() OVER (ORDER BY net_sales DESC,customer_id) AS position
 FROM customer_sales
)
SELECT customer_id,net_sales,position FROM ranked WHERE position<=2 ORDER BY position;

Expected output:

Runnable answer: sql/solutions/09a.sql

Exercise 09b

February changes by -45, or -2.78%; March by 875, or 55.56%. January has no prior period and remains null. This fixture has no missing month; sparse calendars need additional handling.

WITH line_totals AS (
 SELECT order_id,SUM(quantity*unit_price-line_discount) AS line_sales
 FROM aabha_sql.order_items GROUP BY order_id
), refund_totals AS (
 SELECT order_id,SUM(amount) AS refunded
 FROM aabha_sql.refunds WHERE refund_date < DATE '2025-04-01' GROUP BY order_id
), order_net AS (
 SELECT o.order_id,o.customer_id,o.order_date,o.channel,
 l.line_sales,COALESCE(r.refunded,0) AS refunded,
 l.line_sales-COALESCE(r.refunded,0) AS net_sales
 FROM aabha_sql.orders AS o
 JOIN line_totals AS l ON l.order_id=o.order_id
 LEFT JOIN refund_totals AS r ON r.order_id=o.order_id
 WHERE o.status='completed' AND o.order_date >= DATE '2025-01-01'
   AND o.order_date < DATE '2025-04-01'
), monthly AS (
 SELECT date_trunc('month',order_date)::date AS month,SUM(net_sales) AS net_sales FROM order_net GROUP BY 1
), compared AS (
 SELECT month,net_sales,LAG(net_sales) OVER (ORDER BY month) AS previous_sales FROM monthly
)
SELECT month::text AS month,net_sales-previous_sales AS absolute_change,
 ROUND(100.0*(net_sales-previous_sales)/NULLIF(previous_sales,0),2) AS percent_change
FROM compared ORDER BY month;

Expected output:

Runnable answer: sql/solutions/09b.sql