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

  1. Number each customer's orders by date: ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date, order_id).
  2. Add order_id as a tiebreaker in case two orders share a date.
  3. Keep row number 1 in an outer query.
  4. Join customers for the name and sign-up date, and work out the days between.

Edge cases to mention

Common mistakes

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.