Краткий ответ

Сначала нужно определить для каждого клиента дату его первого заказа, затем посчитать новых клиентов по каждой дате и только после этого применить оконную сумму по датам. Накопительный итог по ежедневному COUNT(DISTINCT client_id) некорректен: один и тот же клиент будет учитываться повторно в разные дни.

Исторический контекст

GROUP BY хорошо решает задачу агрегации внутри отдельных групп, но сам по себе не сохраняет состояние между группами. Для накопительных и других аналитических расчётов появились оконные функции, которые вычисляют значение относительно набора строк, не схлопывая их в одну строку.

Однако оконная сумма не устраняет дубли уникальных сущностей автоматически. Поэтому для накопительного числа уникальных клиентов требуется отдельный подготовительный этап — приведение каждого клиента к одной строке с его первой датой.

Постановка проблемы

Если считать уникальных клиентов отдельно по каждому дню, клиент, совершивший заказы в понедельник и вторник, попадёт в оба дневных результата. Простое суммирование таких дневных значений даст число появлений клиентов, а не число клиентов, накопленных к дате.

Ошибка особенно опасна в отчётах о росте клиентской базы: показатель будет завышаться пропорционально повторным покупкам. Дополнительная проблема возникает при нескольких заказах одного клиента в один день — их нужно сначала свернуть до одной даты первого появления.

Подробное решение

Сначала группировка по client_id находит минимальную дату заказа каждого клиента. После этого каждая строка означает ровно одного клиента и его дату первого появления.

Затем выполняется вторая агрегация: число клиентов с каждой датой первого заказа. Оконная функция SUM с сортировкой по дате складывает количество новых клиентов от начала периода до текущей даты.

WITH first_seen AS (
    SELECT client_id, MIN(order_date) AS first_date
    FROM orders
    GROUP BY client_id
), daily_new AS (
    SELECT first_date, COUNT(*) AS new_clients
    FROM first_seen
    GROUP BY first_date
)
SELECT
    first_date,
    SUM(new_clients) OVER (
        ORDER BY first_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cumulative_clients
FROM daily_new
ORDER BY first_date;

Оконная сумма здесь работает уже не по исходным заказам, а по агрегированным датам первого появления. Это важно: порядок вычисления логически такой — сначала GROUP BY в first_seen, затем GROUP BY в daily_new, затем оконная функция.

Если в отчёте должны присутствовать также даты без новых клиентов, нужен отдельный календарь или таблица дат. Её соединяют с daily_new, заменяют пропуски нулями и только затем вычисляют оконную сумму; иначе даты без событий просто отсутствуют в результате.

Временная зона и точность даты должны быть определены заранее. Если order_date хранится как момент времени, преобразование к календарной дате должно использовать согласованную временную зону, иначе первый день клиента может определиться неверно.

Ситуация из практики

Маркетинговый отчёт должен показать рост клиентской базы по дням. Вариант с ежедневным COUNT(DISTINCT client_id) прост, но при накоплении повторно учитывает клиентов, которые возвращаются в последующие дни. Вариант с коррелированным подзапросом, считающим уникальных клиентов для каждой даты, логически корректен, но может многократно просматривать большой набор заказов.

Выбранное решение — вычислить first_seen, агрегировать новых клиентов по дате и применить оконную сумму. Оно делает устранение дублей явным, обычно лучше масштабируется и позволяет отдельно проверять каждый этап. Если после исправления исторических данных дата первого заказа клиента изменилась, накопительный ряд за последующие даты также должен быть пересчитан.

Что кандидаты часто упускают

Почему нельзя просто суммировать ежедневные значения COUNT(DISTINCT client_id)?

Потому что уникальность действует только внутри конкретного дня. Один клиент, присутствующий в пяти днях, будет добавлен в пять дневных подсчётов, хотя в накопительной базе должен учитываться один раз. Для накопления нужно сначала выбрать единственную дату, на которой клиент впервые появляется.
Что изменится, если в данных есть даты без новых клиентов?

Без календаря такие даты не попадут в результат: оконная функция обрабатывает только существующие строки. Для непрерывного временного ряда сначала формируют все требуемые даты, соединяют их с числом новых клиентов, подставляют ноль вместо NULL, а затем считают накопительную сумму.
Почему оконную сумму нужно применять после агрегации новых клиентов?

Если применить её непосредственно к заказам, один клиент может иметь несколько строк, а значит, будет посчитан несколько раз. Предварительная агрегация гарантирует, что вход оконной функции содержит одну строку на дату и число клиентов, впервые появившихся в эту дату.