Each customer's first order
For each customer who has ordered, show their first order — its id, date and amount — and how many days after signing up they placed it.
What it tests
Picking exactly one row per group (the first event per user) with ROW_NUMBER, and joining back for extra columns. Used for activation, onboarding and funnel questions.
The pattern
First (or latest) row per group. "First purchase", "most recent login", "latest status", "earliest", one per user.
How to approach it
- Number each customer's orders by date:
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date, order_id). - Add order_id as a tiebreaker in case two orders share a date.
- Keep row number 1 in an outer query.
- Join customers for the name and sign-up date, and work out the days between.
Edge cases to mention
- Two orders on the same first day: the tiebreaker (order_id) decides; say which one you keep.
- Cancelled first orders: confirm whether they count.
- Customers with no orders: not in the result — LEFT JOIN from customers if they should appear.
Common mistakes
GROUP BY customer_idwithMIN(order_date)andamountin SELECT — the amount isn't tied to the first order.- Forgetting PARTITION BY, so only one order in the whole table gets number 1.
- No tiebreaker in ROW_NUMBER, so the answer can change between runs.
Not true: MIN(order_date) with amount in the same SELECT returns the amount of the first order.
In fact: MIN finds the earliest date, but the amount beside it is not tied to that row. Number each customer's orders with ROW_NUMBER and keep number 1.
Part of Unit 8: SQL interview problems, from easy to hard in the free SQL course.