Cheat sheets · Module 8
Analytics Patterns cheat sheet
Build the metrics product and growth teams ask for.
FLOOR · histogram buckets
A histogram groups numbers into equal-width buckets. FLOOR(x / 100) * 100 maps every value to the start of its $100 bucket (99.99 goes to 0, 100.00 to 100); GROUP BY that expression and count. Postgres also has width_bucket(x, low, high, n), which returns the bucket number.
SELECT FLOOR(minutes / 10) * 10 AS bucket_start, COUNT(*) AS calls FROM support_calls GROUP BY 1 ORDER BY 1;Practice: Order value histogram →
DISTINCT ON · latest row per key
Change logs keep every version of a row, so 'current state' means the newest row per key. Postgres's DISTINCT ON (key) keeps the first row of each key after sorting, so ORDER BY key, changed_at DESC keeps the latest. In other databases, ROW_NUMBER() OVER (PARTITION BY key ORDER BY changed_at DESC) = 1 does the same job.
SELECT DISTINCT ON (device_id) device_id, firmware, installed_at FROM firmware_updates ORDER BY device_id, installed_at DESC;Practice: Current plan per user →
SUM() OVER (PARTITION BY) · percent of group
To get each row's share of its own group, divide by a window total partitioned by that group. After a GROUP BY, SUM(SUM(x)) OVER (PARTITION BY g) adds up the group sums within g only, so the shares inside each g add to 100.
SELECT team, player, SUM(points) AS points, SUM(SUM(points)) OVER (PARTITION BY team) AS team_points FROM box_scores GROUP BY team, player;Practice: Customer share of country revenue →
generate_series · LEFT JOIN · filling date gaps
GROUP BY only returns dates that exist in the data, so quiet days silently vanish from charts. generate_series(start, end, INTERVAL '1 day') builds the full calendar; LEFT JOIN the data onto it and count a column from the data side, which gives 0 when nothing matched.
SELECT d::date AS day, COUNT(t.id) AS tickets
FROM generate_series('2024-03-01'::date, '2024-03-07'::date, INTERVAL '1 day') AS d
LEFT JOIN tickets t ON t.opened_on = d::date
GROUP BY d
ORDER BY d;Practice: Daily active users, zero days included →Self-join on period · YoY %
Year-over-year compares a period with the same period a year earlier, which cancels out seasonality. LAG(x, 4) over quarters only works if no quarter is ever missing; joining each row to the row with year - 1 and the same quarter is safe even when there are gaps.
SELECT cur.year, cur.week, cur.signups, prev.signups AS last_year FROM weekly_signups cur LEFT JOIN weekly_signups prev ON prev.year = cur.year - 1 AND prev.week = cur.week;Practice: Year-over-year growth by quarter →
As-of join · JOIN LATERAL
An as-of (point-in-time) join attaches the version of a row that was in effect at a moment: the latest change on or before the event's date, not the latest change overall. In Postgres, JOIN LATERAL runs a small subquery per row, so you can filter to earlier changes, ORDER BY date DESC and LIMIT 1.
SELECT s.sold_on, s.sku, p.price FROM sales s JOIN LATERAL ( SELECT price FROM price_history h WHERE h.sku = s.sku AND h.valid_from <= s.sold_on ORDER BY h.valid_from DESC LIMIT 1 ) AS p ON true;Practice: Plan at time of purchase →
RANGE window frame · calendar moving average
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW means 'the last 7 rows', which covers more than a week when some days have no data. RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW covers the last 7 calendar days instead. Divide the window's SUM by 7 so days without sales count as zero.
SELECT day, rides,
SUM(rides) OVER (ORDER BY day
RANGE BETWEEN INTERVAL '2 days' PRECEDING AND CURRENT ROW) / 3.0 AS avg_3d
FROM daily_rides;Practice: 7-day moving average revenue →Distinct user-days · days in month · ratios
Stickiness is average daily active users divided by monthly active users. Average DAU is distinct (user, day) pairs divided by the days in the month, so quiet days count as zero; MAU is distinct users in the month. (m + INTERVAL '1 month')::date - m gives the number of days in month m.
SELECT to_char(played_on, 'YYYY-MM') AS month, COUNT(DISTINCT player_id) AS monthly_players FROM game_plays GROUP BY 1;Practice: DAU/MAU stickiness →
Cohorts · date_trunc · LEFT JOIN
A cohort is everyone who signed up in the same month. Month-1 retention is the share of a cohort that was active in the calendar month after their signup month. Cohort size must come from the signup table, so people who never came back still count in the denominator.
SELECT to_char(joined_on, 'YYYY-MM') AS cohort, COUNT(*) AS members FROM gym_members GROUP BY 1;Practice: Month-1 retention by signup cohort →
Anti-join across months · churn
A churned user was active last month and has no activity this month. You can't read churn off monthly totals, because new and returning users hide the ones who left. Build one row per user and month, LEFT JOIN each row to the same user's next month, and count the rows where nothing matched.
SELECT p.season, COUNT(*) AS lapsed_members FROM memberships p LEFT JOIN memberships n ON n.member_id = p.member_id AND n.season = p.season + 1 WHERE n.member_id IS NULL GROUP BY p.season;Practice: Monthly churn rate →
First-seen month · running distinct count
A user is new in the month of their first-ever activity and returning in every later month they show up. Postgres doesn't allow COUNT(DISTINCT ...) OVER (...), so a running count of distinct users is built from first-seen months: count new users per month, then take a running SUM of those counts.
WITH firsts AS ( SELECT reader_id, MIN(read_on) AS first_read FROM article_reads GROUP BY reader_id ) SELECT first_read, COUNT(*) AS new_readers, SUM(COUNT(*)) OVER (ORDER BY first_read) AS total_readers FROM firsts GROUP BY first_read;Practice: New vs returning users →
ROW_NUMBER both ways · attribution
Attribution decides which marketing channel gets credit for a conversion. First-touch credits the earliest touch before the conversion, last-touch the latest one. Number each converter's eligible touches with ROW_NUMBER ordered ascending and descending and keep row 1 of each; touches after the conversion never get credit.
SELECT donor_id, campaign, ROW_NUMBER() OVER (PARTITION BY donor_id ORDER BY sent_on) AS nth_ask, ROW_NUMBER() OVER (PARTITION BY donor_id ORDER BY sent_on DESC) AS nth_from_last FROM appeals;Practice: First-touch vs last-touch attribution →
Gaps and islands · duplicate days
The gaps-and-islands trick (date minus ROW_NUMBER is constant within a streak) breaks when a date appears twice, because the duplicate bumps the row number. Dedupe to one row per user and day first, then GROUP BY the anchor and report MIN and MAX date as the range.
WITH d AS (SELECT DISTINCT shop_id, sale_day FROM sales) SELECT shop_id, sale_day, sale_day - (ROW_NUMBER() OVER (PARTITION BY shop_id ORDER BY sale_day))::int AS anchor FROM d;Practice: Activity streaks as date ranges →
LAG · running SUM · sessionization
Web analytics splits a user's pageviews into sessions: a new session starts when more than 30 minutes have passed since that user's previous view. Flag each row with LAG (1 = starts a session), then a running SUM of the flags numbers the sessions so you can GROUP BY them.
SELECT truck_id, pinged_at,
SUM(new_trip) OVER (PARTITION BY truck_id ORDER BY pinged_at) AS trip_no
FROM (
SELECT truck_id, pinged_at,
CASE WHEN pinged_at - LAG(pinged_at) OVER (PARTITION BY truck_id ORDER BY pinged_at)
<= INTERVAL '2 hours' THEN 0 ELSE 1 END AS new_trip
FROM gps_pings
) AS p;Practice: Sessions from raw pageviews →Funnel with a time window · range join
Funnel steps only count in order and within a window: step 2 must come after step 1 and no later than the deadline. Anchor each user on their first step-1 time, then LEFT JOIN their step-2 events with a range condition, so users who didn't convert keep a row with NULL.
SELECT q.lead_id, q.quoted_at, MIN(s.signed_at) AS signed_at FROM (SELECT lead_id, MIN(sent_at) AS quoted_at FROM quotes GROUP BY lead_id) AS q LEFT JOIN signatures s ON s.lead_id = q.lead_id AND s.signed_at > q.quoted_at AND s.signed_at <= q.quoted_at + INTERVAL '7 days' GROUP BY q.lead_id, q.quoted_at;Practice: Pricing to checkout within an hour →