Level 7 · Boss: the yearly customer status report

This boss turns three set operations into the customer bridge every growth review uses: where this year’s customers came from, and where last year’s went.

Step 1: retained and lapsed

Two buyer lists, one per year, and two comparisons:

• retained = 2024 buyers INTERSECT 2025 buyers
• lapsed = 2024 buyers EXCEPT 2025 buyers

The buyer CTEs are one row per order, so count DISTINCT customer_id when you want the size of a list. The set operations already return distinct ids. At Harbor & Pine, 997 customers bought in 2024; 752 of them bought again (75.4%) and 245 did not. Retained plus lapsed must equal the 2024 total, which is a free sanity check.

Step 2: four statuses, one table

2025 had 1,447 buyers. Two of the groups are easy, new and retained. The third is the one people miss: customers who bought before 2024, skipped 2024 and came back in 2025. Calling them “new” overstates acquisition, so they get their own label, reactivated:

SELECT customer_id FROM (
  SELECT customer_id FROM buyers_2025
  INTERSECT
  SELECT customer_id FROM buyers_before_2025
) AS b
EXCEPT
SELECT customer_id FROM buyers_2024

Wrapping the first operation in a subquery makes the order of operations explicit. (INTERSECT binds more tightly than UNION and EXCEPT in standard SQL, but parentheses are cheaper than remembering.)

The groups do not overlap, so UNION ALL with a status label stacks them, and a GROUP BY counts each: 752 retained, 635 new, 245 lapsed, 60 reactivated. New + retained + reactivated = 1,447, every 2025 buyer exactly once.

Step 3: what each group spent

Now join a set-operation result to real data. Aggregate 2025 revenue to one row per customer first, then join it to the labels. Retained customers bring in about $1.49 million at roughly $1,987 each, new customers about $758,000 at roughly $1,193, and the 60 reactivated customers about $50,000. Lapsed customers have no 2025 revenue, so the inner join drops them, which is what the task asks for.

Common mistakes

• Counting orders instead of customers in step 1 (the starter returns 3,673, the number of completed 2024 orders).
• Putting new and reactivated together, or letting a customer land in two groups. Check that the group totals add up to the yearly buyer counts.
• Joining labels to order_items and counting rows, which turns customers into order lines.

In SQL Server

INTERSECT, EXCEPT and UNION ALL work the same way, and derived tables need an alias as here. The scalar subqueries in step 1 are fine in T-SQL, but 100.0 * matters more there: with two integer counts, / would truncate the percentage to 0.