How we use PostHog's built-in data warehouse

SELECT
    JSONExtractString(properties, 'organization_name') AS organization_name,
    sum(JSONExtractInt(properties, 'exceptions_captured_in_period')) AS exception_events
FROM events
WHERE event = 'organization usage report' AND {filters} AND JSONExtractInt(properties, 'exceptions_captured_in_period') > 0
group by organization_name
order by exception_events desc
limit 50

WITH daily_exceptions AS (
    SELECT 
        JSONExtractString(properties, 'organization_id') as org_id,
        toStartOfMonth(timestamp) as month,
        toDate(timestamp) as report_date,
        JSONExtractInt(properties, 'exceptions_captured_in_period') as exceptions
    FROM events 
    WHERE event = 'organization usage report'
        AND JSONExtractInt(properties, 'exceptions_captured_in_period') > 0
        AND {filters}  -- Same filters as daily customers query
        AND timestamp >= toDateTime('2025-03-01')
),

monthly_activity AS (
    SELECT 
        org_id,
        month,
        count(DISTINCT report_date) as days_with_exceptions,
        -- Calculate weeks in the month to get proper average
        dateDiff('day', min(report_date), max(report_date)) / 7.0 as weeks_active,
        count(DISTINCT report_date) / GREATEST(dateDiff('day', min(report_date), max(report_date)) / 7.0, 1) as avg_days_per_week
    FROM daily_exceptions
    GROUP BY org_id, month
),

regular_users AS (
    SELECT 
        org_id,
        month
    FROM monthly_activity
    WHERE avg_days_per_week > 4
)

SELECT 
    month,
    count(DISTINCT org_id) as orgs_regularly_capturing_exceptions
FROM regular_users
GROUP BY month
ORDER BY month

WITH customers_per_month AS (
    SELECT 
        customer_id,
        dateTrunc('month', toDateTime(period_end, 'UTC')) as month,
        JSONExtractString(data, 'customer_email') AS customer,
        sum(JSONExtractFloat(mrr_per_product, {variables.product})) AS mrr
    FROM postgres_invoice_with_annual_view
    WHERE dateTrunc('month', toDateTime(period_end, 'UTC')) IN ('2025-04-01', '2025-05-01', '2025-06-01')
        AND JSONExtractFloat(mrr_per_product, {variables.product}) > 0
    GROUP BY customer_id, customer, month
),

april_customers AS (
    SELECT 
        customer_id,
        customer,
        mrr as april_mrr
    FROM customers_per_month
    WHERE month = '2025-04-01'
),

may_customers AS (
    SELECT 
        customer_id,
        customer,
        mrr as may_mrr
    FROM customers_per_month
    WHERE month = '2025-05-01'
),

june_customers AS (
    SELECT 
        customer_id,
        customer,
        mrr as june_mrr
    FROM customers_per_month
    WHERE month = '2025-06-01'
),

exception_events AS (
    SELECT 
        customer_id,  -- Assuming this field exists to join with invoice data
        sum(toInt(org_usage_summary.exceptions)) as exception_count,
        count(DISTINCT date) as days_with_exceptions_june
    FROM postgres.prod.billing_usagereport
    WHERE org_usage_summary.exceptions IS NOT NULL
        AND toInt(org_usage_summary.exceptions) > 0  -- Only count days with actual exceptions
        AND date >= '2025-06-01'
        AND date < '2025-07-01'  -- June only
    GROUP BY customer_id
)

SELECT 
    m.customer as _customer,
    m.customer_id,
    round(COALESCE(a.april_mrr, 0), 0) as april_mrr,
    round(m.may_mrr, 0) as may_mrr,
    round(COALESCE(j.june_mrr, 0), 0) as june_mrr,
    round(m.may_mrr - COALESCE(j.june_mrr, 0), 0) as mrr_change,
    COALESCE(e.exception_count, 0) as exception_events,
    COALESCE(e.days_with_exceptions_june, 0) as days_with_exceptions_june,
    'CHURNED IN JUNE' as churn_status
FROM may_customers m
LEFT JOIN april_customers a ON m.customer_id = a.customer_id
LEFT JOIN june_customers j ON m.customer_id = j.customer_id
LEFT JOIN exception_events e ON m.customer_id = e.customer_id
WHERE j.customer_id IS NULL  -- Had May MRR but no June MRR = June churn
    AND m.customer_id IS NOT NULL
ORDER BY m.may_mrr DESC, days_with_exceptions_june DESC, exception_events DESC

WITH
    eu_customers as (select id from postgres_billing_customer where license_id = 1),
    us_customers as (select id from postgres_billing_customer where license_id = 2)

select
    round(sumIf(mrr, customer_id in (select id from us_customers))) * 12 as us_ARR,
    round(sumIf(mrr, customer_id in (select id from eu_customers))) * 12 as eu_ARR,
    coalesce(round(sumIf(mrr, customer_id is null OR (customer_id not in (select id from eu_customers) and customer_id not in (select id from us_customers)))) * 12, 0) as other_ARR,
    dateTrunc('month', toDateTime(period_end, 'UTC')) as period
from postgres_invoice_with_annual_view
where toDateTime(period_end, 'UTC') < dateTrunc('month', now()) + interval 2 month
and toDateTime(period_end, 'UTC') > dateTrunc('month', now() - interval 12 month)

group by period order by period asc

WITH date_range AS (
    SELECT 
        toStartOfMonth(now() - INTERVAL number MONTH) as month
    FROM numbers(35)  -- 33 months back + 2 forward
    WHERE month >= toStartOfMonth(now() - INTERVAL 33 MONTH)
        AND month <= toStartOfMonth(now() + INTERVAL 2 MONTH)
),
all_customers AS (
    SELECT DISTINCT id as customer_id
    FROM iwa_summary_customer_month
    WHERE month < toStartOfMonth(now() + INTERVAL 2 MONTH) 
    AND month > toStartOfMonth(now() - INTERVAL 33 MONTH)
),
customer_month_grid AS (
    SELECT 
        ac.customer_id,
        dr.month
    FROM all_customers ac
    CROSS JOIN date_range dr
),
customers_per_month AS (
    SELECT 
        cmg.customer_id,
        cmg.month,
        COALESCE(sum(iscm.total_mrr), 0) as mrr
    FROM customer_month_grid cmg
    LEFT JOIN iwa_summary_customer_month iscm 
        ON cmg.customer_id = iscm.id 
        AND cmg.month = iscm.month
    WHERE cmg.month < toStartOfMonth(now() + INTERVAL 2 MONTH) 
    AND cmg.month > toStartOfMonth(now() - INTERVAL 33 MONTH)
    GROUP BY cmg.customer_id, cmg.month
), 
churn_calc AS (
    SELECT
        customer_id,
        month AS churn_accounting_month,
        
        mrr AS curr_mrr,
        
        lagInFrame(mrr, 1, 0) OVER (
        PARTITION BY customer_id
        ORDER BY month
        ) AS prev_mrr
    FROM customers_per_month
),
churned AS (
    SELECT
        churn_accounting_month,
        sum(prev_mrr - curr_mrr) AS churned_mrr,
        count(*) as churned_customers
    FROM churn_calc
    WHERE prev_mrr > 0
        AND curr_mrr <= 0
    GROUP BY 1
),
customer_status AS (
    SELECT 
        customer_id,
        month,
        mrr,
        CASE WHEN MIN(month) OVER (PARTITION BY customer_id) = month THEN TRUE ELSE FALSE END as first_month_active,
        CASE WHEN lagInFrame(mrr, 1, 0) OVER (PARTITION BY customer_id ORDER BY month) > 0 THEN TRUE ELSE FALSE END as active_in_previous_month,
        lagInFrame(mrr, 1, 0) OVER (PARTITION BY customer_id ORDER BY month) as prev_month_mrr
    FROM customers_per_month
),
lifecycle AS (
    SELECT
        month,
        CASE 
            WHEN NOT active_in_previous_month THEN 'NEW'
            WHEN active_in_previous_month AND prev_month_mrr = mrr THEN 'FLAT'
            WHEN active_in_previous_month AND prev_month_mrr > mrr THEN 'SHRINKING'
            WHEN active_in_previous_month AND prev_month_mrr < mrr THEN 'GROWING'
            ELSE 'UNCATEGORIZED'
        END AS growth_lifecycle_stage, 
        mrr, 
        prev_month_mrr
    FROM customer_status
    WHERE mrr > 0
), 
growth_base AS (
    SELECT 
        month, 
        SUM(CASE WHEN growth_lifecycle_stage = 'NEW' THEN mrr ELSE 0 END) as new, 
        SUM(CASE 
            WHEN growth_lifecycle_stage = 'FLAT' THEN mrr
            WHEN growth_lifecycle_stage IN ('GROWING', 'SHRINKING') THEN prev_month_mrr 
            ELSE 0 END) as retained,
        SUM(CASE WHEN growth_lifecycle_stage = 'GROWING' THEN mrr - prev_month_mrr ELSE 0 END) as grown_from_existing,
        SUM(CASE WHEN growth_lifecycle_stage = 'SHRINKING' THEN mrr - prev_month_mrr ELSE 0 END) as shrunk_from_existing,
        SUM(CASE WHEN growth_lifecycle_stage = 'UNCATEGORIZED' THEN mrr ELSE 0 END) as uncategorized,
        -- Calculate the base MRR that was "at risk" (from continuing customers)
        SUM(prev_month_mrr) as at_risk_mrr
    FROM lifecycle    
    GROUP BY month
)
SELECT 
    growth_base.month as month, 
    growth_base.new,
    growth_base.retained,
    growth_base.grown_from_existing,
    growth_base.shrunk_from_existing, 
    growth_base.uncategorized, 
    -churned.churned_mrr as churned,
    churned.churned_customers,
        -- Add period start and end MRR
    lagInFrame(
        growth_base.new +
        growth_base.retained + 
        growth_base.grown_from_existing + 
        growth_base.shrunk_from_existing +
        growth_base.uncategorized
    ) OVER (ORDER BY growth_base.month) as period_start_mrr,
    
    -- Period end MRR = current month's total
    growth_base.new +
    growth_base.retained + 
    growth_base.grown_from_existing + 
    growth_base.shrunk_from_existing +
    growth_base.uncategorized as period_end_mrr,
FROM growth_base
LEFT JOIN churned
ON growth_base.month = churned.churn_accounting_month
WHERE growth_base.month < today() + interval 1 month
ORDER BY month ASC

SELECT
    dateTrunc('month', toDateTime(period_end, 'UTC')) AS period_month,
    tupleElement(kv, 1) AS product,
    SUM(toFloat(tupleElement(kv, 2))) AS revenue
FROM postgres_invoice_with_annual_view
ARRAY JOIN JSONExtractKeysAndValuesRaw(assumeNotNull(mrr_per_product)) AS kv
WHERE 
    toDateTime(period_end, 'UTC') < dateTrunc('month', now() + interval 1 month)
    AND toDateTime(period_end, 'UTC') >= dateTrunc('month', now() - interval 12 month)
GROUP BY period_month, product
ORDER BY period_month, product

LIMIT 500

SELECT 
    u.email AS owner,
    opp.name,
    opp.type,
    opp.close_date AS close_date,
    opp.forecast_category_name,
    opp.stage_name,
    opp.amount,
    opp.discount_rate_c AS discount,
    opp.amount_discounted_c AS discounted_amount
FROM 
    salesforce_opportunity opp
JOIN 
    salesforce_user u ON opp.owner_id = u.id
WHERE 
    toDateTime(opp.close_date) >= {variables.start_of_quarter}
    AND toDateTime(opp.close_date) < {variables.start_of_quarter} + INTERVAL 3 MONTH
    AND opp.is_closed = FALSE
    AND opp.self_serve_no_interaction_c = FALSE
    AND opp.self_serve_post_engagement_c = FALSE
    AND u.email LIKE CONCAT('%', {variables.account_owner})
ORDER BY 
    opp.close_date ASC;

SELECT 
    SUM(opp.amount_discounted_c)
FROM 
    salesforce_opportunity opp
JOIN 
    salesforce_user u ON opp.owner_id = u.id
WHERE 
    toDateTime(opp.close_date) >= {variables.start_of_quarter}
    AND toDateTime(opp.close_date) < {variables.start_of_quarter} + INTERVAL 3 MONTH
    AND opp.forecast_category_name IN ('Commit', 'Best Case', 'Pipeline')
    AND opp.self_serve_no_interaction_c = FALSE
    AND opp.self_serve_post_engagement_c = FALSE
    AND opp.type = 'Annual Contract'
    AND u.email LIKE CONCAT('%', {variables.account_owner});

WITH start_of_quarter AS (
    SELECT 
        inv.organization_id AS organization_id,
        acc.organization_name,
        acc.account_executive,
        acc.customer_success_manager,
        ROUND(SUM(inv.mrr * 12), 2) AS arr,
        CASE 
            WHEN arrayExists(x -> x LIKE '%annual%', groupArray(type)) 
            THEN 'annual' 
            ELSE 'monthly' 
        END AS type
    FROM 
        invoice_with_annual_plans_shifted inv
    JOIN 
        vitally_all_managed_accounts acc 
        ON inv.organization_id = acc.organization_id
    WHERE 
        period_end < toDateTime({variables.start_of_quarter})
        AND period_end >= toDateTime({variables.start_of_quarter}) - INTERVAL 1 MONTH
    GROUP BY 
        organization_id, organization_name, account_executive, customer_success_manager
    ORDER BY 
        arr DESC
)

SELECT 
    ROUND(SUM(arr), 0)
FROM 
    start_of_quarter
WHERE 
    (
        account_executive LIKE CONCAT('%', {variables.account_owner}) 
        OR customer_success_manager LIKE CONCAT('%', {variables.account_owner})
    )
    AND CASE 
        WHEN {variables.segment} = 'All' THEN 1
        WHEN {variables.segment} = 'AE Managed' AND account_executive IS NOT NULL THEN 1
        WHEN {variables.segment} = 'CSM Managed' AND customer_success_manager IS NOT NULL THEN 1
        ELSE 0
    END = 1;

with first_escalated as(
    select 
        ticket_id,
        min(created_at) as timestamp
    from zendesk_ticket_events
    where child_events like '%escalated%'
    group by ticket_id
),

before_escalated_breaches_in_time_range as(
    select
        ztme.ticket_id as ticket_id,
    from zendesk_ticket_metric_events ztme
    join first_escalated fe on fe.ticket_id = ztme.ticket_id
    where 
        ztme.type = 'breach'
        and ztme.metric in ('reply_time', 'pausable_update_time')
        and ztme.time <= fe.timestamp
        and ztme.time >= {filters.dateRange.from}
        and ztme.time <= {filters.dateRange.to}
    group by ztme.ticket_id
),

never_escalated as(
    select 
        id as ticket_id,
    from zendesk_tickets
    where tags not like '%escalated%'
    group by id
),

never_escalated_breaches_in_time_range as(
    select
        ztme.ticket_id as ticket_id,
    from zendesk_ticket_metric_events ztme
    join never_escalated ne on ne.ticket_id = ztme.ticket_id
    where 
        ztme.type = 'breach'
        and ztme.metric in ('reply_time', 'pausable_update_time')
        and ztme.time >= {filters.dateRange.from}
        and ztme.time <= {filters.dateRange.to}
    group by ztme.ticket_id
),

select 
    count(distinct(zt.id)) as num_breaches,
    count(distinct(IF(zt.id in (select ticket_id from never_escalated_breaches_in_time_range), zt.id, null)))as never_escalated_breaches,
    count(distinct(IF(zt.id in (select ticket_id from before_escalated_breaches_in_time_range), zt.id, null))) as before_escalated_breaches,
    zg.name
from zendesk_tickets zt
join zendesk_groups zg on zg.id = zt.group_id
where 
    zt.id in (select ticket_id from before_escalated_breaches_in_time_range)
    or zt.id in (select ticket_id from never_escalated_breaches_in_time_range)
group by zg.name
order by num_breaches desc

with first_escalated as(
    select 
        ticket_id,
        min(created_at) as timestamp
    from zendesk_ticket_events
    where child_events like '%"added_tags":["escalated"%'
    group by ticket_id
),

escalated_breaches_in_time_range as(
    select
        ztme.ticket_id as ticket_id

    from zendesk_ticket_metric_events ztme
        join first_escalated fe on fe.ticket_id = ztme.ticket_id
    where 
        ztme.type = 'breach'
        and ztme.metric in ('reply_time', 'pausable_update_time')
        and ztme.time >= fe.timestamp
        and ztme.time >= {filters.dateRange.from}
        and ztme.time <= {filters.dateRange.to}
    group by ztme.ticket_id
),

escalated_fulfill_events_in_time_range as (
    select
        ztme.ticket_id as ticket_id

    from zendesk_ticket_metric_events ztme
        join first_escalated fe on fe.ticket_id = ztme.ticket_id
    where 
        ztme.type = 'fulfill'
        and ztme.metric in ('reply_time', 'pausable_update_time')
        and ztme.time >= fe.timestamp
        and ztme.time >= {filters.dateRange.from}
        and ztme.time <= {filters.dateRange.to}
    group by ztme.ticket_id
),

breaches_by_group as (
    select 
        count(distinct(zt.id)) as num_breaches,
        zg.name as group_name
    from zendesk_tickets zt
        join zendesk_groups zg on zg.id = zt.group_id
    where zt.id in (select ticket_id from escalated_breaches_in_time_range)
    group by zg.name
    order by num_breaches desc
),

fulfills_by_group as (
    select 
        count(distinct(zt.id)) as num_fulfills,
        zg.name as group_name
    from zendesk_tickets zt
        join zendesk_groups zg on zg.id = zt.group_id
    where zt.id in (select ticket_id from escalated_fulfill_events_in_time_range)
    group by zg.name
    order by num_fulfills desc
),

select
    zg.name,
    IF(fbg.num_fulfills=0, 0, (fbg.num_fulfills-bbg.num_breaches)/fbg.num_fulfills) as sla_attainment
from zendesk_groups zg
    left outer join breaches_by_group bbg on bbg.group_name = zg.name
    left outer join fulfills_by_group fbg on fbg.group_name = zg.name
where (bbg.num_breaches>0 or fbg.num_fulfills>0)
order by sla_attainment desc