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

SQL: Определение непрерывных периодов активности

2.0 Middle🔥 161 комментариев
#SQL и базы данных#Аналитика и метрики

Условие

Дана таблица с датами активности пользователей:

CREATE TABLE user_activity (
    user_id INT,
    activity_date DATE
);

Пример данных:

user_idactivity_date
12024-01-01
12024-01-02
12024-01-03
12024-01-05
12024-01-06
22024-01-01
22024-01-03

Задание:

  1. Найдите непрерывные периоды активности для каждого пользователя
  2. Рассчитайте длительность каждого периода
  3. Найдите самый длинный период активности для каждого пользователя

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

user_idperiod_startperiod_endduration
12024-01-012024-01-033
12024-01-052024-01-062

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

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

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

Решение

Задача и подход

Нужно обнаружить непрерывные периоды активности (консецутивные дни) для каждого пользователя. Используем window functions для вычисления разницы между текущей датой и рангом, чтобы идентифицировать разрывы.

Основной алгоритм:

  1. Сортируем активности по дате
  2. Вычисляем ранг каждой даты
  3. Вычитаем ранг из даты — если значение одинаковое для соседних строк, они в одном периоде
  4. Группируем по найденному периоду

Шаг 1: Определение непрерывных периодов

WITH activity_with_period AS (
    SELECT 
        user_id,
        activity_date,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY activity_date) AS rn,
        DATE_SUB(activity_date, INTERVAL ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY activity_date) DAY) AS period_key
    FROM user_activity
)
SELECT 
    user_id,
    MIN(activity_date) AS period_start,
    MAX(activity_date) AS period_end,
    DATE_DIFF(MAX(activity_date), MIN(activity_date), DAY) + 1 AS duration_days
FROM activity_with_period
GROUP BY user_id, period_key
ORDER BY user_id, period_start;

Объяснение:

  • ROW_NUMBER() присваивает порядковый номер каждой дате внутри user_id
  • Вычитая день из даты, получаем "якорь" периода (период_key одинаков для консецутивных дат)
  • GROUP BY период_key группирует непрерывные дни

Шаг 2: Самый длинный период для каждого пользователя

WITH activity_with_period AS (
    SELECT 
        user_id,
        activity_date,
        DATE_SUB(activity_date, INTERVAL ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY activity_date) DAY) AS period_key
    FROM user_activity
),
period_duration AS (
    SELECT 
        user_id,
        period_key,
        MIN(activity_date) AS period_start,
        MAX(activity_date) AS period_end,
        DATE_DIFF(MAX(activity_date), MIN(activity_date), DAY) + 1 AS duration_days
    FROM activity_with_period
    GROUP BY user_id, period_key
),
ranked_periods AS (
    SELECT 
        user_id,
        period_start,
        period_end,
        duration_days,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY duration_days DESC) AS rank
    FROM period_duration
)
SELECT 
    user_id,
    period_start,
    period_end,
    duration_days
FROM ranked_periods
WHERE rank = 1;

Альтернативный подход для PostgreSQL

WITH activity_with_period AS (
    SELECT 
        user_id,
        activity_date,
        activity_date - ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY activity_date) * INTERVAL '1 day' AS period_key
    FROM user_activity
)
SELECT 
    user_id,
    MIN(activity_date) AS period_start,
    MAX(activity_date) AS period_end,
    COUNT(*) AS duration_days
FROM activity_with_period
GROUP BY user_id, period_key
ORDER BY user_id, period_start;

Результаты для примера

Все периоды:

  • User 1, 2024-01-01 to 2024-01-03: 3 дня
  • User 1, 2024-01-05 to 2024-01-06: 2 дня
  • User 2, 2024-01-01: 1 день
  • User 2, 2024-01-03: 1 день

Самый длинный:

  • User 1: 2024-01-01 to 2024-01-03 (3 дня)
  • User 2: 2024-01-01 (1 день)
SQL: Определение непрерывных периодов активности | PrepBro