Layout

Month-over-month revenue change

PROBLEM 20 / 24 · ADVANCEDAll problems ↗

Aggregate completed-order revenue by month, then return month, revenue_cents, previous_revenue_cents, and change_cents. January has NULL for the previous revenue and change. Every January–June month has completed orders in these fixtures. Sort month ascending.

Outputmonthrevenue_centsprevious_revenue_centschange_cents

query.sql

DuckDB SQL

Restoring your saved draft…

⌘ / Ctrl + Enter to run

Run a query to see results.

Reveal solution & explanation
WITH monthly AS (SELECT strftime(ordered_at, '%Y-%m') AS month, SUM(order_total_cents) AS revenue_cents FROM orders WHERE status = 'completed' GROUP BY 1), history AS (SELECT *, LAG(revenue_cents) OVER (ORDER BY month) AS previous_revenue_cents FROM monthly) SELECT month, revenue_cents, previous_revenue_cents, revenue_cents - previous_revenue_cents AS change_cents FROM history ORDER BY month;

LAG sees rows, not calendar gaps. This fixture has every month; a sparse real series would need a calendar scaffold first.

Data & schema

order_id
INTEGER · PK
customer_id
INTEGER
ordered_at
TIMESTAMP
status
VARCHAR
order_total_cents
INTEGER
Table notes & relationships

One row per order. Only completed orders count as revenue unless a question says otherwise. Times are UTC.

  • customer_id → customers.customer_id
  • status completed / cancelled / pending

Fictional teaching data. Money is USD cents; timestamps are UTC.