Running customer spending
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.
Output
customer_idorder_idcumulative_centsquery.sql
DuckDB SQLRestoring your saved draft…
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_idstatuscompleted / cancelled / pending
Fictional teaching data. Money is USD cents; timestamps are UTC.