MRR月次成長率のトラッキング

Question

サブスクリプション事業の月次MRR成長率をデータチームに自動化してほしいと依頼がありました。`subscription_events` の MRR変動額を月次で集計し、前月比を計算するSQLを選んでください。

Accepted Answer

WITH monthly_mrr AS ( SELECT DATE_TRUNC('month', event_date)::date AS event_month, SUM(mrr_delta) AS net_mrr FROM subscription_events GROUP BY 1 ), ranked AS ( SELECT event_month, net_mrr, LAG(net_mrr) OVER (ORDER BY event_month) AS prev_month_mrr FROM monthly_mrr ) SELECT event_month, net_mrr, prev_month_mrr, ROUND((net_mrr - prev_month_mrr) / NULLIF(prev_month_mrr, 0), 4) AS growth_rate FROM ranked ORDER BY event_month;

Answer

SELECT DATE_TRUNC('month', event_date)::date AS event_month, SUM(mrr_delta) AS net_mrr, LAG(SUM(mrr_delta)) OVER (ORDER BY DATE_TRUNC('month', event_date)) AS prev_month_mrr, (SUM(mrr_delta) - LAG(SUM(mrr_delta)) OVER (ORDER BY DATE_TRUNC('month', event_date))) / SUM(mrr_delta) AS growth_rate FROM subscription_events GROUP BY event_month ORDER BY event_month;

Answer

WITH monthly_mrr AS ( SELECT DATE_TRUNC('month', event_date)::date AS event_month, plan_id, SUM(mrr_delta) AS net_mrr FROM subscription_events GROUP BY 1, plan_id ) SELECT event_month, SUM(net_mrr) AS net_mrr, LAG(SUM(net_mrr)) OVER (PARTITION BY plan_id ORDER BY event_month) AS prev_month_mrr FROM monthly_mrr GROUP BY event_month, plan_id ORDER BY event_month;

Answer

SELECT DATE_TRUNC('month', event_date)::date AS event_month, SUM(mrr_delta) AS net_mrr, SUM(mrr_delta) / LAG(SUM(mrr_delta)) OVER (ORDER BY DATE_TRUNC('month', event_date)) - 1 AS growth_rate FROM subscription_events GROUP BY event_month ORDER BY event_month;

SQL演習：マーケティング指標設計（セクション2）