# SQL Snippets — Analytics Patterns

**What this is:** Production-ready SQL snippets for the most common analytical queries in a data team. Written for Snowflake syntax; notes included where BigQuery, Databricks, or Postgres differ.  
**Use for:** Copy-paste starting points for common analytical patterns. Adapt table and column names to your schema.  
**Covered in:** The Data Foundations Guide and Statistical Concepts Guide on biztechprimer.com.

---

## 1. Cohort Retention Analysis

*Which percentage of users who signed up in month X are still active in month X+N?*

```sql
-- COHORT RETENTION
-- Grain: One row per cohort month + months_since_signup combination
-- Required inputs: users table (user_id, signup_date) + activity table (user_id, activity_date)

with cohorts as (

    -- Assign each user to their signup cohort (truncated to month)
    select
        user_id,
        date_trunc('month', signup_date)        as cohort_month
    from users

),

user_activity as (

    -- Flatten user activity to monthly grain
    select distinct
        user_id,
        date_trunc('month', activity_date)      as active_month
    from user_activity_events

),

cohort_activity as (

    -- Join each user's activity back to their cohort
    select
        c.cohort_month,
        a.active_month,
        datediff('month', c.cohort_month, a.active_month) as months_since_signup,
        count(distinct c.user_id)               as active_users
    from cohorts c
    inner join user_activity a using (user_id)
    group by 1, 2, 3

),

cohort_sizes as (

    -- Count users per cohort (denominator for retention %)
    select
        cohort_month,
        count(distinct user_id)                 as cohort_size
    from cohorts
    group by 1

)

select
    ca.cohort_month,
    cs.cohort_size,
    ca.months_since_signup,
    ca.active_users,
    round(ca.active_users / cs.cohort_size * 100, 1)  as retention_pct

from cohort_activity ca
join cohort_sizes cs using (cohort_month)

-- Optional: filter to cohorts with at least N users
where cs.cohort_size >= 10

order by
    ca.cohort_month,
    ca.months_since_signup
```

---

## 2. MRR (Monthly Recurring Revenue) Calculation

*What is the total MRR and how has it changed month-over-month?*

```sql
-- MRR SUMMARY BY MONTH
-- Input: subscriptions table with subscription_id, customer_id, amount_monthly,
--        status, created_at, canceled_at

with active_subscriptions as (

    select
        date_trunc('month', month_date)         as mrr_month,
        customer_id,
        subscription_id,
        amount_monthly                          as mrr
    from subscriptions
    cross join (
        -- Generate a row for each month the subscription was active
        select dateadd('month', seq4(), '2020-01-01') as month_date
        from table(generator(rowcount => 120))
    ) months
    where
        created_at <= month_date
        and (canceled_at is null or canceled_at > month_date)
        and status = 'active'

),

mrr_by_month as (

    select
        mrr_month,
        count(distinct customer_id)             as paying_customers,
        count(distinct subscription_id)         as active_subscriptions,
        sum(mrr)                                as total_mrr,
        avg(mrr)                                as avg_mrr_per_customer
    from active_subscriptions
    group by 1

)

select
    mrr_month,
    paying_customers,
    active_subscriptions,
    total_mrr,
    avg_mrr_per_customer,

    -- Month-over-month change
    total_mrr - lag(total_mrr) over (order by mrr_month)           as mrr_change,
    round(
        (total_mrr - lag(total_mrr) over (order by mrr_month))
        / nullif(lag(total_mrr) over (order by mrr_month), 0) * 100,
        1
    )                                                               as mrr_growth_pct

from mrr_by_month
order by mrr_month
```

---

## 3. Churn Rate (Revenue & Customer)

*What percentage of customers / revenue was lost each month?*

```sql
-- MONTHLY CHURN RATE
-- Customer churn: % of customers present at start of month who were not present at end
-- Revenue churn: % of MRR at start of month that was lost

with monthly_customers as (

    select
        mrr_month,
        customer_id,
        mrr
    from active_subscriptions  -- from snippet #2 above

),

churn_calc as (

    select
        curr.mrr_month,

        -- Customers at start of month (from prior month)
        count(distinct prev.customer_id)                    as customers_start,

        -- Customers at end of month
        count(distinct curr.customer_id)                    as customers_end,

        -- Churned = in prior month, not in current month
        count(distinct case
            when curr.customer_id is null then prev.customer_id
        end)                                                as churned_customers,

        -- MRR at risk = prior month's MRR for customers who will churn
        sum(case
            when curr.customer_id is null then prev.mrr
            else 0
        end)                                                as churned_mrr,

        sum(prev.mrr)                                       as mrr_start

    from monthly_customers prev
    left join monthly_customers curr
        on prev.customer_id = curr.customer_id
        and dateadd('month', 1, prev.mrr_month) = curr.mrr_month

    group by curr.mrr_month

)

select
    mrr_month,
    customers_start,
    churned_customers,
    round(churned_customers / nullif(customers_start, 0) * 100, 2) as customer_churn_rate_pct,
    churned_mrr,
    mrr_start,
    round(churned_mrr / nullif(mrr_start, 0) * 100, 2)            as revenue_churn_rate_pct

from churn_calc
where mrr_month >= '2022-01-01'  -- adjust start date
order by mrr_month
```

---

## 4. Customer LTV Calculation

*What is the average lifetime value of a customer?*

```sql
-- CUSTOMER LTV — Historical (actual realized value)
-- Grain: One row per customer
-- Useful for segmentation and as input to LTV:CAC analysis

with customer_revenue as (

    select
        customer_id,
        min(subscription_created_at)            as first_subscription_date,
        max(coalesce(canceled_at, current_date())) as last_active_date,
        sum(amount_paid)                        as total_revenue_paid,
        count(distinct invoice_id)              as total_invoices
    from subscription_invoices
    where status = 'paid'
    group by 1

),

customer_profile as (

    select
        c.customer_id,
        c.customer_email,
        c.customer_segment,
        cr.first_subscription_date,
        cr.last_active_date,
        datediff('month', cr.first_subscription_date, cr.last_active_date) as tenure_months,
        cr.total_revenue_paid,
        cr.total_invoices,

        -- Monthly revenue rate
        cr.total_revenue_paid / nullif(
            datediff('month', cr.first_subscription_date, cr.last_active_date), 0
        )                                       as avg_monthly_revenue,

        -- Simple LTV (historical only — not predictive)
        cr.total_revenue_paid * 0.75            as historical_ltv_at_75pct_margin

    from customers c
    join customer_revenue cr using (customer_id)

)

select
    customer_segment,
    count(distinct customer_id)                 as customer_count,
    avg(tenure_months)                          as avg_tenure_months,
    avg(total_revenue_paid)                     as avg_historical_revenue,
    avg(historical_ltv_at_75pct_margin)         as avg_ltv_at_75pct_margin,
    percentile_cont(0.5) within group (
        order by total_revenue_paid
    )                                           as median_revenue

from customer_profile
group by 1
order by avg_historical_revenue desc
```

---

## 5. Retention Curve (Survival Analysis)

*What fraction of users from each cohort are still active after N days/months?*

```sql
-- RETENTION CURVE
-- Returns retention % at each time interval since signup
-- Can pivot this output to produce a triangle heatmap

with user_events as (

    select
        user_id,
        min(event_date)                         as first_event_date
    from events
    group by 1

),

activity_by_day as (

    select
        u.user_id,
        u.first_event_date,
        e.event_date,
        datediff('day', u.first_event_date, e.event_date)  as day_number
    from user_events u
    join events e using (user_id)
    where e.event_date >= u.first_event_date

),

cohort_sizes as (

    select
        date_trunc('month', first_event_date)   as cohort_month,
        count(distinct user_id)                 as cohort_size
    from user_events
    group by 1

),

retention_by_day as (

    select
        date_trunc('month', first_event_date)   as cohort_month,
        day_number,
        count(distinct user_id)                 as retained_users
    from activity_by_day
    where day_number in (0, 1, 7, 14, 30, 60, 90, 180, 365)  -- adjust intervals
    group by 1, 2

)

select
    r.cohort_month,
    cs.cohort_size,
    r.day_number,
    r.retained_users,
    round(r.retained_users / cs.cohort_size * 100, 1)  as retention_pct

from retention_by_day r
join cohort_sizes cs using (cohort_month)
order by
    r.cohort_month,
    r.day_number
```

---

## Dialect notes

| Feature | Snowflake | BigQuery | Databricks | Postgres |
|---|---|---|---|---|
| Date truncation | `date_trunc('month', col)` | `date_trunc(col, MONTH)` | `date_trunc('month', col)` | `date_trunc('month', col)` |
| Date diff | `datediff('day', start, end)` | `date_diff(end, start, DAY)` | `datediff(start, end)` | `end - start` (days) |
| Row generator | `table(generator(rowcount => N))` | Unnest an array | `range(N)` | `generate_series` |
| Qualify (window dedup) | `qualify row_number()...` | Not native — use subquery | `qualify row_number()...` | Use subquery |
| Array functions | `ARRAY_AGG`, `FLATTEN` | `ARRAY_AGG`, `UNNEST` | `collect_list`, `explode` | `array_agg`, `unnest` |

---

*Covered in the Data Foundations Guide on biztechprimer.com.*