Библиотека баз данных

🎯 Хитрая SQL-задача для продвинутых
Тема: переходы состояний пользователей через inactive → banned → active?

У нас есть таблица логов смены статусов пользователей:

CREATE TABLE user_status_log (
    user_id INT,
    status TEXT,         -- 'active', 'inactive', 'banned'
    changed_at TIMESTAMP
);

Каждый раз, когда пользователь меняет статус, добавляется запись.

🔍 Найди пользователей, которые хотя бы один раз:

• стали inactive
• потом были banned
• и либо так и остались забанены, либо позже перешли в active

Важно:
• Статусы могут меняться много раз
• Нас интересует первая последовательность inactive → banned (→ optional `active`)
• Если пользователь не вернулся в `active`, всё равно считаем, что условие выполнено

---

🧠 Решение с оконными функциями:

WITH ranked_status AS (
  SELECT
    user_id,
    status,
    changed_at,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY changed_at) AS rn
  FROM user_status_log
),
status_with_next AS (
  SELECT
    user_id,
    status,
    changed_at,
    LEAD(status) OVER (PARTITION BY user_id ORDER BY changed_at) AS next_status,
    LEAD(changed_at) OVER (PARTITION BY user_id ORDER BY changed_at) AS next_changed_at
  FROM ranked_status
),
transitions AS (
  SELECT
    user_id,
    changed_at AS from_time,
    next_changed_at AS to_time,
    status AS from_status,
    next_status AS to_status
  FROM status_with_next
  WHERE next_status IS NOT NULL
),
flagged_users AS (
  SELECT DISTINCT user_id
  FROM (
    SELECT
      user_id,
      MAX(CASE WHEN from_status = 'inactive' AND to_status = 'banned' THEN 1 ELSE 0 END) AS went_inactive_then_banned,
      MIN(CASE WHEN from_status = 'banned' AND to_status = 'active' THEN 1 ELSE 0 END) AS banned_then_active
    FROM transitions
    GROUP BY user_id
  ) t
  WHERE went_inactive_then_banned = 1
)
SELECT *
FROM flagged_users;

🧩 Почему это интересно?

• Используются оконные функции LEAD(), ROW_NUMBER()
• Нужно отслеживать последовательные пары статусов
• Объединяем логику в несколько CTE-слоёв
• Придётся думать не только о текущем статусе, но и о контексте (что было до и что после)

Подобные задачи — хороший способ прокачать мышление о временных событиях в SQL.

❤6👎2🤔2👍1

www.tgoop.com/sql_lib/297

2K viewsJun 11 at 12:10

🎯 Хитрая SQL-задача для продвинутых
Тема: переходы состояний пользователей через inactive → banned → active?

У нас есть таблица логов смены статусов пользователей:

CREATE TABLE user_status_log (
    user_id INT,
    status TEXT,         -- 'active', 'inactive', 'banned'
    changed_at TIMESTAMP
);

WITH ranked_status AS (
  SELECT
    user_id,
    status,
    changed_at,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY changed_at) AS rn
  FROM user_status_log
),
status_with_next AS (
  SELECT
    user_id,
    status,
    changed_at,
    LEAD(status) OVER (PARTITION BY user_id ORDER BY changed_at) AS next_status,
    LEAD(changed_at) OVER (PARTITION BY user_id ORDER BY changed_at) AS next_changed_at
  FROM ranked_status
),
transitions AS (
  SELECT
    user_id,
    changed_at AS from_time,
    next_changed_at AS to_time,
    status AS from_status,
    next_status AS to_status
  FROM status_with_next
  WHERE next_status IS NOT NULL
),
flagged_users AS (
  SELECT DISTINCT user_id
  FROM (
    SELECT
      user_id,
      MAX(CASE WHEN from_status = 'inactive' AND to_status = 'banned' THEN 1 ELSE 0 END) AS went_inactive_then_banned,
      MIN(CASE WHEN from_status = 'banned' AND to_status = 'active' THEN 1 ELSE 0 END) AS banned_then_active
    FROM transitions
    GROUP BY user_id
  ) t
  WHERE went_inactive_then_banned = 1
)
SELECT *
FROM flagged_users;

BY Библиотека баз данных