Karpov ДЗ: Кумулятивно новые пользователи по ОС

Условие

В таблице installs(install_id, user_id, install_date, os) записаны установки приложения. Для каждой даты и ОС посчитайте:

1. Количество новых пользователей в этот день.
2. Кумулятивное количество всех пользователей этой ОС с начала наблюдений.
3. Долю каждой ОС в общем кумулятивном пользовательском пуле на конкретную дату.

Решение

Подход

«Новый» = первая установка пользователя. Сначала отсекаем дубли, потом считаем агрегаты с window-функциями.

WITH first_install AS (
    SELECT user_id,
           os,
           MIN(install_date) AS install_date
    FROM installs
    GROUP BY user_id, os
),
daily AS (
    SELECT install_date,
           os,
           COUNT(*) AS new_users
    FROM first_install
    GROUP BY install_date, os
),
calendar AS (
    SELECT d::date AS install_date, o.os
    FROM generate_series(
        (SELECT MIN(install_date) FROM daily),
        (SELECT MAX(install_date) FROM daily),
        INTERVAL '1 day'
    ) d
    CROSS JOIN (SELECT DISTINCT os FROM daily) o
),
filled AS (
    SELECT c.install_date, c.os, COALESCE(d.new_users, 0) AS new_users
    FROM calendar c LEFT JOIN daily d USING (install_date, os)
),
cum AS (
    SELECT
        install_date, os, new_users,
        SUM(new_users) OVER (PARTITION BY os ORDER BY install_date) AS cum_users_by_os,
        SUM(new_users) OVER (ORDER BY install_date,
                             os ROWS BETWEEN UNBOUNDED PRECEDING
                             AND UNBOUNDED FOLLOWING)
            AS dummy_total_check
    FROM filled
)
SELECT
    install_date,
    os,
    new_users,
    cum_users_by_os,
    SUM(cum_users_by_os) OVER (PARTITION BY install_date) AS cum_users_all_os,
    ROUND(cum_users_by_os * 100.0
          / NULLIF(SUM(cum_users_by_os) OVER (PARTITION BY install_date), 0), 2)
        AS pct_of_total
FROM cum
ORDER BY install_date, os;

Разбор

• first_install — оставляет только первую дату установки на user_id. Это и есть «новый».
• calendar нужен, чтобы в датах без установок строки тоже были (иначе график будет «дырявым»).
• SUM(new_users) OVER (PARTITION BY os ORDER BY install_date) — running total по ОС.
• Доля = cum_by_os / cum_all_os * 100.

Альтернатива: без календаря через LAST_VALUE

Если нужны только даты, где была установка, и пропускать «нулевые» дни приемлемо, calendar можно опустить — running total всё равно корректный для непрерывной шкалы install_date, но визуализация будет «дырявой».

Подводные камни

1. Один user_id с двумя ОС: если кто-то ставил приложение и на Android, и на iOS — MIN(install_date) GROUP BY user_id, os посчитает его дважды (в каждой ОС). Часто это ожидаемое поведение, но согласовать с бизнесом.
2. COUNT(*) vs COUNT(DISTINCT user_id) в daily: после first_install дубли уже сняты, COUNT(*) корректен. На raw-таблице — обязательно DISTINCT.
3. Кумулятив без ORDER BY: SUM() OVER (PARTITION BY os) без ORDER BY даст итог по всей партиции — не running total.
4. Часовые пояса в install_date: timestamptz → date в сессионной TZ; разные клиенты могут видеть разные cohort dates.
5. Pct = 0 для пустой даты: после fillна пропуски pct = 0/0. NULLIF для безопасного деления.
6. generate_series в Snowflake/BQ — синтаксис другой (GENERATOR(ROWCOUNT=>...)); в Postgres — нативно.
7. Большая таблица: MIN(install_date) GROUP BY user_id дорого; на проде используйте ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY install_date) = 1.

Эталонный ответ

WITH first AS (
    SELECT user_id, os, MIN(install_date) AS dt
    FROM installs GROUP BY user_id, os
)
SELECT
    dt,
    os,
    COUNT(*)                                                AS new_users,
    SUM(COUNT(*)) OVER (PARTITION BY os ORDER BY dt)        AS cum_by_os,
    SUM(COUNT(*)) OVER (ORDER BY dt)                        AS cum_all,
    SUM(COUNT(*)) OVER (PARTITION BY os ORDER BY dt) * 100.0
       / NULLIF(SUM(COUNT(*)) OVER (ORDER BY dt), 0)        AS pct_of_total
FROM first
GROUP BY dt, os
ORDER BY dt, os;

Тонкости: MIN(install_date) — первая установка → «новый»; кумулятив через SUM() OVER (PARTITION BY os ORDER BY dt); для дашборда дополнить календарём generate_series.