SQL for Analytics skill for Codex

An analytics query is correct when its metric definition is written down, the denominator is the population that could have converted, time windows are complete and in the business time zone, and its headline number reconciles with a second method. Readable CTEs beat clever one-liners because reviewers must check the logic.

Before writing SQL

1. Write the definition in one sentence: “7-day retention = share of users who signed up in week W and had at least one session on days 1-7 after signup (day 0 excluded).” Confirm ambiguous parts with the user (counted from signup or first activity? calendar or rolling days? which events count as active?).
2. Read AGENTS.md and the warehouse docs or dbt models for canonical tables: prefer modelled fct_events, dim_users over raw logs. Check the grain and dedup rules.
3. Note the dialect. Syntax below is Postgres/DuckDB-flavoured; a dialect table follows.
4. Check data coverage: earliest event date, tracking changes, bot and internal-user filters. Analyses spanning a tracking change need a caveat.

Query structure

with params as (
    select date '2025-09-01' as start_date, date '2025-10-01' as end_date   -- end exclusive
),
users as (...),        -- population: who is eligible
events as (...),       -- filtered, deduplicated activity in the business time zone
joined as (...),       -- population x activity
final as (...)         -- metric computation
select * from final;

One CTE per idea, named for what it holds. Filter time with half-open ranges (>= start and < end), never between on timestamps.

Funnel (ordered, within a window)

with steps as (
    select user_id,
           min(case when event_name = 'view_product' then event_ts end) as t1,
           min(case when event_name = 'add_to_cart'  then event_ts end) as t2_any,
           min(case when event_name = 'checkout'     then event_ts end) as t3_any
    from fct_events
    where event_ts >= timestamp '2025-09-01' and event_ts < timestamp '2025-10-01'
    group by user_id
),
ordered as (
    select user_id, t1,
           (select min(e.event_ts) from fct_events e
             where e.user_id = s.user_id and e.event_name = 'add_to_cart'
               and e.event_ts >= s.t1 and e.event_ts < s.t1 + interval '7 days') as t2
    from steps s where t1 is not null
),
ordered3 as (
    select o.*,
           (select min(e.event_ts) from fct_events e
             where e.user_id = o.user_id and e.event_name = 'checkout'
               and e.event_ts >= o.t2 and e.event_ts < o.t1 + interval '7 days') as t3
    from ordered o
)
select count(*)                                  as viewed,
       count(t2)                                 as added,
       count(t3)                                 as checked_out,
       round(100.0 * count(t2) / count(*), 2)    as view_to_cart_pct,
       round(100.0 * count(t3) / nullif(count(t2), 0), 2) as cart_to_checkout_pct
from ordered3;

• Decide and state: strict order or any order, and the conversion window from step 1.
• Correlated subqueries are clear but slow on big tables; rewrite with window functions or a lateral join when needed, keeping the same semantics, and compare results.
• Step-to-step rates use the previous step as denominator; overall conversion uses step 1.

Cohort retention

with cohort as (
    select user_id, date_trunc('week', signup_ts at time zone 'Europe/Berlin')::date as cohort_week,
           (signup_ts at time zone 'Europe/Berlin')::date as signup_day
    from dim_users
    where signup_ts >= timestamp '2025-06-01' and not is_internal
),
activity as (
    select distinct user_id, (event_ts at time zone 'Europe/Berlin')::date as active_day
    from fct_events
    where event_name in ('session_start')            -- definition of "active"
),
by_day as (
    select c.cohort_week, c.user_id, a.active_day - c.signup_day as day_n
    from cohort c join activity a using (user_id)
    where a.active_day > c.signup_day
)
select c.cohort_week,
       count(distinct c.user_id)                                                      as cohort_size,
       round(100.0 * count(distinct case when b.day_n between 1 and 7 then b.user_id end)
             / count(distinct c.user_id), 1)                                          as d1_7_retention_pct,
       round(100.0 * count(distinct case when b.day_n between 28 and 34 then b.user_id end)
             / count(distinct c.user_id), 1)                                          as w5_retention_pct
from cohort c left join by_day b on b.user_id = c.user_id
group by 1
order by 1;

Cohorts too young for a window must show null, not zero: exclude cohorts where cohort_week + max day_n > current_date or mask those cells.

Retention flavour	Definition	Use
N-day (classic)	Active exactly on day N	Daily-use apps; noisy
Bracket / window	Active at least once in days A-B	Weekly products; smoother
Unbounded (rolling)	Active on day N or any later day	Churn-style “still around”; always higher

Active users and stickiness

with daily as (
    select distinct (event_ts at time zone 'UTC')::date as d, user_id from fct_events
),
calendar as (
    select generate_series(date '2025-09-01', date '2025-09-30', interval '1 day')::date as d
)
select c.d,
       count(distinct case when x.d = c.d then x.user_id end)                       as dau,
       count(distinct case when x.d > c.d - 7  and x.d <= c.d then x.user_id end)  as wau,
       count(distinct case when x.d > c.d - 28 and x.d <= c.d then x.user_id end)  as mau_28d
from calendar c
join daily x on x.d > c.d - 28 and x.d <= c.d
group by c.d
order by c.d;

DAU/MAU = average DAU over the period / MAU. Distinct counts are not additive: never sum DAU to get WAU. On very large tables use approximate distincts (approx_count_distinct, BigQuery APPROX_COUNT_DISTINCT, HyperLogLog sketches) and say so.

Sessionisation

with ordered as (
    select user_id, event_ts,
           case when event_ts - lag(event_ts) over (partition by user_id order by event_ts)
                     > interval '30 minutes'
                  or lag(event_ts) over (partition by user_id order by event_ts) is null
                then 1 else 0 end as new_session
    from fct_events
)
select user_id, event_ts,
       sum(new_session) over (partition by user_id order by event_ts rows unbounded preceding) as session_n
from ordered;

For deeper window-function patterns (frames, gaps and islands, dedup), hand off to $sql-window-functions.

Attribution

with touches as (
    select user_id, channel, touch_ts,
           row_number() over (partition by user_id order by touch_ts)      as first_rank,
           row_number() over (partition by user_id order by touch_ts desc) as last_rank
    from fct_marketing_touches t
    join fct_conversions c using (user_id)
    where t.touch_ts <= c.converted_ts
      and t.touch_ts > c.converted_ts - interval '30 days'     -- lookback window
)
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;

State the model and lookback in the output; first-touch and last-touch can rank channels in opposite order. filter (where ...) is Postgres/DuckDB; elsewhere use count(case when ... then 1 end).

Dialect notes

Need	Postgres / DuckDB	BigQuery	Snowflake
Truncate to week (Monday)	date_trunc('week', ts)	date_trunc(d, isoweek) / timestamp_trunc(ts, isoweek)	date_trunc('week', ts) (week start per WEEK_START param)
Convert time zone	ts at time zone 'Europe/Berlin'	datetime(ts, 'Europe/Berlin')	convert_timezone('Europe/Berlin', ts)
Date difference in days	d2 - d1	date_diff(d2, d1, day)	datediff('day', d1, d2)
Filter on window result	subquery / DuckDB qualify	qualify	qualify
Date series	generate_series	unnest(generate_date_array(a, b))	generator + dateadd or a date dimension

Prefer a date dimension table over generated series when one exists.

Gotchas

• Denominator drift: conversion “by channel” where users appear in multiple channels. Attribute each user to one channel, or report touches, and say which.
• Partial periods: the current week or cohort is incomplete; exclude or mark it.
• Joins between event tables multiply rows; aggregate each side to the user grain first.
• count(column) skips nulls, count(*) does not; pick deliberately.
• Integer division: 1 / 4 = 0 in Postgres and Snowflake integer math; multiply by 100.0.
• Bots, internal and test users inflate small segments; filter in one shared CTE.
• Time zone of date_trunc defaults to the session; set it explicitly.

Verification

• Reconcile one number two ways: funnel step-1 count equals a plain count(distinct user_id) of that event in the window; cohort sizes equal signups per week.
• Run on a tiny hand-checked sample (5 users) where you can compute the answer by hand.
• Compare with the canonical dashboard for the same definition; explain any gap.
• Inspect explain (or the warehouse’s bytes-scanned estimate) before running on large tables; filter partitions on the event date column.

Output template

**Question:** Did September signups retain better than August?
**Definition:** active = session_start; retention = active in days 1-7 after signup (Europe/Berlin days); internal users excluded
**Result:** Aug cohorts 31.2% (n=18,402), Sep cohorts 33.9% (n=19,877), +2.7 pts
**Checks:** cohort sizes match dim_users signups; 5-user sample verified by hand
**Caveats:** week of 2025-09-29 incomplete, excluded; tracking change on 2025-08-12 affects app sessions
**SQL:** queries/retention_aug_sep.sql