Each buyer’s first completed order
For every customer with a completed order, return customer_id, first_order_id, and first_order_cents for their first-ever completed order. Break timestamp ties by the smaller order_id. Pending/cancelled orders do not count. Row order does not matter.
Output
customer_idfirst_order_idfirst_order_centsquery.sql
DuckDB SQLRestoring your saved draft…
Run a query to see results.
Reveal solution & explanation
WITH ranked AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY ordered_at, order_id) AS rn FROM orders WHERE status = 'completed') SELECT customer_id, order_id AS first_order_id, order_total_cents AS first_order_cents FROM ranked WHERE rn = 1;
ROW_NUMBER selects exactly one record per buyer. The order_id tie-breaker makes the result deterministic when timestamps tie.
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.