Cohort & Segmentation Queries — Build cohort/segmentation queries using GROUP BY and CASE - SQL

SECTION 1

Historical Context & Motivation

The practice of grouping customers or users into discrete segments for analysis long predates modern computing. In the early days of direct mail marketing, businesses manually sorted customer lists by purchase recency or geographic region to tailor their campaigns. As relational databases matured through the 1980s and 1990s, the SQL standard provided declarative tools — most notably GROUP BY and CASE expressions — that allowed analysts to perform these segmentation tasks at scale without writing procedural code. The convergence of e-commerce, SaaS subscription models, and data-driven product management in the 2000s and 2010s elevated cohort analysis from a niche marketing technique into a core competency for any data-literate engineer or product analyst.

1986 • SQL-86 Standard Ratified

The first ANSI SQL standard formalized GROUP BY and aggregate functions, giving analysts a portable, declarative way to compute summary statistics across groups of rows.

1992 • CASE Expressions in SQL-92

SQL-92 introduced searched and simple CASE expressions, enabling conditional logic inside SELECT, WHERE, and ORDER BY clauses without leaving the query language.

2003 • Window Functions Emerge

SQL:2003 added OVER and PARTITION BY, complementing GROUP BY with row-level calculations across partitions — a natural extension for sophisticated cohort metrics.

2010s • Rise of Product Analytics

SaaS companies adopted cohort retention tables as a standard KPI dashboard, driving demand for engineers who could write performant cohort queries against event-level data warehouses.

The central question these tools address is straightforward yet powerful: how can we assign each row in a dataset to a semantically meaningful group, and then aggregate metrics within those groups to reveal patterns that would otherwise be invisible in raw, row-level data? That is precisely what cohort and segmentation queries accomplish.

SECTION 2

Core Principles & Definitions

Before writing a single query, it is essential to internalize the foundational ideas that underpin cohort and segmentation analysis. A cohort is a group of entities — typically users — who share a common characteristic anchored to a point in time, such as the month they first registered. A segment is a broader categorization that may or may not be time-based: think "high-value customers" versus "free-tier users." While the terminology differs, the SQL machinery is largely the same: derive a label for each row, then aggregate by that label.

GROUP BY Clause

Collapses rows sharing identical values in one or more columns into a single output row. Aggregate functions (COUNT, SUM, AVG) compute summary metrics within each group.

CASE Expression

Evaluates a series of WHEN conditions and returns a value for the first true branch (or the ELSE default). Used inside SELECT to create derived classification columns on the fly.

Cohort Key

A time-truncated field (e.g., signup month) that partitions users into temporal cohorts. Typically derived via DATE_TRUNC or equivalent functions.

Segment Label

A categorical tag computed from business rules — revenue tier, engagement level, geography — applied via CASE to each row before aggregation.

Retention Period

The offset between a user's cohort date and a subsequent activity date, measured in days, weeks, or months. This dimension forms the columns of a classic retention matrix.

✦

KEY TAKEAWAY

Think of GROUP BY as the SQL equivalent of physically sorting a deck of cards into piles by suit, and CASE as the rule you use to decide which pile each card belongs to when the suit is not printed on the card itself. Together, they let you invent any grouping scheme you need and then count, sum, or average each pile in one pass.

SECTION 3

Visual Explanation — The Cohort Pipeline

The pipeline flows left to right: raw event rows are tagged with derived labels (via CASE and DATE_TRUNC), collapsed by GROUP BY, and emitted as a compact result set of cohort × segment aggregates. The embedded query demonstrates a two-dimension grouping — signup month and value tier.

The diagram above captures the mental model you should carry when constructing any cohort or segmentation query. First, identify the raw grain of your data — typically one row per event or one row per user. Second, define the derived labels that partition rows into groups using CASE expressions, date-truncation functions, or joins to dimension tables. Third, specify the GROUP BY clause listing every non-aggregate column, which collapses potentially millions of rows into a concise summary. Finally, select the aggregate metrics — COUNT, SUM, AVG, MIN, MAX — that answer the business question. This four-step pipeline is universal across dialects: PostgreSQL, MySQL, BigQuery, Snowflake, and others.

SECTION 4

How GROUP BY and CASE Work Together

CASE Expression Syntax

The searched CASE expression evaluates Boolean predicates in order and returns the value associated with the first predicate that evaluates to TRUE. If no predicate matches, the ELSE branch fires (defaulting to NULL when ELSE is omitted). Because CASE is an expression — not a statement — it can appear anywhere a scalar value is legal: in SELECT lists, WHERE filters, ORDER BY, and even inside aggregate functions. This composability is what makes it the workhorse of segmentation logic.

SEARCHED CASE PATTERN

CASE WHEN predicate₁ THEN value₁ WHEN predicate₂ THEN value₂ … ELSE default_value END

predicateₙ = any Boolean expression referencing table columns; valueₙ = scalar result (string, number, date); default_value = returned when no predicate is TRUE.

GROUP BY Execution Model

Conceptually, GROUP BY partitions the filtered row set (after FROM, JOIN, and WHERE) into groups where every row within a group has the same tuple of values for the listed columns. The database engine then evaluates aggregate functions once per group, producing exactly one output row per group. When you combine CASE in the SELECT list with GROUP BY on the same CASE expression (or its alias), you are effectively defining a virtual column that exists only for the purpose of aggregation — no schema change required.

GROUP BY WITH DERIVED COLUMN

SELECT f(col) AS label, AGG(metric) FROM table GROUP BY f(col)

f(col) = any deterministic expression (CASE, DATE_TRUNC, SUBSTRING, etc.); AGG = aggregate function; the GROUP BY must reference the same expression or its ordinal position.

Conditional Aggregation Pattern

A closely related technique places CASE inside the aggregate function rather than alongside GROUP BY. This conditional aggregation pattern pivots segments into separate columns instead of separate rows, which is ideal for building retention matrices or cross-tab reports.

CONDITIONAL AGGREGATION

COUNT(CASE WHEN condition THEN 1 END) or SUM(CASE WHEN condition THEN metric END)

Rows where the condition is FALSE produce NULL, which COUNT and SUM silently skip. This avoids the need for multiple filtered subqueries.

SECTION 5

Building a Cohort Retention Matrix

The classic deliverable of cohort analysis is the retention matrix (also called a retention triangle). Each row represents a signup cohort — for example, all users who created an account in January 2024. Each column represents a period offset from that cohort's origin: Month 0 (the signup month itself), Month 1, Month 2, and so on. The cell values typically show the percentage of the original cohort that was still active during the offset period. Constructing this matrix in SQL requires combining DATE_TRUNC for the cohort key, a date-difference calculation for the period offset, GROUP BY on both dimensions, and COUNT DISTINCT to measure returning users.

The upper half shows a typical retention matrix: each row is a monthly signup cohort, columns are months since signup (M0–M4), and cell values are retention percentages. Cells fade in opacity as retention drops. Dashes indicate periods that have not yet elapsed. The lower half sketches the CTE-based SQL pattern used to generate this matrix.

Notice the triangular shape of the matrix: more recent cohorts have fewer elapsed periods, so their later columns are blank. The period offset is computed by subtracting the cohort date from the event date, typically using DATE_DIFF or equivalent. The conditional aggregation variant uses COUNT(CASE WHEN period = 1 THEN user_id END) to pivot periods into individual columns, producing a result set that maps directly onto the visual matrix above without post-processing.

SECTION 6

Worked Example — Revenue Segmentation by Cohort

Suppose you work at an e-commerce company with a users table (columns: user_id, signup_date) and an orders table (columns: order_id, user_id, order_date, amount). The product manager asks: "For each quarterly signup cohort, show the number of users, average order value, and break them into 'power', 'regular', and 'light' spender segments based on total spend."

Revenue Segmentation by Signup Cohort

1
Step 1 — Compute per-user total spend

We first need each user's total lifetime spend. This requires joining users to orders and aggregating. We use a CTE for clarity: WITH user_spend AS ( SELECT u.user_id, u.signup_date, COALESCE(SUM(o.amount), 0) AS total_spend FROM users u LEFT JOIN orders o ON u.user_id = o.user_id GROUP BY u.user_id, u.signup_date )

One row per user with their signup_date and total_spend.
2
Step 2 — Derive cohort and segment labels

In the outer query, we derive two classification columns. The cohort is the calendar quarter of signup: DATE_TRUNC('quarter', signup_date) AS cohort_qtr. The spending segment uses a CASE expression: CASE WHEN total_spend >= 1000 THEN 'power' WHEN total_spend >= 200 THEN 'regular' ELSE 'light' END AS spend_tier.

Each user row now carries a cohort_qtr label and a spend_tier label.
3
Step 3 — Aggregate with GROUP BY

We group by both derived columns and compute aggregates: SELECT cohort_qtr, spend_tier, COUNT(*) AS user_count, ROUND(AVG(total_spend), 2) AS avg_spend FROM user_spend GROUP BY cohort_qtr, spend_tier ORDER BY cohort_qtr, spend_tier. Note that GROUP BY references the same expressions used in the SELECT list.

Compact result: one row per (cohort_qtr, spend_tier) combination with user_count and avg_spend.
4
Step 4 — Interpret sample output

Assume the query returns: Q1-2024 / power / 85 users / $1,742 avg; Q1-2024 / regular / 430 users / $487 avg; Q1-2024 / light / 685 users / $62 avg. This tells us the Q1 cohort was bottom-heavy: 57% were light spenders. Product can now investigate whether onboarding changes in Q2 shifted this distribution.

The combined CTE + CASE + GROUP BY pattern answers multi-dimensional segmentation questions in a single, readable query.

SECTION 7

Strengths, Limitations & Alternatives

Dimension	GROUP BY + CASE	Window Functions
Output grain	One row per group — collapses detail rows	Preserves every input row; adds columns
Readability	Simple, familiar to all SQL users	More complex syntax (PARTITION BY, ORDER BY)
Percentile / ranking	Requires self-join or subquery	Native NTILE, PERCENT_RANK functions
Running totals	Requires correlated subquery — slow	SUM() OVER (ORDER BY …) — efficient
Performance at scale	Well-optimized in all engines; hash/sort agg	Can be costly if partition is wide
Best for	Summary reports, dashboards, retention matrices	Row-level labeling, sessionization, funnel ordering

GROUP BY + CASE vs. Window Functions for segmentation tasks

⚠

Common Pitfall

When using CASE inside GROUP BY, ensure the CASE expression in the SELECT list is identical to the one in GROUP BY, or use the column alias (supported in PostgreSQL and MySQL, but not in standard SQL or SQL Server). A mismatch will trigger a "not in GROUP BY clause" error or, worse, silently produce incorrect groupings.

✦

KEY TAKEAWAY

GROUP BY + CASE is the Swiss Army knife of analytics SQL — it handles the vast majority of segmentation and cohort reporting needs with minimal syntactic overhead. Reach for window functions when you need row-level context alongside aggregate metrics, but start with GROUP BY + CASE until you hit its limits.

SECTION 8

Connection to Advanced Techniques

The GROUP BY + CASE pattern forms the foundation upon which more sophisticated analytical techniques are built. As your SQL proficiency grows, you will encounter scenarios where basic grouping is insufficient and you need to layer additional tools on top. Understanding how today's patterns extend into advanced territory will help you recognize when to level up.

Basic Pattern	Advanced Extension	When to Upgrade
GROUP BY with CASE tiers	NTILE() / PERCENT_RANK() window functions	When bucket boundaries should be data-driven (percentiles) rather than hard-coded
Conditional aggregation for pivoting	PIVOT / CROSSTAB or dynamic SQL	When the number of pivot columns is unknown at query-write time
Single-level GROUP BY	GROUPING SETS / ROLLUP / CUBE	When you need subtotals, grand totals, or multiple grouping levels in one pass
Static cohort assignment	LAG / LEAD for churn detection	When you need to compare each user's current period to their prior period
Retention by period offset	Survival analysis via cumulative sums	When you need to model time-to-churn as a continuous distribution

From basic GROUP BY + CASE to advanced analytics patterns

One particularly powerful extension is GROUPING SETS, which lets you produce multiple levels of aggregation — for example, totals by cohort only, by segment only, and by both — in a single query pass. The ROLLUP and CUBE operators are syntactic shortcuts for common GROUPING SETS patterns and are particularly useful in BI dashboards that display drill-down hierarchies. As you internalize the core GROUP BY + CASE workflow, these extensions will feel like natural generalizations rather than foreign concepts.

SECTION 9

Practice Problems

Question 1: CONCEPTUAL

Explain why a CASE expression used in the SELECT list must also appear (identically or by alias) in the GROUP BY clause when that query contains aggregate functions. What would happen if you omitted it from GROUP BY?

Show answer

The SQL standard requires that every non-aggregated expression in the SELECT list be functionally determined by the GROUP BY columns. A CASE expression produces a scalar value per row, and if it is not included in GROUP BY, the engine cannot determine which row's CASE result to display for each group — the value is ambiguous. Most databases will raise an error such as "column must appear in the GROUP BY clause or be used in an aggregate function." In MySQL with ONLY_FULL_GROUP_BY disabled, the engine silently picks an arbitrary row's value, leading to nondeterministic and potentially incorrect results.

Question 2: BASIC CALCULATION

Given a table orders(order_id, user_id, order_date, total), write a query that classifies each order as 'small' (total < 50), 'medium' (50 ≤ total < 200), or 'large' (total ≥ 200) and returns the count and average total for each size category.

Show answer

SELECT CASE WHEN total < 50 THEN 'small' WHEN total < 200 THEN 'medium' ELSE 'large' END AS size_cat, COUNT() AS order_count, ROUND(AVG(total), 2) AS avg_total FROM orders GROUP BY size_cat ORDER BY avg_total; — The CASE expression creates a derived column size_cat. GROUP BY size_cat (or the equivalent full CASE expression if your database does not support alias references) collapses all orders in each tier. COUNT() gives the number of orders per tier and AVG(total) gives the mean order value within each tier.

Question 3: INTERMEDIATE

Using tables users(user_id, signup_date) and logins(login_id, user_id, login_date), write a query that computes monthly cohort retention — specifically, for each signup month, the count of distinct users who logged in during each of the first three calendar months after their signup month (period 1, 2, and 3). Return columns: cohort_month, period_1_users, period_2_users, period_3_users.

Show answer

WITH cohorts AS ( SELECT user_id, DATE_TRUNC('month', signup_date) AS cohort_month FROM users ) SELECT c.cohort_month, COUNT(DISTINCT CASE WHEN DATE_TRUNC('month', l.login_date) = c.cohort_month + INTERVAL '1 month' THEN l.user_id END) AS period_1_users, COUNT(DISTINCT CASE WHEN DATE_TRUNC('month', l.login_date) = c.cohort_month + INTERVAL '2 months' THEN l.user_id END) AS period_2_users, COUNT(DISTINCT CASE WHEN DATE_TRUNC('month', l.login_date) = c.cohort_month + INTERVAL '3 months' THEN l.user_id END) AS period_3_users FROM cohorts c LEFT JOIN logins l ON c.user_id = l.user_id GROUP BY c.cohort_month ORDER BY c.cohort_month; — This uses conditional aggregation: each COUNT(DISTINCT CASE …) counts users who appeared in a specific offset month. The LEFT JOIN ensures cohorts with zero retention in a period show 0 rather than being dropped.

Question 4: APPLIED

A product manager wants a report showing, for each quarterly signup cohort, what percentage of users fall into the 'high engagement' segment (≥ 20 sessions in their first 30 days) versus 'low engagement' (< 20 sessions). You have users(user_id, signup_date) and sessions(session_id, user_id, session_date). Write the complete query and explain each CTE.

Show answer

WITH early_sessions AS ( SELECT u.user_id, DATE_TRUNC('quarter', u.signup_date) AS cohort_qtr, COUNT(s.session_id) AS session_count FROM users u LEFT JOIN sessions s ON u.user_id = s.user_id AND s.session_date BETWEEN u.signup_date AND u.signup_date + INTERVAL '30 days' GROUP BY u.user_id, cohort_qtr ), classified AS ( SELECT cohort_qtr, CASE WHEN session_count >= 20 THEN 'high_engagement' ELSE 'low_engagement' END AS engagement_tier, user_id FROM early_sessions ) SELECT cohort_qtr, engagement_tier, COUNT() AS user_count, ROUND(100.0 COUNT() / SUM(COUNT()) OVER (PARTITION BY cohort_qtr), 1) AS pct_of_cohort FROM classified GROUP BY cohort_qtr, engagement_tier ORDER BY cohort_qtr, engagement_tier; — early_sessions counts sessions within 30 days of signup for each user and derives the quarterly cohort key. classified applies the CASE threshold. The final SELECT uses GROUP BY plus a window function SUM(COUNT(*)) OVER (PARTITION BY cohort_qtr) to compute each tier's share of the total cohort in a single pass.

Question 5: CRITICAL THINKING

Discuss the trade-offs between defining segment boundaries with hard-coded CASE thresholds (e.g., total_spend ≥ 500 → 'high') versus data-driven boundaries using NTILE or PERCENT_RANK window functions. Under what business circumstances would each approach be preferable, and what are the implications for query reproducibility and interpretability?

Show answer

Hard-coded CASE thresholds are deterministic and stable: the same user always lands in the same segment regardless of how the rest of the population shifts. This is ideal when business stakeholders have agreed on fixed definitions (e.g., a 'premium' customer is anyone who spends ≥ $500/year), because the thresholds carry semantic meaning, are easy to communicate, and the query is fully reproducible across runs. The downside is that thresholds may become stale as the user base evolves — a $500 threshold set in 2020 might be the median by 2025. Data-driven approaches like NTILE(4) always split users into equal-sized quartiles based on the current data distribution, so segments automatically adapt. However, this means that a user classified as 'top quartile' last month may drop to the second quartile this month without changing behavior — the boundary moved because other users changed. This makes trend comparisons across time periods ambiguous. The best practice is often a hybrid: use PERCENT_RANK to explore the distribution first, then set CASE thresholds based on the percentile values you observe, revisiting them periodically.

SUMMARY

Lesson Summary

Summary

This lesson covered the construction of cohort and segmentation queries using two foundational SQL constructs. The CASE expression provides inline conditional logic to derive segment labels (such as spending tiers or engagement levels), while GROUP BY collapses rows sharing the same label into summary aggregates like COUNT, SUM, and AVG. When the label is a time-truncated field — such as DATE_TRUNC('month', signup_date) — the groups become temporal cohorts suitable for retention analysis.

We explored the conditional aggregation technique — placing CASE inside an aggregate function — to pivot retention periods or segment metrics into columns. We examined how CTEs improve readability by isolating the per-user pre-aggregation step from the cohort-level summary. The trade-off between hard-coded thresholds and data-driven percentile boundaries was discussed, along with forward-looking extensions such as GROUPING SETS, ROLLUP, and window-function-based approaches like NTILE and LAG.