Crash course / SQL that survives production
The patterns that keep coming up
A short catalogue of the query shapes that appear in real work over and over — cohorts, funnels, running totals, deduplication, pivots — with the version that actually works.
Most SQL you write in a career is one of about ten shapes. Here they are, with the version I would actually ship.
Cohort retention#
Group users by when they started, then measure how many are still around N periods later. The chart everyone wants and few build correctly.
with cohorts as (
select account_id,
date_trunc('month', signed_up_on) as cohort_month,
churned_at
from saas_subscriptions
),
activity as (
select c.cohort_month,
c.account_id,
m.month_start,
datediff('month', c.cohort_month, m.month_start) as month_number
from cohorts c
cross join (select distinct date_trunc('month', signed_up_on) as month_start
from saas_subscriptions) m
where m.month_start >= c.cohort_month
and (c.churned_at is null or m.month_start < date_trunc('month', c.churned_at))
)
select cohort_month,
month_number,
count(distinct account_id) as accounts
from activity
group by 1, 2
order by 1, 2;The subtle part is the churn condition. An account that churned on 14 March was active in March, so < on the truncated month is right and <= is wrong — a one-character bug that shifts every retention number by a month.
Funnels#
with steps as (
select user_id,
min(case when step = 'view' then event_at end) as viewed_at,
min(case when step = 'add_cart' then event_at end) as carted_at,
min(case when step = 'checkout' then event_at end) as checked_at,
min(case when step = 'purchase' then event_at end) as purchased_at
from events group by 1
)
select
count(viewed_at) as viewed,
count(carted_at) as carted,
count(checked_at) as checked_out,
count(purchased_at) as purchased,
round(100.0 * count(purchased_at) / nullif(count(viewed_at), 0), 2) as overall_pct
from steps
where carted_at is null or carted_at >= viewed_at
and checked_at is null or checked_at >= carted_at;Two decisions worth making explicitly. Ordering: must the steps happen in sequence, or does reaching step 3 count regardless? Both are defensible; only one is what your stakeholder means. Windowing: does a purchase 40 days after the view count as converted? Pick a window and write it down.
Running totals and period comparison#
select
month,
revenue,
sum(revenue) over (order by month rows unbounded preceding) as cumulative,
lag(revenue, 12) over (order by month) as same_month_last_year,
round(100.0 * (revenue / nullif(lag(revenue, 12) over (order by month), 0) - 1), 1) as yoy_pct
from monthly_revenue
order by month;lag(revenue, 12) only works if there is a row for every month. If a month can be missing, join to a date spine first — otherwise lag(12) silently reaches back thirteen months.
Deduplication#
select * from (
select *, row_number() over (partition by natural_key order by updated_at desc, _loaded_at desc) as rn
from raw_table
) t where rn = 1;Two columns in the order by, deliberately. A single tie-breaker leaves the result non-deterministic, which means the same query returns different rows on different runs and someone eventually notices during a reconciliation.
Pivots without a pivot clause#
select
country,
sum(case when channel = 'web' then revenue_usd else 0 end) as web,
sum(case when channel = 'ios' then revenue_usd else 0 end) as ios,
sum(case when channel = 'android' then revenue_usd else 0 end) as android,
sum(case when channel = 'marketplace' then revenue_usd else 0 end) as marketplace,
sum(revenue_usd) as total
from retail_orders
group by 1
order by total desc;Portable, readable, and works everywhere. Only pivot at the very edge of a pipeline, for presentation — never in a model, because every new channel becomes a schema change.
Median and percentiles#
select
borough,
percentile_cont(0.5) within group (order by fare_usd) as median_fare,
percentile_cont(0.9) within group (order by fare_usd) as p90_fare,
avg(fare_usd) as mean_fare
from ride_hail_trips
group by 1;Report the median next to the mean on anything money-shaped. The gap between them is the finding — on this dataset the mean sits well above the median, and a stakeholder shown only the mean will form a wrong picture of a typical trip.
Sessionisation#
Covered in the window functions lesson, and worth repeating because it appears constantly: flag the gaps, then cumulative-sum the flag.
select *,
sum(case when event_at - lag(event_at) over w > interval '30 minutes' then 1 else 0 end)
over (partition by user_id order by event_at rows unbounded preceding) as session_id
from events
window w as (partition by user_id order by event_at);Date spines#
The fix for every "why does my chart skip days" question.
with spine as (
select generate_series(date '2026-01-01', date '2026-03-31', interval '1 day')::date as day
)
select s.day, coalesce(count(t.trip_id), 0) as trips
from spine s
left join ride_hail_trips t on t.pickup_at::date = s.day
group by 1 order by 1;Zero and missing are different, and a line chart draws them identically unless you force the issue.