Behavioral Analytics — Learn Window Functions, Recursive CTEs, and Multi-Period Analysis in Practice

QUESTION 1

Window Function ROW_NUMBER() — Extract the First Purchase Record for Each User

ROW_NUMBERPARTITION BYPurchase analysisFirst-event extraction

Background ▼

Window functions add information such as a rank or aggregate within a group to each row without collapsing rows like GROUP BY. ROW_NUMBER() assigns consecutive numbers starting at 1 in ORDER BY order within each partition.

ROW_NUMBER() OVER (
  PARTITION BY user_id            -- Number rows independently for each user
  ORDER BY     purchased_at ASC  -- Number from oldest to newest (rn=1 is first)
)

ROW_NUMBER vs RANK vs DENSE_RANK: When timestamps tie exactly, ROW_NUMBER still assigns unique numbers (which row receives 1 is unspecified). RANK gives tied rows the same rank and skips the next rank. To extract exactly one first event, ROW_NUMBER + WHERE rn = 1 is the safest pattern.

Problem ▼

From the purchase_events table, extract each user's first purchase record (user_id, first_item, first_purchase_date). Use a CTE to assign each row a number (rn) ordered by purchased_at from oldest to newest, then filter to rn=1 in the outer query. Return the rows in ascending user_id order.

Tables used

► purchase_events (7 rows)

user_id	item_id	purchased_at
1	A001	2024-01-05
2	B001	2024-01-10
1	C002	2024-01-15
3	F002	2024-01-18
3	D001	2024-01-20
2	E003	2024-02-01
1	G004	2024-02-10

✱ user1=3 purchases / user2=2 / user3=2. Extract only the first purchase for each user.

Expected Output ▼

Expected output (ascending user_id):

user_id	first_item	first_purchase_date
1	A001	2024-01-05
2	B001	2024-01-10
3	F002	2024-01-18

user1: A001 is first (01-05). user2: B001 is first (01-10). user3: F002 is first (01-18, earlier than D001 on 01-20).

Model Answer ▼

WITH ranked AS (
  SELECT
    user_id,
    item_id,
    purchased_at,
    ROW_NUMBER() OVER (
      PARTITION BY user_id           -- Independent numbering for each user
      ORDER BY     purchased_at ASC  -- Oldest first, so rn=1 is the first purchase
    ) AS rn
  FROM   purchase_events
)
SELECT
  user_id,
  item_id        AS first_item,
  purchased_at   AS first_purchase_date
FROM   ranked
WHERE  rn = 1                        -- Keep only the first row for each user
ORDER BY user_id;

/*
  Logical evaluation order:
  1. Define CTE ranked       → Evaluate the window function (row count is preserved)
  2. WHERE rn = 1            → Filter rows
  3. SELECT                  → Evaluate columns (first_item, first_purchase_date)
  4. ORDER BY user_id        → Sort and output
*/

Explanation (table transitions & key points) ▼

WITH ranked AS (
  SELECT
    user_id, item_id, purchased_at,
    ROW_NUMBER() OVER (
      PARTITION BY user_id
      ORDER BY     purchased_at ASC
    ) AS rn
  FROM   purchase_events
)
SELECT
  user_id,
  item_id      AS first_item,
  purchased_at AS first_purchase_date
FROM   ranked
WHERE  rn = 1
ORDER BY user_id;

LEGEND ▼

Rows read / loaded

① FROM purchase_events (7 rows) ▼

FROM purchase_eventsLoad the 7 rows from purchase_events. The window function starts here and, unlike GROUP BY, does not collapse rows. Blue=user1 (3 rows), gray=user2 (2 rows), and orange=user3 (2 rows) are interleaved.

1 / 5

user_id	item_id	purchased_at
1	A001	2024-01-05
2	B001	2024-01-10
1	C002	2024-01-15
3	F002	2024-01-18
3	D001	2024-01-20
2	E003	2024-02-01
1	G004	2024-02-10

7 rows loaded (all rows preserved for window-function evaluation)

LEARNING POINTS

Window functions add information without removing rows: GROUP BY collapses multiple rows into one, while ROW_NUMBER() OVER (...) preserves every row and adds an rn column. Apply the filter (WHERE rn=1) in the outer query of the CTE. This “assign in a CTE → filter outside” pattern is extremely common in production.

Generalize to the latest or earliest row per group: With ORDER BY purchased_at DESC, the latest purchase becomes rn=1. The same pattern appears in recent user sessions, last logins, latest order status, and many other behavioral-analytics tasks.

ROW_NUMBER vs RANK for ties: If purchased_at is exactly tied for one user_id, filtering with RANK() = 1 can return multiple rows. Use ROW_NUMBER when you need exactly one row, and RANK when you want to return every tied row.

ANTI-PATTERNS

GROUP BY + MIN(purchased_at) alone cannot return item_id: SELECT user_id, MIN(purchased_at) FROM ... GROUP BY user_id returns the earliest timestamp but not the item_id from that row. Retrieving item_id requires another self-join and makes the query more complex. The ROW_NUMBER pattern solves it in one query.

Put WHERE inside the CTE: Because window functions are evaluated in the SELECT phase, SELECT ... ROW_NUMBER() AS rn FROM t WHERE rn = 1 is invalid (rn does not exist yet when WHERE runs). Generate rn in a CTE or subquery first, then filter it in the outer WHERE.

Practical note: Use cases for first-purchase analysis

Extracting the first-purchase record is a basic preprocessing step for causal analysis of campaign effects. Common uses include the distribution of first-purchase item_id and first_purchase_date after seeing Ad A, the distribution of days from registration to first purchase, and changes in first-purchase CVR across cohorts before and after a campaign. ROW_NUMBER-based first-row extraction is one of the most important patterns in behavioral-analytics SQL.

Model answers and explanations are for members

Read every model answer, explanation and table-transition visualization across all 48 advanced sets (240 questions)
One-time purchase — no subscription. Future advanced sets are included
Background, problem and expected output stay free

Unlock for $20 USD

Payments are securely processed by Stripe — your card details never touch this site.

You are charged in US dollars. If your card is issued in another currency, your bank’s exchange rate and any foreign transaction fee apply.

Already purchased? Sign in on your account page.

QUESTION 2

Window Function NTILE() — Score Users on a Four-Level Purchase-Frequency Scale

NTILECTEUser segmentationFrequency score

Background ▼

NTILE(n) is a window function that divides all rows into n buckets and assigns each row a bucket number (1–n). It is often used for Frequency scores in RFM analysis and for automatically classifying user engagement levels.

NTILE(4) OVER (ORDER BY purchase_count ASC)
-- Split 5 users into 4 buckets: [2 rows, 1 row, 1 row, 1 row]
-- The first bucket receives the extra row (bucket1 has 2 users)
-- ORDER BY ASC → smaller values go to tile1 = low score

NTILE bucketing rule: When the row count is not divisible by the number of buckets, the first buckets receive one extra row each. Splitting 5 rows into 4 buckets gives (5÷4=1 remainder 1) → bucket1 has 2 rows and buckets2–4 have 1 each. With ORDER BY ASC, smaller values go into lower buckets, so the highest score means the highest-frequency users (bucket4).

Problem ▼

From the purchases table, aggregate purchase counts per user and assign a four-level frequency score (freq_score) with NTILE(4). Also flag users with freq_score=4 as is_high_value = true, and return the result in ascending user_id order. Output columns: user_id, purchase_count, freq_score, is_high_value.

Tables used

► purchases (15 rows)

user_id	order_id	ordered_at
1	101	2024-01-05
1	102	2024-01-20
1	103	2024-02-10
2	104	2024-01-08
2	105	2024-02-05
3	106	2024-01-12
3	107	2024-01-18
3	108	2024-01-24
3	109	2024-02-02
3	110	2024-02-15
4	111	2024-01-30
5	112	2024-01-09
5	113	2024-01-16
5	114	2024-02-08
5	115	2024-02-20

✱ user1=3 purchases / user2=2 / user3=5 / user4=1 / user5=4

Expected Output ▼

Expected output (ascending user_id):

user_id	purchase_count	freq_score	is_high_value
1	3	2	false
2	2	1	false
3	5	4	true
4	1	1	false
5	4	3	false

Split 5 rows into 4 buckets: user4 (1 purchase) and user2 (2) → tile1, user1 (3) → tile2, user5 (4) → tile3, user3 (5) → tile4. Because the extra row goes to tile1, user4 and user2 share score 1.

Model Answer ▼

WITH purchase_counts AS (
  -- Aggregate purchase counts per user
  SELECT
    user_id,
    COUNT(*) AS purchase_count
  FROM   purchases
  GROUP BY user_id
),
scored AS (
  -- Score in four levels with NTILE(4) (1=low frequency, 4=high frequency)
  SELECT
    user_id,
    purchase_count,
    NTILE(4) OVER (
      ORDER BY purchase_count ASC  -- Fewest first → tile1=low frequency
    ) AS freq_score
  FROM   purchase_counts
)
SELECT
  user_id,
  purchase_count,
  freq_score,
  (freq_score = 4) AS is_high_value  -- Score 4 is the highest-frequency segment
FROM   scored
ORDER BY user_id;

/*
  Logical evaluation order:
  1. Define CTE purchase_counts  → Evaluate grouping and aggregate functions
  2. Define CTE scored            → Evaluate the window function (row count is preserved)
  3. SELECT                       → Evaluate columns (freq_score, is_high_value)
  4. ORDER BY user_id             → Sort and output
*/

Explanation (table transitions & key points) ▼

WITH purchase_counts AS (
  SELECT
    user_id,
    COUNT(*) AS purchase_count
  FROM   purchases
  GROUP BY user_id
),
scored AS (
  SELECT
    user_id, purchase_count,
    NTILE(4) OVER (
      ORDER BY purchase_count ASC
    ) AS freq_score
  FROM   purchase_counts
)
SELECT
  user_id, purchase_count, freq_score,
  (freq_score = 4) AS is_high_value
FROM   scored
ORDER BY user_id;

LEGEND ▼

Rows read / loaded

① CTE purchase_counts — Aggregate purchase counts ▼

GROUP BY user_id → COUNT(*) AS purchase_countGroup the 15 rows in purchases by user_id and count each user's purchases. These 5 rows become the input to NTILE.

1 / 3

user_id	► purchase_count
1	3
2	2
3	5
4	1
5	4

purchase_counts CTE: 5 rows

LEARNING POINTS

NTILE adapts scoring to the data distribution: Hard-coded thresholds (for example, CASE WHEN count >= 5 THEN 4) lose their meaning when the period or data changes. NTILE scores each user by relative position, such as “top N%”, so the buckets continue to contain roughly one nth of the users as the data changes.

Separate concerns with two CTE stages: purchase_counts handles “aggregation” and scored handles “scoring,” so each piece can be tested and changed independently. Changing the NTILE count in scored from 4 to 5 is enough to switch to five-level scoring.

Extend the pattern to RFM analysis: In addition to Frequency (this example), calculate Recency (days since the last purchase → NTILE ORDER BY days_since_last ASC) and Monetary (total purchase amount → NTILE ORDER BY total_amount ASC) in the same way, then combine the three scores into an overall RFM score.

ANTI-PATTERNS

Reversing ASC/DESC reverses the score meaning: With ORDER BY purchase_count ASC, smaller values enter smaller buckets, so tile4 means high frequency. With ORDER BY purchase_count DESC, tile4 means low frequency and the is_high_value judgment is reversed. Record explicitly whether the rule is “ORDER BY ASC + tile4 = high” or “ORDER BY DESC + tile1 = high.”

Over-partition NTILE: If you want a score across all users, NTILE(4) OVER (PARTITION BY segment ORDER BY ...) instead produces a relative rank within each segment. Omit PARTITION BY when you need an overall ranking.

Practical note: Use Frequency scores for campaign delivery

Writing the NTILE-derived freq_score back to a CRM user table enables segment-based email delivery. Branch campaigns such as a “VIP thank-you coupon” for score=4 (high-frequency) users and a “repurchase promotion” for score=1 (low-frequency) users. Recalculating the score in a monthly batch keeps it aligned with current purchase behavior. Because NTILE segmentation has a clear, reproducible basis, it is easy to explain to business stakeholders.

Model answers and explanations are for members

Read every model answer, explanation and table-transition visualization across all 48 advanced sets (240 questions)
One-time purchase — no subscription. Future advanced sets are included
Background, problem and expected output stay free

Unlock for $20 USD

Payments are securely processed by Stripe — your card details never touch this site.

You are charged in US dollars. If your card is issued in another currency, your bank’s exchange rate and any foreign transaction fee apply.

Already purchased? Sign in on your account page.

QUESTION 3

Window Function LAG() — Calculate Elapsed Days Between Steps and Judge Time-Bounded Conversion

LAGPARTITION BYFunnel analysisTime-bounded CVR

Background ▼

LAG(col) retrieves the value from the previous row in a window. The first row in each partition becomes NULL.

LAG(stepped_at) OVER (
  PARTITION BY user_id         -- Evaluate each user independently
  ORDER BY     stepped_at ASC  -- Chronological order
) AS prev_stepped_at
-- The first row (page_view) is NULL; the next row (signup) receives page_view's timestamp

date - date returns an integer (days): In PostgreSQL, subtracting date - date returns an integer number of days. By contrast, timestamp - timestamp returns an interval. You can compare dates directly with stepped_at - prev_stepped_at <= 7 and express a within-seven-day conversion check in one line.

Problem ▼

From the step_events table, calculate the elapsed days from the previous step (page_view) to each user's signup step and whether the conversion occurred within 7 days. Use LAG() to retrieve the previous timestamp, then keep only signup rows with WHERE. Output columns: user_id, stepped_at, prev_stepped_at, days_from_prev, within_7days (ascending user_id).

Tables used

► step_events (8 rows)

user_id	step	stepped_at
1	page_view	2024-01-01
1	signup	2024-01-03
2	page_view	2024-01-05
2	signup	2024-01-07
3	page_view	2024-01-10
3	signup	2024-01-18
4	page_view	2024-01-15
4	signup	2024-01-16

✱ user3 takes 8 days from page_view to signup → outside the seven-day conversion window

Expected Output ▼

Expected output (ascending user_id):

user_id	stepped_at	prev_stepped_at	days_from_prev	within_7days
1	2024-01-03	2024-01-01	2	true
2	2024-01-07	2024-01-05	2	true
3	2024-01-18	2024-01-10	8	false
4	2024-01-16	2024-01-15	1	true

Only user3 signs up after 8 days and falls outside the conversion window. The other three users are within 7 days.

Model Answer ▼

WITH step_lagged AS (
  SELECT
    user_id,
    step,
    stepped_at,
    LAG(stepped_at) OVER (     -- Get the previous step timestamp for the same user
      PARTITION BY user_id
      ORDER BY     stepped_at ASC
    ) AS prev_stepped_at
  FROM   step_events
)
SELECT
  user_id,
  stepped_at,
  prev_stepped_at,
  (stepped_at - prev_stepped_at)       AS days_from_prev,  -- date-date → integer
  (stepped_at - prev_stepped_at) <= 7  AS within_7days     -- Boolean: within 7 days
FROM   step_lagged
WHERE  step = 'signup'                  -- Signup rows only (exclude page_view rows)
ORDER BY user_id;

/*
  Logical evaluation order:
  1. Define CTE step_lagged  → Evaluate the window function (row count is preserved)
  2. WHERE step = 'signup'   → Filter rows
  3. SELECT                  → Evaluate columns (days_from_prev, within_7days)
  4. ORDER BY user_id        → Sort and output
*/

Explanation (table transitions & key points) ▼

WITH step_lagged AS (
  SELECT
    user_id, step, stepped_at,
    LAG(stepped_at) OVER (
      PARTITION BY user_id
      ORDER BY     stepped_at ASC
    ) AS prev_stepped_at
  FROM   step_events
)
SELECT
  user_id, stepped_at, prev_stepped_at,
  (stepped_at - prev_stepped_at)      AS days_from_prev,
  (stepped_at - prev_stepped_at) <= 7 AS within_7days
FROM   step_lagged
WHERE  step = 'signup'
ORDER BY user_id;

LEGEND ▼

Rows read / loaded

① FROM step_events (8 rows) ▼

FROM step_eventsLoad the 8 rows from step_events. Each user has two rows, page_view and signup. LAG() evaluates all 8 rows without collapsing them with GROUP BY.

1 / 5

user_id	step	stepped_at
1	page_view	2024-01-01
1	signup	2024-01-03
2	page_view	2024-01-05
2	signup	2024-01-07
3	page_view	2024-01-10
3	signup	2024-01-18
4	page_view	2024-01-15
4	signup	2024-01-16

8 rows loaded (2 steps per user)

LEARNING POINTS

The three arguments of LAG(col, n, default): LAG(col) is shorthand for LAG(col, 1) and retrieves the previous row. Use LAG(col, 2) for two rows back, or specify a default value instead of NULL with the third argument, as in LAG(col, 1, stepped_at). This is useful when the first row should not be NULL.

date - date is integer; timestamp - timestamp is interval: When stepped_at is a date, stepped_at - prev_stepped_at directly returns an integer number of days. With timestamp values it returns an interval, so conversion such as EXTRACT(epoch FROM (ts1 - ts2)) / 86400 is needed. Check the types and choose the appropriate handling.

When to use LEAD(): LAG looks backward, while LEAD looks forward. LEAD(stepped_at) OVER (PARTITION BY user_id ORDER BY stepped_at) retrieves the next step's timestamp and can be used to predict whether a purchase arrives within 7 days after signup or detect churn signals when the next active month is not the following month.

ANTI-PATTERNS

Use a self-join to retrieve the previous row: FROM step_events e1 JOIN step_events e2 ON e1.user_id = e2.user_id AND e2.step = 'page_view' can multiply rows when a user has the same step more than once. LAG() safely and efficiently references only the immediately preceding row within the partition.

Let a LAG NULL row enter the calculation: The first row in each partition (page_view) has prev_stepped_at=NULL. NULL - date is NULL, and NULL <= 7 is NULL (effectively false). Remove the NULL row with WHERE step='signup', or guard it with COALESCE(prev_stepped_at, stepped_at).

Practical note: Measure engagement speed with a time-bounded funnel

The share of users who sign up within 7 days is a measure of the funnel's temporal quality. Even if signup volume grows, an ad has little immediate effect if everyone arrives 30 days later. LAG-based step intervals also support comparing engagement speed by marketing channel and device. Applied to session logs, the days from the last login to today can directly become a churn-signal feature.

Model answers and explanations are for members

Read every model answer, explanation and table-transition visualization across all 48 advanced sets (240 questions)
One-time purchase — no subscription. Future advanced sets are included
Background, problem and expected output stay free

Unlock for $20 USD

Payments are securely processed by Stripe — your card details never touch this site.

You are charged in US dollars. If your card is issued in another currency, your bank’s exchange rate and any foreign transaction fee apply.

Already purchased? Sign in on your account page.

QUESTION 4

UNNEST(ARRAY[]) + CROSS JOIN — Generate a Multi-Month Retention Matrix in One Query

UNNESTCROSS JOINRetention analysisMulti-period

Background ▼

In basic Q2, we calculated retention for only one following month with INTERVAL '1 month'. Combining UNNEST(ARRAY[1,2,3]) with CROSS JOIN generates Month 1–3 retention for all periods in one query, without a loop.

-- Expand an array into rows with UNNEST
SELECT UNNEST(ARRAY[1, 2, 3]) AS offset_month
-- → 3 rows: offset=1, offset=2, offset=3

-- Generate a dynamic INTERVAL
cohort_month + (offset_month || ' month')::interval
-- offset=1 → cohort_month + 1 month → target month

CROSS JOIN generates the Cartesian product (all combinations): 2 cohorts × 3 offsets = 6 cohort-period combinations. Because every cohort gets a row for every period, an absent active month at offset=3 still leaves a retained=0 row rather than deleting the row. LEFT JOIN + GROUP BY covers the zero-match case.

Problem ▼

From the users and login_events tables, calculate a cohort-based Month 1–3 retention-rate matrix. Use UNNEST to generate three rows for offset=1,2,3, CROSS JOIN to expand every cohort×offset combination, and LEFT JOIN to attach active months. Output columns: cohort_month, offset_month, cohort_size, retained, retention_pct (ascending cohort_month, then offset_month).

Tables used

► users (6 rows)

user_id	registered_at
1	2024-01-10
2	2024-01-15
3	2024-01-22
4	2024-02-05
5	2024-02-14
6	2024-02-20

► login_events (12 rows)

user_id	event_date
1	2024-01-12
2	2024-01-18
3	2024-01-25
1	2024-02-05
3	2024-02-10
4	2024-02-07
5	2024-02-14
6	2024-02-22
1	2024-03-05
4	2024-03-08
6	2024-03-15
4	2024-04-03

✱ January cohort: M1=user1,3 / M2=user1 / M3=none. February cohort: M1=user4,6 / M2=user4 / M3=none.

Expected Output ▼

Expected output (ascending cohort_month, then offset_month):

cohort_month	offset_month	cohort_size	retained	retention_pct
2024-01-01	1	3	2	66.7
2024-01-01	2	3	1	33.3
2024-01-01	3	3	0	0.0
2024-02-01	1	3	2	66.7
2024-02-01	2	3	1	33.3
2024-02-01	3	3	0	0.0

Both cohorts have retained=0 → 0.0% at offset=3. CROSS JOIN keeps the zero-match rows from disappearing.

Model Answer ▼

WITH cohorts AS (
  SELECT
    user_id,
    DATE_TRUNC('month', registered_at)::date AS cohort_month
  FROM users
),
activity AS (
  SELECT DISTINCT
    user_id,
    DATE_TRUNC('month', event_date)::date   AS active_month
  FROM login_events
),
offsets(offset_month) AS (            -- Generate Month 1, 2, and 3 offset values
  SELECT UNNEST(ARRAY[1, 2, 3])
)
SELECT
  c.cohort_month,
  o.offset_month,
  COUNT(DISTINCT c.user_id)  AS cohort_size,
  COUNT(DISTINCT a.user_id)  AS retained,
  ROUND(
    COUNT(DISTINCT a.user_id) * 100.0 /
    NULLIF(COUNT(DISTINCT c.user_id), 0), 1  -- Guard against division by zero
  ) AS retention_pct
FROM       cohorts  c
CROSS JOIN offsets  o                  -- All combinations (2 cohorts × 3 offsets)
LEFT JOIN  activity a
  ON  a.user_id     = c.user_id
  AND a.active_month = c.cohort_month + (o.offset_month || ' month')::interval
GROUP BY c.cohort_month, o.offset_month
ORDER BY c.cohort_month, o.offset_month;

/*
  Logical evaluation order:
  1. Define CTE cohorts       → Normalize values
  2. Define CTE activity      → Remove duplicates
  3. Define CTE offsets       → Evaluate the derived table
  4. CROSS JOIN offsets o     → Join (Cartesian product)
  5. LEFT JOIN activity a     → Join (preserve every left-side row)
  6. GROUP BY                 → Group rows
  7. SELECT                   → Evaluate aggregates (cohort_size, retained)
  8. ROUND(...)               → Format the value
  9. ORDER BY                 → Sort and output
*/

Explanation (table transitions & key points) ▼

WITH cohorts AS (
  SELECT
    user_id,
    DATE_TRUNC('month', registered_at)::date AS cohort_month
  FROM users
),
activity AS (
  SELECT DISTINCT
    user_id,
    DATE_TRUNC('month', event_date)::date AS active_month
  FROM login_events
),
offsets(offset_month) AS (
  SELECT UNNEST(ARRAY[1, 2, 3])
)
SELECT
  c.cohort_month, o.offset_month,
  COUNT(DISTINCT c.user_id) AS cohort_size,
  COUNT(DISTINCT a.user_id) AS retained,
  ROUND(COUNT(DISTINCT a.user_id)*100.0/NULLIF(COUNT(DISTINCT c.user_id),0),1) AS retention_pct
FROM       cohorts  c
CROSS JOIN offsets  o
LEFT JOIN  activity a
  ON  a.user_id     = c.user_id
  AND a.active_month = c.cohort_month + (o.offset_month || ' month')::interval
GROUP BY c.cohort_month, o.offset_month
ORDER BY c.cohort_month, o.offset_month;

LEGEND ▼

Rows read / loaded

① CTE cohorts — Define registration-month cohorts ▼

DATE_TRUNC('month', registered_at)::date AS cohort_monthCalculate each user's registration month (the first day of the month) from users. Two cohorts form: January registrants user1, user2, and user3, and February registrants user4, user5, and user6 (6 users total).

1 / 7

user_id	registered_at	► cohort_month
1	2024-01-10	2024-01-01
2	2024-01-15	2024-01-01
3	2024-01-22	2024-01-01
4	2024-02-05	2024-02-01
5	2024-02-14	2024-02-01
6	2024-02-20	2024-02-01

cohorts CTE: 6 rows (2 cohorts)

LEARNING POINTS

UNNEST converts any chosen array into rows: UNNEST(ARRAY[1,2,3]) generates 3 rows. It is more flexible than GENERATE_SERIES because you can specify non-contiguous offsets such as UNNEST(ARRAY[1,3,6,12]) to calculate retention only for Months 1, 3, 6, and 12.

CROSS JOIN prevents zero-match rows from disappearing: If no active user exists at offset=3, a LEFT JOIN alone has no matching row for that combination and it disappears. Expanding all combinations with CROSS JOIN first and then applying LEFT JOIN ensures that the retained=0 row remains in the result. Building the skeleton first is a standard pattern for retention-matrix generation.

Generalize periods with a dynamic INTERVAL: (o.offset_month || ' month')::interval concatenates an integer with a string and converts it to interval. offset=1 becomes '1 month'::interval, while offset=3 becomes '3 month'::interval. Generating INTERVAL values from strings is useful for many time-offset calculations.

ANTI-PATTERNS

Separate UNION ALL queries for each month are expensive to maintain: Combining Month 1, 2, and 3 with separate SELECT + UNION ALL blocks requires a query edit every time you add a period. With UNNEST + CROSS JOIN, add a value to the ARRAY and the analysis expands.

Omit the NULLIF guard and you risk division by zero: If a group with cohort size 0 appears (for example, in a test period), COUNT / 0 raises a runtime error. Use NULLIF(COUNT(DISTINCT c.user_id), 0) to return NULL for a zero denominator, so ROUND(NULL, 1) = NULL is handled safely.

Practical note: Turn the retention matrix into a heatmap

Pass this query's result to a BI tool such as Looker, Tableau, or Metabase to easily create a retention heatmap with offset month on the x-axis, cohort month on the y-axis, and retention_pct as the cell value. Comparing darker (higher-retention) cohorts can reveal long-term campaign effects, such as a cohort from the month Campaign X launched having noticeably higher retention from Month 2 onward. Changing the UNNEST ARRAY also supports weekly retention such as Week 1–12.

Model answers and explanations are for members

Read every model answer, explanation and table-transition visualization across all 48 advanced sets (240 questions)
One-time purchase — no subscription. Future advanced sets are included
Background, problem and expected output stay free

Unlock for $20 USD

Payments are securely processed by Stripe — your card details never touch this site.

You are charged in US dollars. If your card is issued in another currency, your bank’s exchange rate and any foreign transaction fee apply.

Already purchased? Sign in on your account page.

QUESTION 5

Recursive CTE (WITH RECURSIVE) — Generate a Calendar and Fill Missing DAU Dates with 0

WITH RECURSIVECOALESCEDaily DAU trendFill missing dates

Background ▼

WITH RECURSIVE is a CTE that references itself. Its structure consists of an “anchor” (initial row) + UNION ALL + a recursive step. Always specify a termination condition (WHERE), or the recursion becomes an infinite loop.

WITH RECURSIVE date_series AS (
  SELECT '2024-01-01'::date AS dt    -- ① Anchor (starting point)
  UNION ALL
  SELECT (dt + INTERVAL '1 day')::date  -- ② Recursive step
  FROM   date_series
  WHERE  dt < '2024-01-07'              -- ③ Termination condition (required)
)

How a recursive CTE works: Starting from the anchor row dt=2024-01-01, it keeps adding the next row while WHERE dt < '2024-01-07' is true. The final addition (dt=2024-01-07) occurs from dt=2024-01-06, and recursion stops when dt=2024-01-07 < 2024-01-07 becomes false. Join the generated date column to real data with LEFT JOIN + COALESCE and fill missing dates with 0.

Problem ▼

From the user_sessions table, output the daily DAU trend from 2024-01-01 through 2024-01-07, filling dates with no sessions with 0. Generate a date column (date_series) with WITH RECURSIVE, join the real data with LEFT JOIN, and convert NULL to 0 with COALESCE. Output columns: dt, dau (ascending dt).

Tables used

► user_sessions (8 rows)

user_id	session_date
1	2024-01-01
2	2024-01-01
1	2024-01-03
2	2024-01-03
1	2024-01-05
3	2024-01-05
1	2024-01-07
2	2024-01-07

✱ 01-02, 01-04, and 01-06 have no sessions → fill them with 0

Expected Output ▼

Expected output (ascending dt):

dt	dau
2024-01-01	2
2024-01-02	0
2024-01-03	2
2024-01-04	0
2024-01-05	2
2024-01-06	0
2024-01-07	2

The recursive CTE generates a seven-day calendar, and dates without real data are filled with COALESCE(NULL, 0) = 0.

Model Answer ▼

WITH RECURSIVE date_series AS (
  SELECT '2024-01-01'::date AS dt  -- Anchor: aggregation start date
  UNION ALL
  SELECT (dt + INTERVAL '1 day')::date
  FROM   date_series
  WHERE  dt < '2024-01-07'              -- Termination condition: through 01-07
),
daily_dau AS (
  SELECT
    session_date,
    COUNT(DISTINCT user_id) AS dau
  FROM   user_sessions
  GROUP BY session_date
)
SELECT
  ds.dt,
  COALESCE(d.dau, 0) AS dau          -- Fill NULL dates with 0
FROM      date_series ds
LEFT JOIN daily_dau   d ON d.session_date = ds.dt
ORDER BY  ds.dt;

/*
  Logical evaluation order:
  1. WITH RECURSIVE date_series
  2. CTE daily_dau
  3. FROM date_series ds
  4. COALESCE(d.dau, 0)
  5. ORDER BY ds.dt  → Ascending date order
  */

Explanation (table transitions & key points) ▼

WITH RECURSIVE date_series AS (
  SELECT '2024-01-01'::date AS dt
  UNION ALL
  SELECT (dt + INTERVAL '1 day')::date
  FROM   date_series
  WHERE  dt < '2024-01-07'
),
daily_dau AS (
  SELECT
    session_date,
    COUNT(DISTINCT user_id) AS dau
  FROM   user_sessions
  GROUP BY session_date
)
SELECT
  ds.dt,
  COALESCE(d.dau, 0) AS dau
FROM      date_series ds
LEFT JOIN daily_dau   d ON d.session_date = ds.dt
ORDER BY  ds.dt;

LEGEND ▼

Rows read / loaded

① Anchor — Generate the starting row ▼

SELECT '2024-01-01'::date AS dtA recursive CTE starts with an anchor (base case). The anchor is the first row that starts recursion; here it generates dt=2024-01-01. All later recursive steps use this row as their starting point.

1 / 6

dt	Status
2024-01-01	← Anchor row (initial value)

date_series: 1 row (anchor)

LEARNING POINTS

The three parts of a recursive CTE: anchor, recursive step, and termination condition: The anchor is the initial row (starting point), the recursive step uses the accumulated result to generate the next row, and the termination condition is the WHERE clause that prevents an infinite loop. Understanding this structure supports date generation, organizational-hierarchy expansion, and sequential processing.

Think in three stages: accumulated result, newly added row, and next row to generate: Each iteration adds a new row with UNION ALL to the previous UNION ALL result (the accumulated set). Follow the thought process “dt=01-03 exists now” → “01-03 < 01-07 ✓ → generate dt=01-04” → “01-04 is added to the accumulation.” Step through the visualizer to experience this flow.

Recursive CTE vs GENERATE_SERIES: PostgreSQL has the dedicated function GENERATE_SERIES('2024-01-01'::date, '2024-01-07'::date, '1 day'), which is more concise for date generation. Recursive CTEs are more general: the same concept applies to non-date sequences and tree expansion, including in databases such as BigQuery and DuckDB.

ANTI-PATTERNS

INNER JOIN makes missing dates disappear: With FROM date_series ds INNER JOIN daily_dau d ON ..., dates without sessions (01-02, 04, and 06) disappear from the result. Only a LEFT JOIN with the calendar as the left table both preserves every date and attaches real data correctly.

Forget the termination condition and recursion loops forever: Omitting WHERE dt < '2024-01-07' prevents recursion from stopping and eventually reaches PostgreSQL's default limit (max_recursion_depth=100), causing an error. Always write a termination condition together with every recursive CTE.

Practical note: Use a calendar table

In production, it is more efficient to create a persistent calendar table (dim_date) in advance than to generate date_series recursively every time. A dim_date table containing year, month, day, weekday, holiday flags, and business-day flags can be LEFT JOINed to simplify DAU filling, weekly aggregation, and business-day-based SLA calculations. A recursive CTE can generate dim_date or create a temporary ETL table. The idea of “using the calendar as a skeleton and LEFT JOINing real data” is a basic principle of data-warehouse design.

Model answers and explanations are for members

Read every model answer, explanation and table-transition visualization across all 48 advanced sets (240 questions)
One-time purchase — no subscription. Future advanced sets are included
Background, problem and expected output stay free

Unlock for $20 USD

Payments are securely processed by Stripe — your card details never touch this site.

You are charged in US dollars. If your card is issued in another currency, your bank’s exchange rate and any foreign transaction fee apply.

Already purchased? Sign in on your account page.