Аналитические SQL-запросы¶

База данных: MySQL 8, 10.99.87.62, схема vpn Подключение: ./scripts/ssh-internal.sh 10.99.87.62 'docker exec vpn-db mysql -uvpn -pPASSWORD vpn -e "QUERY"'

Пароль: [Vaultwarden](https://vault.shivavpn.io)

Выручка¶

Ежедневная выручка (последние 30 дней)¶

SELECT
  DATE(created_at) as date,
  COUNT(*) as transactions,
  SUM(total_amount_usd) as revenue_usd,
  SUM(total_amount_rub) as revenue_rub,
  ROUND(AVG(total_amount_usd), 2) as avg_usd
FROM payment
WHERE created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
GROUP BY DATE(created_at)
ORDER BY date DESC;

Месячная выручка (MRR, последние 12 месяцев)¶

SELECT
  DATE_FORMAT(created_at, '%Y-%m') as month,
  COUNT(*) as payments,
  SUM(total_amount_usd) as mrr_usd,
  ROUND(AVG(total_amount_usd), 2) as avg_payment
FROM payment
WHERE created_at >= DATE_SUB(NOW(), INTERVAL 12 MONTH)
GROUP BY month ORDER BY month DESC;

По способу оплаты¶

SELECT
  payment_type,
  COUNT(*) as transactions,
  SUM(total_amount_usd) as total_usd,
  ROUND(SUM(total_amount_usd) / (SELECT SUM(total_amount_usd) FROM payment) * 100, 2) as pct
FROM payment
GROUP BY payment_type ORDER BY total_usd DESC;

Исторические данные (15 Nov 2025): SBP 56.6%, Cypix 18.6%, NowPayments 11.7%, Apple 8%, Stripe 4%.

Рост выручки по месяцам¶

SELECT month, revenue_usd,
  LAG(revenue_usd) OVER (ORDER BY month) as prev,
  ROUND((revenue_usd - LAG(revenue_usd) OVER (ORDER BY month)) /
    NULLIF(LAG(revenue_usd) OVER (ORDER BY month), 0) * 100, 2) as growth_pct
FROM (
  SELECT DATE_FORMAT(created_at, '%Y-%m') as month, SUM(total_amount_usd) as revenue_usd
  FROM payment WHERE created_at >= DATE_SUB(NOW(), INTERVAL 12 MONTH)
  GROUP BY month
) t ORDER BY month DESC;

Пользователи¶

Статусы аккаунтов¶

SELECT pay_status, COUNT(*) as count,
  ROUND(COUNT(*) / (SELECT COUNT(*) FROM account WHERE deleted_at IS NULL) * 100, 2) as pct
FROM account WHERE deleted_at IS NULL
GROUP BY pay_status ORDER BY count DESC;

Новые аккаунты по дням¶

SELECT DATE(created_at) as date, COUNT(*) as new_accounts,
  SUM(COUNT(*)) OVER (ORDER BY DATE(created_at)) as cumulative
FROM account
WHERE created_at >= DATE_SUB(NOW(), INTERVAL 90 DAY)
GROUP BY DATE(created_at) ORDER BY date DESC;

Конверсия Trial → Paid¶

SELECT
  COUNT(*) as total,
  SUM(CASE WHEN pay_status='TRIAL' THEN 1 ELSE 0 END) as trial,
  SUM(CASE WHEN pay_status IN ('PAID','PAID_WAS_TRIAL') THEN 1 ELSE 0 END) as paid,
  ROUND(
    SUM(CASE WHEN pay_status IN ('PAID','PAID_WAS_TRIAL') THEN 1 ELSE 0 END) /
    NULLIF(SUM(CASE WHEN pay_status='TRIAL' THEN 1 ELSE 0 END), 0) * 100, 2
  ) as conversion_rate
FROM account WHERE deleted_at IS NULL;

Воронка конверсии¶

SELECT 'Total Accounts' as stage, COUNT(*) as count, 100.00 as pct
FROM account WHERE deleted_at IS NULL
UNION ALL
SELECT 'Trial Started', COUNT(*),
  ROUND(COUNT(*) / (SELECT COUNT(*) FROM account WHERE deleted_at IS NULL) * 100, 2)
FROM account WHERE pay_status IN ('TRIAL','PAID_WAS_TRIAL')
UNION ALL
SELECT 'Converted to Paid', COUNT(*),
  ROUND(COUNT(*) / (SELECT COUNT(*) FROM account WHERE deleted_at IS NULL) * 100, 2)
FROM account WHERE pay_status IN ('PAID','PAID_WAS_TRIAL')
UNION ALL
SELECT 'Active Subscription', COUNT(*),
  ROUND(COUNT(*) / (SELECT COUNT(*) FROM account WHERE deleted_at IS NULL) * 100, 2)
FROM account a JOIN subscription s ON a.id=s.account_id
WHERE s.subscription_status='ACTIVE';

Подписки¶

Активные по типу¶

SELECT subscription_type, subscription_status, is_with_trial,
  COUNT(*) as count,
  ROUND(COUNT(*) / (SELECT COUNT(*) FROM subscription) * 100, 2) as pct
FROM subscription
GROUP BY subscription_type, subscription_status, is_with_trial
ORDER BY count DESC;

Churn rate по месяцам¶

SELECT DATE_FORMAT(created_at, '%Y-%m') as month,
  COUNT(*) as total,
  SUM(CASE WHEN subscription_status='CANCELED' THEN 1 ELSE 0 END) as canceled,
  ROUND(SUM(CASE WHEN subscription_status='CANCELED' THEN 1 ELSE 0 END) / COUNT(*) * 100, 2) as churn_rate
FROM subscription
WHERE created_at >= DATE_SUB(NOW(), INTERVAL 12 MONTH)
GROUP BY month ORDER BY month DESC;

LTV и ARPU¶

ARPU по месяцам¶

SELECT DATE_FORMAT(p.created_at, '%Y-%m') as month,
  COUNT(DISTINCT p.account_id) as paying_users,
  SUM(p.total_amount_usd) as revenue_usd,
  ROUND(SUM(p.total_amount_usd) / COUNT(DISTINCT p.account_id), 2) as arpu_usd
FROM payment p
WHERE p.created_at >= DATE_SUB(NOW(), INTERVAL 12 MONTH)
GROUP BY month ORDER BY month DESC;

Когортное удержание¶

SELECT DATE_FORMAT(a.created_at, '%Y-%m') as cohort,
  COUNT(*) as size,
  ROUND(SUM(CASE WHEN a.valid_to > DATE_ADD(a.created_at, INTERVAL 1 MONTH) THEN 1 ELSE 0 END) / COUNT(*) * 100, 2) as ret_1m,
  ROUND(SUM(CASE WHEN a.valid_to > DATE_ADD(a.created_at, INTERVAL 3 MONTH) THEN 1 ELSE 0 END) / COUNT(*) * 100, 2) as ret_3m,
  ROUND(SUM(CASE WHEN a.valid_to > DATE_ADD(a.created_at, INTERVAL 6 MONTH) THEN 1 ELSE 0 END) / COUNT(*) * 100, 2) as ret_6m
FROM account a
WHERE a.pay_status IN ('PAID','PAID_WAS_TRIAL')
  AND a.created_at >= DATE_SUB(NOW(), INTERVAL 12 MONTH)
GROUP BY cohort ORDER BY cohort DESC;

Топ-100 клиентов по LTV¶

SELECT HEX(a.id) as account_id, a.pay_status,
  COUNT(p.id) as payments,
  SUM(p.total_amount_usd) as ltv_usd,
  DATEDIFF(NOW(), a.created_at) as days_active
FROM account a
LEFT JOIN payment p ON a.id=p.account_id
WHERE a.pay_status IN ('PAID','PAID_WAS_TRIAL')
GROUP BY a.id HAVING payments > 0
ORDER BY ltv_usd DESC LIMIT 100;

Диагностика¶

Сводка по таблицам¶

SELECT 'Accounts' as t, COUNT(*) as total,
  COUNT(CASE WHEN deleted_at IS NULL THEN 1 END) as active
FROM account
UNION ALL
SELECT 'Subscriptions', COUNT(*),
  COUNT(CASE WHEN subscription_status='ACTIVE' THEN 1 END)
FROM subscription
UNION ALL
SELECT 'Payments', COUNT(*), NULL FROM payment
UNION ALL
SELECT 'Servers', COUNT(*),
  COUNT(CASE WHEN is_active=1 THEN 1 END)
FROM server;

Активность за последние периоды¶

SELECT 'Last Hour' as period,
  (SELECT COUNT(*) FROM payment WHERE created_at > DATE_SUB(NOW(), INTERVAL 1 HOUR)) as payments,
  (SELECT COUNT(*) FROM account WHERE created_at > DATE_SUB(NOW(), INTERVAL 1 HOUR)) as signups,
  (SELECT SUM(total_amount_usd) FROM payment WHERE created_at > DATE_SUB(NOW(), INTERVAL 1 HOUR)) as revenue_usd
UNION ALL
SELECT 'Last 24h',
  (SELECT COUNT(*) FROM payment WHERE created_at > DATE_SUB(NOW(), INTERVAL 24 HOUR)),
  (SELECT COUNT(*) FROM account WHERE created_at > DATE_SUB(NOW(), INTERVAL 24 HOUR)),
  (SELECT SUM(total_amount_usd) FROM payment WHERE created_at > DATE_SUB(NOW(), INTERVAL 24 HOUR))
UNION ALL
SELECT 'Last 7d',
  (SELECT COUNT(*) FROM payment WHERE created_at > DATE_SUB(NOW(), INTERVAL 7 DAY)),
  (SELECT COUNT(*) FROM account WHERE created_at > DATE_SUB(NOW(), INTERVAL 7 DAY)),
  (SELECT SUM(total_amount_usd) FROM payment WHERE created_at > DATE_SUB(NOW(), INTERVAL 7 DAY));

Производительность запросов (Tips)¶

Добавить индексы если slow: CREATE INDEX idx_payment_created_at ON payment(created_at)
Перед DELETE/UPDATE: SET SESSION sql_safe_updates=1
Для больших выборок: добавить LIMIT
Проверить план запроса: EXPLAIN SELECT ...

Ссылки¶

Grafana дашборды: https://monitor.karmann.tech (папки Business, Product)
Мониторинг: docs/operations/monitoring.md

См. также: Подключение к production БД · База данных платежей · Маппинг аккаунтов