Layout

Fill missing months before accumulating

PROBLEM 24 / 24 · ADVANCEDAll problems ↗

For customer 57, return all six months January–June 2026, including months with zero completed spending. Return month (YYYY-MM), spend_cents, and cumulative_cents. Sort month ascending. Build a month calendar before applying the running sum.

Outputmonthspend_centscumulative_cents

query.sql

DuckDB SQL

Restoring your saved draft…

⌘ / Ctrl + Enter to run

Run a query to see results.

Reveal solution & explanation
WITH calendar AS (SELECT strftime(d, '%Y-%m') AS month FROM generate_series(DATE '2026-01-01', DATE '2026-06-01', INTERVAL '1 month') AS t(d)), spend AS (SELECT strftime(ordered_at, '%Y-%m') AS month, SUM(order_total_cents) AS spend_cents FROM orders WHERE status = 'completed' AND customer_id = 57 GROUP BY 1), filled AS (SELECT c.month, COALESCE(s.spend_cents, 0) AS spend_cents FROM calendar c LEFT JOIN spend s ON s.month = c.month) SELECT month, spend_cents, SUM(spend_cents) OVER (ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_cents FROM filled ORDER BY month;

A window function cannot invent absent calendar rows. Fill the calendar first, and distinguish ‘zero spending’ from a missing month in the source.

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.