Как написать один запрос, который одновременно считает количество новых и количество вернувшихся пользователей по дням?

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

Нужно сначала для каждого пользователя определить дату первой активности (через MIN(event_date) в CTE), затем в основном запросе помечать событие как «новый пользователь», если его дата совпадает с датой первой активности, и как «вернувшийся» — если не совпадает, и сгруппировать по дате и этой метке.

Как это работает подробнее

сравнение event_date = first_seen_date для каждой строки активности однозначно классифицирует её на «первый визит» (новый пользователь в этот день) или «повторный визит» (вернувшийся пользователь); агрегация COUNT(DISTINCT user_id) по дате и метке даёт обе метрики за один проход по данным.

Пример кода

question-69.sqlКопировать

WITH first_seen AS (
  SELECT user_id, MIN(event_date) AS first_date
  FROM activity GROUP BY user_id
)
SELECT a.event_date,
       COUNT(DISTINCT CASE WHEN a.event_date = f.first_date THEN a.user_id END) AS new_users,
       COUNT(DISTINCT CASE WHEN a.event_date != f.first_date THEN a.user_id END) AS returning_users
FROM activity a
JOIN first_seen f ON f.user_id = a.user_id
GROUP BY a.event_date
ORDER BY a.event_date;

Что использовать на практике

• Практика. важно явно договориться об определении «нового» и «вернувшегося» пользователя относительно выбранного события — активность в приложении, покупка, регистрация — разные определения дают разные абсолютные числа, хотя общая логика расчёта одна и та же.
• Проверка. Проверка: new_users, просуммированные по всем дням, должны равняться общему числу уникальных пользователей в activity.

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

• Частая ошибка. считают «новых пользователей» по дате регистрации в системе, а «активность» — по другому событию (например, покупке), без явного разделения этих двух разных понятий, что приводит к путанице в терминологии между командами, обсуждающими один и тот же отчёт.