Analytics SQL skill for Claude Code

# Analytical query patterns

Read this when the task is one of the analyses below. SQL is written for Postgres
unless noted; swap date functions with the dialect table in SKILL.md. Every pattern
lists its output grain.

## Cohort retention (monthly)

Output: one row per cohort month per months-since-first-activity.

```sql
with activity as (                         -- one row per user per active month
    select distinct user_id, date_trunc('month', event_at)::date as month
    from events
    where event_name in ('login', 'purchase')
),
cohorts as (                               -- one row per user
    select user_id, min(month) as cohort_month
    from activity
    group by user_id
),
cohort_size as (                           -- one row per cohort
    select cohort_month, count(*) as users
    from cohorts
    group by cohort_month
),
retention as (                             -- one row per cohort per offset
    select c.cohort_month,
           (extract(year from age(a.month, c.cohort_month)) * 12
            + extract(month from age(a.month, c.cohort_month)))::int as month_offset,
           count(distinct a.user_id) as active_users
    from activity a
    join cohorts c using (user_id)
    group by 1, 2
)
select r.cohort_month, r.month_offset, s.users as cohort_users, r.active_users,
       round(100.0 * r.active_users / s.users, 1) as retention_pct
from retention r
join cohort_size s using (cohort_month)
order by 1, 2;
```

Caveats: the newest cohorts have fewer observable offsets; do not average incomplete
offsets into "average retention".

## Funnel with ordered steps and a time limit

Output: one row per step. Counts users who completed each step after the previous one
within 7 days of entering.

```sql
with step1 as (
    select user_id, min(event_at) as t1
    from events where event_name = 'view_product'
      and event_at >= '2026-09-01' and event_at < '2026-10-01'
    group by user_id
),
step2 as (
    select s.user_id, s.t1, min(e.event_at) as t2
    from step1 s
    join events e on e.user_id = s.user_id and e.event_name = 'add_to_cart'
     and e.event_at >= s.t1 and e.event_at < s.t1 + interval '7 days'
    group by s.user_id, s.t1
),
step3 as (
    select s.user_id, min(e.event_at) as t3
    from step2 s
    join events e on e.user_id = s.user_id and e.event_name = 'purchase'
     and e.event_at >= s.t2 and e.event_at < s.t1 + interval '7 days'
    group by s.user_id
)
select 'view_product' as step, 1 as step_no, count(*) as users from step1
union all select 'add_to_cart', 2, count(*) from step2
union all select 'purchase', 3, count(*) from step3
order by step_no;
```

Add conversion: `users / first_value(users) over (order by step_no)` and step-over-step
`users / lag(users) over (order by step_no)` (cast to numeric).

## Sessionization (30-minute inactivity gap)

Output: one row per session.

```sql
with ordered as (
    select user_id, event_at,
           case when event_at - lag(event_at) over (partition by user_id order by event_at)
                     > interval '30 minutes'
                  or lag(event_at) over (partition by user_id order by event_at) is null
                then 1 else 0 end as is_new_session
    from events
),
numbered as (
    select *, sum(is_new_session) over (partition by user_id order by event_at
                                        rows between unbounded preceding and current row) as session_no
    from ordered
)
select user_id, session_no, min(event_at) as session_start, max(event_at) as session_end,
       count(*) as events, max(event_at) - min(event_at) as duration
from numbered
group by user_id, session_no;
```

## Gaps and islands (consecutive active days / streaks)

Output: one row per streak.

```sql
with days as (
    select distinct user_id, event_at::date as d from events
),
grp as (
    select user_id, d,
           d - (row_number() over (partition by user_id order by d))::int as island
    from days
)
select user_id, min(d) as streak_start, max(d) as streak_end, count(*) as streak_days
from grp
group by user_id, island
order by streak_days desc;
```

## Month-over-month growth

Output: one row per month.

```sql
with monthly as (
    select date_trunc('month', created_at)::date as month, sum(amount) as revenue
    from orders
    group by 1
)
select month, revenue,
       lag(revenue) over (order by month) as prev_revenue,
       round(100.0 * (revenue - lag(revenue) over (order by month))
             / nullif(lag(revenue) over (order by month), 0), 1) as mom_pct
from monthly
order by month;
```

Use a month spine if any month can have zero revenue, otherwise `lag` compares against
the wrong month.

## DAU / WAU / MAU with rolling windows

Output: one row per day. Distinct counts over rolling windows need a self-join or
per-day user sets; summing DAU is wrong.

```sql
with active as (
    select distinct user_id, event_at::date as d from events
),
days as (
    select generate_series(date '2026-09-01', date '2026-09-30', interval '1 day')::date as d
)
select days.d,
       count(distinct a.user_id) filter (where a.d = days.d) as dau,
       count(distinct a.user_id) filter (where a.d > days.d - 7) as wau,
       count(distinct a.user_id) as mau
from days
join active a on a.d > days.d - 28 and a.d <= days.d
group by days.d
order by days.d;
```

(`filter (where ...)` is Postgres/DuckDB; elsewhere use `count(distinct case when ... then user_id end)`.)
On very large tables use HyperLogLog approximations (`approx_count_distinct`,
`hll_*` functions) and say so.

## First-touch and last-touch attribution

Output: one row per channel.

```sql
with touches as (
    select t.user_id, t.channel, t.touched_at, c.converted_at,
           row_number() over (partition by t.user_id order by t.touched_at asc)  as first_rank,
           row_number() over (partition by t.user_id order by t.touched_at desc) as last_rank
    from touchpoints t
    join conversions c on c.user_id = t.user_id and t.touched_at <= c.converted_at
                      and t.touched_at > c.converted_at - interval '30 days'
)
select channel,
       count(*) filter (where first_rank = 1) as first_touch_conversions,
       count(*) filter (where last_rank = 1)  as last_touch_conversions
from touches
group by channel
order by last_touch_conversions desc;
```

Assumes one conversion per user; partition by conversion ID otherwise.

## Top N per group

```sql
select * from (
    select category, product_id, revenue,
           row_number() over (partition by category order by revenue desc) as rn
    from product_revenue
) t
where rn <= 3;
```

Use `rank()` instead of `row_number()` if ties should all be included.

## Median and percentiles per group

```sql
-- Postgres / Snowflake / DuckDB
select region,
       percentile_cont(0.5) within group (order by amount) as median_amount,
       percentile_cont(0.95) within group (order by amount) as p95_amount
from orders
group by region;
-- BigQuery: approx_quantiles(amount, 100)[offset(50)]
```