Layout

Each buyer’s first completed order

PROBLEM 13 / 24 · ADVANCEDAll problems ↗

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.

Outputcustomer_idfirst_order_idfirst_order_cents

query.sql

DuckDB SQL

Restoring your saved draft…

⌘ / Ctrl + Enter to run

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_id
  • status completed / cancelled / pending

Fictional teaching data. Money is USD cents; timestamps are UTC.