Cheat sheets · Module 9
Interview Question Bank cheat sheet
Walk into a SQL screen having already solved the kinds of questions it asks.
Order-level average · COUNT(DISTINCT) · to_char
Average order value (AOV) is revenue divided by the number of orders. When revenue lives on line items, AVG(line_total) averages lines, not orders, and an order with three lines counts three times. Divide the summed revenue by COUNT(DISTINCT order_id), or total each order in a subquery first and average those totals.
SELECT to_char(b.booked_on, 'YYYY-MM') AS month, ROUND(SUM(l.nights * l.rate) / COUNT(DISTINCT b.id), 2) AS avg_booking_value FROM bookings b JOIN booking_lines l ON l.booking_id = b.id GROUP BY 1 ORDER BY 1;Practice: Average order value by month →
COUNT(*) FILTER · IS NULL · conditional ratio
An abandonment rate is the share of started carts that never reached checkout. A NULL foreign key (no order_id) usually marks 'didn't happen', so count those rows with COUNT(*) FILTER (WHERE order_id IS NULL) and divide by COUNT(*). Decide up front which rows belong in the denominator at all, and multiply by 100.0 so the division isn't integer division.
SELECT to_char(started_on, 'YYYY-MM') AS month, ROUND(100.0 * COUNT(*) FILTER (WHERE finished_on IS NULL) / COUNT(*), 2) AS dropout_pct FROM course_enrolments WHERE lessons_opened > 0 GROUP BY 1;Practice: Cart abandonment rate →
NOT EXISTS · NOT IN and NULLs · date ranges
'Never happened in a period' is an anti-join: keep rows from the main table for which NOT EXISTS (a matching row in the period). Avoid NOT IN on a column that can be NULL: x NOT IN (1, 2, NULL) is never true, so the whole query silently returns nothing. For a date range, >= the first day and < the day after the last day is the safest boundary.
SELECT b.title
FROM books b
WHERE NOT EXISTS (
SELECT 1 FROM loans l
WHERE l.book_id = b.id
AND l.loaned_on >= '2025-06-01' AND l.loaned_on < '2025-07-01'
);Practice: Products not sold in Q1 →DENSE_RANK · PARTITION BY · ties
Top-N per group means ranking inside each group with a window function, then filtering in an outer query (window functions can't go in WHERE). ROW_NUMBER breaks ties arbitrarily, RANK leaves gaps after a tie (1, 1, 3), and DENSE_RANK doesn't (1, 1, 2). With DENSE_RANK, 'top 3' means the three highest distinct values, so ties can return more than 3 rows.
WITH r AS (
SELECT league, player, goals,
DENSE_RANK() OVER (PARTITION BY league ORDER BY goals DESC) AS pos
FROM scorers
)
SELECT * FROM r WHERE pos <= 3;Practice: Top 3 products per category →NULLIF · divide-by-zero · conditional SUM
Any ratio can hit a group whose denominator is 0, and Postgres stops the whole query with 'division by zero'. Wrap the divisor in NULLIF(divisor, 0): it turns 0 into NULL, and x / NULL is NULL, which is the honest answer for 'no rate'. Use SUM over a quantity column for unit rates; COUNT(*) counts rows, not units.
SELECT region, ROUND(100.0 * SUM(refunded_amount) / NULLIF(SUM(paid_amount), 0), 1) AS refund_pct FROM payments GROUP BY region;Practice: Return rate by category →
LAG · PARTITION BY · filtering after a window
LAG(col) OVER (PARTITION BY key ORDER BY time) puts the previous row's value next to the current one, so each row can be compared with the one before it. Without PARTITION BY, the first row of one key is compared with the last row of another. The first row of each key has no previous value (NULL), and since window functions run after WHERE, filter on the LAG result in an outer query.
WITH r AS (
SELECT plan, effective_on, fee,
LAG(fee) OVER (PARTITION BY plan ORDER BY effective_on) AS prev_fee
FROM plan_fees
)
SELECT * FROM r WHERE fee > prev_fee;Practice: Price change history →Pivot with SUM FILTER · COALESCE · quarter boundaries
To pivot long data into columns, GROUP BY the row label and write one aggregate per column, each restricted to its own slice: SUM(x) FILTER (WHERE ...) in Postgres, or SUM(CASE WHEN ... THEN x ELSE 0 END) anywhere. A FILTER that matches no rows returns NULL, not 0, so wrap it in COALESCE when the report should show zero.
SELECT store, COALESCE(SUM(amount) FILTER (WHERE sold_on < '2025-07-01'), 0) AS h1, COALESCE(SUM(amount) FILTER (WHERE sold_on >= '2025-07-01'), 0) AS h2 FROM sales GROUP BY store;Practice: Category revenue by quarter →
Sales velocity · LEFT JOIN · fixed date window
Days of inventory = stock on hand / average daily units sold. The average must divide by every day in the window, not just the days with sales, so take the window's total units and divide by its length. Products with no recent sales have no velocity, so LEFT JOIN the sales onto the catalog and let their result be NULL instead of crashing on a divide by zero.
WITH u AS ( SELECT part_id, SUM(qty) AS used_14d FROM part_usage WHERE used_on BETWEEN '2025-05-18' AND '2025-05-31' GROUP BY part_id ) SELECT p.part_no, ROUND(p.in_stock * 14.0 / u.used_14d, 1) AS days_cover FROM parts p LEFT JOIN u ON u.part_id = p.id;Practice: Days of inventory remaining →
First order from full history · FILTER split · COALESCE
To split revenue into first purchases and repeat purchases, find each customer's first order over their whole history, then compare every order in the reporting window against it. If the first-order lookup only sees the window, everyone who bought before it looks brand new. Define 'first' using the same rules as the report (here: completed orders only).
WITH f AS ( SELECT member_id, MIN(visited_on) AS first_visit FROM gym_visits GROUP BY member_id ) SELECT v.visited_on, COUNT(*) FILTER (WHERE v.visited_on = f.first_visit) AS first_visits FROM gym_visits v JOIN f ON f.member_id = v.member_id WHERE v.visited_on >= '2025-06-01' GROUP BY v.visited_on;Practice: First-order vs repeat revenue →
ROW_NUMBER · self-join on rn = 1 and rn = 2 · date difference
Questions about a customer's 1st and 2nd purchase need the orders numbered per customer: ROW_NUMBER() OVER (PARTITION BY customer ORDER BY date, id). Join the numbered set to itself (rn = 1 with rn = 2) to put both on one row. LAG would compare every pair of neighbouring orders, not just the first two. In Postgres, date - date is a whole number of days.
WITH n AS (
SELECT patient_id, visit_on,
ROW_NUMBER() OVER (PARTITION BY patient_id ORDER BY visit_on) AS rn
FROM clinic_visits
)
SELECT a.patient_id, b.visit_on - a.visit_on AS days_to_follow_up
FROM n a
JOIN n b ON b.patient_id = a.patient_id AND b.rn = 2
WHERE a.rn = 1;Practice: Second order within 30 days →Monthly totals · pivot or LAG · strict increase with no gaps
'Grew every month' has two traps: values must be totals per month (not per order), and a missing month is a break, not a skip. Pivot each month into its own column with SUM(...) FILTER and compare neighbours with >: a missing month is NULL, and NULL > x is never true. For longer periods, LAG over monthly totals works too, but also require the number of months to equal the full period.
WITH w AS (
SELECT runner_id,
SUM(km) FILTER (WHERE week = 1) AS w1,
SUM(km) FILTER (WHERE week = 2) AS w2
FROM runs GROUP BY runner_id
)
SELECT runner_id FROM w WHERE w2 > w1;Practice: Spend rose every month →ROW_NUMBER · COUNT(*) OVER () · CEIL · Pareto share
A Pareto check asks what share of revenue the top X% of customers bring in. Rank customers by revenue with ROW_NUMBER (a tie-breaker makes the cut exact), put the customer count on every row with COUNT(*) OVER (), and keep rows with rn <= CEIL(0.2 * count). RANK and PERCENT_RANK let ties stretch or shrink the group, and the count must be of customers who actually bought.
WITH r AS (
SELECT seller_id, gmv,
ROW_NUMBER() OVER (ORDER BY gmv DESC, seller_id) AS rn,
COUNT(*) OVER () AS sellers
FROM seller_gmv
)
SELECT SUM(gmv) FILTER (WHERE rn <= CEIL(0.1 * sellers)) AS top_10pct_gmv
FROM r;Practice: Revenue share of the top 20% of customers →CASE buckets · NULL handling · custom sort
CASE WHEN ... THEN ... END turns a number into a label. The WHENs are checked top to bottom and the first true one wins, so after the first bucket each WHEN only needs its upper bound. A NULL fails every comparison and falls through to ELSE, so decide where NULLs belong and test for them explicitly.
SELECT CASE
WHEN score IS NULL THEN 'unscored'
WHEN score < 580 THEN 'poor'
WHEN score < 670 THEN 'fair'
ELSE 'good'
END AS band,
COUNT(*) AS applicants
FROM credit_checks
GROUP BY band;Practice: Loan delinquency buckets →Conditional COUNT · HAVING · minimum volume
A rate is a filtered count over the total count of the same group. COUNT(*) FILTER (WHERE ...) counts the matching rows without dropping the others, which a WHERE clause would. Small groups give noisy rates, so set a minimum volume with HAVING, which filters groups after they are counted.
SELECT courier, COUNT(*) AS deliveries, ROUND(100.0 * COUNT(*) FILTER (WHERE on_time) / COUNT(*), 1) AS on_time_pct FROM deliveries GROUP BY courier HAVING COUNT(*) >= 20;Practice: Card approval rate by merchant →
PERCENTILE_CONT · JOIN · ROUND
Card spend is skewed: a few big tickets drag the average far above a typical purchase, so the median is the sturdier 'typical' figure. PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY x) returns it per group, averaging the two middle values when a group has an even count. It returns double precision, so cast to numeric before ROUND(x, 2).
SELECT d.region, ROUND((PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY r.fare))::numeric, 2) AS median_fare FROM rides r JOIN drivers d ON d.id = r.driver_id GROUP BY d.region;Practice: Median purchase by customer segment →
GROUP BY month · running SUM over groups
A balance is the running total of every movement so far, not the sum of one period. Aggregate first to one row per account and month (the month's net change), then carry it forward with SUM(...) OVER (PARTITION BY account ORDER BY month). PARTITION BY restarts the total for each account; ORDER BY makes it cumulative.
SELECT warehouse_id, moved_on, qty, SUM(qty) OVER (PARTITION BY warehouse_id ORDER BY moved_on) AS on_hand FROM stock_moves;Practice: Month-end balance per account →
Filter, then first row per group · DISTINCT ON
'The first X per group' is two steps in a fixed order: filter to the rows that qualify, then take the earliest per group. DISTINCT ON (group) with ORDER BY group, time keeps each group's first row; ROW_NUMBER() OVER (PARTITION BY group ORDER BY time) = 1 does the same. Ranking every row and then keeping row 1 only works when that row happens to qualify.
SELECT DISTINCT ON (student_id) student_id, exam, score, taken_on FROM exam_results WHERE score >= 90 ORDER BY student_id, taken_on, exam;Practice: First deposit over $1,000 →
Window AVG · compare a row with its own group
To compare each row with its own group's average, AVG(x) OVER (PARTITION BY group) puts that average on every row without collapsing them. Window functions can't be used in WHERE, because WHERE runs before they are computed, so calculate the average in a CTE or subquery and filter in the outer query.
SELECT store_id, sale_id, total, ROUND(AVG(total) OVER (PARTITION BY store_id), 2) AS store_avg FROM sales;Practice: Purchases over 3x the customer's average →
SUM(SUM()) OVER (PARTITION BY) · share of group
A share of a group needs two levels at once: each row's total and its group's total. GROUP BY gives the row level, and a window over the grouped rows, SUM(SUM(x)) OVER (PARTITION BY group), adds the group total to each of them. Leave out PARTITION BY and you divide by the grand total instead.
SELECT region, store_id, SUM(sales) AS store_sales, SUM(SUM(sales)) OVER (PARTITION BY region) AS region_sales FROM daily_sales GROUP BY region, store_id;Practice: Merchant share of category spend →
generate_series · LEFT JOIN · signed sums
A daily report needs a row for every day, but GROUP BY only returns days that have data. Build the calendar with generate_series(start, end, INTERVAL '1 day'), LEFT JOIN the activity onto it, and wrap sums in COALESCE(..., 0), because SUM over no rows is NULL. When the direction is a separate column, turn it into a sign with CASE before summing.
SELECT d::date AS day,
COALESCE(SUM(CASE WHEN e.kind = 'checkout' THEN 1 ELSE -1 END), 0) AS net_loans
FROM generate_series('2024-05-01'::date, '2024-05-07'::date, INTERVAL '1 day') AS d
LEFT JOIN library_events e ON e.happened_at::date = d::date
GROUP BY d
ORDER BY d;Practice: Daily net cash flow, quiet days included →Self-join on a time window
To compare every event with every later event of the same entity, self-join the table: same key, the second row strictly later and inside the window (b.t > a.t AND b.t <= a.t + INTERVAL '...'). LAG and LEAD only look one row away, so they miss pairs with another event in between. Then add the condition that makes a pair interesting.
SELECT a.badge_id, a.building AS first_building, b.building AS second_building FROM badge_swipes a JOIN badge_swipes b ON b.badge_id = a.badge_id AND b.swiped_at > a.swiped_at AND b.swiped_at <= a.swiped_at + INTERVAL '5 minutes' AND b.building <> a.building;Practice: Same card, two cities, one hour →
LATERAL · latest row on or before a date
Rate tables only have rows on the days a rate changes, so most dates need 'the latest row on or before this date'. A LATERAL subquery runs once per outer row and can use its columns: WHERE r.day <= outer.day ORDER BY r.day DESC LIMIT 1 picks exactly that row. An equality join misses the dates between changes, and a plain <= join returns every earlier rate.
SELECT o.order_id, o.ordered_on, t.vat_rate FROM orders o CROSS JOIN LATERAL ( SELECT vat_rate FROM vat_rates v WHERE v.country = o.country AND v.valid_from <= o.ordered_on ORDER BY v.valid_from DESC LIMIT 1 ) t;Practice: Convert at the rate in effect →
LAG with offsets · consecutive declines
LAG(x, n) OVER (PARTITION BY key ORDER BY t) reads the value n rows back in the same partition, so one row can see several earlier ones at once. 'Fell three months in a row' needs four month-ends: this month below last month, last month below the one before, and that one below the one before it. The first rows of each partition get NULL from LAG, and a comparison with NULL is never true, so they drop out on their own.
SELECT sensor_id, read_at, temp
FROM (
SELECT sensor_id, read_at, temp,
LAG(temp, 1) OVER w AS t1,
LAG(temp, 2) OVER w AS t2
FROM readings
WINDOW w AS (PARTITION BY sensor_id ORDER BY read_at)
) r
WHERE temp > t1 AND t1 > t2;Practice: Balance down three months in a row →FULL OUTER JOIN · IS DISTINCT FROM · CASE
Reconciliation compares two lists that should agree. FULL OUTER JOIN on the shared key keeps rows from both sides, with NULLs where one side has no match. A comparison with NULL is never true, so a plain a.amount <> b.amount silently drops the one-sided rows; IS DISTINCT FROM treats a missing side as a difference. A CASE that checks which side is NULL then labels each break.
SELECT COALESCE(s.sku, w.sku) AS sku, s.qty AS system_qty, w.qty AS counted_qty FROM stock_system s FULL OUTER JOIN warehouse_count w ON w.sku = s.sku WHERE s.qty IS DISTINCT FROM w.qty;Practice: Reconcile the books with the bank →
COALESCE · decimal division
Utilization is used divided by available, times 100. Two things go wrong: a NULL anywhere in the math makes the whole result NULL, and dividing two integers throws away the decimals. COALESCE(column, 0) turns a missing count into zero, and writing 100.0 (or casting to numeric) keeps the fraction.
SELECT room, COALESCE(booked_desks, 0) AS booked, ROUND(100.0 * COALESCE(booked_desks, 0) / total_desks, 1) AS occupancy_pct FROM office_floors;Practice: Seat utilization →
LEFT JOIN · MAX · date arithmetic
To find each account's most recent activity, join the activity table and take MAX of its timestamp per account. Use a LEFT JOIN so accounts with no activity at all still appear (with NULL). Subtracting two dates in Postgres gives whole days, so cast a timestamp to ::date first.
SELECT m.name, MAX(v.visited_at)::date AS last_visit FROM gym_members m LEFT JOIN gym_visits v ON v.member_id = m.id WHERE m.cancelled GROUP BY m.id, m.name;Practice: Churned accounts and their last activity →
date_trunc('week') · COUNT FILTER
date_trunc('week', d) snaps any date to the Monday that starts its week, which makes a clean weekly bucket. Inside each bucket, COUNT(*) FILTER (WHERE ...) counts only the rows that pass a condition, so you can get a total and a subset in one pass. Leave out rows whose outcome isn't known yet, or they drag the rate down.
SELECT date_trunc('week', applied_on)::date AS week,
COUNT(*) AS applications,
COUNT(*) FILTER (WHERE outcome = 'hired') AS hires
FROM job_applications
WHERE outcome <> 'pending'
GROUP BY 1
ORDER BY 1;Practice: Trial-to-paid conversion by signup week →Timestamp intervals · NULL as breach · FILTER
Subtracting two timestamps gives an interval, and intervals compare directly: response - opened > 4 * INTERVAL '1 hour'. Don't pull out EXTRACT(HOUR ...), which reads only the hours field and ignores days. A missing response is NULL, and any comparison with NULL is NULL (not true), so unanswered rows need their own IS NULL check.
SELECT carrier,
COUNT(*) FILTER (WHERE delivered_at IS NULL
OR delivered_at - shipped_at > promised_days * INTERVAL '1 day') AS late
FROM shipments
GROUP BY carrier;Practice: Support SLA breach rate by priority →LAG · PARTITION BY · CASE
LAG(col) OVER (PARTITION BY entity ORDER BY time) reads the previous row's value for the same entity, so each row can be compared with what came before. Without PARTITION BY, the previous row may belong to someone else. Each entity's first row has no previous value (NULL), so filter it out when you only want changes.
SELECT employee_id, effective_on, title,
CASE WHEN salary > LAG(salary) OVER w THEN 'raise'
WHEN salary < LAG(salary) OVER w THEN 'cut'
ELSE 'same' END AS move
FROM job_history
WINDOW w AS (PARTITION BY employee_id ORDER BY effective_on);Practice: Upgrade, downgrade or lateral →COUNT(DISTINCT) · scalar denominator · timestamp ranges
Adoption is the share of active users (here, workspaces) that used a feature at least once, so both numerator and denominator count distinct workspaces, not events. The denominator is the active base for the same period, computed once in a CTE or scalar subquery and divided into every feature's count. For a month of timestamps, use >= the 1st and < the 1st of the next month, so the last day's afternoon isn't cut off.
WITH base AS ( SELECT COUNT(DISTINCT store_id) AS n FROM sales WHERE sold_at >= '2025-01-01' AND sold_at < '2025-02-01' ) SELECT promo, ROUND(100.0 * COUNT(DISTINCT store_id) / MAX(base.n), 1) AS pct FROM sales CROSS JOIN base WHERE sold_at >= '2025-01-01' AND sold_at < '2025-02-01' GROUP BY promo;Practice: Feature adoption across active workspaces →
PERCENT_RANK · ties
PERCENT_RANK() OVER (ORDER BY x DESC) gives each row the share of other rows ranked strictly ahead of it: (rank - 1) / (rows - 1). Tied rows share a rank, so they get the same percent rank and land on the same side of any cutoff. NTILE and ROW_NUMBER, by contrast, cut groups of equal size and will split a tie arbitrarily.
SELECT seller_id, revenue, PERCENT_RANK() OVER (ORDER BY revenue DESC) AS pr FROM seller_totals ORDER BY revenue DESC;Practice: Power users: the top 10% →
MIN per entity · PERCENTILE_CONT
Time-to-value is measured per account: find each account's first qualifying event with MIN, subtract its start date, then summarize. Use the median, not the average, because a few slow accounts drag the mean up. PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY x) returns the middle value, or the halfway point between the two middle values when the count is even.
WITH firsts AS ( SELECT p.id, MIN(r.reviewed_on) - p.opened_on AS days FROM pull_requests p JOIN reviews r ON r.pr_id = p.id GROUP BY p.id, p.opened_on ) SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY days) AS median_days FROM firsts;Practice: Median days to first dashboard →
generate_series · LEFT JOIN · half-open ranges
Grouping an events table can only produce periods that have events, so a quiet week silently disappears from the chart. Generate the full calendar with generate_series, LEFT JOIN the events onto it, and count a column from the events side so empty periods show 0. Match a timestamp to its week with a half-open range: >= week_start AND < week_start + INTERVAL '1 week'.
SELECT d.day::date, COUNT(o.id) AS orders FROM generate_series(TIMESTAMP '2025-01-01', TIMESTAMP '2025-01-07', INTERVAL '1 day') AS d(day) LEFT JOIN orders o ON o.placed_at >= d.day AND o.placed_at < d.day + INTERVAL '1 day' GROUP BY d.day ORDER BY d.day;Practice: Weekly active workspaces, including empty weeks →
Range join · open-ended periods · generate_series
Subscriptions are periods, not events: one row covers many months. To get a monthly snapshot, generate the months and join every period that covers each month's first day: start <= month AND (end IS NULL OR end > month). An open-ended row has NULL as its end, and a plain comparison with NULL is never true, so it needs the IS NULL branch. Treat the end as exclusive so a plan that ends the day another begins isn't counted twice.
SELECT d.day::date, COUNT(*) AS guests FROM generate_series(TIMESTAMP '2025-08-01', TIMESTAMP '2025-08-07', INTERVAL '1 day') AS d(day) JOIN hotel_stays s ON s.check_in <= d.day AND (s.check_out IS NULL OR s.check_out > d.day) GROUP BY d.day ORDER BY d.day;Practice: MRR by month from subscription periods →
LAG with a gap check · strict thresholds
LAG returns the previous row, which isn't always the previous period: if a month is missing, it quietly compares with two months ago. Pull the previous row's date too (LAG(month)) and check it is exactly one period back, or self-join on month = previous month + INTERVAL '1 month'. Thresholds like 'more than half' are strict, so test < 0.5, not <= 0.5.
WITH r AS (
SELECT store_id, week, sales,
LAG(week) OVER w AS prev_week, LAG(sales) OVER w AS prev_sales
FROM weekly_sales
WINDOW w AS (PARTITION BY store_id ORDER BY week)
)
SELECT * FROM r
WHERE prev_week = week - 7 AND sales > prev_sales * 2;Practice: Accounts whose usage halved →Cohort snapshots · conditional SUM · revenue bridge
Net revenue retention follows a fixed cohort: the accounts paying at the start. Take each account's MRR on the start date and on the end date (0 if it churned), then split the change into expansion (went up), contraction (went down but still paying) and churn (went to 0). NRR is ending MRR over starting MRR for that cohort only; revenue from accounts that joined later is new business and stays out.
WITH m AS (
SELECT member_id,
SUM(fee) FILTER (WHERE year = 2024) AS fee_2024,
COALESCE(SUM(fee) FILTER (WHERE year = 2025), 0) AS fee_2025
FROM club_fees GROUP BY member_id
)
SELECT ROUND(100.0 * SUM(fee_2025) / SUM(fee_2024), 1) AS retention_pct
FROM m WHERE fee_2024 IS NOT NULL;Practice: Net revenue retention for a cohort →COUNT(*) FILTER · share of a group
To get the share of rows that meet a condition, count the matching rows and divide by the count of all rows in the group. COUNT(*) FILTER (WHERE condition) counts only the matching rows, while COUNT(*) counts them all. Decide what the denominator is before you write it: a NULL in the tested column fails the condition, but the row still belongs in the total.
SELECT warehouse, COUNT(*) AS shipments, COUNT(*) FILTER (WHERE days_late > 0) AS late, ROUND(100.0 * COUNT(*) FILTER (WHERE days_late > 0) / COUNT(*), 1) AS late_pct FROM shipments GROUP BY warehouse;Practice: Share of trips with surge pricing →
COUNT(*) vs COUNT(column) · NULL in the denominator
A rate is the matching events divided by all events, and NULLs are the usual way the denominator goes wrong. COUNT(*) counts every row, while COUNT(column) quietly skips rows where that column is NULL. Decide what a NULL means for the business first, then pick the COUNT that matches it.
SELECT agent_id, COUNT(*) AS tickets, COUNT(closed_at) AS closed, ROUND(100.0 * COUNT(closed_at) / COUNT(*), 1) AS closed_pct FROM support_tickets GROUP BY agent_id;Practice: Driver acceptance rate →
COALESCE · NULLIF · safe ratios
Arithmetic with NULL gives NULL: 84 + 12 + NULL is NULL, not 96. When a missing value really means zero, wrap it in COALESCE(column, 0). On the other side of the division, NULLIF(denominator, 0) turns a zero into NULL, so the result is NULL instead of a division-by-zero error.
SELECT campaign_id, COALESCE(clicks, 0) AS clicks, ROUND(100.0 * COALESCE(clicks, 0) / NULLIF(sends, 0), 2) AS click_pct FROM email_campaigns;Practice: Engagement rate per post →
Self-join on reversed pairs
A follow only goes one way, so a mutual follow is two rows: A follows B and B follows A. Join the table to itself with the columns swapped, b.follower_id = a.followee_id AND b.followee_id = a.follower_id. Every mutual pair then matches twice, once from each side; keep one copy with a.follower_id < a.followee_id, which also drops anyone who follows themselves.
SELECT a.sender_id, a.receiver_id FROM referrals a JOIN referrals b ON b.sender_id = a.receiver_id AND b.receiver_id = a.sender_id WHERE a.sender_id < a.receiver_id;Practice: Mutual follows →
EXTRACT(HOUR) · conditional COUNT
To compare times of day across many dates, group by EXTRACT(HOUR FROM ts), which returns 0-23 and merges 8 a.m. on Monday with 8 a.m. on Tuesday. date_trunc('hour', ts) keeps the date, so it gives one row per calendar hour instead. When several status values mean the same thing, list them all with IN.
SELECT EXTRACT(HOUR FROM placed_at)::int AS hour,
COUNT(*) AS deliveries,
COUNT(*) FILTER (WHERE status IN ('lost', 'damaged')) AS problems
FROM deliveries
GROUP BY 1
ORDER BY 1;Practice: Cancellation rate by hour of day →Date window · HAVING · COUNT(column)
A window like 'the 30 days ending June 30' has two edges, and both are easy to get wrong by a day. Filter the rows to the window in WHERE, then apply a minimum-count rule to each group with HAVING. COUNT(*) counts every row, but COUNT(rating) counts only rows with a rating, which are the same rows AVG(rating) averages.
SELECT restaurant_id, COUNT(score) AS scored_visits, ROUND(AVG(score), 1) AS avg_score FROM inspections WHERE inspected_on >= '2025-01-01' AND inspected_on < '2025-04-01' GROUP BY restaurant_id HAVING COUNT(score) >= 5;Practice: Recent rating with a minimum review count →
Timestamp to date · COUNT(DISTINCT) · HAVING
Event tables store timestamps, so 'the same day' means casting to a date first: started_at::date. Rewatches and replays create several rows for the same item, so count COUNT(DISTINCT item) when the question is about different items. HAVING then keeps only the groups that clear the bar.
SELECT customer_id, visited_at::date AS visit_day, COUNT(DISTINCT store_id) AS stores FROM store_visits GROUP BY customer_id, visited_at::date HAVING COUNT(DISTINCT store_id) >= 2;Practice: Binge-watch days →
RANK() · top-1 per group with ties
For 'the top item in each group', rank inside each group and keep rank 1. ROW_NUMBER gives every row a different number, so when two items tie it silently picks one; RANK and DENSE_RANK give tied rows the same number, so both survive. Aggregate first and rank the totals: window functions run after GROUP BY, so RANK() OVER (... ORDER BY SUM(x) DESC) works in the same query.
SELECT region, rep, total_sales
FROM (
SELECT region, rep, SUM(amount) AS total_sales,
DENSE_RANK() OVER (PARTITION BY region ORDER BY SUM(amount) DESC) AS rnk
FROM deals
GROUP BY region, rep
) AS ranked
WHERE rnk <= 3;Practice: Top artist per country, ties included →Aggregate before joining · clipping stays to a month
Joining a one-row-per-listing table to many bookings repeats the listing's columns once per booking, so SUM(nights_available) after that join counts the same nights several times. Total the bookings per listing in a CTE first, then LEFT JOIN that single row per listing. To count only the nights inside a month, clip each stay with LEAST(check_out, next_month_start) - GREATEST(check_in, month_start), and keep only stays that overlap the month so the result never goes negative.
SELECT room_id, SUM(LEAST(ends_on, DATE '2025-04-01') - GREATEST(starts_on, DATE '2025-03-01')) AS march_days FROM room_holds WHERE starts_on < DATE '2025-04-01' AND ends_on > DATE '2025-03-01' GROUP BY room_id;Practice: Host occupancy rate →
Relational division · HAVING COUNT(DISTINCT) = total
'Users who did every X' is relational division: count the distinct X each user covered and compare it with the total number of X in HAVING. Take the total from the table that defines the set, not from the activity table, because an item nobody has touched never appears in the activity. Count DISTINCT on the activity side, so doing one item twice can't make up for one you skipped.
SELECT s.student_id FROM submissions s JOIN assignments a ON a.assignment_id = s.assignment_id WHERE a.course_id = 7 GROUP BY s.student_id HAVING COUNT(DISTINCT s.assignment_id) = (SELECT COUNT(*) FROM assignments WHERE course_id = 7);Practice: Watched every episode of a season →
Both directions of a pair · two-hop self-join · anti-join
Friendships are often stored once per pair (user_a < user_b), so first unfold them into both directions with UNION ALL; after that, 'my friends' is one lookup. A friend of a friend is a two-hop self-join on that list, and the number of paths from you to someone is the number of mutual friends you share. Remove yourself and people who are already your friends before counting, with <> and NOT EXISTS.
WITH links AS ( SELECT city_a AS src, city_b AS dst FROM routes UNION ALL SELECT city_b, city_a FROM routes ) SELECT l1.src, l2.dst AS two_hops_away FROM links l1 JOIN links l2 ON l2.src = l1.dst WHERE l2.dst <> l1.src;Practice: People you may know →
Gaps and islands on weeks
Gaps and islands works on any regular step, not just days. Bucket each event into its week with date_trunc('week', ts) (Postgres weeks start on Monday), dedupe to one row per creator and week, then subtract 7 days times ROW_NUMBER: every week in an unbroken run lands on the same anchor date. Avoid week numbers like EXTRACT(WEEK), which restart every year, and the last days of December can already be week 1, so a streak over New Year breaks.
WITH m AS (
SELECT DISTINCT store_id, date_trunc('month', sold_at)::date AS month
FROM sales
)
SELECT store_id, month,
ROW_NUMBER() OVER (PARTITION BY store_id ORDER BY month) AS rn
FROM m;Practice: Longest weekly posting streak →Date subtraction · AVG · IS NOT NULL
Subtracting two dates in Postgres gives a whole number of days, so discharge minus admission is a stay's length in nights. AVG skips NULLs on its own, but COUNT(*) does not: rows that haven't finished yet (no end date) still get counted. Filter them out with IS NOT NULL so the count and the average describe the same rows.
SELECT warehouse, COUNT(*) AS delivered, ROUND(AVG(delivered_on - shipped_on), 2) AS avg_days FROM shipments WHERE delivered_on IS NOT NULL GROUP BY warehouse;Practice: Average length of stay by department →
COUNT FILTER · choosing the denominator
A utilization rate is used capacity over offered capacity. The hard part is the denominator: time that was never offered (holidays, blocked time) isn't idle capacity, so it belongs in neither number. COUNT(*) FILTER (WHERE ...) gives you both counts in one pass, and keeps a group whose numerator is 0 instead of dropping it.
SELECT room, COUNT(*) FILTER (WHERE state = 'occupied') AS used, COUNT(*) FILTER (WHERE state <> 'maintenance') AS offered FROM room_hours GROUP BY room;Practice: Doctor utilization →
WHERE vs HAVING · minimum volume
WHERE filters rows before grouping, and HAVING filters groups after. A rate should only use rows whose outcome is known, so undecided rows go in WHERE. A minimum-volume rule (at least N cases) is about the group, so it goes in HAVING, and it should count the same rows the rate uses.
SELECT referee, COUNT(*) AS reviewed, ROUND(100.0 * COUNT(*) FILTER (WHERE overturned) / COUNT(*), 1) AS overturn_pct FROM match_calls WHERE review_status = 'final' GROUP BY referee HAVING COUNT(*) >= 10;Practice: Claim denial rate by insurer →
COUNT(DISTINCT) · HAVING · date range
To count different things, not rows, use COUNT(DISTINCT col): five visits with two people is 2. HAVING then keeps only groups that pass a threshold. Apply the row-level rules (which statuses count, which dates) in WHERE first, so they shape both counts.
SELECT customer_id, COUNT(DISTINCT store_id) AS stores FROM purchases WHERE status = 'completed' AND bought_on BETWEEN '2025-01-01' AND '2025-03-31' GROUP BY customer_id HAVING COUNT(DISTINCT store_id) >= 3;Practice: Patients seen by 3+ doctors →
LEFT JOIN with ON filters · MAX · NULLS FIRST
To include people with no matching rows, LEFT JOIN and put the conditions on the joined table inside ON. In WHERE they would turn the NULL rows into failures and remove them. Filter the groups with HAVING on MAX(...) IS NULL OR the date test. Ascending sorts put NULLs last in Postgres, so add NULLS FIRST when they should lead.
SELECT c.name, MAX(s.serviced_on) AS last_service FROM cars c LEFT JOIN services s ON s.car_id = c.id AND s.kind = 'annual' GROUP BY c.id, c.name HAVING MAX(s.serviced_on) IS NULL OR MAX(s.serviced_on) < DATE '2025-01-01' - 365 ORDER BY last_service NULLS FIRST, c.name;Practice: Patients overdue for a checkup →
to_char weekday · RANK per group · ties
to_char(date, 'FMDay') gives the weekday name; without FM, Postgres pads it to 9 characters ('Monday '). To find the top group within each partition, aggregate, then RANK() OVER (PARTITION BY ... ORDER BY COUNT(*) DESC) and keep rank 1. RANK gives tied groups the same rank, so a tie returns both, where ROW_NUMBER or LIMIT would silently pick one.
WITH h AS (
SELECT store, EXTRACT(HOUR FROM sold_at) AS hr, COUNT(*) AS sales,
RANK() OVER (PARTITION BY store ORDER BY COUNT(*) DESC) AS rk
FROM sales GROUP BY store, 2
)
SELECT store, hr, sales FROM h WHERE rk = 1;Practice: Busiest day of the week per clinic →age() · CASE buckets · EXISTS
age(as_of, birth_date) returns an interval that accounts for whether the birthday has happened yet, so EXTRACT(YEAR FROM age(...)) is the real age. Subtracting birth years is wrong for everyone whose birthday is later in the year. Bucket ages with CASE, and test 'has at least one matching row' with EXISTS, so people with no rows stay in the denominator and people with two rows aren't counted twice.
SELECT CASE WHEN tenure < 1 THEN '<1y' WHEN tenure < 5 THEN '1-4y' ELSE '5y+' END AS band, COUNT(*) AS staff, COUNT(*) FILTER (WHERE EXISTS (SELECT 1 FROM trainings t WHERE t.staff_id = s.id)) AS trained FROM (SELECT id, EXTRACT(YEAR FROM age(DATE '2025-01-01', hired_on)) AS tenure FROM staff) s GROUP BY 1 ORDER BY 1;Practice: Flu vaccination coverage by age band →
Correlated MIN · PERCENTILE_CONT · ignoring NULLs
Wait time is measured per referral: find the first qualifying event on or after the start date with MIN, subtract, then take the median. A scalar subquery per referral returns NULL when nothing qualifies, which keeps the referral in the count while PERCENTILE_CONT skips it. Median beats average here because a few long waits pull the mean up.
WITH w AS (
SELECT t.category,
(SELECT MIN(r.replied_at::date) FROM replies r
WHERE r.ticket_id = t.id AND r.replied_at >= t.opened_at) - t.opened_at::date AS days
FROM tickets t
)
SELECT category, COUNT(*) AS tickets, COUNT(days) AS answered,
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY days) AS median_days
FROM w GROUP BY category;Practice: Median wait from referral to first visit →EXISTS · self-reference · inclusive date window · observation cutoff
A readmission rate asks, for each finished stay, whether the same patient came back within N days. EXISTS answers yes or no once per stay; a plain self-join returns one row per return visit and double counts. Exclude the stay itself, count the boundary day, and only use stays that ended early enough for the full window to be observed.
WITH o AS (
SELECT o.region,
EXISTS (SELECT 1 FROM orders r WHERE r.customer_id = o.customer_id AND r.id <> o.id
AND r.ordered_on BETWEEN o.ordered_on AND o.ordered_on + 14) AS reordered
FROM orders o WHERE o.ordered_on <= DATE '2025-03-17'
)
SELECT region, ROUND(100.0 * COUNT(*) FILTER (WHERE reordered) / COUNT(*), 1) AS pct
FROM o GROUP BY region;Practice: 30-day readmission rate →Self-join · interval overlap · open-ended ranges
Two ranges overlap when each one starts on or before the other ends: a.start <= b.end AND b.start <= a.end. Self-join on the shared key with a.id < b.id so each pair appears once and nothing pairs with itself. An open-ended range has NULL as its end, so swap in COALESCE(end, 'infinity'); GREATEST of the starts and LEAST of the ends give the overlap itself (LEAST skips NULLs).
SELECT a.room, a.id, b.id, GREATEST(a.starts_on, b.starts_on) AS clash_from FROM bookings a JOIN bookings b ON b.room = a.room AND a.id < b.id AND a.starts_on <= COALESCE(b.ends_on, 'infinity') AND b.starts_on <= COALESCE(a.ends_on, 'infinity');Practice: Overlapping prescriptions →
Running SUM · opening balance · generate_series
Occupancy from in/out events is a running total: +1 for each arrival, -1 for each departure, summed in date order with SUM(...) OVER (ORDER BY day). Two things break it: days with no events vanish unless you generate a calendar, and anyone who arrived before your window is missing unless you add the opening balance (net arrivals before the first day).
WITH cal AS ( SELECT g::date AS day FROM generate_series(TIMESTAMP '2025-01-01', TIMESTAMP '2025-01-07', INTERVAL '1 day') g ), d AS ( SELECT c.day, COUNT(m.id) FILTER (WHERE m.kind = 'join') - COUNT(m.id) FILTER (WHERE m.kind = 'leave') AS net FROM cal c LEFT JOIN memberships m ON m.at::date = c.day GROUP BY c.day ) SELECT day, (SELECT COUNT(*) FROM members_before_2025) + SUM(net) OVER (ORDER BY day) AS members FROM d;Practice: ICU beds occupied per day →
Month × group grid · LAG · NULLIF · RANK NULLS LAST
Before using LAG for month-over-month change, build every month for every group (CROSS JOIN groups with generate_series, LEFT JOIN the facts), so a month with zero activity is a 0 row and LAG doesn't jump over the gap. Growth from 0 is undefined: NULLIF(prev, 0) makes it NULL instead of an error. When ranking with DESC, Postgres puts NULLs first, so say NULLS LAST.
WITH g AS ( SELECT s.store, m.month, COUNT(o.id) AS orders FROM (SELECT DISTINCT store FROM orders) s CROSS JOIN generate_series(TIMESTAMP '2025-01-01', TIMESTAMP '2025-04-01', INTERVAL '1 month') AS m(month) LEFT JOIN orders o ON o.store = s.store AND o.placed_on >= m.month AND o.placed_on < m.month + INTERVAL '1 month' GROUP BY s.store, m.month ) SELECT store, month, orders, ROUND(100.0 * (orders - LAG(orders) OVER w) / NULLIF(LAG(orders) OVER w, 0), 1) AS growth FROM g WINDOW w AS (PARTITION BY store ORDER BY month);Practice: Ranking departments by month-over-month growth →
LEFT JOIN · condition in ON · COUNT(DISTINCT)
Conversion is the share of all players who paid, so the denominator has to include the players who never bought anything. Start from the players table and LEFT JOIN the purchases; non-payers then get NULLs on the purchase side. Put any filter on the purchases (like skipping refunds) in the ON clause: in WHERE it would also throw away the NULL rows. After the join, count players with COUNT(DISTINCT ...) so repeat buyers aren't counted twice.
SELECT m.gym, COUNT(DISTINCT m.id) AS members, COUNT(DISTINCT c.member_id) AS booked, ROUND(100.0 * COUNT(DISTINCT c.member_id) / COUNT(DISTINCT m.id), 1) AS pct FROM members m LEFT JOIN class_bookings c ON c.member_id = m.id AND NOT c.cancelled GROUP BY m.gym;Practice: Paying conversion by acquisition channel →
COUNT FILTER · HAVING
A rate needs two counts from the same group: all the rows (the denominator) and the ones that pass a condition. COUNT(*) FILTER (WHERE ...) gives you the second without throwing away the first, which a WHERE clause would. To ignore groups that are too small to trust, filter after grouping with HAVING, since WHERE can't see counts.
SELECT agent, COUNT(*) AS calls, ROUND(100.0 * COUNT(*) FILTER (WHERE outcome = 'resolved') / COUNT(*), 1) AS resolved_pct FROM support_calls GROUP BY agent HAVING COUNT(*) >= 20;Practice: Win rate for regular players →
MAX per player · LEFT JOIN · COALESCE
To see where people stop, first boil each person down to one number (their furthest step, with MAX), then count people per value. Counting raw rows per step answers a different question: how many people passed it. People with no rows at all stopped before step one, so start from the full list of people, LEFT JOIN the progress, and COALESCE their NULL to 0.
WITH furthest AS ( SELECT s.id, COALESCE(MAX(m.module_no), 0) AS last_module FROM students s LEFT JOIN module_passes m ON m.student_id = s.id GROUP BY s.id ) SELECT last_module, COUNT(*) AS students FROM furthest GROUP BY last_module;Practice: Where players stop: furthest level reached →
generate_series · LEFT JOIN · COUNT(DISTINCT)
A funnel needs one row per step, even steps nobody has reached, and grouping the event table can only produce steps that have events. Generate the steps with generate_series, LEFT JOIN the events onto them, and count a column from the events side so empty steps show 0. Use COUNT(DISTINCT person) because people repeat steps, and divide by the whole population, not by step one.
SELECT s.step, COUNT(DISTINCT v.visitor_id) AS visitors FROM generate_series(1, 4) AS s(step) LEFT JOIN checkout_steps v ON v.step = s.step GROUP BY s.step ORDER BY s.step;Practice: Campaign completion funnel →
Per-player totals · LEFT JOIN · two denominators
ARPU (average revenue per user) divides revenue by every user; ARPPU (per paying user) divides the same revenue by payers only. Total each user's spend first, then LEFT JOIN those totals onto the users table: COUNT(*) is then all users and COUNT(spend column) is just the payers, since COUNT skips NULLs. Averaging the raw purchase rows gives the average purchase, which is a third, different number.
WITH tips AS ( SELECT driver_id, SUM(tip) AS total FROM rides GROUP BY driver_id ) SELECT d.city, ROUND(COALESCE(SUM(t.total), 0) / COUNT(*), 2) AS per_driver, ROUND(SUM(t.total) / COUNT(t.driver_id), 2) AS per_tipped_driver FROM drivers d LEFT JOIN tips t ON t.driver_id = d.id GROUP BY d.city;Practice: ARPU vs ARPPU by platform →
Self-join · strict comparison
When each event is stored as one row per participant, a self-join puts two participants side by side: join the table to itself on the shared id, and give each copy a role with a condition (here, one copy is the winner and the other the loser). Spelling out both roles also stops each pair from appearing twice, once in each direction. Then compare the two copies' columns directly.
SELECT a.game_id, a.team AS home, b.team AS away FROM game_teams a JOIN game_teams b ON b.game_id = a.game_id AND b.side = 'away' WHERE a.side = 'home' AND a.payroll < b.payroll;Practice: Upsets: the lower-rated player won →
DENSE_RANK · PARTITION BY · NULL sorting
DENSE_RANK() OVER (PARTITION BY group ORDER BY score DESC) numbers rows within each group so tied scores share a place and the next place follows without a gap (1, 1, 2). RANK leaves a gap after ties (1, 1, 3) and ROW_NUMBER splits ties arbitrarily. Watch out for NULL scores: Postgres sorts NULL first in descending order, so filter them out or add NULLS LAST.
WITH r AS (
SELECT league, club, goals,
DENSE_RANK() OVER (PARTITION BY league ORDER BY goals DESC) AS place
FROM club_goals
WHERE goals IS NOT NULL
)
SELECT * FROM r WHERE place <= 2;Practice: Season podium with ties →RANK with ties · SUM() OVER () before filtering
To list the top spenders and the share each brings in, total spend per person, rank them with RANK() so ties share a place, and divide each total by SUM(total) OVER (), the grand total across every row. Window functions run after WHERE, so compute that share in a CTE before filtering to the top ranks; otherwise the grand total only covers the rows you kept.
WITH t AS (
SELECT author_id, SUM(copies) AS sold,
RANK() OVER (ORDER BY SUM(copies) DESC) AS r,
100.0 * SUM(copies) / SUM(SUM(copies)) OVER () AS share
FROM book_sales GROUP BY author_id
)
SELECT * FROM t WHERE r <= 5;Practice: Whales and their share of revenue →LEAD · PARTITION BY · pairing events
When start and end are separate event rows, pair each start with the event right after it for the same person: LEAD(event_at) and LEAD(event_type) OVER (PARTITION BY person ORDER BY event_at). Keep only the pairs where a start is followed by an end; a start followed by another start (or by nothing) never finished. Measure the gap with EXTRACT(EPOCH FROM interval) / 60, which counts the whole interval in minutes.
WITH e AS (
SELECT machine_id, kind, at,
LEAD(kind) OVER w AS next_kind, LEAD(at) OVER w AS next_at
FROM machine_events
WINDOW w AS (PARTITION BY machine_id ORDER BY at)
)
SELECT machine_id, AVG(EXTRACT(EPOCH FROM next_at - at) / 60) AS avg_run_minutes
FROM e WHERE kind = 'power_on' AND next_kind = 'power_off'
GROUP BY machine_id;Practice: Session length from start and end events →Calendar-day offsets · LEFT JOIN · cohort cutoff
Day-N retention asks whether someone came back on the Nth calendar day after they started, so compare dates, not timestamps: activity::date = start::date + N. Put that condition in a LEFT JOIN's ON clause so people who didn't return still count in the denominator, and count with DISTINCT in case they returned twice that day. Leave out anyone whose day N is after the data ends, since they haven't had the chance to come back yet.
SELECT date_trunc('month', m.joined_at)::date AS cohort,
COUNT(DISTINCT m.id) AS members,
COUNT(DISTINCT v.member_id) AS back_on_day_30
FROM members m
LEFT JOIN visits v ON v.member_id = m.id
AND v.visited_at::date = m.joined_at::date + 30
WHERE m.joined_at::date + 30 <= DATE '2025-06-30'
GROUP BY 1;Practice: Day-7 retention by install week →LAG per player and item · interval comparison
To compare each event with the previous one of the same kind, partition by everything that defines 'same' (person and item) and use LAG(timestamp). Subtracting two timestamps gives an interval you can compare directly with INTERVAL '24 hours', which is exact to the second, unlike comparing calendar dates. A self-join on 'within 24 hours' finds every pair in the window, not just consecutive ones, so it over-counts when there are three or more events.
WITH c AS (
SELECT patient_id, test, taken_at,
LAG(taken_at) OVER (PARTITION BY patient_id, test ORDER BY taken_at) AS prev_at
FROM lab_tests
)
SELECT * FROM c WHERE taken_at - prev_at <= INTERVAL '48 hours';Practice: Same item bought twice within 24 hours →Event sweep · running SUM · tie order at the same instant
To find the most intervals open at once, turn each interval into two events, +1 at its start and -1 at its end, and take a running SUM in time order: the running total is how many are open at that moment, and its maximum is the peak. When an end and a start share a timestamp and ends are exclusive, process the -1 first (ORDER BY time, delta), or you count a moment of overlap that never happened.
WITH ev AS ( SELECT check_in AS at, 1 AS delta FROM hotel_stays UNION ALL SELECT check_out, -1 FROM hotel_stays ) SELECT at, SUM(delta) OVER (ORDER BY at, delta ROWS UNBOUNDED PRECEDING) AS occupied FROM ev ORDER BY occupied DESC, at LIMIT 1;Practice: Peak concurrent players per region →
COUNT FILTER · timestamp vs date · excluding NULLs
An on-time rate is on-time deliveries divided by deliveries, so first decide what belongs in the denominator: a shipment still in transit has no outcome yet. When a promise is a date and the delivery is a timestamp, compare like with like: a timestamp compared to a date is compared to midnight at the start of that day. Cast the timestamp with ::date first.
SELECT clinic, COUNT(*) AS seen, COUNT(*) FILTER (WHERE seen_at::date <= booked_for) AS seen_on_day FROM appointments WHERE seen_at IS NOT NULL GROUP BY clinic;Practice: On-time delivery rate by carrier →
Date subtraction · AVG skips NULLs, COUNT(*) doesn't
Subtracting one date from another in Postgres gives a whole number of days, so casting two timestamps to ::date and subtracting them counts calendar days. Subtracting the timestamps themselves gives an interval like '1 day 20:00', which can't be averaged into a plain number of days without extra work. AVG ignores NULLs, but COUNT(*) does not, so filter out unfinished rows if you report a count next to an average.
SELECT branch, COUNT(*) AS closed_loans, ROUND(AVG(returned_at::date - borrowed_at::date), 1) AS avg_days FROM library_loans WHERE returned_at IS NOT NULL GROUP BY branch;Practice: Average transit days by route →
WHERE with NULLs · COUNT · MIN
A stock-out is a day with nothing to pick, and that includes oversold counts that went below zero, so test on_hand <= 0 rather than = 0. A NULL count means the number is unknown, not zero: a plain comparison with NULL is never true, so WHERE on_hand <= 0 already skips those days. Turning NULL into 0 with COALESCE invents stock-outs that may never have happened.
SELECT station, COUNT(*) AS empty_days, MIN(reading_date) AS first_empty FROM fuel_tank_readings WHERE litres <= 0 GROUP BY station;Practice: Warehouse stock-out days →
COALESCE to a snapshot date · date arithmetic
Lateness has two cases: delivered after the promise, or not delivered and already past it. COALESCE(delivered_at::date, snapshot_date) handles both in one expression, treating an open shipment as if it lands on the snapshot day, so the same comparison and subtraction work for every row. Use a fixed snapshot date rather than current_date, or the answer changes every day the data sits still.
SELECT id, due_on, COALESCE(returned_on, DATE '2025-01-31') - due_on AS days_overdue FROM tool_rentals WHERE COALESCE(returned_on, DATE '2025-01-31') > due_on;Practice: Late shipments and days late →
CASE bands · ratio of sums · sort key for labels
Bucket a number with CASE WHEN x < a THEN ... WHEN x < b THEN ... ELSE ... END; checking the upper bounds in order makes each boundary value land in exactly one band. A rate like cost per kg is total cost divided by total kg, not the average of each row's rate, which would give a 1 kg parcel the same say as a 40 kg pallet. Text labels sort alphabetically, so keep a numeric band key for ORDER BY.
SELECT CASE WHEN km < 10 THEN 1 WHEN km < 50 THEN 2 ELSE 3 END AS band, ROUND(SUM(fare) / SUM(km), 2) AS fare_per_km FROM taxi_trips GROUP BY 1 ORDER BY 1;Practice: Cost per kg by carrier and weight band →
Latest snapshot · SUM across locations · LEFT JOIN
A daily snapshot table holds one row per day, so current stock is the rows for the latest date only; summing every day counts the same units over and over. A network-wide threshold compares against the total across all locations, so SUM before comparing. Start from the item list and LEFT JOIN the stock: an item with no stock rows at all is the most urgent, and an inner join silently drops it.
WITH today AS ( SELECT drug_id, SUM(units) AS units FROM pharmacy_stock WHERE counted_on = DATE '2025-02-28' GROUP BY drug_id ) SELECT d.name, COALESCE(t.units, 0) AS units FROM drugs d LEFT JOIN today t ON t.drug_id = d.id WHERE COALESCE(t.units, 0) < d.min_units;Practice: Reorder alerts with supplier lead times →
EXTRACT(HOUR) · RANK per group · ties
EXTRACT(HOUR FROM ts) gives the hour of day (0-23), so the same hour on different days falls in one bucket; date_trunc('hour', ts) keeps the date and makes a separate bucket for every day. To keep the top row in each group, rank within the group with RANK() OVER (PARTITION BY group ORDER BY count DESC) and keep rank 1. RANK gives tied rows the same rank, so a tie for first shows every winner, while ROW_NUMBER keeps one at random.
WITH h AS ( SELECT branch, EXTRACT(HOUR FROM paid_at) AS hr, COUNT(*) AS sales FROM till_sales GROUP BY 1, 2 ) SELECT branch, hr, sales FROM (SELECT *, RANK() OVER (PARTITION BY branch ORDER BY sales DESC) AS r FROM h) x WHERE r = 1;Practice: Busiest pick hour per warehouse →
First row per group · DISTINCT ON · denominators
To judge each entity by its first event, reduce the events to one row per entity first: DISTINCT ON (entity) ... ORDER BY entity, event_time keeps the earliest. Order by the timestamp, not the id, because ids record when a row was inserted, not when the event happened. Then aggregate those first rows; counting raw event rows would put retries in the denominator.
WITH first_try AS ( SELECT DISTINCT ON (student_id) student_id, passed FROM exam_sittings ORDER BY student_id, sat_on ) SELECT ROUND(100.0 * COUNT(*) FILTER (WHERE passed) / COUNT(*), 1) AS first_time_pass_pct FROM first_try;Practice: First-attempt delivery success rate →
LAG over two partition keys · interval thresholds
LAG(ts) OVER (PARTITION BY ... ORDER BY seq) puts the previous row's value beside the current one, and the partition decides what 'previous' means: a route is a driver on a day, so partition by both. Subtracting timestamps gives an interval, and EXTRACT(MINUTE ...) reads only its minutes field (1 hour 10 minutes gives 10). Compare the interval to INTERVAL '45 minutes' directly, or use EXTRACT(EPOCH ...) / 60 for total minutes.
WITH t AS (
SELECT nurse, shift_date, round_no, checked_at,
checked_at - LAG(checked_at) OVER (PARTITION BY nurse, shift_date ORDER BY round_no) AS gap
FROM ward_rounds
)
SELECT nurse, shift_date, round_no, EXTRACT(EPOCH FROM gap) / 60 AS gap_min
FROM t WHERE gap > INTERVAL '2 hours';Practice: Long gaps between a driver's stops →Signed running SUM · PARTITION BY · tie-breaking the frame
A running balance is SUM(signed quantity) OVER (PARTITION BY location ORDER BY time). With ORDER BY, the default frame is RANGE ... CURRENT ROW, which includes every row that ties on the sort key, so two moves at the same timestamp both show the balance after both. Add a unique tie-breaker (ORDER BY time, id) or use ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW so each row shows its own balance.
SELECT till, logged_at, kind, amount,
SUM(CASE WHEN kind = 'in' THEN amount ELSE -amount END)
OVER (PARTITION BY till ORDER BY logged_at, id) AS cash
FROM till_movements;Practice: Running inventory balance →Latest row per group · filter after picking · interval cutoff
To judge something by its latest state, pick the latest row per group first (DISTINCT ON ... ORDER BY group, time DESC) and only then filter. Filtering the detail rows first changes which row is latest: drop 'delivered' scans first and a delivered package falls back to its previous scan. For an age test, compare against a fixed cutoff (snapshot - INTERVAL '48 hours'), and get total hours with EXTRACT(EPOCH ...) / 3600 rather than EXTRACT(HOUR ...), which only reads the hours field.
WITH last AS ( SELECT DISTINCT ON (ticket_id) ticket_id, changed_at, status FROM ticket_history ORDER BY ticket_id, changed_at DESC ) SELECT ticket_id, changed_at FROM last WHERE status <> 'closed' AND changed_at <= TIMESTAMP '2025-02-01 09:00' - INTERVAL '7 days';Practice: Packages with no scan in 48 hours →
Pre-aggregate before joining · LEFT JOIN · join fan-out
When one row (an order line) can match several rows in another table (its partial shipments), joining them repeats the first row once per match, and any SUM over its columns is inflated. Aggregate the many side to one row per key first, in a CTE, then join. Use a LEFT JOIN so lines that never shipped still count in the denominator.
WITH paid AS ( SELECT invoice_id, SUM(amount) AS paid FROM payments GROUP BY invoice_id ) SELECT i.client, SUM(i.total) AS billed, COALESCE(SUM(p.paid), 0) AS collected FROM invoices i LEFT JOIN paid p ON p.invoice_id = i.id GROUP BY i.client;Practice: Fill rate per customer →
Ratio of sums · NULLIF for zero denominators
Click-through rate is clicks / impressions and cost per click is spend / clicks. Compute both from totals (SUM over SUM), not as an average of daily ratios, which weights a quiet day the same as a busy one. When a denominator can be 0, NULLIF(denominator, 0) turns it into NULL, so the ratio comes out NULL instead of failing with division by zero.
SELECT store, ROUND(100.0 * SUM(returns) / NULLIF(SUM(orders), 0), 2) AS return_rate_pct, ROUND(SUM(refunded) / NULLIF(SUM(returns), 0), 2) AS avg_refund FROM store_days GROUP BY store;Practice: CTR and CPC per campaign →
LOWER · TRIM · NULLIF · COALESCE in GROUP BY
Hand-typed tracking values split one source into many: 'Google', ' google' and 'GOOGLE ' are three groups unless you normalize them. LOWER(TRIM(x)) fixes case and stray spaces, and grouping by that expression merges them. A blank value is an empty string, not NULL, so NULLIF(..., '') turns it into NULL before COALESCE gives every missing value one label.
SELECT COALESCE(NULLIF(LOWER(TRIM(coupon_code)), ''), '(no code)') AS coupon, COUNT(*) AS orders FROM checkouts GROUP BY 1 ORDER BY orders DESC;Practice: Clean up UTM sources before counting →
Derived denominator · date range · decimal division
Email rates are measured against delivered messages, not everything you tried to send: delivered = recipients - bounces. Compute the denominator once in the expression and divide each count by it with decimal math. For a quarter on a DATE column, >= the first day and < the first day of the next quarter catches every day, including the last one.
SELECT title, seats - no_shows AS attended, ROUND(100.0 * rated / (seats - no_shows), 1) AS rating_rate_pct FROM webinars WHERE held_on >= '2025-07-01' AND held_on < '2025-10-01' ORDER BY rating_rate_pct DESC, title;Practice: Email open, click and unsubscribe rates →
Aggregate before joining · NULLIF · NULLS LAST
Return on ad spend is revenue / spend. Spend and revenue live in different tables, each with many rows per campaign, so joining both at once pairs every spend row with every revenue row and inflates both sums. Total each table per channel on its own (one CTE each), then join the totals. A channel with no spend gets NULL ROAS via NULLIF, and NULLS LAST keeps it at the bottom of a descending sort.
WITH cost AS (SELECT region, SUM(amount) AS cost FROM shipments GROUP BY region), sales AS (SELECT region, SUM(total) AS sales FROM invoices GROUP BY region) SELECT c.region, ROUND(s.sales / NULLIF(c.cost, 0), 2) AS sales_per_cost FROM cost c LEFT JOIN sales s ON s.region = c.region ORDER BY sales_per_cost DESC NULLS LAST;Practice: ROAS by channel without double counting →
Filter before ranking · ROW_NUMBER · tie-breakers
To pick one winner per group, number the rows with ROW_NUMBER() OVER (PARTITION BY group ORDER BY metric DESC, tie-breakers) and keep row 1. Small samples produce extreme rates, so apply a minimum-volume rule in WHERE before ranking. Spell out tie-breakers in the window's ORDER BY, or the database picks between tied rows arbitrarily.
WITH r AS (
SELECT store, product, units, returns,
ROW_NUMBER() OVER (PARTITION BY store ORDER BY 1.0 * returns / units, units DESC, product) AS rn
FROM product_returns
WHERE units >= 50
)
SELECT store, product FROM r WHERE rn = 1;Practice: Best ad per campaign, with a minimum of clicks →COUNT(DISTINCT) · FILTER · CASE guard
Experiment logs often record the same user more than once, so count users with COUNT(DISTINCT user_id), and converters the same way with a FILTER. A rate from a handful of users is noise, so a common guard is CASE WHEN users >= threshold THEN rate END: the row stays in the report, but its rate is NULL until the sample is big enough.
SELECT menu_version,
COUNT(DISTINCT diner_id) AS diners,
CASE WHEN COUNT(DISTINCT diner_id) >= 30
THEN ROUND(100.0 * COUNT(DISTINCT diner_id) FILTER (WHERE ordered_dessert) / COUNT(DISTINCT diner_id), 1)
END AS dessert_pct
FROM menu_trials
GROUP BY menu_version;Practice: A/B test conversion with a sample-size guard →date_trunc('week') · LAG · PARTITION BY
Week-over-week change needs each week's count next to the previous week's for the same series. Bucket rows with date_trunc('week', d), which snaps every date to its Monday, count per bucket, then read the prior bucket with LAG(count) OVER (PARTITION BY series ORDER BY week). The first week has nothing before it, so its change is NULL.
WITH w AS (
SELECT store, date_trunc('week', sold_on)::date AS week, COUNT(*) AS sales
FROM sales GROUP BY 1, 2
)
SELECT store, week, sales,
LAG(sales) OVER (PARTITION BY store ORDER BY week) AS prev_sales
FROM w;Practice: Week-over-week signups by channel →LEFT JOIN with conditions in ON · COUNT(column)
To count related rows that meet a condition while keeping parents with none, put the condition in the LEFT JOIN's ON clause and count a column from the joined table: COUNT(t.id) is 0 when nothing matched. The same condition in WHERE throws away the unmatched parents, which quietly turns the LEFT JOIN into an inner join and shrinks your denominator.
WITH per_ticket AS ( SELECT k.id, k.opened_at, COUNT(r.id) AS replies FROM tickets k LEFT JOIN replies r ON r.ticket_id = k.id AND r.sent_at <= k.closed_at GROUP BY k.id, k.opened_at ) SELECT COUNT(*) FILTER (WHERE replies >= 2) AS back_and_forth, COUNT(*) AS tickets FROM per_ticket;Practice: Share of conversions that were multi-touch →
Spend to date · inclusive day counts · pacing ratio
Pacing compares how much of the budget is gone with how much of the flight is gone: (spend_to_date / budget) / (days_elapsed / flight_days). Both day counts include the first and last day, so add 1: end_date - start_date + 1. When the data already holds later days, sum only up to the report date, and LEFT JOIN the spend so a campaign that hasn't spent yet shows 0 rather than vanishing.
WITH used AS (SELECT project_id, SUM(hours) AS hours FROM timesheets WHERE work_date <= DATE '2025-06-30' GROUP BY project_id) SELECT p.name, COALESCE(u.hours, 0) / p.budget_hours AS budget_used, (DATE '2025-06-30' - p.kickoff + 1)::numeric / (p.deadline - p.kickoff + 1) AS time_used FROM projects p LEFT JOIN used u ON u.project_id = p.id;Practice: Budget pacing as of a report date →
NOT EXISTS vs NOT IN with NULLs · 7-day window
To find rows with no match in another table, NOT EXISTS is the safe tool. NOT IN (subquery) returns nothing at all if the subquery yields a single NULL, because x NOT IN (1, NULL) is unknown, never true. For 'the last 7 days' including the report date, that's the date minus 6 through the date; on timestamps, end with < the next day so the evening isn't cut off.
SELECT d.name
FROM drivers d
WHERE NOT EXISTS (
SELECT 1 FROM deliveries x
WHERE x.driver_id = d.id
AND x.delivered_at >= TIMESTAMP '2025-05-25'
AND x.delivered_at < TIMESTAMP '2025-06-01'
);Practice: Spend with zero conversions in the last 7 days →COUNT(*) OVER (PARTITION BY) · fractional credit
Linear attribution splits each conversion evenly across the touches that led to it: with n touches, each one gets 1/n of the conversion and 1/n of its revenue. Join the eligible touches to their conversion, get n with COUNT(*) OVER (PARTITION BY conversion), then sum the shares per channel. Write 1.0 / n, since 1 / n in integers is 0, and round only after summing.
WITH shares AS (
SELECT b.id, a.author, b.royalty,
COUNT(*) OVER (PARTITION BY b.id) AS n
FROM books b JOIN book_authors a ON a.book_id = b.id
)
SELECT author, ROUND(SUM(1.0 / n), 2) AS books_credited, ROUND(SUM(royalty / n), 2) AS royalty
FROM shares GROUP BY author;Practice: Linear attribution across touches →Running SUM per cohort · first crossing · open cohorts
Payback asks when cumulative revenue first covers the acquisition cost. Build the running total with SUM(revenue) OVER (PARTITION BY cohort ORDER BY month), then take the smallest month where it reaches the cost: MIN(month) FILTER (WHERE running >= cost). Cohorts that haven't paid back must still appear with NULL, so start from the cohort table and LEFT JOIN, rather than filtering them away in WHERE.
WITH run AS (
SELECT machine_id, week,
SUM(output_value) OVER (PARTITION BY machine_id ORDER BY week) AS cum_value
FROM machine_output
)
SELECT m.id, MIN(r.week) FILTER (WHERE r.cum_value >= m.price) AS payback_week
FROM machines m LEFT JOIN run r ON r.machine_id = m.id
GROUP BY m.id;Practice: CAC payback by cohort →CASE · INTERVAL anniversaries · custom sort
CASE turns a continuous value into labelled buckets: the first WHEN that matches wins, so order the branches from one end of the scale to the other. For tenure, compare the hire date with the snapshot date minus an INTERVAL ('1 year', '2 years'), which lands on real anniversaries; dividing a day count by 365 drifts across leap days. Labels sort alphabetically, so order the groups by something on the real scale, like MIN(hire_date).
SELECT CASE WHEN joined_on > DATE '2025-01-01' - INTERVAL '6 months' THEN 'New'
ELSE 'Established' END AS member_type,
COUNT(*) AS members
FROM gym_members
GROUP BY 1
ORDER BY MIN(joined_on) DESC;Practice: Tenure buckets for current staff →Self LEFT JOIN · COUNT(column) · filter in ON
To count each manager's reports, join the employees table to itself: one copy plays the manager, the other the report. A LEFT JOIN keeps managers with nobody under them, and COUNT(r.id) counts only matched reports, so they get 0 (COUNT(*) would count the empty row as 1). A condition on the report side belongs in the ON clause; in WHERE it throws away the very rows the LEFT JOIN kept.
SELECT t.name, COUNT(s.id) AS open_tasks FROM teams t LEFT JOIN tasks s ON s.team_id = t.id AND s.done = false GROUP BY t.id, t.name;Practice: Span of control, zero included →
WHERE IN · COUNT FILTER · HAVING
A rate needs the right denominator: only outcomes that have actually been decided. Filter rows first with WHERE, count the successes with COUNT(*) FILTER (WHERE ...), and divide with 100.0 so the math stays decimal. HAVING then drops groups too small to judge; it runs after WHERE, so it only counts the rows that survived.
SELECT agent, COUNT(*) AS closed_tickets, ROUND(100.0 * COUNT(*) FILTER (WHERE solved_first_contact) / COUNT(*), 1) AS fcr_pct FROM support_tickets WHERE status = 'closed' GROUP BY agent HAVING COUNT(*) >= 10;Practice: Offer acceptance rate by recruiter →
DISTINCT ON · as-of filter · employment check
A history table holds one row per change, so 'the value on date X' is the latest row on or before X. Filter effective_date <= X first, then keep each entity's newest remaining row with DISTINCT ON (entity) ... ORDER BY entity, effective_date DESC. Skip the filter and you pick up changes made after X, including ones scheduled for the future; use MAX(value) and you get the highest value, which isn't always the latest.
SELECT DISTINCT ON (product_id) product_id, price, valid_from FROM product_prices WHERE valid_from <= DATE '2025-03-31' ORDER BY product_id, valid_from DESC;Practice: Salary in effect on a given date →
Date subtraction · PERCENTILE_CONT · status filter
Subtracting two dates gives whole days, so closed_on - opened_on is each requisition's time to fill. PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY x) is the median, and it works per group with GROUP BY. Decide which rows the metric describes before aggregating: a cancelled requisition also has a closing date, but nobody was hired.
SELECT clinic, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY seen_on - booked_on) AS median_wait_days FROM appointments WHERE status = 'attended' GROUP BY clinic;Practice: Median time to fill by department →
Join · MIN per person · INTERVAL boundary
To test 'within N years of an event', add an INTERVAL to the start date: hire_date + INTERVAL '2 years' lands on the exact anniversary, while + 730 days falls a day short across February 29. Define the event from the data itself (here a higher job level, not just a new title), and collapse to one row per person with MIN when someone qualifies more than once.
SELECT c.name, MIN(o.ordered_on) AS first_reorder FROM customers c JOIN orders o ON o.customer_id = c.id WHERE o.ordered_on > c.first_order_on AND o.ordered_on <= c.first_order_on + INTERVAL '90 days' GROUP BY c.id, c.name;Practice: Promoted within two years of hire →
LAG · ROW_NUMBER · skipping NULLs
LAG(x) OVER (PARTITION BY person ORDER BY date) puts each row next to the same person's previous one. To compare only the most recent pair, also number each person's rows newest-first with ROW_NUMBER and keep row 1. Rows that aren't final yet (a NULL rating) must be filtered out before the window runs, or they become the 'latest' row and hide the real comparison.
WITH r AS (
SELECT runner_id, race_date, finish_minutes,
LAG(finish_minutes) OVER (PARTITION BY runner_id ORDER BY race_date) AS prev_minutes,
ROW_NUMBER() OVER (PARTITION BY runner_id ORDER BY race_date DESC) AS rn
FROM race_results
WHERE finish_minutes IS NOT NULL
)
SELECT * FROM r WHERE rn = 1 AND finish_minutes > prev_minutes;Practice: Latest performance rating fell →PERCENTILE_CONT per group · join back · same population
PERCENTILE_CONT is an ordered-set aggregate, so Postgres won't run it as a window function with OVER. Compute each group's median in a CTE with GROUP BY, then join it back to the detail rows so every row can be compared with its own group. Build both from the same filtered population, or rows that are out of scope shift the median.
WITH med AS ( SELECT region, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY rent) AS median_rent FROM listings WHERE active GROUP BY region ) SELECT l.id, l.region, l.rent, med.median_rent FROM listings l JOIN med USING (region) WHERE l.active AND l.rent > 1.5 * med.median_rent;Practice: Paid well below the department median →
Point-in-time headcount · COUNT FILTER · average denominator
Attrition is leavers in a period divided by the average headcount over it, usually (headcount at the start + headcount at the end) / 2. Headcount on a day d counts everyone hired by d whose end date is NULL or on or after d, and COUNT(*) FILTER computes several of these per group in one pass. Leavers are counted by termination date, even people who joined and left within the year and appear in neither snapshot. Divide by 2.0, not 2, or the average is truncated.
SELECT store,
COUNT(*) FILTER (WHERE cancelled_on >= DATE '2024-01-01' AND cancelled_on < DATE '2025-01-01') AS cancels,
COUNT(*) FILTER (WHERE joined_on <= DATE '2024-01-01'
AND (cancelled_on IS NULL OR cancelled_on >= DATE '2024-01-01')) AS members_jan1
FROM memberships
GROUP BY store;Practice: Annual attrition rate by department →generate_series · month-end dates · range join
Headcount is a snapshot, not an event count: on a given day it's everyone hired by then and not yet gone. Generate the snapshot days with generate_series, turn each month's first day into its last with + INTERVAL '1 month' - INTERVAL '1 day', and join every employee whose employment covers that day. A NULL end date means still employed and needs its own IS NULL branch, and an inclusive end date (the last day worked) needs >=.
SELECT d.day::date, COUNT(r.id) AS rooms_occupied FROM generate_series(DATE '2025-03-01', DATE '2025-03-07', INTERVAL '1 day') AS d(day) LEFT JOIN room_stays r ON r.check_in <= d.day AND (r.check_out IS NULL OR r.check_out > d.day) GROUP BY d.day ORDER BY d.day;Practice: Headcount at the end of every month →
WITH RECURSIVE · carrying the root · pruning in the recursive step
A recursive CTE can carry a column from its anchor row all the way down, such as the id of the person the walk started from, so one query explores several subtrees and you can GROUP BY that root afterwards. Keeping the root itself as a depth-0 row means a root with nothing below still produces a group. A WHERE in the recursive step prunes as it walks: rows it rejects aren't counted, and neither is anything only reachable through them.
WITH RECURSIVE tree AS ( SELECT id AS root_id, id, 0 AS depth FROM folders WHERE parent_id IS NULL UNION ALL SELECT t.root_id, f.id, t.depth + 1 FROM folders f JOIN tree t ON f.parent_id = t.id ) SELECT root_id, MAX(depth) AS depth, COUNT(*) AS folders FROM tree GROUP BY root_id;Practice: Org depth and size under each VP →
Self-join · range overlap · de-duplicating pairs
Two date ranges overlap when each starts on or before the other ends: a.start <= b.end AND b.start <= a.end. With inclusive end dates, ranges that touch share that day, so the test needs <=. Self-join the table to compare every pair, and use a.id < b.id so each pair appears once (never twice, never with itself). The shared stretch runs from GREATEST of the starts to LEAST of the ends.
-- ends_at is exclusive here, so touching bookings don't clash SELECT a.room, a.id AS booking_1, b.id AS booking_2, GREATEST(a.starts_at, b.starts_at) AS clash_from FROM room_bookings a JOIN room_bookings b ON b.room = a.room AND a.id < b.id AND a.starts_at < b.ends_at AND b.starts_at < a.ends_at;Practice: Overlapping leave on the same team →
CASE · EXISTS · NOT IN and NULL
In an id/parent_id tree, a node with no parent is a root, a node that is someone's parent is inner, and everything else is a leaf. Check the root case first in the CASE, then test for children with EXISTS. Avoid id NOT IN (SELECT parent_id ...): the roots put a NULL in that list, and NOT IN against a list containing NULL is never true.
SELECT f.id, f.name, EXISTS (SELECT 1 FROM folders sub WHERE sub.parent_folder = f.id) AS has_subfolders FROM folders f;Practice: Root, inner or leaf →
MIN(id) per group · IN subquery
To deduplicate without deleting anything, decide which row survives in each group, then select only those rows. MIN(id) per email in a subquery names the survivors, and WHERE id IN (...) keeps their full rows. SELECT DISTINCT doesn't do this: it only merges rows that match in every selected column.
SELECT * FROM support_tickets WHERE id IN ( SELECT MIN(id) FROM support_tickets GROUP BY customer_id, subject );Practice: Keep the first row per email →
Self-join on date - 1 · missing days
To compare a day with the previous calendar day, join the table to itself on b.day = a.day - 1 (in Postgres, a date minus an integer is that many days earlier). LAG gives the previous row instead, which is only the previous day when no day is missing. With gaps in the data, the date join is the safe choice: a day whose neighbour is missing simply finds no match.
SELECT today.sales_day, today.orders, yday.orders AS orders_day_before FROM daily_orders today JOIN daily_orders yday ON yday.sales_day = today.sales_day - 1;Practice: Warmer than the day before →
CASE · modulo · last odd row
To swap each pair of neighbours, rewrite the id instead of moving the names: odd ids become id + 1 and even ids become id - 1, which seat_id % 2 tells apart. With an odd number of rows the last odd id has no partner, so it needs its own branch that compares it with MAX(id). Then sort by the new id.
SELECT CASE WHEN slot % 2 = 1 THEN slot + 1 ELSE slot - 1 END AS new_slot, speaker FROM talk_slots ORDER BY new_slot;Practice: Swap neighbouring seats →
LAG with an offset · ids with gaps
To compare a row with the rows just before it, use LAG(col) OVER (ORDER BY id) and LAG(col, 2) OVER (ORDER BY id). They follow the sort order, so they still work when ids have gaps, unlike a self-join on id + 1. A value that repeats four or more times makes several matching windows, so finish with DISTINCT.
SELECT reading_id, sensor_state, LAG(sensor_state) OVER (ORDER BY reading_id) AS one_before, LAG(sensor_state, 2) OVER (ORDER BY reading_id) AS two_before FROM sensor_readings;Practice: The same code three times in a row →
CROSS JOIN · NOT EXISTS · NULL scores
To find what didn't happen, first build every combination that could have happened with a CROSS JOIN, then remove the ones that did with NOT EXISTS. Starting from the facts table can't work: a missing fact has no row to start from. Be clear about what counts as happening: a row with a NULL measurement may mean the event never took place.
SELECT v.name, s.shift_date FROM volunteers v CROSS JOIN shifts s WHERE NOT EXISTS ( SELECT 1 FROM shift_signups x WHERE x.volunteer_id = v.id AND x.shift_id = s.id );Practice: Exams each student missed →
Pivot with SUM FILTER · COALESCE
To turn months into columns, group by the row label only and write one aggregate per column, each filtered to its month: SUM(x) FILTER (WHERE EXTRACT(MONTH FROM d) = 1). Filter the date range in WHERE first, because a month number alone matches every year. A month with no rows sums to NULL, so wrap each column in COALESCE(..., 0) when the report wants zeros.
SELECT warehouse, COALESCE(SUM(units) FILTER (WHERE EXTRACT(DOW FROM shipped_on) = 1), 0) AS monday, COALESCE(SUM(units) FILTER (WHERE EXTRACT(DOW FROM shipped_on) = 2), 0) AS tuesday FROM shipments GROUP BY warehouse;Practice: Q1 revenue, one column per month →
DENSE_RANK · PARTITION BY · NULLs sort first
DENSE_RANK() OVER (PARTITION BY group ORDER BY value DESC) numbers distinct values 1, 2, 3 within each group, giving ties the same number with no gaps. Filter on it in an outer query. Watch for NULLs: Postgres sorts NULL as larger than any value, so in DESC order a NULL takes rank 1 unless you drop it or write NULLS LAST.
SELECT team, player, points
FROM (
SELECT team, player, points,
DENSE_RANK() OVER (PARTITION BY team ORDER BY points DESC NULLS LAST) AS lvl
FROM scores
) s
WHERE lvl <= 2;Practice: Top three salary levels per department →RANGE frame with INTERVAL · per-group MAX
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW means the two previous rows, which is only the two previous months when no month is missing. RANGE BETWEEN INTERVAL '2 months' PRECEDING AND CURRENT ROW looks at the values themselves, so a skipped month simply adds nothing. Use MAX(...) OVER (PARTITION BY ...) when each group needs its own cutoff, such as its latest month.
SELECT store_id, week_start, sales,
SUM(sales) OVER (PARTITION BY store_id ORDER BY week_start
RANGE BETWEEN INTERVAL '14 days' PRECEDING AND CURRENT ROW) AS three_week_sales
FROM weekly_sales;Practice: Three-month pay total, latest month left out →Running SUM · weighted median · zero counts
A frequency table stores each value once with a count, so a plain median over its rows ignores the counts. Picture the values lined up count times each: with a running total of the counts, the middle value is the one whose span covers position total / 2, which is where running >= total / 2.0 and running - count <= total / 2.0. With an even total that can match two values; average them. Drop zero counts first, since their empty span can sit right on the midpoint.
SELECT score, students, SUM(students) OVER (ORDER BY score) AS running, SUM(students) OVER () AS total FROM score_counts WHERE students > 0;Practice: Median from a frequency table →
Gaps and islands · two states · ROW_NUMBER difference
To collapse a daily log into periods of the same state, number the rows twice: once overall and once within each state. Their difference stays constant along an unbroken run of one state and changes when the run is interrupted. Group by the state and that difference (the difference alone can repeat across states) and report MIN and MAX date. Filter to the reporting window before numbering, so periods are cut at its edges.
SELECT day, mood, ROW_NUMBER() OVER (ORDER BY day) - ROW_NUMBER() OVER (PARTITION BY mood ORDER BY day) AS run_id FROM mood_log;Practice: Success and failure periods →
Dedupe both sides · LEFT JOIN · cohort by request month
An acceptance rate needs a clean denominator and numerator. Reduce requests to one row per sender-receiver pair (dated by its first request), reduce accepts to distinct pairs, then LEFT JOIN accepts onto requests, so stray accepts with no request are ignored and months with no accepts stay at zero. Group by the request's month, not the accept's, so a late answer still counts for the month it was asked.
WITH inv AS ( SELECT host_id, guest_id, MIN(invited_on) AS first_invite FROM invites GROUP BY host_id, guest_id ) SELECT to_char(first_invite, 'YYYY-MM') AS month, COUNT(*) AS invites FROM inv GROUP BY 1;Practice: Friend request acceptance by month →
CASE buckets · COUNT FILTER
To compare groups that don't exist as a column, make the column: a CASE expression turns a number (here, days between booking and arrival) into labelled buckets, and you GROUP BY it. Write each bucket's edge once, with < on the upper bound, so a value on the edge lands in exactly one bucket. Text labels sort alphabetically, so order the buckets by something numeric, like the smallest value in each.
SELECT CASE WHEN age_days < 3 THEN '0-2 days'
WHEN age_days < 14 THEN '3-13 days'
ELSE '14+ days' END AS age_bucket,
COUNT(*) AS tickets,
ROUND(100.0 * COUNT(*) FILTER (WHERE escalated) / COUNT(*), 1) AS escalated_pct
FROM support_tickets
GROUP BY 1
ORDER BY MIN(age_days);Practice: Cancellation rate by booking lead time →COUNT FILTER · boundary · excluding cancellations
On-time performance is the share of operated flights that arrived within a tolerance, so get three things right: which rows are in the denominator (flights that actually flew), which side of the tolerance the boundary falls on ("more than 15 minutes late" means exactly 15 is on time), and decimal division. COUNT(*) FILTER (WHERE ...) gives the on-time count in the same group as the total.
SELECT line, COUNT(*) AS runs, COUNT(*) FILTER (WHERE minutes_late <= 5) AS punctual, ROUND(100.0 * COUNT(*) FILTER (WHERE minutes_late <= 5) / COUNT(*), 1) AS punctual_pct FROM train_runs WHERE NOT cancelled GROUP BY line;Practice: On-time performance by route →
COUNT(DISTINCT) · HAVING
"How many different X" is COUNT(DISTINCT x), not COUNT(*): a guest who returns to the same hotel adds rows but not hotels. Filter the rows that count before grouping (WHERE), then filter the groups by their counts after grouping (HAVING), since WHERE can't see aggregates.
SELECT customer_id, COUNT(DISTINCT store_id) AS stores, COUNT(*) AS visits FROM store_visits WHERE completed GROUP BY customer_id HAVING COUNT(DISTINCT store_id) >= 3;Practice: Guests who stayed at 3+ different hotels →
generate_series · LEFT JOIN · date subtraction
A distribution should show the values nobody hit, and GROUP BY can only return values that exist. Generate every value with generate_series, LEFT JOIN the data onto it, and count a column from the data side, because COUNT(*) counts the one all-NULL row an empty value still gets. For shares, divide by the total number of rows, computed once in a subquery.
SELECT s.size, COUNT(o.items) AS orders FROM generate_series(1, (SELECT MAX(items) FROM orders)) AS s(size) LEFT JOIN orders o ON o.items = s.size GROUP BY s.size ORDER BY s.size;Practice: Length-of-stay distribution, gaps included →
PERCENTILE_CONT · WITHIN GROUP
The median is the middle value, so a handful of bookings made months ahead can't drag it the way they drag an average. Postgres computes it with the ordered-set aggregate PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY x), which averages the two middle values when the count is even; PERCENTILE_DISC returns an actual value instead (the lower middle one). PERCENTILE_CONT returns double precision, so cast to numeric before ROUND.
SELECT region, COUNT(*) AS shipments, ROUND((PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY delivered_on - ordered_on))::numeric, 1) AS median_days FROM shipments GROUP BY region;Practice: Median booking lead time by channel →
AVG per group · RANK() OVER (PARTITION BY)
"The top item per group" is two steps: aggregate to one row per item and group, then rank within each group with RANK() OVER (PARTITION BY group ORDER BY value DESC) and keep rank 1 in an outer query. RANK gives tied rows the same rank, so ties survive; ROW_NUMBER silently picks one. AVG skips NULLs on its own, so don't turn missing values into zeros unless they really are zeros.
WITH waits AS (
SELECT sale_date, store, AVG(wait_min) AS avg_wait
FROM checkouts
GROUP BY sale_date, store
)
SELECT sale_date, store, avg_wait
FROM (SELECT *, RANK() OVER (PARTITION BY sale_date ORDER BY avg_wait DESC) AS rk
FROM waits) r
WHERE rk = 1;Practice: Most delayed airport each day, ties included →generate_series per row · EXTRACT(ISODOW) · CASE in SUM
When each row covers a range of days, a question about particular days needs one row per day: CROSS JOIN generate_series(start, end - 1, INTERVAL '1 day') expands every row into its days, and a date range that ends on a checkout or return date stops the day before. EXTRACT(ISODOW ...) numbers Monday 1 to Sunday 7 (plain DOW makes Sunday 0). SUM(CASE WHEN ... THEN amount ELSE 0 END) gives 0 rather than NULL when no day matches.
SELECT r.car_id, SUM(r.daily_price) AS revenue, SUM(CASE WHEN EXTRACT(ISODOW FROM d) IN (6, 7) THEN r.daily_price ELSE 0 END) AS sat_sun_revenue FROM rentals r CROSS JOIN generate_series(r.pickup_on, r.return_on - 1, INTERVAL '1 day') AS d GROUP BY r.car_id;Practice: Share of room revenue from weekend nights →
Self-join · reversed match · one direction only
Pairing rows of the same table is a self-join: join the table to itself under two aliases and spell out how the second row relates to the first. For a reversed pair, both ends have to swap (b.from = a.to AND b.to = a.from), or one-way connections slip in. Add an ordering condition such as b.date > a.date, or every pair shows up twice, once from each side.
SELECT a.from_acct, a.to_acct, a.amount, b.amount AS sent_back, b.sent_on - a.sent_on AS days_later FROM transfers a JOIN transfers b ON b.from_acct = a.to_acct AND b.to_acct = a.from_acct AND b.sent_on > a.sent_on;Practice: Round trips: outbound and return legs →
Nights via generate_series · days in month · two denominators
Hotel KPIs share a numerator, room revenue, and differ in the denominator: ADR (average daily rate) divides by room nights sold, RevPAR (revenue per available room) by room nights available, which is rooms × days in the month. A stay that crosses a month end belongs to two months, so expand stays into nights before grouping by month. Days in a month come from date arithmetic: (month_start + INTERVAL '1 month')::date - month_start.
WITH days AS (
SELECT r.lot_id, r.price, d::date AS day
FROM parking_permits r
CROSS JOIN generate_series(r.start_on, r.end_on - 1, INTERVAL '1 day') AS d
)
SELECT l.name, date_trunc('month', day)::date AS month,
ROUND(SUM(price) / COUNT(*), 2) AS avg_paid_per_day,
ROUND(SUM(price) / (l.spaces * ((date_trunc('month', day) + INTERVAL '1 month')::date - date_trunc('month', day)::date)), 2) AS revenue_per_space_day
FROM days JOIN lots l ON l.id = days.lot_id
GROUP BY l.name, l.spaces, date_trunc('month', day);Practice: ADR and RevPAR per hotel per month →generate_series per booking · HAVING against a joined capacity
Capacity questions are per unit of time, so first turn every booking into one row per night it occupies, then count rows per hotel and night and compare with capacity in HAVING. A stay holds a room from check_in through the night before check_out, so the series ends at check_out - 1. Overbooked means strictly more bookings than rooms; a hotel that's exactly full is fine.
WITH d AS ( SELECT r.kit_id, day::date AS day FROM gear_rentals r CROSS JOIN generate_series(r.out_on, r.back_on - 1, INTERVAL '1 day') AS day WHERE NOT r.cancelled ) SELECT k.name, d.day, COUNT(*) AS rentals, k.units FROM d JOIN kits k ON k.id = d.kit_id GROUP BY k.id, k.name, k.units, d.day HAVING COUNT(*) > k.units;Practice: Overbooked nights →
Self-join on a date window · first match with ROW_NUMBER
"Did X happen within N days after Y" is a self-join whose ON clause sets a window: same person, the second event on or after the first (>=, so earlier events don't sneak in), and no later than the first + N (<=, so exactly N counts). Measure from the right event: here the cancellation date, not the original booking date. When the window can hold several matches, number them with ROW_NUMBER() OVER (PARTITION BY first_event ORDER BY ...) and keep the first.
WITH m AS (
SELECT c.id, c.user_id, c.ended_on, n.started_on,
ROW_NUMBER() OVER (PARTITION BY c.id ORDER BY n.started_on, n.id) AS rn
FROM subscriptions c
JOIN subscriptions n ON n.user_id = c.user_id
AND n.id <> c.id
AND n.started_on >= c.ended_on
AND n.started_on <= c.ended_on + 14
)
SELECT * FROM m WHERE rn = 1;Practice: Cancelled, then rebooked within 7 days →SUM OVER (RANGE BETWEEN INTERVAL ... PRECEDING) · CASE
A trailing total over time needs a window frame measured in time, not in rows: SUM(x) OVER (PARTITION BY person ORDER BY date RANGE BETWEEN INTERVAL '12 months' PRECEDING AND CURRENT ROW) adds every row dated from 12 months back through the current one, however many rows that is. ROWS counts rows instead, and an unframed ORDER BY gives an all-time running total. INTERVAL '12 months' follows the calendar, while 365 days comes up one day short across a leap day. Then a CASE turns the total into a tier.
SELECT member_id, visit_date, points,
SUM(points) OVER (PARTITION BY member_id ORDER BY visit_date
RANGE BETWEEN INTERVAL '6 months' PRECEDING AND CURRENT ROW) AS points_6m
FROM gym_visits;Practice: Loyalty tier from trailing 12-month nights →CASE buckets · GROUP BY
CASE turns a number into a label: Postgres checks the WHEN branches top to bottom and uses the first one that's true, so listing thresholds from highest to lowest (>= 90, then >= 80, ...) needs only one bound per branch. Ranges like BETWEEN 80 AND 89 leave gaps that decimal values fall through. A NULL fails every WHEN and lands in ELSE, so filter NULLs out first if they don't belong.
SELECT CASE WHEN minutes >= 60 THEN 'long'
WHEN minutes >= 20 THEN 'medium'
ELSE 'short' END AS length_band,
COUNT(*) AS calls
FROM support_calls
WHERE minutes IS NOT NULL
GROUP BY 1
ORDER BY 1;Practice: Grade distribution by score band →GROUP BY two columns · MAX · NULL attempts
When learners can retake a quiz, there's one row per attempt, and the question decides the aggregate: MAX for the best try, AVG for typical performance. Group by both the person and the quiz so each pair collapses to one row. MAX skips NULLs, but COUNT(*) counts every row, so an attempt that was never submitted still adds to the count unless you filter it out.
SELECT driver_id, track, COUNT(*) AS laps, MIN(lap_seconds) AS best_lap FROM practice_laps WHERE lap_seconds IS NOT NULL GROUP BY driver_id, track ORDER BY driver_id, track;Practice: Best quiz score with retakes →
Timestamp comparison · COUNT FILTER
A submission is late when its timestamp is after the deadline, so compare the two columns directly: submitted_at > due_at. Strictly greater means a hand-in exactly at the deadline is on time. COUNT(*) FILTER (WHERE ...) counts the late ones next to the total in the same GROUP BY, and multiplying by 100.0 keeps the percentage from being cut to a whole number.
SELECT route, COUNT(*) AS trips, COUNT(*) FILTER (WHERE arrived_at > scheduled_at) AS delayed FROM bus_trips GROUP BY route ORDER BY route;Practice: Late submission rate by course →
COUNT(DISTINCT) · LEFT JOIN · finished vs enrolled
A completion rate takes its two counts from different tables: the denominator is everyone enrolled, and the numerator is everyone whose finished lessons cover the whole course. Work out each student's distinct completed lessons first, then LEFT JOIN that onto the enrollments so students who never started still count as enrolled. Count distinct lessons, because a student who redoes a lesson has two rows for it.
WITH stamps AS ( SELECT member_id, COUNT(DISTINCT cafe_id) AS cafes FROM cafe_visits GROUP BY member_id ) SELECT COUNT(*) AS members, COUNT(*) FILTER (WHERE s.cafes = (SELECT COUNT(*) FROM cafes)) AS full_cards FROM loyalty_members m LEFT JOIN stamps s ON s.member_id = m.id;Practice: Course completion rate →
AVG() OVER (PARTITION BY) · filter in an outer query
A window function like AVG(score) OVER (PARTITION BY class_id) puts each group's average on every row without collapsing the rows, so a student's score and their class average sit side by side. Window results don't exist yet when WHERE runs, so compute them in a CTE or subquery and filter in the outer query. AVG skips NULLs, so ungraded rows don't drag the average down.
WITH t AS (
SELECT store, clerk, sales,
AVG(sales) OVER (PARTITION BY store) AS store_avg
FROM weekly_sales
)
SELECT store, clerk, sales
FROM t
WHERE sales > store_avg;Practice: Students who beat their class average →RANK() · PARTITION BY · NULLs sort first
To find the best row in each group, rank inside each group with RANK() OVER (PARTITION BY group ORDER BY value DESC) and keep rank 1. RANK gives tied rows the same number, so a shared first place returns every winner, while ROW_NUMBER picks one arbitrarily. In Postgres, DESC puts NULLs first, so filter them out before ranking, or an ungraded row will 'win'.
WITH r AS (
SELECT league, team, points,
RANK() OVER (PARTITION BY league ORDER BY points DESC) AS rnk
FROM standings
WHERE points IS NOT NULL
)
SELECT league, team, points FROM r WHERE rnk = 1;Practice: Top student in each course, ties included →HAVING · RANK() over an aggregate
To rank groups by an average, GROUP BY the group, compute AVG, and put RANK() OVER (ORDER BY AVG(...) DESC) in the same SELECT, since window functions run after grouping. HAVING COUNT(*) >= n drops groups with too little data first, so a handful of perfect reviews can't top the list. Average the raw rows rather than per-course averages, or a course with two reviews weighs as much as one with six.
SELECT c.name AS chef, COUNT(*) AS ratings, ROUND(AVG(r.stars), 2) AS avg_stars, RANK() OVER (ORDER BY AVG(r.stars) DESC) AS place FROM dish_ratings r JOIN chefs c ON c.id = r.chef_id GROUP BY c.id, c.name HAVING COUNT(*) >= 10;Practice: Instructors ranked by rating, minimum 5 reviews →
generate_series · LEFT JOIN · zero-filled weeks
GROUP BY only returns buckets that have data, so a week with no activity simply disappears. Build the full list of weeks with generate_series(start, end, INTERVAL '1 week'), LEFT JOIN the activity onto it, and count a column from the joined table, which gives 0 for empty weeks. Match events with ts >= week_start AND ts < week_start + INTERVAL '1 week' so late-Sunday timestamps stay in their week.
SELECT d.day::date AS day, COUNT(o.id) AS orders FROM generate_series(TIMESTAMP '2025-05-01', TIMESTAMP '2025-05-07', INTERVAL '1 day') AS d(day) LEFT JOIN bakery_orders o ON o.ordered_at >= d.day AND o.ordered_at < d.day + INTERVAL '1 day' GROUP BY d.day ORDER BY d.day;Practice: Weekly active learners, empty weeks included →
Relational division · HAVING with FILTER · NULLs in bool_and
'Every' questions are relational division: group each person's rows and keep the group only if the rows meeting the condition are all of them, as in HAVING COUNT(*) FILTER (WHERE ok) = COUNT(*). Watch NULLs: bool_and and NOT EXISTS (... failed ...) both treat an unknown outcome as harmless, so rows with no result yet slip through. A person with no rows at all passes NOT EXISTS vacuously, so start from the rows, not the people.
SELECT supplier_id, COUNT(*) AS shipments FROM shipments GROUP BY supplier_id HAVING COUNT(*) FILTER (WHERE arrived_on <= promised_on) = COUNT(*);Practice: Students who passed every course they took →
Weighted average · lookup join · rows without points
A GPA is a weighted average: multiply each course's grade points by its credits, add those up, and divide by the credits that earned points. A plain AVG of grade points treats a 2-credit course like a 4-credit one. Grades that carry no points (a withdrawal, or no grade yet) must leave the numerator and the denominator together, while an F stays in both: it's 0 points on real credits.
SELECT p.name, ROUND(SUM(r.score * j.weight) / SUM(j.weight), 2) AS weighted_score FROM judge_scores r JOIN judges j ON j.id = r.judge_id JOIN performers p ON p.id = r.performer_id WHERE r.score IS NOT NULL GROUP BY p.id, p.name;Practice: Credit-weighted GPA →
Per-learner furthest point · inactivity cutoff · mode with RANK
Dropout analysis runs in two passes. First collapse each learner to one row: how far they got (the highest step reached, not the last thing they touched), whether they finished, and when they were last active. Then keep the learners who didn't finish and have gone quiet, count them by the step they stopped at, and rank those counts within each group to find the most common one, keeping ties.
WITH per AS (
SELECT user_id, MAX(step_no) AS furthest, MAX(seen_at) AS last_seen,
bool_or(step_no = 5) AS finished
FROM onboarding_steps GROUP BY user_id
)
SELECT furthest, COUNT(*) AS stuck
FROM per
WHERE NOT finished AND last_seen < TIMESTAMP '2025-06-01'
GROUP BY furthest
ORDER BY stuck DESC;Practice: Where learners drop out →WITH RECURSIVE · shortest depth · dedupe
A recursive CTE walks a hierarchy one level at a time: the anchor query picks the starting rows, and the recursive part joins the CTE back to the edge table to find the next level, repeating until no new rows appear. Carry a depth counter (depth + 1) as you go. When a node can be reached by more than one path it shows up once per path, so GROUP BY the node and take MIN(depth) for the shortest route.
WITH RECURSIVE up AS ( SELECT manager_id AS id, 1 AS levels FROM staff WHERE id = 42 UNION ALL SELECT s.manager_id, up.levels + 1 FROM up JOIN staff s ON s.id = up.id WHERE s.manager_id IS NOT NULL ) SELECT id, levels FROM up;Practice: Every prerequisite of the capstone →
Timestamp subtraction · EXTRACT(EPOCH) · AVG
Subtracting two timestamps gives an interval. EXTRACT(EPOCH FROM interval) turns the whole interval into seconds, so dividing by 60 gives total minutes. EXTRACT(MINUTE FROM interval) is a trap: it reads only the minutes field, so 1 hour 12 minutes comes out as 12.
SELECT clinic, ROUND(AVG(EXTRACT(EPOCH FROM seen_at - arrived_at) / 60)::numeric, 1) AS avg_wait_min FROM er_visits WHERE seen_at IS NOT NULL GROUP BY clinic;Practice: Average delivery time per restaurant →
Conditional COUNT · HAVING · minimum volume
A share is a filtered count over the group's full count. COUNT(*) FILTER (WHERE ...) counts the matching rows while the others stay in the denominator, which a WHERE clause would remove. HAVING filters groups after they are counted, so that is where a minimum-volume rule goes.
SELECT driver, COUNT(*) AS trips, ROUND(100.0 * COUNT(*) FILTER (WHERE rating <= 2) / COUNT(*), 1) AS low_rating_pct FROM trips GROUP BY driver HAVING COUNT(*) >= 10;Practice: Restaurants with the most cancellations →
Timestamp comparison · FILTER · strict boundary
Timestamps compare like numbers, so 'late' is just delivered_at > promised_at. A rate needs the right denominator: only the rows where the event could happen at all. Decide whether the boundary counts: arriving on the promised minute is on time, so late is strictly greater.
SELECT warehouse, COUNT(*) AS shipped, ROUND(100.0 * COUNT(*) FILTER (WHERE shipped_at > ship_by) / COUNT(*), 1) AS late_pct FROM shipments WHERE shipped_at IS NOT NULL GROUP BY warehouse;Practice: Late deliveries against the promised time →
GROUP BY + RANK · mode with ties
The mode is the most common value in a group. Count per (person, value), rank those counts with RANK() OVER (PARTITION BY person ORDER BY COUNT(*) DESC), and keep rank 1. RANK gives tied values the same rank, so a tie returns every winner, while ROW_NUMBER quietly keeps an arbitrary one.
WITH g AS (
SELECT l.reader_id, b.genre, COUNT(*) AS loans,
RANK() OVER (PARTITION BY l.reader_id ORDER BY COUNT(*) DESC) AS rk
FROM loans l JOIN books b ON b.id = l.book_id
GROUP BY l.reader_id, b.genre
)
SELECT reader_id, genre, loans FROM g WHERE rk = 1;Practice: Each customer's favourite cuisine →PERCENTILE_CONT · interval to minutes
Kitchen times are skewed: one slow ticket drags the average well above a typical wait, so the median is the sturdier figure. PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY x) averages the two middle values when a group has an even count, while PERCENTILE_DISC picks one real value. It returns double precision, so cast to numeric before ROUND(x, 1).
SELECT branch, ROUND((PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY EXTRACT(EPOCH FROM served_at - ordered_at) / 60))::numeric, 1) AS median_wait FROM coffee_orders GROUP BY branch;Practice: Median food prep time by restaurant →
EXTRACT(HOUR) · time buckets · conditional share
EXTRACT(HOUR FROM ts) returns the hour as a number from 0 to 23, which makes time-of-day buckets easy. Define a window by whole hours so its edges are unambiguous: 11:00 to 14:59 is hours 11 through 14. BETWEEN includes both ends, so BETWEEN 11 AND 15 also catches everything from 15:00 to 15:59.
SELECT store, COUNT(*) AS sales, ROUND(100.0 * COUNT(*) FILTER (WHERE EXTRACT(HOUR FROM sold_at) BETWEEN 6 AND 9) / COUNT(*), 1) AS breakfast_pct FROM sales GROUP BY store;Practice: Lunch vs dinner order share by city →
NULLIF · COALESCE · CASE groups
Missing values often come in more than one disguise: NULL and an empty string '' both mean 'nothing here', but only NULL passes IS NULL. NULLIF(x, '') turns the empty string into NULL so one test catches both. In arithmetic a NULL spreads (10 - NULL is NULL), so wrap optional amounts in COALESCE(x, 0) before subtracting.
SELECT CASE WHEN NULLIF(referrer, '') IS NULL THEN 'direct' ELSE 'referred' END AS source, COUNT(*) AS signups, SUM(plan_price - COALESCE(credit, 0)) AS billed FROM signups GROUP BY 1;Practice: Revenue with and without promo codes →
LAG per pair · interval comparison
LAG(x) OVER (PARTITION BY a, b ORDER BY t) fetches the previous row's value within the same (a, b) group, which is how you compare each event with the one before it. Subtracting timestamps gives an interval, and comparing it with INTERVAL '7 days' keeps the hours. Casting to dates first throws the time of day away, so 7 days and 2 hours would pass as 7.
SELECT patient_id, clinic, visited_at, visited_at - LAG(visited_at) OVER (PARTITION BY patient_id, clinic ORDER BY visited_at) AS gap FROM visits;Practice: Reorders from the same restaurant within 7 days →
Aggregate before joining · fan-out · COALESCE
When two child tables hang off the same parent, joining both at once multiplies rows: 2 sessions × 5 jobs gives 10 rows, and every SUM is inflated. Aggregate each child to one row per parent first, in its own CTE, then join the totals. A LEFT JOIN keeps parents with no rows in one child, and COALESCE turns their NULL total into 0.
WITH h AS (SELECT tutor_id, SUM(hours) AS hours FROM shifts GROUP BY tutor_id),
p AS (SELECT tutor_id, SUM(amount) AS paid FROM payouts GROUP BY tutor_id)
SELECT h.tutor_id, h.hours, COALESCE(p.paid, 0) AS paid
FROM h
LEFT JOIN p ON p.tutor_id = h.tutor_id;Practice: Courier earnings per hour online →Self-join · interval overlap · strict boundaries
Two time windows overlap when each one starts before the other ends: a.start < b.end AND b.start < a.end. Strict < leaves out windows that merely touch, where one ends the minute the next begins. Self-join on the shared key with a.id < b.id so each pair shows up once, and LEAST of the ends minus GREATEST of the starts is the overlap itself.
SELECT a.studio, a.id AS first_class, b.id AS second_class, LEAST(a.ends_at, b.ends_at) - GREATEST(a.starts_at, b.starts_at) AS clash FROM classes a JOIN classes b ON b.studio = a.studio AND a.id < b.id AND a.starts_at < b.ends_at AND b.starts_at < a.ends_at;Practice: Batched deliveries: overlapping courier jobs →
First event per user · EXISTS · inclusive time window
Cohort retention starts from each user's first qualifying event: filter out the events that don't count, then take MIN(ts) per user. Then ask once per user, with EXISTS, whether a later qualifying event falls in the window: strictly after the first (so it can't match itself) and on or before first + INTERVAL '30 days'. Finally group users by the month of their first event.
WITH f AS (SELECT member_id, MIN(checked_in_at) AS first_at FROM gym_visits GROUP BY member_id)
SELECT to_char(first_at, 'YYYY-MM') AS cohort,
COUNT(*) AS members,
COUNT(*) FILTER (WHERE EXISTS (SELECT 1 FROM gym_visits v WHERE v.member_id = f.member_id
AND v.checked_in_at > f.first_at AND v.checked_in_at <= f.first_at + INTERVAL '14 days')) AS came_back
FROM f
GROUP BY 1;Practice: First-order cohorts and 30-day retention →Monthly AVG · self-join on the previous month · gaps
Month-over-month means comparing with the previous calendar month, not the previous row. LAG fetches the previous row, which is the wrong month whenever a group skipped one. Aggregate to one row per group and month, then LEFT JOIN that result to itself on prev.month = cur.month - INTERVAL '1 month', so a missing month gives NULL instead of an older value.
WITH m AS (SELECT store, date_trunc('month', sold_on) AS month, SUM(amount) AS sales FROM sales GROUP BY 1, 2)
SELECT cur.store, cur.month, cur.sales, prev.sales AS prev_sales
FROM m cur
LEFT JOIN m prev ON prev.store = cur.store AND prev.month = cur.month - INTERVAL '1 month';Practice: Monthly rating vs the previous month →COUNT(*) OVER (PARTITION BY) · every version · NULL keys
GROUP BY key HAVING COUNT(*) > 1 tells you which keys are duplicated, but it collapses each key to one row. To see every version side by side, put COUNT(*) OVER (PARTITION BY key) on each row and filter on it in an outer query. Watch out for NULL keys: PARTITION BY and GROUP BY put all NULLs in one group, so they look like duplicates of each other.
SELECT * FROM ( SELECT b.*, COUNT(*) OVER (PARTITION BY isbn) AS copies FROM book_imports b WHERE isbn IS NOT NULL ) t WHERE copies > 1;Practice: Duplicate keys in staging →
Watermark · COALESCE(updated_at, created_at) · strict >
An incremental load copies only the rows that changed after the last successful run, which is recorded as a watermark. Compare each row's last-change time with the watermark using a strict >, because a row stamped exactly at the watermark was already picked up. If updated_at stays NULL until a row is first edited, brand-new rows have no updated_at, so fall back to created_at with COALESCE.
SELECT p.* FROM products p JOIN sync_state s ON s.feed = 'products' WHERE COALESCE(p.modified_at, p.added_at) > s.synced_until;Practice: Rows changed since the watermark →
LEFT JOIN with an ON filter · MAX · interval vs SLA
Freshness is the time since a table's last successful load. Filter to successful runs inside the LEFT JOIN's ON clause, not in WHERE: a WHERE on the joined table throws away tables with no matching run, which are the stalest of all. Subtracting timestamps gives an interval, and EXTRACT(EPOCH FROM interval) / 3600 turns it into hours.
SELECT s.sensor, MAX(r.read_at) AS last_ok, ROUND(EXTRACT(EPOCH FROM TIMESTAMP '2025-01-01 00:00' - MAX(r.read_at)) / 3600, 1) AS hours_quiet FROM sensors s LEFT JOIN readings r ON r.sensor = s.sensor AND r.ok GROUP BY s.sensor;Practice: Stale tables →
DISTINCT ON / ROW_NUMBER · tie-breaker · deletes
A change-data-capture log holds every insert, update and delete, so the current state of a key is its newest row. Rank each key's rows by change time, newest first, and break ties with the log sequence number so exactly one row wins. Drop deletes only after picking the winner: removing 'D' rows first would bring a deleted record back to life.
SELECT * FROM ( SELECT DISTINCT ON (sku) sku, action, qty FROM stock_changes ORDER BY sku, changed_at DESC, seq DESC ) latest WHERE action <> 'remove';Practice: Current state from a change log →
Aggregate first · FULL OUTER JOIN · COALESCE
To reconcile two systems, count each side per day on its own, then join the two summaries; joining raw rows first multiplies them. A FULL OUTER JOIN keeps days that exist on only one side, and COALESCE turns the missing side's count into 0 and picks whichever date is present.
WITH a AS (SELECT sold_on, COUNT(*) AS n FROM pos_sales GROUP BY sold_on),
b AS (SELECT sold_on, COUNT(*) AS n FROM ledger_sales GROUP BY sold_on)
SELECT COALESCE(a.sold_on, b.sold_on) AS day,
COALESCE(a.n, 0) AS pos, COALESCE(b.n, 0) AS ledger
FROM a FULL OUTER JOIN b ON b.sold_on = a.sold_on;Practice: Source vs warehouse row counts →LEAD · PARTITION BY · gap ranges
To find holes in a sequence, put each value next to the one after it with LEAD(value) OVER (ORDER BY value). Wherever next minus current is more than 1, the values in between are missing: the gap runs from current + 1 to next - 1. Partition by the sequence's owner so one sequence's last value isn't compared with another's first.
SELECT till_id, receipt_no + 1 AS first_missing, next_no - 1 AS last_missing
FROM (
SELECT till_id, receipt_no,
LEAD(receipt_no) OVER (PARTITION BY till_id ORDER BY receipt_no) AS next_no
FROM receipts
) r
WHERE next_no - receipt_no > 1;Practice: Missing offsets in a stream →CROSS JOIN LATERAL (VALUES) · unpivot · FILTER
A profiling report lists each column as its own row, which means unpivoting: turning columns into rows. CROSS JOIN LATERAL (VALUES ('col_a', t.col_a IS NULL), ('col_b', t.col_b IS NULL)) gives every source row one output row per column, and a normal GROUP BY then counts NULLs per column. A UNION ALL of one SELECT per column does the same job with more typing.
SELECT f.field, COUNT(*) FILTER (WHERE f.missing) AS missing
FROM survey_answers a
CROSS JOIN LATERAL (VALUES ('age', a.age IS NULL), ('city', a.city IS NULL)) AS f(field, missing)
GROUP BY f.field;Practice: Null-rate profile, one row per column →JSONB -> and ->> · casting · missing keys
In Postgres, payload -> 'key' returns JSONB and payload ->> 'key' returns text; chain them for nested fields, like payload -> 'cart' ->> 'item_count'. Cast the text to the type you need, such as (payload ->> 'total')::numeric, which works whether the JSON holds a number or a string. A key that isn't there comes back as NULL, so flags need a default.
SELECT payload ->> 'country' AS country, SUM((payload -> 'order' ->> 'qty')::int) AS units FROM app_logs WHERE NOT COALESCE((payload ->> 'is_bot')::boolean, false) GROUP BY 1;Practice: Revenue from raw JSON events →
Range join · half-open intervals · NULL valid_to
A type 2 slowly changing dimension keeps one row per version, valid from valid_from (inclusive) up to valid_to (exclusive), with valid_to NULL on the current row. To attach the version that was true when a fact happened, join on the business key and fact_time >= valid_from AND (valid_to IS NULL OR fact_time < valid_to). BETWEEN includes both ends, so a fact at the exact switch moment would match two versions.
SELECT s.sale_id, p.list_price FROM sales s LEFT JOIN price_versions p ON p.sku = s.sku AND s.sold_at >= p.valid_from AND (p.valid_to IS NULL OR s.sold_at < p.valid_to);Practice: Point-in-time join to an SCD2 dimension →
Gaps and islands · LAG change flag · LEAD for valid_to
To turn daily snapshots into version ranges, mark each row whose value differs from the previous day's (value IS DISTINCT FROM LAG(value)), and a running SUM of those marks numbers the versions. Each version starts at its first snapshot date, and its valid_to is the next version's valid_from, which LEAD gives you. Grouping by the value alone would merge a value that comes back later with its earlier run.
WITH marked AS (
SELECT store, day, manager,
CASE WHEN manager IS DISTINCT FROM LAG(manager) OVER (PARTITION BY store ORDER BY day)
THEN 1 ELSE 0 END AS changed
FROM store_daily
), numbered AS (
SELECT *, SUM(changed) OVER (PARTITION BY store ORDER BY day) AS stint FROM marked
)
SELECT store, manager, MIN(day) AS started
FROM numbered
GROUP BY store, stint, manager;Practice: Build SCD2 ranges from daily snapshots →Half-open windows · LEFT JOIN · FILTER · NULLIF
A batch owns the events loaded during its window, window_start <= loaded_at < window_end; the strict < at the end keeps an event on the boundary from landing in two batches. An event is late when it happened before its batch's window opened. Start from the batch table with a LEFT JOIN so an empty batch still shows up, and count a joined column rather than * so it reports 0.
SELECT s.shift_id, COUNT(c.call_id) AS calls, COUNT(c.call_id) FILTER (WHERE c.placed_at < s.shift_start) AS carried_over FROM shifts s LEFT JOIN calls c ON c.answered_at >= s.shift_start AND c.answered_at < s.shift_end GROUP BY s.shift_id;Practice: Late arrivals per load batch →
generate_series · LATERAL latest-before · carry forward
Event tables record only changes, so a day with no events still has a state: whatever the last change before it set. Build the calendar with generate_series, cross join it with every entity, and use LEFT JOIN LATERAL to fetch each entity's latest event before the end of that day (changed_at < day + 1 day, ORDER BY changed_at DESC LIMIT 1). Entities with no event yet don't exist on that day.
SELECT d::date AS day, r.room, s.state FROM generate_series(TIMESTAMP '2025-01-01', TIMESTAMP '2025-01-07', INTERVAL '1 day') AS d CROSS JOIN rooms r LEFT JOIN LATERAL ( SELECT state FROM room_log l WHERE l.room = r.room AND l.logged_at < d + INTERVAL '1 day' ORDER BY l.logged_at DESC LIMIT 1 ) s ON true;Practice: Daily snapshot from status events →