Level 6 · Boss: first orders and repeat purchases

Retention questions all start the same way: find each customer’s first event, then look at what happens after it. CTEs are made for this, because each step has a clear grain.

Step 1: first order per customer

SELECT customer_id, CAST(MIN(order_date) AS DATE) AS first_order_date
FROM orders
WHERE status = 'completed'
GROUP BY customer_id;

One row per customer. Whether a cancelled order counts as “acquiring” a customer is a business definition. Megan’s ticket says completed only, so the filter goes in before the MIN. Put it after, and a customer whose first order was cancelled would be acquired on the wrong date.

Step 2: new customers per month

With the first orders in a CTE, “new customers per month” is a DATE_TRUNC and a count. The year filter applies to first_order_at, not to all orders: a customer who first bought in 2023 and again in 2025 is not new in 2025. Mixing up those two filters is one of the most common errors in acquisition reporting.

Step 3: 90-day repeat rate

Define the cohort (first completed order in 2024), then flag each member who came back:

CASE WHEN EXISTS (
  SELECT 1 FROM orders AS o
  WHERE o.customer_id = c.customer_id
    AND o.status = 'completed'
    AND o.order_date >  c.first_order_at
    AND o.order_date <= c.first_order_at + INTERVAL '90 days'
) THEN 1 ELSE 0 END AS repeated

EXISTS checks for at least one qualifying order, so a customer with five repeat orders is still counted once. A JOIN here would fan out and inflate the numerator. The strict > excludes the first order itself. The rate is the sum of flags over the cohort size.

Using a fixed window (90 days from each customer’s own first order) is deliberate. “Ordered again in 2024” would give December customers almost no time to return and January customers a whole year, which makes the rate depend on when people joined rather than on how loyal they are.

INTERVAL '90 days' works in DuckDB and Postgres. MySQL and BigQuery write INTERVAL 90 DAY (BigQuery inside TIMESTAMP_ADD).

Making it a story

A single repeat rate is useful; a trend is better. Group step 3 by the month of the first order and you have a cohort table: did customers acquired in the spring come back more than those from the holiday season? That is the next chapter’s boss, built with window functions.