SQL: Найти пользователей с регулярными платежами

Решение

Это задача на выявление регулярных плательщиков (recurring revenue users) — критична для MRR прогнозов в подписочных моделях.

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

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

SQL запрос для выявления регулярных плательщиков:

WITH monthly_payments AS (
  SELECT 
    user_id,
    DATE_TRUNC('week', payment_date)::date as payment_week,
    COUNT(*) as weekly_count,
    SUM(amount) as weekly_amount
  FROM payments
  WHERE payment_date >= CURRENT_DATE - INTERVAL '30 days'
  GROUP BY user_id, DATE_TRUNC('week', payment_date)
),
user_regularity AS (
  SELECT 
    user_id,
    COUNT(DISTINCT payment_week) as weeks_with_payments,
    COUNT(*) as total_payments,
    SUM(weekly_amount) as total_amount,
    ROUND(AVG(weekly_amount), 2) as avg_weekly_payment
  FROM monthly_payments
  GROUP BY user_id
)
SELECT 
  user_id,
  weeks_with_payments,
  total_payments,
  total_amount,
  avg_weekly_payment,
  CASE 
    WHEN weeks_with_payments >= 4 THEN 'Very Regular (4+ weeks)'
    WHEN weeks_with_payments = 3 THEN 'Regular (3 weeks)'
    WHEN weeks_with_payments = 2 THEN 'Semi-Regular (2 weeks)'
    ELSE 'Occasional'
  END as regularity_segment
FROM user_regularity
WHERE weeks_with_payments >= 2
ORDER BY weeks_with_payments DESC, total_amount DESC;

Вариант с расширенным анализом (включая variance):

WITH week_stats AS (
  SELECT 
    user_id,
    DATE_TRUNC('week', payment_date)::date as payment_week,
    SUM(amount) as weekly_total
  FROM payments
  WHERE payment_date >= CURRENT_DATE - INTERVAL '30 days'
  GROUP BY user_id, payment_week
),
user_patterns AS (
  SELECT 
    user_id,
    COUNT(DISTINCT payment_week) as weeks_with_payments,
    COUNT(*) as total_payments,
    SUM(weekly_total) as total_amount,
    ROUND(AVG(weekly_total), 2) as avg_payment,
    ROUND(100.0 * STDDEV(weekly_total) / AVG(weekly_total), 2) as payment_variance_pct
  FROM week_stats
  GROUP BY user_id
)
SELECT 
  user_id,
  weeks_with_payments,
  total_payments,
  ROUND(total_amount, 2) as avg_payment,
  COALESCE(payment_variance_pct, 0) as variance_pct,
  CASE 
    WHEN weeks_with_payments >= 4 AND payment_variance_pct <= 25 THEN 'Highly Predictable'
    WHEN weeks_with_payments >= 4 THEN 'Regular but Variable'
    WHEN weeks_with_payments >= 3 THEN 'Semi-Regular'
    ELSE 'Occasional'
  END as segment
FROM user_patterns
WHERE weeks_with_payments >= 2
ORDER BY weeks_with_payments DESC, total_amount DESC;

Сравнение регулярных vs нерегулярных:

WITH user_patterns AS (
  SELECT 
    user_id,
    COUNT(DISTINCT DATE_TRUNC('week', payment_date)) as weeks_with_payments,
    COUNT(*) as total_payments,
    SUM(amount) as total_amount,
    CASE WHEN COUNT(DISTINCT DATE_TRUNC('week', payment_date)) >= 4 THEN 'Regular' ELSE 'Irregular' END as user_type
  FROM payments
  WHERE payment_date >= CURRENT_DATE - INTERVAL '30 days'
  GROUP BY user_id
)
SELECT 
  user_type,
  COUNT(*) as user_count,
  ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 2) as pct_of_users,
  ROUND(AVG(total_amount), 2) as avg_ltv_30days,
  SUM(total_amount) as total_revenue,
  ROUND(100.0 * SUM(total_amount) / SUM(SUM(total_amount)) OVER (), 2) as pct_of_revenue
FROM user_patterns
GROUP BY user_type;

Прогноз MRR (Monthly Recurring Revenue):

WITH regular_users AS (
  SELECT 
    user_id,
    ROUND(AVG(amount), 2) as avg_weekly_payment
  FROM payments
  WHERE payment_date >= CURRENT_DATE - INTERVAL '30 days'
  GROUP BY user_id
  HAVING COUNT(DISTINCT DATE_TRUNC('week', payment_date)) >= 4
)
SELECT 
  COUNT(*) as regular_users_count,
  ROUND(SUM(avg_weekly_payment) * 4.33, 2) as projected_monthly_revenue
FROM regular_users;

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

ОБЩАЯ СТАТИСТИКА:
Total Users Last 30 Days: 5,000
Regular Users (4+ weeks): 800 (16%)
Semi-Regular Users (2-3 weeks): 1,200 (24%)
Occasional Users: 3,000 (60%)

ПО РЕГУЛЯРНОСТИ:
user_id | weeks_with_payments | avg_payment | segment
1001    | 4                   | $15.50      | Highly Predictable
1002    | 4                   | $18.00      | Regular but Variable
1003    | 3                   | $22.00      | Semi-Regular

СРАВНЕНИЕ:
user_type | user_count | avg_ltv_30days | pct_of_revenue
Regular   | 800        | $310.00        | 68%
Irregular | 4,200      | $54.00         | 32%

Ключевые метрики:

Regularity (регулярность):

• Highly Predictable — платит каждую неделю, сумма стабильна (variance < 25%)
• Regular — платит 4+ недели, но суммы варьируют
• Semi-Regular — платит 2-3 недели
• Occasional — платит нерегулярно

Применение в бизнесе:

1. MRR прогнозирование — Regular users дают стабильный доход
2. Retention стратегия — Semi-Regular нужно привести в Regular
3. LTV расчет — Regular users имеют 3-5x выше LTV
4. Риск управление — Occasional users высокий риск отока