Level 1 · Customer retention in SQL

How an analyst reads this ticket

“Do customers come back?” sounds like one number. It becomes a useful number only after three decisions:

1. What counts as an order. Here, completed only. A cancelled order is not a purchase, and a customer whose only order was refunded did not really start a relationship with the store.
2. Who is in the denominator. Customers who bought at least once. The ~230 sign-ups who never ordered are an acquisition problem, not a retention one. The 18 duplicate customer records have no orders, so they drop out on their own.
3. How long each customer has had to come back. This is the decision most first attempts miss.

Step 1: cohorts by first order

A cohort is a group of customers who started at the same time. The first step is always the same: reduce orders to one row per customer with their first order and their order count. Everything after that is a GROUP BY over this small table. COUNT(*) FILTER (WHERE n_orders >= 2) counts the repeat customers inside each cohort.

Look at the result before moving on. The 2025 cohort has a much lower repeat rate, and it is tempting to report that retention is collapsing. It isn’t, necessarily: someone who first bought in November 2025 has had a few weeks to order again, while a 2023 customer has had three years. That is right-censoring, and it makes any all-time repeat rate unfair to recent cohorts.

Step 2: a fair 90-day window

The standard fix is a fixed window: “repeat within 90 days of the first order”, counted only for customers who have been around for at least 90 days. The data ends on 2025-12-31, so customers whose first order is before 2025-10-01 are eligible.

To find each customer’s second order, number their completed orders with ROW_NUMBER(), ordered by order_date and then order_id so ties are broken the same way every time. Conditional MAX pivots rn = 1 and rn = 2 into two columns. A one-time buyer has NULL as second_order, and NULL <= anything is not true, so they correctly fail the test without any special handling.

Compare times with first_order + INTERVAL 90 DAY rather than date_diff('day', ...). date_diff counts calendar-day boundaries, while interval arithmetic measures exactly 90 × 24 hours. Both are defensible; using the interval makes the SQL and pandas versions agree to the customer.

Step 3: consumer vs business

Join the per-customer table to customers for the segment. The join is one-to-one, because both sides have one row per customer, so nothing is double counted. Business accounts are few, but they order far more often, which is why the board should see them separately: a blended repeat rate mostly describes consumers.

SQL and pandas side by side

Idea	SQL	pandas
one row per customer	GROUP BY customer_id in a CTE	groupby("customer_id").agg(...)
nth order	ROW_NUMBER() OVER (PARTITION BY ...)	sort_values + groupby().cumcount()
plus 90 days	+ INTERVAL 90 DAY	+ pd.Timedelta(days=90)
year of a date	year(first_order)	.dt.year

Pitfalls

• Counting non-completed orders. A cancelled first order would put a customer in the wrong cohort.
• Joining customers to orders first and then counting. COUNT(*) over that join counts orders, not customers; aggregate to one row per customer first.
• Comparing cohorts with different exposure. Report the 90-day rate beside the all-time rate, and say why.