SQL Patterns 10–12: Conditional Aggregation, Cohort/Retention, Date Arithmetic
These patterns appear in almost every analytics and data engineering role. Cohort analysis is the most frequently asked "hard" business SQL question.
🧠 MEMORY MAP
🧠 ANALYTICS PATTERNS = "CAD"
ANALYTICS PATTERNS"CAD"
CConditional Aggregation: SUM(CASE WHEN ...) = pivot/segment in one query
AcAhort/Retention: MIN(event_date) as cohort key + offset joins
DDate Arithmetic: DATEDIFF, DATE_TRUNC, INTERVAL for time-based calculations
CONDITIONAL AGGREGATION RULE
"Count/Sum by category WITHOUT multiple queries"
SUM(CASE WHEN category='A' THEN 1 ELSE 0 END) AS count_A
SUM(CASE WHEN category='B' THEN amount ELSE 0 END) AS revenue_B
COHORT RULE: "C-M-R" (Cohort → Measure → Rate)
CCohort: MIN(event_date) per user = their "birth date" in this system
MMeasure: COUNT(users who returned on day/week N after cohort date)
RRate: returned_count / cohort_size × 100
DATE ARITHMETIC RULE: "DIFF-TRUNC-ADD"
DIFFDATEDIFF(end, start) for age/interval
TRUNCDATE_TRUNC('month', date) for grouping by period
ADDDATE_ADD(date, 7) for deadlines and offsets
PATTERN 10: CONDITIONAL AGGREGATION / PIVOT
What Is It?
Count or sum values BY CONDITION within a single row using CASE WHEN inside aggregate functions. Creates a "wide" result with category-based columns.
Recognize It When You See:
- "Show [metric] broken down by [category] in the same row"
- "Count paying vs non-paying users per date"
- "Revenue by quarter as columns"
- "Click-through rate (clicks / impressions)"
- "Success vs failure count side by side"
- "Pivot [categories] into columns"
THE TEMPLATE
sql
-- TEMPLATE: Conditional Aggregation
SELECT
grouping_col, -- row identifier (date, user, region)
-- Count rows matching a condition
COUNT(CASE WHEN category = 'A' THEN 1 END) AS count_a,
SUM(CASE WHEN category = 'A' THEN 1 ELSE 0