PrepBro
← Назад к вопросам

SQL: Определить новых vs возвращающихся пользователей

2.3 Middle🔥 141 комментариев
#SQL и базы данных#Метрики продукта

Условие

У вас есть таблица sessions:

  • session_id (integer)
  • user_id (integer)
  • session_date (date)
  • session_duration (integer) - длительность в секундах

Для каждого дня определите количество новых пользователей (первая сессия) и возвращающихся пользователей.

Ожидаемый результат:

Таблица с колонками:

  • date
  • new_users (количество пользователей с первой сессией в этот день)
  • returning_users (количество пользователей, у которых уже были сессии ранее)
  • total_users (всего уникальных пользователей)

Источник: типовая задача на собеседованиях аналитиков

Комментарии (1)

🐱
claude-haiku-4.5PrepBro AI23 мар. 2026 г.(ред.)

Ответ сгенерирован нейросетью и может содержать ошибки

Решение

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

Логика решения:

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

SQL запрос:

WITH first_sessions AS (
  -- Определяем дату первой сессии для каждого пользователя
  SELECT 
    user_id,
    MIN(session_date) as first_session_date
  FROM sessions
  GROUP BY user_id
),
user_activity AS (
  -- Определяем статус пользователя в каждый день
  SELECT 
    s.session_date,
    s.user_id,
    CASE 
      WHEN s.session_date = fs.first_session_date THEN 'new'
      ELSE 'returning'
    END as user_status
  FROM sessions s
  LEFT JOIN first_sessions fs ON s.user_id = fs.user_id
)
-- Итоговая агрегация
SELECT 
  session_date as date,
  COUNT(DISTINCT CASE WHEN user_status = 'new' THEN user_id END) as new_users,
  COUNT(DISTINCT CASE WHEN user_status = 'returning' THEN user_id END) as returning_users,
  COUNT(DISTINCT user_id) as total_users
FROM user_activity
GROUP BY session_date
ORDER BY session_date;

Пояснения:

first_sessions — CTE, где для каждого пользователя находим минимальную дату сессии. Это дата первого визита.

user_activity — второе CTE, где проверяем каждую сессию: если дата сессии совпадает с датой первой сессии пользователя, он новый; иначе возвращающийся.

Финальный SELECT — группируем по дате и считаем уникальных пользователей в каждой категории, используя DISTINCT чтобы избежать двойного подсчёта (пользователь может иметь несколько сессий в день).

Альтернативный подход (без CTE):

SELECT 
  s.session_date as date,
  COUNT(DISTINCT CASE 
    WHEN s.session_date = MIN(s.session_date) OVER (PARTITION BY s.user_id) 
    THEN s.user_id 
  END) as new_users,
  COUNT(DISTINCT CASE 
    WHEN s.session_date > MIN(s.session_date) OVER (PARTITION BY s.user_id) 
    THEN s.user_id 
  END) as returning_users,
  COUNT(DISTINCT s.user_id) as total_users
FROM sessions s
GROUP BY s.session_date
ORDER BY s.session_date;

Используется оконная функция MIN() OVER (PARTITION BY user_id) для определения даты первой сессии. Первый вариант с CTE более читаемый для больших объёмов данных.