SQL для финансового анализа: P&L, когорты, unit-экономика

SQL — основной инструмент финансового аналитика для работы с транзакционными данными. Разберём ключевые финансовые метрики и как их считать в SQL.

MRR (Monthly Recurring Revenue)

-- Текущий MRR: сумма активных подписок
SELECT
    DATE_TRUNC('month', CURRENT_DATE) AS month,
    COUNT(DISTINCT user_id) AS paying_users,
    SUM(monthly_amount) AS mrr,
    SUM(monthly_amount) * 12 AS arr
FROM subscriptions
WHERE status = 'active'
  AND started_at <= CURRENT_DATE
  AND (ended_at IS NULL OR ended_at > CURRENT_DATE);

-- MRR по месяцам (история)
SELECT
    DATE_TRUNC('month', s.started_at) AS month,
    SUM(s.monthly_amount) AS new_mrr,
    COUNT(*) AS new_subscriptions
FROM subscriptions s
WHERE s.status IN ('active', 'cancelled')
GROUP BY 1
ORDER BY 1;

MRR движение: New / Expansion / Churned

WITH monthly_revenue AS (
    SELECT
        user_id,
        DATE_TRUNC('month', period_start) AS month,
        monthly_amount
    FROM subscriptions
    WHERE status IN ('active', 'cancelled')
),
mrr_changes AS (
    SELECT
        curr.month,
        curr.user_id,
        curr.monthly_amount AS curr_mrr,
        prev.monthly_amount AS prev_mrr
    FROM monthly_revenue curr
    LEFT JOIN monthly_revenue prev
        ON prev.user_id = curr.user_id
        AND prev.month = curr.month - INTERVAL '1 month'
)
SELECT
    month,
    SUM(CASE WHEN prev_mrr IS NULL THEN curr_mrr ELSE 0 END) AS new_mrr,
    SUM(CASE WHEN prev_mrr IS NOT NULL AND curr_mrr > prev_mrr
             THEN curr_mrr - prev_mrr ELSE 0 END) AS expansion_mrr,
    SUM(CASE WHEN prev_mrr IS NOT NULL AND curr_mrr < prev_mrr
             THEN curr_mrr - prev_mrr ELSE 0 END) AS contraction_mrr,
    SUM(CASE WHEN curr_mrr = 0 AND prev_mrr IS NOT NULL
             THEN -prev_mrr ELSE 0 END) AS churned_mrr,
    SUM(curr_mrr) AS total_mrr
FROM mrr_changes
GROUP BY 1
ORDER BY 1;

P&L (Profit and Loss) отчёт

WITH revenue AS (
    SELECT
        DATE_TRUNC('month', created_at) AS month,
        SUM(amount) AS gross_revenue,
        SUM(amount * 0.03) AS payment_fees,    -- комиссии платёжного провайдера
        SUM(amount * 0.20) AS vat              -- НДС
    FROM payments
    WHERE status = 'completed'
    GROUP BY 1
),
costs AS (
    SELECT
        DATE_TRUNC('month', date) AS month,
        SUM(CASE WHEN category = 'server' THEN amount END) AS infrastructure,
        SUM(CASE WHEN category = 'salary' THEN amount END) AS personnel,
        SUM(CASE WHEN category = 'marketing' THEN amount END) AS marketing,
        SUM(amount) AS total_costs
    FROM expenses
    GROUP BY 1
)
SELECT
    r.month,
    r.gross_revenue,
    r.gross_revenue - r.payment_fees - r.vat AS net_revenue,
    c.infrastructure,
    c.personnel,
    c.marketing,
    c.total_costs,
    (r.gross_revenue - r.payment_fees - r.vat) - c.total_costs AS operating_profit,
    ROUND(
        ((r.gross_revenue - r.payment_fees - r.vat) - c.total_costs)
        / NULLIF(r.gross_revenue - r.payment_fees - r.vat, 0) * 100,
        1
    ) AS operating_margin_pct
FROM revenue r
JOIN costs c USING (month)
ORDER BY r.month;

CAC (Customer Acquisition Cost)

WITH monthly_marketing AS (
    SELECT
        DATE_TRUNC('month', date) AS month,
        SUM(amount) AS spend
    FROM expenses
    WHERE category = 'marketing'
    GROUP BY 1
),
monthly_new_customers AS (
    SELECT
        DATE_TRUNC('month', first_payment_at) AS month,
        COUNT(*) AS new_customers
    FROM users
    WHERE first_payment_at IS NOT NULL
    GROUP BY 1
)
SELECT
    m.month,
    m.spend AS marketing_spend,
    c.new_customers,
    ROUND(m.spend / NULLIF(c.new_customers, 0), 0) AS cac
FROM monthly_marketing m
JOIN monthly_new_customers c USING (month)
ORDER BY m.month;

LTV / CAC Payback Period

WITH user_cohorts AS (
    SELECT
        user_id,
        DATE_TRUNC('month', first_payment_at) AS cohort_month,
        first_payment_at
    FROM users
    WHERE first_payment_at IS NOT NULL
),
cohort_revenue AS (
    SELECT
        uc.cohort_month,
        DATE_TRUNC('month', p.created_at) AS payment_month,
        EXTRACT(EPOCH FROM (DATE_TRUNC('month', p.created_at) - uc.cohort_month))
            / (30.44 * 24 * 3600) AS months_since_start,
        SUM(p.amount) AS revenue,
        COUNT(DISTINCT uc.user_id) AS cohort_size
    FROM user_cohorts uc
    JOIN payments p ON p.user_id = uc.user_id
    WHERE p.status = 'completed'
    GROUP BY 1, 2, 3
),
cumulative AS (
    SELECT
        cohort_month,
        months_since_start::INTEGER AS month_number,
        SUM(revenue) OVER (PARTITION BY cohort_month ORDER BY months_since_start) AS cumulative_revenue,
        cohort_size,
        SUM(revenue) OVER (PARTITION BY cohort_month ORDER BY months_since_start) / cohort_size AS ltv_per_user
    FROM cohort_revenue
)
SELECT
    cohort_month,
    month_number,
    ROUND(ltv_per_user, 0) AS cumulative_ltv,
    cohort_size
FROM cumulative
WHERE month_number <= 12
ORDER BY cohort_month, month_number;

Когортный анализ выручки

-- Выручка когорт: сколько платят пользователи разных месяцев регистрации
SELECT
    cohort_month,
    SUM(CASE WHEN month_number = 0  THEN revenue END) AS month_0,
    SUM(CASE WHEN month_number = 1  THEN revenue END) AS month_1,
    SUM(CASE WHEN month_number = 2  THEN revenue END) AS month_2,
    SUM(CASE WHEN month_number = 3  THEN revenue END) AS month_3,
    SUM(CASE WHEN month_number = 6  THEN revenue END) AS month_6,
    SUM(CASE WHEN month_number = 12 THEN revenue END) AS month_12,
    SUM(revenue) AS total_ltv,
    COUNT(DISTINCT user_id) AS cohort_size,
    ROUND(SUM(revenue) / COUNT(DISTINCT user_id), 0) AS avg_ltv
FROM (
    SELECT
        DATE_TRUNC('month', u.created_at) AS cohort_month,
        p.user_id,
        EXTRACT(MONTH FROM AGE(DATE_TRUNC('month', p.created_at),
                               DATE_TRUNC('month', u.created_at)))::INTEGER AS month_number,
        SUM(p.amount) AS revenue
    FROM payments p
    JOIN users u ON u.id = p.user_id
    WHERE p.status = 'completed'
    GROUP BY 1, 2, 3
) cohort_data
GROUP BY cohort_month
ORDER BY cohort_month;

Gross Margin по продукту

SELECT
    p.name AS product,
    p.category,
    COUNT(DISTINCT o.id) AS orders,
    SUM(oi.quantity * oi.price) AS revenue,
    SUM(oi.quantity * p.cost_price) AS cogs,
    SUM(oi.quantity * (oi.price - p.cost_price)) AS gross_profit,
    ROUND(
        SUM(oi.quantity * (oi.price - p.cost_price))
        / NULLIF(SUM(oi.quantity * oi.price), 0) * 100,
        1
    ) AS gross_margin_pct
FROM order_items oi
JOIN products p ON p.id = oi.product_id
JOIN orders o ON o.id = oi.order_id
WHERE o.status = 'completed'
  AND o.created_at >= DATE_TRUNC('month', CURRENT_DATE)
GROUP BY p.name, p.category
ORDER BY gross_profit DESC;

Анализ просроченной дебиторки (Aging Report)

SELECT
    customer_id,
    customer_name,
    SUM(CASE WHEN days_overdue = 0 THEN amount END) AS current,
    SUM(CASE WHEN days_overdue BETWEEN 1 AND 30 THEN amount END) AS overdue_1_30,
    SUM(CASE WHEN days_overdue BETWEEN 31 AND 60 THEN amount END) AS overdue_31_60,
    SUM(CASE WHEN days_overdue BETWEEN 61 AND 90 THEN amount END) AS overdue_61_90,
    SUM(CASE WHEN days_overdue > 90 THEN amount END) AS overdue_90_plus,
    SUM(amount) AS total_receivable
FROM (
    SELECT
        inv.customer_id,
        c.name AS customer_name,
        inv.amount - COALESCE(SUM(pay.amount), 0) AS amount,
        GREATEST(0, CURRENT_DATE - inv.due_date) AS days_overdue
    FROM invoices inv
    JOIN customers c ON c.id = inv.customer_id
    LEFT JOIN payments pay ON pay.invoice_id = inv.id AND pay.status = 'completed'
    WHERE inv.status != 'paid'
    GROUP BY inv.id, c.name, inv.due_date, inv.amount
) aging
GROUP BY customer_id, customer_name
HAVING SUM(amount) > 0
ORDER BY overdue_90_plus DESC NULLS LAST;

Burn Rate и Runway

WITH monthly_expenses AS (
    SELECT
        DATE_TRUNC('month', date) AS month,
        SUM(amount) AS expenses
    FROM expenses
    WHERE date >= CURRENT_DATE - INTERVAL '3 months'
    GROUP BY 1
),
monthly_revenue AS (
    SELECT
        DATE_TRUNC('month', created_at) AS month,
        SUM(amount) AS revenue
    FROM payments
    WHERE status = 'completed'
      AND created_at >= CURRENT_DATE - INTERVAL '3 months'
    GROUP BY 1
)
SELECT
    AVG(e.expenses) AS avg_monthly_expenses,
    AVG(r.revenue) AS avg_monthly_revenue,
    AVG(e.expenses) - AVG(r.revenue) AS avg_net_burn,
    -- Cash balance / net burn = runway
    -- Подставьте реальный баланс:
    10000000 / NULLIF(AVG(e.expenses) - AVG(r.revenue), 0) AS runway_months
FROM monthly_expenses e
JOIN monthly_revenue r USING (month);

Итог: ключевые финансовые метрики в SQL

SQL даёт воспроизводимый, автоматизируемый финансовый анализ вместо ручных Excel-таблиц.