Return one row per customer — the customer's earliest PAID order.
Customers without paid orders should not appear.
If two paid orders tie on order_ts for the same customer, keep the one with the lower order_id.
Returncustomer_idorder_ts::DATE AS order_dateamount_usd· any row order
About this problem
This problem practises picking exactly one row per entity with ROW_NUMBER, plus a filter that has to run at the right moment. The business question is each customer’s first purchase that was actually paid: for every customer with at least one paid order in orders, return the date of the earliest one and its amount_usd. You need a deterministic ordering – order_ts first, then the lower order_id when two paid orders share a timestamp – so that exactly one winner exists per customer and the result is the same every time it runs.
The most common slip is ranking first and filtering second. If you number each customer’s orders by order_ts and then keep rows where the rank is 1 and the status is paid, a customer whose very first order was cancelled drops out entirely, even if they paid for a later order – exactly what happens to c_007 in the sample. The result still looks clean, because every row it returns really is a paid first order; the error is the customers who are missing. The other trap is joining back to MIN(order_ts): when two paid orders share the earliest timestamp, the join returns both and the customer appears twice. The prompt gives the rule for that case, the lower order_id, and ordering by order_ts alone leaves the tie for the database to settle. Finally, ordering by the date after casting turns same-day orders into ties.
Read the lesson: Deduplication & Latest Row (P18) →