Every analyst eventually owns the retention query. At a SaaS company it’s "what percent of customers who signed up in January were still active in February, March, April." At a marketplace it’s "do buyers come back in week 2." At a consumer app it’s the D1 / D7 / D30 retention chart that every investor asks about on day one. The math is always the same; the SQL is always where people get stuck.

This post walks through the full build: what a cohort actually is (and isn’t), how to pick the anchor date, how to write the grid query cleanly, the one NULL trap that catches everyone, and how to read the resulting triangle the way stakeholders read it.

A **segment** is a group defined by what users *are* — their plan tier, their country, their signup source. A **cohort** is a group defined by *when* something happened to them — the month they signed up, the week they first purchased, the day they hit 10 sessions. Retention analysis lives in cohort-land because we care about time-since-event, not about static attributes.

The easiest way to see the difference: segments stay fixed when you re-run the query next week, but cohorts grow — July cohort is July cohort forever, but every week there’s a new "this week’s cohort" that didn’t exist before.

The first thing a retention query has to settle is **the anchor**: what event defines membership in a cohort? Common choices: `signed_up_at` (the default for most SaaS), `first_purchase_at` (for ecommerce), `onboarded_at` (for products with a multi-step setup). Pick the event that best represents "user is now a real user."

-- Anchor = signup month. One row per customer, with their cohort label.
WITH cohorts AS (
  SELECT
    customer_id,
    DATE_TRUNC('month', signed_up_at)::date AS cohort_month
  FROM dim_customers
  WHERE signed_up_at IS NOT NULL
)
SELECT cohort_month, COUNT(*) AS cohort_size
FROM cohorts
GROUP BY cohort_month
ORDER BY cohort_month;

Two design choices worth noting. First, `DATE_TRUNC` buckets at month-granularity — weekly is also common (swap to `WEEK`), daily is too granular to read. Second, the `WHERE signed_up_at IS NOT NULL` drops customers who somehow made it into the dim table without a signup date; that’s almost always data-quality exhaust and you don’t want them skewing the cohort size.

Here’s the canonical shape. Join the cohort anchor to an activity fact, compute "months since signup" per activity row, then pivot.

WITH cohorts AS (
  SELECT
    customer_id,
    DATE_TRUNC('month', signed_up_at)::date AS cohort_month
  FROM dim_customers
),
activity AS (
  SELECT
    c.cohort_month,
    c.customer_id,
    (EXTRACT(YEAR  FROM s.session_at) - EXTRACT(YEAR  FROM c.cohort_month)) * 12
      + (EXTRACT(MONTH FROM s.session_at) - EXTRACT(MONTH FROM c.cohort_month)) AS months_since_signup
  FROM cohorts c
  JOIN fact_sessions s USING (customer_id)
)
SELECT
  cohort_month,
  months_since_signup,
  COUNT(DISTINCT customer_id) AS active_users
FROM activity
WHERE months_since_signup BETWEEN 0 AND 11
GROUP BY cohort_month, months_since_signup
ORDER BY cohort_month, months_since_signup;

Note `COUNT(DISTINCT customer_id)` rather than `COUNT(*)` — a customer with five sessions in the same month should still count once. This is the line that separates "retention query that looks right" from "retention query that overcounts by 3x."

To turn this into the percentage triangle that stakeholders want, divide each cell by the cohort size (month 0 count). That’s easiest as a second CTE:

WITH grid AS (
  -- ... the query above ...
),
sizes AS (
  SELECT cohort_month, active_users AS cohort_size
  FROM grid
  WHERE months_since_signup = 0
)
SELECT
  g.cohort_month,
  g.months_since_signup,
  g.active_users,
  s.cohort_size,
  ROUND(100.0 * g.active_users / NULLIF(s.cohort_size, 0), 1) AS retention_pct
FROM grid g
JOIN sizes s USING (cohort_month)
ORDER BY g.cohort_month, g.months_since_signup;

If a cohort had zero active users in some month (everyone churned early), your grid won’t have a row for that cell — inner joins only emit matches. When the output is fed to a chart tool, missing cells get rendered as "no data" rather than 0%, and the retention line has a gap where it should have a floor. Use `generate_series` to pre-build the `(cohort_month, months_since_signup)` grid, then LEFT JOIN the activity onto it.

Missing cells are not zero

WITH cohort_months AS (
  SELECT DISTINCT DATE_TRUNC('month', signed_up_at)::date AS cohort_month
  FROM dim_customers
),
full_grid AS (
  SELECT
    cm.cohort_month,
    m AS months_since_signup
  FROM cohort_months cm
  CROSS JOIN generate_series(0, 11) AS m
)
SELECT
  fg.cohort_month,
  fg.months_since_signup,
  COUNT(DISTINCT a.customer_id) AS active_users
FROM full_grid fg
LEFT JOIN activity a
  ON a.cohort_month = fg.cohort_month
 AND a.months_since_signup = fg.months_since_signup
GROUP BY fg.cohort_month, fg.months_since_signup
ORDER BY fg.cohort_month, fg.months_since_signup;

Reading the triangle (what stakeholders ask)

Once the grid is built, stakeholders ask three questions in order. First: **is retention improving?** Compare cohorts row-by-row at the same `months_since_signup` — if May’s month-3 retention is higher than February’s month-3 retention, something’s working.

Second: **where do we lose people?** Find the biggest percentage-point drop across consecutive MOBs within a single cohort. If retention goes 100% → 42% from MOB 0 to MOB 1, month 1 is where onboarding is failing. If it’s flat at 100% → 94% → 92% and then plunges at month 3, investigate what happens at month 3 (free trial ending, invoice landing, seasonal effect).

Third: **what’s the long-run floor?** Retention decays asymptotically to some number — the "loyal core." A healthy SaaS business has a floor in the 60–75% range by month 12; a consumer app might floor at 10–20% by month 3. If yours floors lower, you either have a product-market-fit problem or a cohort definition problem — often the latter.

When you ship a retention triangle, send it with a one-paragraph summary hitting all three questions: "retention is up 4 points at MOB 3 vs Q1. Biggest drop is MOB 0 → 1 (onboarding). Long-run floor stabilizing around 62%." Stakeholders remember the summary, not the chart. Writing it is the senior move.

Seniors answer these before being asked

Retention queries look intimidating the first time because of the date arithmetic and the pivot, but the underlying pattern — anchor, join, bucket, pivot — is the same one you already use for campaign performance and funnel reporting. Master the triangle once, and you can build it on any product schema inside a day of learning the tables.

Build cohort retention grids in SQL end-to-end — anchoring the cohort, computing the grid, handling the "never returned" NULL trap, and interpreting the triangle for stakeholders.

Cohort Retention Analysis in SQL: The Analyst’s Complete Guide | caseSQL

Cohort Retention in SQL: The Query Every Analyst Eventually Owns

Ask a dozen analysts what the most-written SQL construct on their job is, and a dozen will tell you the same thing: `CASE WHEN`. It is how vague stakeholder asks — "group the big accounts separately," "flag anything unusual," "count the ones that converted quickly" — become queryable rules. Window functions get the press. `CASE WHEN` does the work.

This post is the practical shape of it: the basic form, the NULL trap that burns every junior, the three idiomatic patterns (labeling, bucketing, pivoting), and the senior-level decision of when to write it inline vs push it into a dbt model so every downstream query gets the same definition.

Why CASE WHEN beats IF in every warehouse

Every major warehouse — PostgreSQL, Snowflake, BigQuery, Redshift — supports `CASE WHEN`. Only some support `IF(...)` or `IIF(...)`. Writing `CASE WHEN` from muscle memory means your SQL ports across engines without edits, and PR reviewers don’t have to squint to understand which dialect you were thinking in.

There’s a second reason. `CASE WHEN` chains naturally: three, four, five mutually exclusive conditions read top-to-bottom with `WHEN ... THEN ...` and a final `ELSE`. An `IF` tower of nested `IF(cond1, val1, IF(cond2, val2, ...))` is a maintenance hazard. Senior analysts don’t write trees when they can write lists.

SELECT
  customer_id,
  CASE
    WHEN lifetime_value > 10000 THEN 'VIP'
    WHEN lifetime_value > 1000  THEN 'High'
    WHEN lifetime_value > 100   THEN 'Mid'
    ELSE 'Low'
  END AS segment
FROM dim_customers;

Three things to internalize. First, **order matters** — the first `WHEN` that matches wins. If you swap "High" and "VIP" above, every VIP gets labeled "High." Second, `ELSE` is optional but almost always worth writing; omitting it means unmatched rows get `NULL`, which is rarely what a stakeholder wants in a segment column. Third, the `CASE` expression is a single value; you can use it anywhere a column would go — in `SELECT`, in `GROUP BY`, even inside `SUM()`.

`CASE WHEN col = NULL THEN ...` never matches. NULL equality doesn’t work the way newcomers expect — `NULL = NULL` is NULL, not true, because NULL means "unknown." Use `CASE WHEN col IS NULL THEN ...` (or `IS NOT NULL`) every single time. This is the single most common bug in CASE expressions, and it’s silent: the query runs, it just returns the wrong answer.

The NULL trap that burns every junior

While we’re on it: `CASE WHEN col IN (1, 2, NULL)` also doesn’t match NULL rows. `IN` and `=` are both NULL-unsafe in the same way. If you need to treat NULL as a real value, say so explicitly: `CASE WHEN col IS NULL OR col IN (1, 2) THEN ...`.

Half of what `CASE WHEN` gets used for in analyst work is bucketing a continuous number — revenue, session length, days-since-signup — into a small set of human-readable labels. The trick is ordering from most-restrictive to least-restrictive so the first match wins correctly, and being explicit about boundary conditions.

SELECT
  order_id,
  purchase_amount,
  CASE
    WHEN purchase_amount >= 500 THEN 'Enterprise'
    WHEN purchase_amount >= 100 THEN 'Standard'
    WHEN purchase_amount >= 10  THEN 'Small'
    WHEN purchase_amount >  0   THEN 'Micro'
    ELSE 'Invalid'
  END AS order_tier
FROM fact_purchases;

Notice the `> 0` branch at the bottom — that catches zero-dollar rows (refunds, free trials, corrupted inserts) and flags them as `Invalid` rather than letting them silently become `Micro`. Stakeholders never ask for this, but they always appreciate it when they see it. Your manager will read the segment column and immediately trust the rest of the query.

The single most powerful `CASE WHEN` pattern, and the one that separates junior from senior: pivoting rows into columns with `CASE` inside an aggregate. Most warehouses don’t have a clean `PIVOT` operator (or the syntax varies) — so everyone writes pivots this way.

SELECT
  campaign_id,
  SUM(CASE WHEN channel = 'email' THEN revenue ELSE 0 END) AS email_revenue,
  SUM(CASE WHEN channel = 'paid'  THEN revenue ELSE 0 END) AS paid_revenue,
  SUM(CASE WHEN channel = 'organic' THEN revenue ELSE 0 END) AS organic_revenue,
  SUM(revenue) AS total_revenue
FROM fact_purchases
WHERE purchased_at >= DATE('now', '-30 days')
GROUP BY campaign_id;

Read that carefully. The `CASE` returns `revenue` when the channel matches, and `0` otherwise; `SUM()` adds up only the matching rows per campaign. The result: one row per campaign, one column per channel, plus a total. That’s the structure stakeholders actually want in a dashboard. Junior analysts produce a long table with a `channel` column; senior analysts pivot it for them.

`CASE WHEN condition THEN 1 ELSE 0 END` inside `SUM()` is the idiomatic "count where" pattern. `COUNT(CASE WHEN condition THEN 1 END)` works too because `COUNT` ignores NULLs — pick whichever reads more cleanly to your team.

The moment multiple queries start repeating the same `CASE WHEN` block, you have a source-of-truth problem. Imagine three dashboards all defining "VIP customer" slightly differently — one uses `> 10000`, one uses `>= 10000`, one uses lifetime revenue net of refunds. Stakeholders will pull those three dashboards in the same meeting, see three different VIP counts, and lose trust in the whole data team.

The right fix is to push the CASE into a dbt model (or equivalent warehouse view), so `customer_segment` is a column on `dim_customers` and every downstream query uses the same definition. `CASE WHEN` is cheap to write inline; it’s expensive to write inline *twice*. The second time is your signal to lift it upstream.

If the same CASE expression shows up in two queries that both go to stakeholders, it belongs in dbt. If it shows up in three, you’re already in trouble — the definitions have almost certainly drifted slightly, and someone’s looking at the wrong VIP count right now.

A rule of thumb from senior analysts

`CASE WHEN` is a tiny piece of SQL syntax, but it’s the construct analyst work rests on. Master its bucketing and pivoting patterns, respect the NULL rules, and know when to lift it upstream — and you’ll produce queries that get trusted on first read.

How analysts use CASE WHEN to turn stakeholder rules into SQL — bucketing, pivots, NULL traps, and when to push the logic upstream. With runnable exercises against a real campaign database.

CASE WHEN for Business Logic: The SQL Pattern Analysts Underrate | caseSQL

CASE WHEN for Business Logic: The Pattern Analysts Underrate

SQL appears in roughly **95% of data analyst interviews**, and it’s the most common reason candidates are rejected. The good news: the questions aren’t as varied as they look. From DataLemur and StrataScratch data on thousands of screens at Amazon, Meta, Google, Uber, Stripe, Airbnb, DoorDash, LinkedIn, and Walmart, **seven patterns account for roughly 80% of what gets asked**. Learn these cold and you’re prepared for nearly any SQL interview surface, entry-level through mid.

Each pattern below follows the same shape: the verbatim FAANG-style framing, the canonical solution, and the trap variant interviewers use to separate candidates who memorized syntax from candidates who’ve been bitten by real data. Where it fits the schema, there’s also an exercise against the `dim_customers` / `dim_campaigns` / `fact_sends` / `fact_purchases` star schema that the other caseSQL tutorials use.

Interviewers aren’t testing whether you’ve memorized SQL syntax — reference docs exist. They’re testing whether you can recognize the *shape* of a business question and map it to a query pattern without stalling. These seven patterns are the shapes that come up over and over because they each answer a category of real analyst question: “top N by something,” “trend over time,” “find pairs,” “find who’s missing,” “dedupe,” “pivot,” “bucket by time.”

Every solution below has a "trap variant" the interviewer may probe. If you name the trap before they ask — "I’ll use `<` instead of `!=` here so I don't duplicate the pairs" — you're doing the thing senior candidates do automatically. Narration compounds.

Narrating trade-offs is how entry-level candidates signal mid-level

The single most-asked pattern. Verbatim examples from the wild: *"Find the 2nd highest-grossing product in each Amazon category"* (Amazon), *"Find each user’s 3rd transaction on the Uber platform"* (Uber), *"For every product keyword, find products with at least one position with more than 80% highly relevant displays"* (Etsy). All the same shape: partition by a grouping column, order by a ranking metric, pick the Nth row per partition.

The canonical solution is a window function wrapped in a CTE, then filtered on the rank:

WITH ranked AS (
  SELECT
    product_id,
    category,
    revenue,
    ROW_NUMBER() OVER (
      PARTITION BY category
      ORDER BY revenue DESC
    ) AS rn
  FROM products
)
SELECT product_id, category, revenue
FROM ranked
WHERE rn = 2;   -- 2nd highest-grossing per category

`ROW_NUMBER()` arbitrarily breaks ties; `RANK()` skips ranks after ties (1, 1, 3); `DENSE_RANK()` does not (1, 1, 2). Before you write anything, ask the interviewer: "how should we handle ties?" That single question moves you from junior to mid-level in their mental grading.

The tie trap

Pattern 2 — Running totals and rolling averages

Every product screen and every finance screen uses this pattern. Verbatim: *"What’s the 7-day rolling average of Daily Active Users?"* (Meta), *"Maintain a cumulative transaction balance over time per account"* (Stripe), *"For each user who posted at least twice in 2024, find the number of days between their first and last post"* (Meta).

The canonical solution is `SUM() OVER (PARTITION BY ... ORDER BY ... ROWS BETWEEN ...)` for windows, and `LAG()` for period-over-period deltas:

-- 7-day rolling average of DAU
SELECT
  event_date,
  AVG(dau) OVER (
    ORDER BY event_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS dau_7d_avg
FROM daily_active_users
ORDER BY event_date;

-- Week-over-week delta
SELECT
  event_date,
  dau,
  dau - LAG(dau, 7) OVER (ORDER BY event_date) AS wow_delta
FROM daily_active_users;

Window functions assume the rows you're windowing over are contiguous. If dates are missing (weekends, holidays, gaps in the data), your "7-day rolling" is actually "last 7 rows." Fix: generate a date spine with `generate_series` (Postgres) or a calendar CTE and LEFT JOIN onto it before windowing.

The gap trap

Pattern 3 — Self-joins for pair detection

Pairs of rows from the same table, usually with a time or identity constraint between them. Verbatim: *"Find frequently purchased pairs of items"* (Walmart), *"Identify repeated payments at the same merchant with the same credit card for the same amount within 10 minutes"* (Stripe), *"Detect overlapping subscription periods per customer"* (subscription businesses).

-- Item pairs bought together in the same order
SELECT
  a.item_id AS item_a,
  b.item_id AS item_b,
  COUNT(*) AS pair_count
FROM order_items a
JOIN order_items b
  ON  a.order_id = b.order_id
  AND a.item_id  < b.item_id   -- note the < operator
GROUP BY a.item_id, b.item_id
ORDER BY pair_count DESC
LIMIT 20;

Using `a.item_id != b.item_id` instead of `<` produces every pair twice (A–B and B–A). Interviewers flag this immediately. Always use `<` on the self-join so each unordered pair appears once.

The `!=` trap

Pattern 4 — LEFT JOIN with NULL filter ("who never did X")

Find rows in one table with no matching rows in another. Verbatim: *"Find users who registered but never made a purchase"*, *"Find neighborhoods with zero users"*, *"Find customers who placed their first order but never a second"* (retention / churn).

SELECT u.id, u.email
FROM users u
LEFT JOIN purchases p ON p.user_id = u.id
WHERE p.user_id IS NULL;

The tempting alternative is `WHERE id NOT IN (SELECT user_id FROM purchases)`. But if `purchases.user_id` has any NULLs, `NOT IN` returns zero rows — because `id != NULL` evaluates to UNKNOWN, not FALSE. Use `NOT EXISTS` or the LEFT JOIN + IS NULL pattern above. Knowing this one gotcha alone separates candidates; see the [traps category](/questions/general/traps) for a deeper drill.

The `NOT IN` NULL gotcha

Pattern 5 — Deduplication keeping the latest

Given duplicate rows, keep only the most recent record per entity. Verbatim: *"Keep only the most recent update per user_id"* (data cleaning, every team), *"Identify duplicate job listings accidentally posted by the same company"* (LinkedIn).

WITH latest AS (
  SELECT
    *,
    ROW_NUMBER() OVER (
      PARTITION BY user_id
      ORDER BY updated_at DESC
    ) AS rn
  FROM user_snapshots
)
SELECT *
FROM latest
WHERE rn = 1;

Alternatives to mention: Postgres’ `SELECT DISTINCT ON (user_id) ... ORDER BY user_id, updated_at DESC`, or a correlated subquery with `MAX(updated_at)`. Narrate why you picked the window-function form: it's dialect-portable and reads as one scan instead of a correlated per-row lookup.

Pattern 6 — Pivoting with conditional aggregation

Turn rows into columns with `SUM + CASE`. Verbatim: *"Build a quarterly spend report with one column per department"* (finance), *"Compute the average star rating per product per month, with months as columns"* (Amazon).

SELECT
  DATE_TRUNC('quarter', booked_at) AS quarter,
  SUM(CASE WHEN dept = 'IT'  THEN amount END) AS it_spend,
  SUM(CASE WHEN dept = 'HR'  THEN amount END) AS hr_spend,
  SUM(CASE WHEN dept = 'Eng' THEN amount END) AS eng_spend,
  SUM(CASE WHEN dept NOT IN ('IT','HR','Eng') THEN amount END) AS other_spend
FROM expenses
GROUP BY 1
ORDER BY 1;

Including a catch-all bucket for unclassified rows signals you think about data drift — new departments that appear without your dashboard knowing. It's a tiny touch that reads as senior.

The "Other" bucket wins bonus points

Pattern 7 — Date truncation and time bucketing

Aggregate a timestamp column to a coarser time grain. Verbatim: *"For each month, count Facebook users who created their account that month"* (Meta), *"Aggregate transactions into weekly time series"*, *"Compute month-over-month growth"*.

SELECT
  DATE_TRUNC('month', created_at) AS signup_month,
  COUNT(*) AS new_users
FROM users
GROUP BY 1
ORDER BY 1;

`DATE_TRUNC` exists in Postgres, Redshift, and Snowflake with similar semantics, but BigQuery uses `TIMESTAMP_TRUNC(ts, MONTH)` and MySQL has no direct equivalent (use `DATE_FORMAT(ts, '%Y-%m-01')`). Always name the dialect you're writing in; interviewers respect the awareness.

Dialect is not portable here

Beyond the seven patterns, a short list of single-answer gotchas shows up in virtually every screen. These aren’t patterns — they’re tests of whether you've actually been bitten by real data. Each one is worth rehearsing until your answer is reflexive:

If you recognize only three or four of those as things you'd catch live, your next prep session should be the [traps category](/questions/general/traps) — these are exactly the gotchas built into the explainers there.

Recognition is cheap; *reflex* is the goal. You want the mental move from "this is a top-N per group" to a written window-function CTE to happen in under 15 seconds on a whiteboard. Three concrete practice loops:

The biggest gap between offers and rejections at entry-level is not SQL knowledge — it’s structured business communication. Candidates who can write a window function but cannot articulate why they chose the metric, what trade-offs it carries, and what the business should do next lose to candidates with weaker SQL but stronger framing. Learn the seven patterns. Then practice explaining them.

The pattern after the patterns

The seven SQL patterns that account for ~80% of data analyst interview questions at Amazon, Meta, Uber, Stripe, and Airbnb — with verbatim examples, canonical solutions, and the trap variants that separate candidates.

Seven SQL Patterns for Data Analyst Interviews | caseSQL

The Seven SQL Patterns That Cover 80% of Data Analyst Interviews

If you’re measuring your SQL progress, there’s a single gate that separates analysts who can handle a real job from analysts who stall: window functions. Before window functions, you’re writing aggregates and self-joins; after, you’re writing queries that would have taken a junior two stabs and a coffee break in 15 lines of readable SQL. This post covers the three window-function patterns that do 90% of real analyst work, by use case — running totals, top-N-per-group, and LAG/LEAD for growth math.

Why window functions are the career milestone

The question every window function answers is: _for each row, what’s the value of some expression over a related group of rows?_ "Related" is flexible — it might be "all rows for the same customer," or "all rows before this one chronologically," or "all rows in the same month." Once you have that frame, you can attack every real-world analytics problem that SQL can solve.

Before window functions, analysts solved these problems with self-joins, correlated subqueries, or by pulling data into Python for post-processing. All three work; all three are either slow, ugly, or both. Window functions replace all of them with a syntax that runs on the database engine, scales to any row count the engine can handle, and reads in order from top to bottom the way your brain wants to read SQL.

A window function has three pieces: the function itself (SUM, COUNT, ROW_NUMBER, LAG, etc.), a PARTITION BY (which rows to group together), and an ORDER BY (how to sequence rows within the group). Master those three pieces and you’ve mastered the feature.

The vocabulary

Everybody learning window functions eventually has the same "oh" moment. It goes like this: `PARTITION BY` is just `GROUP BY` that doesn’t collapse the rows. In a `GROUP BY` query, you get one row per group. In a `PARTITION BY` query, you get all the original rows back, each annotated with its group’s aggregate.

That’s the whole mental model. Once it clicks, every window function makes sense. Want a running total of revenue by customer? Partition by customer, order by date, sum the amount. Want each customer’s total revenue duplicated onto every one of their purchase rows? Partition by customer, no order by, sum the amount. Want the largest single purchase for each customer stamped onto every row? Same pattern with `MAX`.

The `ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW` is the default when you include `ORDER BY` in a window with a cumulative aggregate, but making it explicit is worth it — partly for readability, partly because the default is different across dialects and you’ll forget which is which.

The second canonical window-function pattern is "top N per group." Top 3 campaigns by revenue in each quarter. Top 5 customers by spend in each region. Most recent purchase per customer. All of them are some flavor of: partition, order, pick the top rows.

The three ranking functions differ only in how they handle ties:

99% of the time, you want `ROW_NUMBER`. The only common case for `RANK` is when you’re asked for "the top 3" and want to include all ties at rank 3. `DENSE_RANK` mostly shows up in sports-ranking problems and very rarely in real analyst work.

The CTE-then-filter pattern is worth burning into your fingers. You’ll use it for every top-N-per-group query, first-per-customer query, and most-recent-per-entity query for the rest of your career. Note that the WHERE clause filters on the computed `rn` column — SQL doesn’t allow window functions inside WHERE directly, which is why the CTE is necessary rather than optional.

The single most common real-world window-function query is "give me the most recent X per entity." It’s always ROW_NUMBER, partition by the entity, order by timestamp DESC, filter rn = 1. Memorize that skeleton — most-recent-login-per-user, last-purchase-per-customer, current-price-per-product all fit it.

The "first per group" shortcut

The third pattern is the one that quietly earns analysts their seniors stripes: any time you need to compare a row to the row before or after it, you want `LAG` or `LEAD`. Month-over-month growth. Week-over-week change. Time between consecutive purchases. Session-to-next-session gap.

Both functions take an offset (default 1) and optionally a default value for when there is no previous row (usually NULL). `LAG(x)` is "the value of x in the previous row." `LEAD(x)` is "the value of x in the next row." With a `PARTITION BY`, "previous" and "next" are scoped to the group. With an `ORDER BY`, they’re scoped to the sort order.

Month-over-month revenue is the canonical `LAG` query and one you’ll write dozens of times. The skeleton never changes:

WITH monthly AS (
  SELECT STRFTIME('%Y-%m', date_col) AS month,
         SUM(metric) AS total
  FROM source_table
  GROUP BY month
)
SELECT month,
       total,
       LAG(total) OVER (ORDER BY month) AS prev,
       total - LAG(total) OVER (ORDER BY month) AS delta,
       ROUND((total - LAG(total) OVER (ORDER BY month)) * 100.0
             / NULLIF(LAG(total) OVER (ORDER BY month), 0), 1) AS pct
FROM monthly
ORDER BY month;

Swap the metric and the source table and this is half of every business-review slide deck ever built.

Watch what happens with `PARTITION BY campaign_id`: January-to-February comparisons stay within the same campaign, not across campaigns. If you forgot the partition, you’d get month-over-month growth comparing Campaign A’s February to Campaign B’s January, which is nonsense. This is the single most common window-function bug — missing partition means bleeding rows across the group boundary you intended.

Window functions are the moment SQL stops feeling like a query language and starts feeling like a modeling language. Once you reach for them by reflex, the queries you write stop needing post-processing in Python or Excel for 90% of analyst work. That’s the career milestone — not knowing the syntax, but knowing which pattern to reach for.

If you want to build window-function reflexes against a realistic dataset with marketing-style time-series quirks (uneven reporting gaps, mid-month backfills, campaigns that end and restart), the Business Intelligence path on caseSQL is designed around exactly these patterns.

Four gotchas that bite every analyst who’s new to windows, in rough order of how often they show up in real queries:

The frame-clause one is the most subtle and the hardest to debug — numbers come out looking _almost_ right, and only eagle-eyed readers catch that two adjacent rows with the same date share a running total. Once you’ve been burned once, you write `ROWS BETWEEN` explicitly for every cumulative window for the rest of your career.

PostgreSQL, Snowflake, BigQuery, Redshift, MySQL 8+, and SQLite 3.25+ all support the core window-function set (ROW_NUMBER, RANK, LAG, LEAD, SUM/AVG/COUNT with OVER). Snowflake and BigQuery add `QUALIFY` for filtering on window results without a CTE. BigQuery adds `NAVIGATION` functions for richer temporal logic. For 95% of analyst work, stick to the core set — it ports anywhere.

The dialect cheat sheet

Window functions are the career-milestone SQL skill. Running totals, top-N-per-group, and LAG/LEAD for month-over-month growth, explained by use case with exercises.

SQL Window Functions in Real Analyst Work | caseSQL

SQL Window Functions in Real Analyst Work

The `WITH` statement — the common table expression, or CTE — is the single biggest readability improvement SQL has seen in the last twenty years. And yet many analysts still write deeply nested subqueries because that’s what their courses taught. This post is the case for CTEs becoming your default, plus a short tour of the recursive variant that unlocks hierarchical data problems every analyst eventually faces.

Both forms below return the same result. One of them is pleasant to read six weeks later.

-- Subquery form: reads inside-out, nested, hard to comment
SELECT segment, AVG(ltv) AS avg_ltv
FROM (
  SELECT c.segment, p.ltv
  FROM dim_customers c
  JOIN (
    SELECT customer_id, SUM(amount) AS ltv
    FROM fact_purchases
    WHERE purchased_at >= '2025-01-01'
    GROUP BY customer_id
  ) p USING (customer_id)
) x
GROUP BY segment;

-- CTE form: reads top-down, each block named, each block comment-able
WITH recent_ltv AS (
  -- One row per customer with their 2025 lifetime value
  SELECT customer_id, SUM(amount) AS ltv
  FROM fact_purchases
  WHERE purchased_at >= '2025-01-01'
  GROUP BY customer_id
),
segment_ltv AS (
  -- Join LTV back to customer segment
  SELECT c.segment, r.ltv
  FROM dim_customers c
  JOIN recent_ltv r USING (customer_id)
)
SELECT segment, AVG(ltv) AS avg_ltv
FROM segment_ltv
GROUP BY segment;

The CTE form is longer by a few lines. It’s also the form that survives code review, that a colleague can land on and modify confidently, and that comments usefully when the logic gets complex. For any query longer than three clauses, the CTE form is the correct default.

Once you’re using CTEs, the next decision is whether to chain (multiple CTEs in sequence) or nest (one CTE referenced from multiple places). The rule is simpler than it sounds:

Most analyst queries are sequences of transformations, not branching graphs. Chain your CTEs and keep the forward motion. If you find yourself wanting to reference a CTE twice, check whether you’re accidentally recomputing the same thing; usually you can simplify further.

Chain by default

Recursive CTEs: the one pattern analysts need

A recursive CTE references itself. That sounds esoteric; it turns out to be the one SQL feature that makes hierarchical data tractable without writing a loop in Python. The skeleton is always the same shape:

WITH RECURSIVE walk AS (
  -- BASE CASE: the starting set of rows
  SELECT node_id, parent_id, 0 AS depth
  FROM nodes
  WHERE parent_id IS NULL          -- roots

  UNION ALL

  -- RECURSIVE CASE: one level deeper each iteration
  SELECT n.node_id, n.parent_id, w.depth + 1
  FROM nodes n
  JOIN walk w ON n.parent_id = w.node_id
)
SELECT * FROM walk;

Every recursive CTE has two parts: a base case that seeds the recursion, and a recursive case that generates the next layer from the previous one, joined via UNION ALL. The database keeps iterating until the recursive case returns zero new rows, then stops.

Recursive CTEs on a graph with cycles will run forever. If your data could theoretically have cycles (manager loops, circular category references), add a `depth < 100` check or maintain a visited-set column. Most databases will error after some max recursion depth, but you’d rather fail loudly on your terms.

Always have a termination condition

Org charts, category trees, and session chains

Three places recursive CTEs show up in real analyst work. Each one is a hiring-interview-level problem and a real ticket you’ll see.

Org chart: count all reports under a manager

-- How many people (direct + indirect) report to each manager?
WITH RECURSIVE reports AS (
  SELECT employee_id, manager_id, employee_id AS top_manager, 0 AS depth
  FROM dim_employees

  UNION ALL

  SELECT r.employee_id, e.manager_id, r.top_manager, r.depth + 1
  FROM reports r
  JOIN dim_employees e ON e.employee_id = r.manager_id
)
SELECT
  top_manager,
  COUNT(*) - 1 AS total_reports  -- minus 1 to exclude the manager themselves
FROM reports
GROUP BY top_manager;

Category tree: all descendants of a parent category

E-commerce category trees are classic recursive territory. "Electronics → Computers → Laptops → Gaming Laptops" is four levels deep; a product tagged to Gaming Laptops should roll up into all three parent categories for reporting.

Less obviously recursive: "starting from landing page X, what’s the most common 5-step user journey?" can be written as a recursive CTE that walks next-page edges from each session. In practice most analyst teams solve this with window functions, but the recursive form handles variable-length journeys that LAG/LEAD can’t.

CTEs are the single biggest lever for writing SQL that other humans can read. Chain them by default, reach for the recursive variant the next time a manager asks you something about hierarchy, and you’ll stop writing the kind of inside-out nested subqueries that separate juniors from seniors in code review.

Common table expressions (WITH blocks) for readable analyst SQL, and recursive CTEs for hierarchical data — org charts, category trees, and session chains. With exercises.

CTEs and Recursive Queries for Analysts | caseSQL

CTEs and Recursive Queries for Analysts

The gap between "I wrote a query that works" and "I wrote a query that finishes in under a minute on production data" is wider than most SQL courses admit. You don’t need to become a DBA to close it. You need five moves — how to read EXPLAIN, when to lean on an index, when to pre-materialize a subquery, and how to notice when a query is slow because it’s computing the wrong thing against 10x the data.

This post is written for analysts on Postgres (Snowflake, BigQuery, and Redshift have their own wrinkles, but the mental model translates). Everything below is the 80% that pays for itself; the other 20% is rare and belongs to the platform team.

Before any tactic, the posture. Queries have an implicit budget set by what they’re used for:

Most "slow query" tickets are mis-framed. The right question is "what does this query need to be fast enough for?" A 4-minute query powering a Monday morning report doesn’t need tuning; a 4-minute query embedded in a live dashboard tile does. Ask before you optimize.

Know the budget before you tune

Reading EXPLAIN without reading the whole manual

EXPLAIN shows the plan the database made for your query. The full output is intimidating; the analyst-useful parts are a small subset. Prefix any query with `EXPLAIN` to see the plan without running it, or `EXPLAIN ANALYZE` to run it and see actual timings.

EXPLAIN ANALYZE
SELECT c.customer_id, SUM(p.amount) AS lifetime_value
FROM dim_customers c
LEFT JOIN fact_purchases p USING (customer_id)
GROUP BY c.customer_id;

Three things to look for in the output, in order of impact:

Skim for the three patterns above. If none of them are present, the query is usually as fast as it’s going to get without schema changes. The temptation to "just read more of the plan" rarely pays.

You don’t have to read the whole plan

An index helps a SELECT when the database can use it to avoid scanning the whole table. The two cases where an index is worth asking the platform team to create:

What DOESN’T get faster with an index: aggregations over everything (GROUP BY with no filter), LIKE '%...' patterns (leading wildcard bypasses the index), function calls on the indexed column (`WHERE LOWER(email) = '...'` can’t use an index on `email`; needs a functional index instead).

Analysts don’t (usually, or shouldn’t) create indexes in production; indexes slow down writes and consume storage, so they’re the data-platform team’s decision. What you DO is file a clean ticket: "I’m scanning fact_purchases on customer_id for every dashboard refresh; could we add an index?" with the EXPLAIN output attached.

You don’t create indexes

Materialization: CTEs, temp tables, and cached views

Sometimes the same expensive subquery runs three times in one query. Materializing it — forcing the database to compute it once and reuse the result — can be the biggest single optimization available.

-- Force materialization of an expensive CTE in Postgres 12+
WITH active_customers AS MATERIALIZED (
  SELECT customer_id, MAX(session_at) AS last_seen
  FROM fact_sessions
  WHERE session_at >= CURRENT_DATE - INTERVAL '30 days'
  GROUP BY customer_id
)
SELECT
  c.segment,
  COUNT(*)    AS active_count,
  AVG(p.amt)  AS avg_spend
FROM active_customers a
JOIN dim_customers c USING (customer_id)
LEFT JOIN (
  SELECT customer_id, SUM(amount) AS amt FROM fact_purchases
  GROUP BY customer_id
) p USING (customer_id)
GROUP BY c.segment;

Slow queries that are actually grain bugs

The single most common "performance problem" an analyst brings to their tech lead is not a performance problem. It’s a JOIN that’s fanning out into a cross product, producing 100x the expected rows, then slowly aggregating them back down. The query "runs slow" and returns the wrong number — both symptoms of the same underlying bug.

Reflex check before you tune anything: `COUNT(*)` on each table unjoined, `COUNT(*)` on the joined result. If the joined count is much larger than either unjoined count, the query isn’t slow — it’s wrong. Fixing the grain will speed it up AND produce correct numbers.

Once you’ve internalized that most "slow queries" are grain bugs in disguise, the EXPLAIN-index-materialization tactics above will cover almost everything else. The combination is enough to make you the person on the team whose queries "just work" at production scale — without needing to become the DBA.

The five performance moves analysts actually need — how to read EXPLAIN, when an index helps, when to materialize, and how to spot a grain bug pretending to be a slow query.

SQL Performance for Analysts: EXPLAIN, Indexes, Materialization | caseSQL

SQL Performance for Analysts (Without Becoming a DBA)

Join semantics are the first thing you learn in SQL and the last thing you get fully right. Most analysts who have written SQL for a year can still tell you, in the moment, which join they meant — and then type a different one. This post is the practical read-through: when each of the five joins is the correct answer, which default you should reach for before thinking, and the three join mistakes that quietly produce wrong dashboards.

Everything below assumes a star schema that looks like the one used in caseSQL missions: `dim_customers`, `dim_campaigns`, `fact_sends`, `fact_purchases`. The joins work the same shape against any schema.

Before the tactics, the one-liners. Memorize these the way you memorized the first ten prime numbers.

INNER JOIN is almost never what you actually want

The modal analyst writes INNER JOIN as their reflex. The modal analyst is wrong. INNER is the right answer when the business question is "show me the rows that exist on both sides." That’s rarer than it sounds. Most questions are "show me every customer, and include their purchases if they have any" — that’s LEFT, not INNER.

-- "How many customers bought something?" — INNER is correct
SELECT COUNT(DISTINCT c.customer_id)
FROM dim_customers c
INNER JOIN fact_purchases p USING (customer_id);

-- "How many customers do we have, and how many have bought?" — INNER is WRONG
-- It silently drops customers with no purchases, which is exactly the
-- population we care about for the answer.
SELECT COUNT(DISTINCT c.customer_id) AS total_customers,
       COUNT(DISTINCT p.customer_id) AS purchasing_customers
FROM dim_customers c
LEFT JOIN fact_purchases p USING (customer_id);

When in doubt, start with LEFT JOIN from the table you care about the COUNT of. Switch to INNER only after you’ve decided you’re OK dropping non-matching rows. Reversing this default has kept more dashboards honest than any lint rule.

Default to LEFT

LEFT JOIN: the “keep everyone” default

LEFT JOIN keeps every row from the left table and fills unmatched right-side columns with NULL. That NULL is the data; it’s how you tell which left-side rows had no match. The two patterns worth burning into your reflex:

COUNT(*) counts rows; on a LEFT JOIN with no match you still have one row, with NULLs on the right side. COUNT(*) would return 1 for a non-buyer instead of 0. COUNT of a specific right-side column ignores NULLs and gets the answer right.

Why COUNT(p.purchase_id), not COUNT(*)

Anti-joins and reconciliation with FULL OUTER

An anti-join asks "what’s on the left side but NOT on the right?" It’s one of the three or four most useful patterns in analyst SQL, and the one most likely to be written wrong. The canonical form:

-- Customers who signed up but never purchased
SELECT c.*
FROM dim_customers c
LEFT JOIN fact_purchases p USING (customer_id)
WHERE p.customer_id IS NULL;

The key detail: the `IS NULL` check goes on the JOIN-right side column, _after_ the LEFT JOIN. If you tried to write this as `WHERE customer_id NOT IN (SELECT customer_id FROM fact_purchases)`, the NOT IN returns no rows as soon as any purchase has a NULL `customer_id` — SQL’s three-valued logic quietly breaks the filter. The LEFT JOIN + IS NULL form is the correct idiom.

FULL OUTER JOIN is the reconciliation tool: rows that exist on the left but not the right, rows that exist on the right but not the left, and rows in both. Budget vs actuals, claims vs payments, source-of-truth comparisons.

-- Reconciling: which budgets have actuals, which actuals have no budget line
SELECT
  COALESCE(b.cost_center_id, a.cost_center_id) AS cost_center_id,
  b.budget_amount,
  a.actual_amount,
  CASE
    WHEN b.cost_center_id IS NULL THEN 'Actual with no budget line'
    WHEN a.cost_center_id IS NULL THEN 'Budget line with no actuals'
    ELSE 'Matched'
  END AS reconciliation_status
FROM fact_budgets b
FULL OUTER JOIN fact_actuals a USING (cost_center_id, month);

The three join bugs that break dashboards

These three are the ones that hit every analyst at least once, usually in a way that doesn’t surface for weeks.

1. The fanout (many-to-many multiplication)

If the right side of a JOIN has multiple rows per join key, the left side gets multiplied. SUM(revenue) across that join returns 3x the real revenue because every purchase row got joined to three campaign rows. The fix is to pre-aggregate the fanning-out table to one row per key before the JOIN.

-- BROKEN: campaigns fans out because a customer can be on multiple campaigns
SELECT SUM(p.amount) AS revenue
FROM fact_purchases p
JOIN fact_campaign_memberships m ON p.customer_id = m.customer_id;

-- FIXED: roll up memberships before joining
SELECT SUM(p.amount) AS revenue
FROM fact_purchases p
JOIN (
  SELECT customer_id, MIN(campaign_id) AS primary_campaign
  FROM fact_campaign_memberships
  GROUP BY customer_id
) m USING (customer_id);

2. NULLs eating your rows (the INNER-by-accident bug)

A working analyst’s tour of INNER, LEFT, RIGHT, FULL, and CROSS joins — with the three join bugs that silently break dashboards. Free exercises included.

Technique