First-ever order in March, over $100
Find customers whose first-ever completed order occurred in March 2026 and was strictly over $100. Return customer_id, first_order_id, and first_order_cents. Rank across all completed-order history; break timestamp ties by order_id. 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 AND ordered_at >= TIMESTAMP '2026-03-01' AND ordered_at < TIMESTAMP '2026-04-01' AND order_total_cents > 10000;
Filtering month first finds the first order inside March, not the first-ever order. Filtering amount first finds the first expensive order, not the actual first order.
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.