Write a query to compute the cumulative distinct count of new customers acquired each month.

Answer

For each month, count distinct customers whose first-ever order occurred in that month or earlier - a running count of when new customers were first acquired.

Solution

WITH first_orders AS (
  SELECT customer_id, MIN(DATE_TRUNC('month', order_date)) AS acquisition_month
  FROM Orders
  GROUP BY customer_id
),
monthly_new AS (
  SELECT acquisition_month, COUNT(DISTINCT customer_id) AS new_customers
  FROM first_orders
  GROUP BY acquisition_month
)
SELECT acquisition_month, new_customers,
       SUM(new_customers) OVER (ORDER BY acquisition_month) AS cumulative_customers
FROM monthly_new
ORDER BY acquisition_month;

Step by Step

1. first_orders finds each customer's very first order month - their acquisition month - using MIN() per customer.
2. monthly_new counts how many distinct customers were newly acquired in each month.
3. SUM(new_customers) OVER (ORDER BY acquisition_month) is a running total, adding up new customers from every month up to and including the current one - the cumulative distinct customer count.

Why "Cumulative Distinct" Isn't the Same as a Naive Running Count

-- INCORRECT: this counts every order per month cumulatively, not distinct new customers
SELECT DATE_TRUNC('month', order_date) AS month,
       COUNT(*) OVER (ORDER BY DATE_TRUNC('month', order_date)) AS wrong_running_count
FROM Orders;

A naive running total over raw order rows would repeatedly re-count returning customers every time they order again - the entire trick of this question is first collapsing each customer down to their single acquisition month before running the cumulative sum, so each customer is only ever counted once, in the month they first appeared.

Key Exam Traps

• "Cumulative distinct count" is the real difficulty here - you cannot simply wrap COUNT(DISTINCT customer_id) in a running-total window function directly, because DISTINCT inside a window aggregate isn't reliably supported for a growing frame the way a plain SUM is. Pre-aggregating to one row per customer's first month (as shown) sidesteps this limitation entirely.
• Months with zero new customers simply don't appear as a row in monthly_new - if a continuous month-by-month timeline (including zero-growth months) is required, a calendar/date-spine table would need to be joined in.
• Customer re-acquisition after churn isn't handled by this logic - a customer's MIN(order_date) only reflects their very first order ever, even if they later churned and returned; that's a distinct concept from "new" in a period-over-period reactivation sense.