Repeat Purchase Rate — Solution & ExplanationProblems / Repeat Purchase Rate / EditorialApproach

Collapse to one row per customer. GROUP BY customer_id with COUNT(*) turns the order log into a per-customer order count.

Aggregate the aggregate. Over that CTE, COUNT(*) is the total customer count and a conditional count (FILTER clause or SUM(CASE WHEN order_count >= 2 ...)) is the repeat-customer count.

Rate. Multiply by 100.0 (forcing float math) before dividing, and round to 2 decimals.

Key InsightRates about *customers* cannot be computed directly from a table of *orders* — the grain is wrong. The two-step shape (per-entity aggregate, then a summary over entities) is the standard fix, and it is worth recognizing on sight: retention, repeat rate, and power-user metrics are all "aggregate of an aggregate" problems.Hints

This is an aggregation of an aggregation: first collapse orders to one row per customer with an order count, then summarize those counts.

A conditional count picks out the repeaters: COUNT(*) FILTER (WHERE order_count >= 2) in DuckDB, or SUM(CASE WHEN ... THEN 1 ELSE 0 END) anywhere.

The denominator is every distinct customer — which, after the per-customer GROUP BY, is simply COUNT(*) of the CTE. No second DISTINCT needed.

Solve Repeat Purchase Rate yourself →