SQL: Найти пользователей без активности более 30 дней

Решение: Пользователи без активности более 30 дней

SQL-запрос (базовый вариант)

SELECT
  u.user_id,
  u.email,
  MAX(ua.activity_date) as last_activity_date,
  CURRENT_DATE - MAX(ua.activity_date) as days_inactive
FROM users u
LEFT JOIN user_activity ua ON u.user_id = ua.user_id
GROUP BY u.user_id, u.email
HAVING CURRENT_DATE - MAX(ua.activity_date) > 30
  OR MAX(ua.activity_date) IS NULL  -- Пользователи без активности вообще
ORDER BY days_inactive DESC;

Объяснение

Ключевые элементы:

1. LEFT JOIN user_activity — важно левое соединение, чтобы показать пользователей без активности

2. MAX(ua.activity_date) — последняя дата активности

3. CURRENT_DATE - MAX(ua.activity_date) — считаем дни неактивности

4. HAVING ... > 30 — только те, у кого более 30 дней неактивности

5. OR MAX(ua.activity_date) IS NULL — включаем пользователей, у которых нет записей активности вообще

Пример

Исходные данные:

users:
user_id | email
--------|---------------
1       | john@example.com
2       | jane@example.com
3       | bob@example.com

user_activity:
user_id | activity_date | activity_type
--------|---------------|---------------
1       | 2024-01-15    | login
1       | 2024-01-20    | purchase
2       | 2024-01-10    | login
3       | 2024-02-20    | login

Предположим, сегодня 2024-03-25

Результат:

user_id | email               | last_activity_date | days_inactive
--------|---------------------|--------------------|-
1       | john@example.com    | 2024-01-20         | 64
2       | jane@example.com    | 2024-01-10         | 74

Пользователь 3 (bob@example.com) был активен 2024-02-20, что 33 дня назад — он попадает

Вариант с детализацией

SELECT
  u.user_id,
  u.email,
  u.registration_date,
  MAX(ua.activity_date) as last_activity_date,
  CURRENT_DATE - MAX(ua.activity_date) as days_inactive,
  COUNT(ua.user_id) as total_activities,
  COUNT(DISTINCT ua.activity_date) as activity_days,
  STRING_AGG(DISTINCT ua.activity_type, ', ') as activity_types
FROM users u
LEFT JOIN user_activity ua ON u.user_id = ua.user_id
GROUP BY u.user_id, u.email, u.registration_date
HAVING CURRENT_DATE - MAX(ua.activity_date) > 30
  OR MAX(ua.activity_date) IS NULL
ORDER BY days_inactive DESC;

Это покажет:

• Когда пользователь зарегистрировался
• Все типы активности
• Сколько дней он был активен

Разделение на сегменты

WITH inactive_users AS (
  SELECT
    u.user_id,
    u.email,
    MAX(ua.activity_date) as last_activity_date,
    CURRENT_DATE - MAX(ua.activity_date) as days_inactive
  FROM users u
  LEFT JOIN user_activity ua ON u.user_id = ua.user_id
  GROUP BY u.user_id, u.email
)
SELECT
  user_id,
  email,
  last_activity_date,
  days_inactive,
  CASE
    WHEN days_inactive IS NULL THEN 'Never Active'
    WHEN days_inactive <= 30 THEN 'Active'
    WHEN days_inactive <= 60 THEN 'At Risk (30-60 дней)'
    WHEN days_inactive <= 90 THEN 'Inactive (60-90 дней)'
    ELSE 'Churned (90+ дней)'
  END as user_segment
FROM inactive_users
WHERE days_inactive > 30 OR days_inactive IS NULL
ORDER BY days_inactive DESC NULLS FIRST;

Результат:

user_id | email                | last_activity_date | days_inactive | user_segment
--------|----------------------|--------------------|-
5       | null                 | null               | null          | Never Active
3       | bob@example.com      | 2024-02-20         | 33            | At Risk (30-60 дней)
1       | john@example.com     | 2024-01-20         | 64            | Inactive (60-90 дней)
2       | jane@example.com     | 2024-01-10         | 74            | Inactive (60-90 дней)

Вариант с типом активности

SELECT
  u.user_id,
  u.email,
  MAX(ua.activity_date) as last_activity_date,
  CURRENT_DATE - MAX(ua.activity_date) as days_inactive,
  FIRST_VALUE(ua.activity_type) OVER (PARTITION BY u.user_id ORDER BY ua.activity_date DESC) as last_activity_type
FROM users u
LEFT JOIN user_activity ua ON u.user_id = ua.user_id
GROUP BY u.user_id, u.email, ua.activity_type
HAVING CURRENT_DATE - MAX(ua.activity_date) > 30
ORDER BY days_inactive DESC;

Поиск пользователей для re-engagement кампании

WITH inactive_users AS (
  SELECT
    u.user_id,
    u.email,
    MAX(ua.activity_date) as last_activity_date,
    CURRENT_DATE - MAX(ua.activity_date) as days_inactive
  FROM users u
  LEFT JOIN user_activity ua ON u.user_id = ua.user_id
  GROUP BY u.user_id, u.email
)
SELECT
  user_id,
  email,
  last_activity_date,
  days_inactive
FROM inactive_users
WHERE days_inactive BETWEEN 30 AND 180  -- Целевой размер для re-engagement
ORDER BY days_inactive DESC;

Это даст список пользователей, которым стоит отправить реактивирующееся письмо (не слишком давно ушли, но уже неактивны).

Вариант с историей по дням

WITH last_activity AS (
  SELECT
    u.user_id,
    u.email,
    MAX(ua.activity_date) as last_activity_date
  FROM users u
  LEFT JOIN user_activity ua ON u.user_id = ua.user_id
  GROUP BY u.user_id, u.email
)
SELECT
  user_id,
  email,
  last_activity_date,
  CURRENT_DATE - last_activity_date as days_inactive,
  CASE
    WHEN last_activity_date IS NULL THEN 'Никогда не активен'
    WHEN CURRENT_DATE - last_activity_date < 7 THEN 'Активен на этой неделе'
    WHEN CURRENT_DATE - last_activity_date < 14 THEN 'Активен на прошлой неделе'
    WHEN CURRENT_DATE - last_activity_date < 30 THEN 'Активен этот месяц'
    WHEN CURRENT_DATE - last_activity_date < 60 THEN 'Активен в прошлом месяце'
    ELSE 'Неактивен 60+ дней'
  END as activity_period
FROM last_activity
WHERE CURRENT_DATE - last_activity_date > 30 OR last_activity_date IS NULL
ORDER BY days_inactive DESC NULLS FIRST;

Производительность

Для оптимизации больших таблиц:

-- Добавьте индекс на (user_id, activity_date)
CREATE INDEX idx_user_activity_user_date 
ON user_activity(user_id, activity_date DESC);

-- Это ускорит MAX(activity_date) поиск для каждого пользователя