InterviewQuery — Churn по сегментам tenure

Условие

Таблица customers(customer_id, signup_date, last_activity_date, status) — status ∈ {'active', 'churned'}.

Постройте таблицу churn rate по сегментам tenure (срок с регистрации):

• < 1 месяца — new,
• 1-6 месяцев — young,
• 6-24 месяцев — mid,
• > 24 месяцев — mature.

Для каждого сегмента: число клиентов, число churned, churn_rate.

Решение

WITH tenure_seg AS (
  SELECT
    customer_id,
    status,
    CASE
      WHEN CURRENT_DATE - signup_date < INTERVAL '1 month'  THEN 'new'
      WHEN CURRENT_DATE - signup_date < INTERVAL '6 months' THEN 'young'
      WHEN CURRENT_DATE - signup_date < INTERVAL '24 months' THEN 'mid'
      ELSE 'mature'
    END AS segment
  FROM customers
)
SELECT
  segment,
  COUNT(*) AS customers,
  SUM(CASE WHEN status = 'churned' THEN 1 ELSE 0 END) AS churned,
  ROUND(
    100.0 * SUM(CASE WHEN status = 'churned' THEN 1 ELSE 0 END) / COUNT(*),
    2
  ) AS churn_rate
FROM tenure_seg
GROUP BY segment
ORDER BY
  CASE segment
    WHEN 'new' THEN 1
    WHEN 'young' THEN 2
    WHEN 'mid' THEN 3
    WHEN 'mature' THEN 4
  END;

Зачем второй CASE в ORDER BY

Лексическое ORDER BY segment даст mature, mid, new, young — не имеет смысла. Поэтому второй CASE для бизнес-порядка.

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

1. CURRENT_DATE - signup_date в Postgres даёт INTERVAL. В MySQL DATEDIFF(CURDATE(), signup_date) даёт дни как int — границы будут в днях, не месяцах.
2. Перекрытие границ. < 1 month и затем < 6 months — корректно, второй WHEN сработает только если первый не подошёл (порядок CASE). Если бы было <=/</< — границы могли бы пересекаться (зависит от смысла).
3. status мог быть NULL. Не попадёт в churned-счётчик; останется в COUNT(*). Решите — считать как active или исключать.
4. Tenure для churned vs current. Tenure обычно считается на момент churn для churned клиентов, а не на сегодня. Если хочется честно — LEAST(last_activity_date, CURRENT_DATE) - signup_date.

Эталонный ответ

CASE-сегментация по tenure, SUM(CASE WHEN status='churned'), отношение к COUNT(*). Сортировка по бизнес-порядку, а не лексическая.