FinSight

CREATE TABLE fct_revenue_monthly AS
SELECT
    month || '-' || tier || '-' || region         AS row_key,
    DATE(month || '-01')                          AS month_start,
    tier,
    region,
    CAST(start_subs   AS INTEGER)                 AS start_subs,
    CAST(new_subs     AS INTEGER)                 AS new_subs,
    CAST(churned_subs AS INTEGER)                 AS churned_subs,
    CAST(end_subs     AS INTEGER)                 AS end_subs,
    CAST(revenue      AS REAL)                    AS revenue,
    CAST(cogs         AS REAL)                    AS cogs,
    CAST(revenue AS REAL) - cogs                  AS gross_profit,
    CAST(budget_revenue AS REAL)                  AS budget_revenue,
    revenue - budget_revenue                      AS variance
FROM raw_subscription_monthly;

SELECT 'duplicate_keys' AS check_name,
       COUNT(*) - COUNT(DISTINCT row_key) AS failed_rows
FROM fct_revenue_monthly
UNION ALL
SELECT 'subscriber_roll_forward_break',      -- end = start + new - churned
       SUM(ABS(end_subs - (start_subs + new_subs - churned_subs)) > 1)
FROM fct_revenue_monthly
UNION ALL
SELECT 'negative_revenue', SUM(revenue < 0) FROM fct_revenue_monthly
UNION ALL
SELECT 'missing_budget',   SUM(budget_revenue IS NULL) FROM fct_revenue_monthly;

SELECT
    STRFTIME('%Y-%m', month_start)                          AS month,
    ROUND(SUM(revenue))                                     AS mrr,
    SUM(end_subs)                                           AS subscribers,
    ROUND(100.0 * SUM(churned_subs) / SUM(start_subs), 2)   AS churn_pct,
    ROUND(SUM(revenue) / SUM(end_subs), 2)                  AS arpu,
    ROUND(100.0 * SUM(gross_profit) / SUM(revenue), 1)      AS gross_margin_pct
FROM fct_revenue_monthly
GROUP BY month
ORDER BY month;

WITH monthly AS (
    SELECT STRFTIME('%Y-%m', month_start) AS month,
           SUM(revenue)                   AS mrr
    FROM fct_revenue_monthly
    GROUP BY month
)
SELECT month,
       ROUND(mrr)                                                AS mrr,
       ROUND(LAG(mrr) OVER (ORDER BY month))                     AS prior_mrr,
       ROUND(100.0 * (mrr - LAG(mrr) OVER (ORDER BY month))
             / LAG(mrr) OVER (ORDER BY month), 2)                AS mom_growth_pct,
       ROUND(SUM(mrr) OVER (ORDER BY month))                     AS ytd_revenue
FROM monthly
ORDER BY month;

SELECT
    region,
    ROUND(SUM(revenue))                                          AS actual,
    ROUND(SUM(budget_revenue))                                   AS budget,
    ROUND(SUM(variance))                                         AS variance,
    ROUND(100.0 * SUM(variance) / SUM(budget_revenue), 1)        AS variance_pct,
    RANK() OVER (ORDER BY SUM(variance) / SUM(budget_revenue))   AS worst_rank
FROM fct_revenue_monthly
GROUP BY region
ORDER BY worst_rank;

WITH dec AS (
    SELECT tier,
           SUM(end_subs) AS subs,
           SUM(revenue)  AS mrr
    FROM fct_revenue_monthly
    WHERE month_start = '2025-12-01'
    GROUP BY tier
)
SELECT tier,
       subs,
       ROUND(100.0 * subs / SUM(subs) OVER (), 1)  AS pct_of_subscribers,
       ROUND(mrr)                                   AS mrr,
       ROUND(100.0 * mrr / SUM(mrr) OVER (), 1)     AS pct_of_revenue,
       ROUND(mrr / subs, 2)                         AS arpu
FROM dec
ORDER BY mrr DESC;

WITH seg AS (
    SELECT tier, region,
           1.0 * SUM(churned_subs) / SUM(start_subs) AS churn
    FROM fct_revenue_monthly
    GROUP BY tier, region
),
company AS (
    SELECT 1.0 * SUM(churned_subs) / SUM(start_subs) AS avg_churn
    FROM fct_revenue_monthly
)
SELECT s.tier, s.region,
       ROUND(100 * s.churn, 2)                          AS churn_pct,
       ROUND(s.churn / c.avg_churn, 2)                  AS vs_company_avg,
       CASE WHEN s.churn > 1.25 * c.avg_churn
            THEN 'Hotspot' ELSE 'OK' END                AS flag
FROM seg s CROSS JOIN company c
ORDER BY s.churn DESC
LIMIT 6;