Calculate Repeat Purchase Rate in SQL

Question

Given an `orders` table, write a SQL query to calculate the `repeat_purchase_rate` as a percentage: (customers with > 1 order / total customers) * 100, rounded to 2 decimal places.

Accepted Answer

Optimal Solution:
WITH customer_orders AS (
  SELECT 
    customer_id,
    COUNT(order_id) AS order_count
  FROM orders
  GROUP BY customer_id
)
SELECT 
  ROUND(
    100.0 * COUNT(CASE WHEN order_count > 1 THEN 1 END) / COUNT(*),
    2
  ) AS repeat_purchase_rate_pct
FROM customer_orders;

Technical Explanation:
A CTE counts orders per customer. The outer query divides repeat customers (order_count > 1) by total customers, multiplying by 100.0 to prevent integer truncation.

order_id	customer_id
1	101
2	101
3	102
4	103