Cumulative Distinct Customers

Problem

Schema: Orders(order_id INT, customer_id INT, order_date DATE). For each date with at least one order, return the date and the cumulative count of distinct customers through that date.

Constraints

• Days without orders don't appear.

Approach — Core Patterns

This is a Core Patterns problem. The idea: pick the data structure best matched to the constraints and solve it in one clean pass. Work through the reference code below line by line, then re-derive it yourself in the editor — that's how the pattern sticks.

Complexity: see the walkthrough below.

Solution code

SQL

WITH first_seen AS (
  SELECT customer_id, MIN(order_date) AS first_date
  FROM Orders
  GROUP BY customer_id
),
new_per_day AS (
  SELECT first_date AS d, COUNT(*) AS new_customers
  FROM first_seen
  GROUP BY first_date
)
SELECT d AS order_date,
       SUM(new_customers) OVER (ORDER BY d
                                ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_distinct
FROM new_per_day
ORDER BY d;

Practice it

Reading a solution isn't the same as being able to write it under pressure. Open this problem in the in-browser editor, hide the solution, and solve it from scratch — your code runs against real test cases instantly.