Layout

Running customer spending

PROBLEM 19 / 24 · ADVANCEDAll problems ↗

For each completed order, return customer_id, order_id, and cumulative_cents through that order. Use ordered_at then order_id for deterministic ordering and an explicit ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW frame. Row order does not matter.

Outputcustomer_idorder_idcumulative_cents

query.sql

DuckDB SQL

Restoring your saved draft…

⌘ / Ctrl + Enter to run

Run a query to see results.

Reveal solution & explanation
SELECT customer_id, order_id, SUM(order_total_cents) OVER (PARTITION BY customer_id ORDER BY ordered_at, order_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_cents FROM orders WHERE status = 'completed';

The explicit frame documents accumulation semantics. A unique tie-breaker prevents ambiguous progression for same-timestamp orders.

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.