SQL KPI Analysis — Applied MoM Growth, NTILE, Funnel CVR

QUESTION 6

MoM Growth — Pull the Previous Month with LAG() and Calculate Month-over-Month Change

LAGWindow FunctionsMoM GrowthNULLIF

Background ▼

The month-over-month growth rate (MoM) of monthly revenue or MAU is an essential KPI for management reporting. It can be calculated only after placing the current month's value and the previous month's value on the same row. LAG() is a window function that pulls the value from N rows earlier into the current row after the rows have been ordered with ORDER BY, making it the standard tool for this job.

LAG(revenue)     OVER (ORDER BY month)   -- revenue from the previous row (default offset = 1)
LAG(revenue, 12) OVER (ORDER BY month)   -- 12 rows earlier = the same month last year (YoY)
LEAD(revenue)    OVER (ORDER BY month)   -- LEAD pulls the next row instead

Protect the growth-rate division with NULLIF: Growth rate = (current month − previous month) / previous month. If the previous month is 0, division by zero raises an error. Using NULLIF(prev, 0) turns a zero denominator into NULL and avoids the error. For the first month, no previous month exists, so LAG returns NULL and the growth rate naturally becomes NULL.

Problem ▼

From monthly_revenue, calculate each month's revenue, previous-month revenue, previous-month difference (mom_diff), and month-over-month growth rate (mom_pct, %). Return the columns month, revenue, prev_revenue, mom_diff, mom_pct in ascending month order, and round the growth rate to two decimal places.

Tables used

► monthly_revenue (6 rows)

month	revenue
2024-01	1000
2024-02	1200
2024-03	1100
2024-04	1500
2024-05	1500
2024-06	1800

Expected Output ▼

month	revenue	prev_revenue	mom_diff	mom_pct
2024-01	1000	NULL	NULL	NULL
2024-02	1200	1000	200	20.00
2024-03	1100	1200	-100	-8.33
2024-04	1500	1100	400	36.36
2024-05	1500	1500	0	0.00
2024-06	1800	1500	300	20.00

Model Answer ▼

WITH m AS (
  SELECT
    month,
    revenue,
    LAG(revenue) OVER (ORDER BY month) AS prev_revenue  -- pull the previous month's revenue
  FROM monthly_revenue
)
SELECT
  month,
  revenue,
  prev_revenue,
  revenue - prev_revenue AS mom_diff,                              -- difference from the previous month (absolute change)
  ROUND(
    (revenue - prev_revenue) * 100.0 / NULLIF(prev_revenue, 0), 2  -- avoid division by zero with NULLIF
  ) AS mom_pct                                                  -- month-over-month growth rate (%)
FROM m
ORDER BY month;

/*
  Execution order (SQL's logical evaluation order):
  1. FROM monthly_revenue                → read the rows
  2. LAG(revenue) OVER (ORDER BY month)  → attach the previous month's revenue
  3. Outer SELECT                         → calculate the difference and growth rate
  4. ROUND(..., 2)                        → round to two decimal places
  5. ORDER BY month                       → output in ascending month order
  */

Explanation (table transitions & key points) ▼

WITH m AS (
  SELECT month, revenue,
    LAG(revenue) OVER (ORDER BY month) AS prev_revenue
  FROM monthly_revenue
)
SELECT
  month, revenue, prev_revenue,
  revenue - prev_revenue AS mom_diff,
  ROUND(
    (revenue - prev_revenue) * 100.0 / NULLIF(prev_revenue, 0), 2
  ) AS mom_pct
FROM m
ORDER BY month;

LEGEND ▼

Rows read / loaded

① FROM monthly_revenue (6 rows) ▼

FROM monthly_revenueRead the monthly revenue. Raw values alone do not make it immediately clear whether revenue grew from the previous month, so the previous value must be placed beside the current one.

1 / 4

month	revenue
2024-01	1000
2024-02	1200
2024-03	1100
2024-04	1500
2024-05	1500
2024-06	1800

6 rows read

LEARNING POINTS

LAG pulls the value from N rows earlier onto the same row: LAG(revenue) OVER (ORDER BY month) provides the previous month's value, so the MoM calculation needs no self-join. The second argument sets the offset (LAG(revenue, 12) = the same month last year), and the third sets a default (LAG(revenue, 1, 0) = fill the first row with 0).

Calculate each series independently with PARTITION BY: For monthly data from multiple products, LAG(revenue) OVER (PARTITION BY product ORDER BY month) finds the previous month separately for each product. This prevents a previous value from being taken across a partition boundary.

Always prevent division by zero with NULLIF: In data where the previous month's value can be 0, NULLIF(prev, 0) is a safety device. Returning NULL for zero prevents the entire query from failing.

ANTI-PATTERNS

Build the previous month with a self-join: JOIN ... ON b.month = a.month - 1 is verbose and prone to misalignment across month boundaries or missing months. LAG accurately picks the previous row according to the ordering. However, with missing months, the “previous row” is not necessarily the “previous month” (→ fill the gaps with date spine).

Divide as integers and get zero: Dividing (revenue - prev) / prev as integers truncates the decimal and makes most results 0. Always promote to a fractional type with * 100.0 (or ::numeric) before dividing.

Practical column: Choosing between MoM, YoY, and CAGR

MoM (month-over-month) measures short-term momentum, while YoY (year-over-year, LAG(x, 12)) measures underlying strength after removing seasonality. In businesses with large month-to-month swings, looking only at MoM can lead to overreacting, so reading it alongside YoY is standard practice. For average growth over multiple periods, CAGR (compound annual growth rate) smooths the path and summarizes the trend's slope in a single number. One LAG pattern can express both MoM and YoY.

QUESTION 7

Customer Segmentation — Split Spending into Equal Quartiles with NTILE() and Create Tiers

NTILEPARTITION/ORDERSegmentationQuartilesCASE

Background ▼

“Top 25% customers” and “cutting spending into quartile-based tiers” are examples of segmentation by equal-sized groups, where NTILE(n) is useful. NTILE divides rows ordered by ORDER BY into n buckets that are as even as possible and assigns each row a bucket number (1–n).

NTILE(4) OVER (ORDER BY spend DESC)  -- split spending into four descending buckets: 1 = top 25%, 4 = bottom 25%
-- When the row count is not evenly divisible, the remainder is distributed one row at a time from the first bucket
--   Example: split 9 rows into 4 → 3,2,2,2 (the first bucket gets one extra row)

The decisive difference from RANK: RANK/DENSE_RANK rank by whether the values are equal. NTILE prioritizes making the row counts as even as possible, regardless of the size of the value gaps. As a result, two customers with very different spending can land in the same tier, while two near-ties can be split at a bucket boundary.

Problem ▼

From customer_spend, split customers into four quartiles in descending spending order and assign each tier a VIP / Gold / Silver / Bronze label. Return user_id, spend, quartile, segment in descending spend order.

Tables used

► customer_spend (9 rows)

user_id	spend
U1	100
U2	250
U3	400
U4	550
U5	700
U6	900
U7	1200
U8	1500
U9	1800

Expected Output ▼

user_id	spend	quartile	segment
U9	1800	1	VIP
U8	1500	1	VIP
U7	1200	1	VIP
U6	900	2	Gold
U5	700	2	Gold
U4	550	3	Silver
U3	400	3	Silver
U2	250	4	Bronze
U1	100	4	Bronze

Model Answer ▼

WITH tiered AS (
  SELECT
    user_id,
    spend,
    NTILE(4) OVER (ORDER BY spend DESC) AS quartile  -- split into four equal-sized buckets in descending spend order
  FROM customer_spend
)
SELECT
  user_id,
  spend,
  quartile,
  CASE quartile                          -- translate the tier number into a business label
    WHEN 1 THEN 'VIP'
    WHEN 2 THEN 'Gold'
    WHEN 3 THEN 'Silver'
    ELSE        'Bronze'
  END AS segment
FROM tiered
ORDER BY spend DESC;

/*
  Execution order (SQL's logical evaluation order):
  1. FROM customer_spend          → read the rows
  2. ORDER BY spend DESC (window) → sort by descending spend
  3. NTILE(4)                     → split into four buckets and assign tier numbers
  4. Outer CASE                   → convert tier numbers into labels
  5. ORDER BY spend DESC          → output in descending spend order
  */

Explanation (table transitions & key points) ▼

WITH tiered AS (
  SELECT user_id, spend,
    NTILE(4) OVER (ORDER BY spend DESC) AS quartile
  FROM customer_spend
)
SELECT user_id, spend, quartile,
  CASE quartile
    WHEN 1 THEN 'VIP'    WHEN 2 THEN 'Gold'   WHEN 3 THEN 'Silver' ELSE 'Bronze'
  END AS segment
FROM tiered
ORDER BY spend DESC;

LEGEND ▼

Rows read / loaded

① FROM customer_spend (9 rows) ▼

FROM customer_spendRead each customer's cumulative spending. The rows are still in user_id order and have no tier numbers yet.

1 / 5

user_id	spend
U1	100
U2	250
U3	400
U4	550
U5	700
U6	900
U7	1200
U8	1500
U9	1800

9 rows read

LEARNING POINTS

NTILE(n) makes row counts as even as possible: It splits rows into n equal-sized groups by count, not by the size of their values. A remainder is distributed one row at a time from the first bucket, so 9 rows ÷ 4 becomes 3,2,2,2. The same approach works for deciles with NTILE(10).

Wrap a window function in a CTE or subquery for reuse: Because of the evaluation order, the result of NTILE cannot be used directly in WHERE or in another expression in the same SELECT. Wrapping it once as WITH tiered AS (...) SELECT ... FROM tiered enables CASE conversion and filters such as WHERE quartile = 1.

Use PARTITION BY for quartiles within each group: NTILE(4) OVER (PARTITION BY region ORDER BY spend DESC) calculates quartiles separately for each region. This is useful when tiers should be relative within a segment rather than uniform across the entire company.

ANTI-PATTERNS

Confuse it with splitting a monetary range into four: NTILE equalizes the number of rows, not the monetary range. For value ranges such as “0–500, 500–1000, ...”, use CASE or width_bucket(). Make clear whether the requirement is “top 25% by count” or “high-spend tier by amount.”

Forget the ORDER BY: Without ORDER BY, the contents of NTILE buckets are non-deterministic and the tiers can change from run to run. If equal values can cross a boundary, add a tie-breaker such as ORDER BY spend DESC, user_id for stable results.

Practical column: RFM analysis and decile scoring

In the standard RFM framework (Recency, Frequency, Monetary), each metric can be scored on five levels with NTILE(5), then combined into a score such as “555 = best customer.” Because NTILE splits by count even when the distribution is skewed, it is relatively robust to outliers and easy to compare. When you need the true median or percentile boundary, use an ordered-set aggregate such as PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY spend) instead for greater precision.

QUESTION 8

Funnel Analysis — Calculate Step-Level CVR with COUNT(DISTINCT) + FIRST_VALUE/LAG

COUNT(DISTINCT)FIRST_VALUEFunnel/CVRLAGCASE Ordering

Background ▼

Funnel analysis is the starting point for improving conversion: where do users drop off as they move from “view → cart → purchase”? Count the users who reach each step and calculate the conversion rate (CVR) between steps. CVR has two definitions; placing both in one query provides a richer view.

-- ① Overall CVR: the pass-through rate using the entry step as the denominator
FIRST_VALUE(users) OVER (ORDER BY step_no)   -- fix the first value = the entry count
-- ② Step CVR: the conversion rate using the immediately previous step as the denominator
LAG(users) OVER (ORDER BY step_no)           -- the count at the previous step

Give the steps an explicit order: Because 'view'/'cart'/'purchase' are strings, ordering them directly produces alphabetical order. Use CASE step WHEN 'view' THEN 1 ... to assign a numeric step_no that represents the funnel order. In a funnel, order is everything.

Problem ▼

From events (behavior logs), calculate the number of unique users reaching each step, along with overall CVR and step CVR. Return step, users, overall_cvr, step_cvr in funnel order (view→cart→purchase), with CVRs as percentages rounded to two decimal places.

Tables used

► events (12 rows)

user_id	step
U1	view
U1	cart
U1	purchase
U2	view
U2	cart
U3	view
U4	view
U4	cart
U4	purchase
U5	view
U6	view
U6	cart

Expected Output ▼

step	users	overall_cvr	step_cvr
view	6	100.00	NULL
cart	4	66.67	66.67
purchase	2	33.33	50.00

Model Answer ▼

WITH step_users AS (
  SELECT
    step,
    COUNT(DISTINCT user_id) AS users        -- unique users reaching each step
  FROM events
  GROUP BY step
),
ordered AS (
  SELECT
    step, users,
    CASE step WHEN 'view' THEN 1
                WHEN 'cart' THEN 2
                WHEN 'purchase' THEN 3 END AS step_no  -- turn funnel order into a number
  FROM step_users
)
SELECT
  step,
  users,
  ROUND(users * 100.0 / FIRST_VALUE(users) OVER (ORDER BY step_no), 2) AS overall_cvr,  -- versus the entry step
  ROUND(users * 100.0 / LAG(users)        OVER (ORDER BY step_no), 2) AS step_cvr     -- versus the previous step
FROM ordered
ORDER BY step_no;

/*
  Execution order (SQL's logical evaluation order):
  1. FROM events                              → read the rows
  2. GROUP BY step + COUNT(DISTINCT user_id)  → aggregate unique users by step
  3. Assign step_no with CASE                  → fix the order
  4. FIRST_VALUE / LAG OVER (...)              → attach entry and previous-step counts
  5. ORDER BY step_no                          → output in funnel order
  */

Explanation (table transitions & key points) ▼

WITH step_users AS (
  SELECT step,
    COUNT(DISTINCT user_id) AS users
  FROM events
  GROUP BY step
),
ordered AS (
  SELECT step, users,
    CASE step WHEN 'view' THEN 1 WHEN 'cart' THEN 2 WHEN 'purchase' THEN 3 END AS step_no
  FROM step_users
)
SELECT step, users,
  ROUND(users * 100.0 / FIRST_VALUE(users) OVER (ORDER BY step_no), 2) AS overall_cvr,
  ROUND(users * 100.0 / LAG(users) OVER (ORDER BY step_no), 2) AS step_cvr
FROM ordered
ORDER BY step_no;

LEGEND ▼

Rows read / loaded

① FROM events (12 rows) ▼

FROM eventsThese are raw behavior logs. One user can have rows for multiple steps, so DISTINCT is required later to avoid counting the same user twice.

1 / 5

user_id	step
U1	view
U1	cart
U1	purchase
U2	view
U2	cart
U3	view
U4	view
U4	cart
U4	purchase
U5	view
U6	view
U6	cart

12 rows read

LEARNING POINTS

Count reached users with COUNT(DISTINCT user_id): Even if one user reaches the same step multiple times, the funnel counts that user once. The rule is to use COUNT(DISTINCT user_id) (people), not COUNT(*) (rows).

FIRST_VALUE means entry rate; LAG means previous-step rate: Even for the same “CVR,” the denominator changes its meaning. FIRST_VALUE fixes the first value (the entry) to show overall passage, while LAG shows conversion from the immediately preceding step. Showing both reveals both “where do users drop most?” (step CVR) and “what fraction ultimately remains?” (overall CVR).

Order string steps with CASE: If step names are strings, ORDER BY sorts them alphabetically and breaks the funnel direction. Convert the order to numbers with CASE ... END AS step_no before sorting. If a master table has a sort_order column, use that instead.

ANTI-PATTERNS

Ignore whether users reached earlier steps: The logs in this question are clean (purchasers always viewed), but real data can contain a purchase row without a view row. Strictly, flag whether each user reached step X or later with MAX(CASE WHEN step_no >= k THEN 1 END) at the user level, then aggregate those flags.

Confuse step CVR with overall CVR: “Purchase CVR 50%” and “purchase CVR 33%” differ because their denominators are cart and view. Always state which step is the denominator, or estimates of improvement impact will be wrong.

Practical column: Read both funnel rates and counts

To find a bottleneck, look for the step with the largest drop in step CVR (versus the previous step). To estimate improvement impact, look at overall CVR and the denominator (users). A low CVR with a small denominator may be less valuable to improve, while a moderate CVR with a large denominator can produce a huge impact from a one-point improvement. Adding the elapsed time between steps (for example, the median time from cart to purchase) by comparing timestamps with LAG reveals not only the volume of drop-off but also where the process slows down.

QUESTION 9

Cohort Retention — Use Conditional Aggregation by Month Offset to Build a Retention Matrix

CohortsFILTER AggregationRetentionPivotNULLIF

Background ▼

Cohort analysis groups users by “when they were acquired (their registration month)” and tracks retention by elapsed month afterward. It enables cross-generation comparisons of acquisition campaigns and product improvements. The two keys are calculating the month offset and expanding it into columns with conditional aggregation (a pivot).

-- month offset = active month − cohort month (difference in whole months)
(EXTRACT(YEAR FROM (active_month || '-01')::date) - EXTRACT(YEAR FROM (cohort_month || '-01')::date)) * 12
  + (EXTRACT(MONTH FROM (active_month || '-01')::date) - EXTRACT(MONTH FROM (cohort_month || '-01')::date))

-- Pivot: place active-user counts for each elapsed month side by side (deduplicated)
COUNT(DISTINCT user_id) FILTER (WHERE month_offset = 1)   -- active users one month later

Retention rate = active users at month N ÷ cohort size at month 0: The denominator for each cohort is the number of users in the registration month (offset=0). Retention is the share of that cohort still active at each elapsed month. Use NULLIF(m0, 0) to prevent division by zero. Churn rate (basic set) is the inverse relationship (1 − retention).

Problem ▼

From activity (registration-month cohort × active month), calculate each cohort's initial size and one-month and two-month retention rates. Return cohort_month, cohort_size, ret_m1, ret_m2 in ascending cohort_month order, with rates as percentages rounded to one decimal place.

Tables used

► activity (11 rows)

user_id	cohort_month	active_month
U1	2024-01	2024-01
U1	2024-01	2024-02
U1	2024-01	2024-03
U2	2024-01	2024-01
U2	2024-01	2024-02
U3	2024-01	2024-01
U4	2024-02	2024-02
U4	2024-02	2024-03
U4	2024-02	2024-04
U5	2024-02	2024-02
U5	2024-02	2024-03

Expected Output ▼

cohort_month	cohort_size	ret_m1	ret_m2
2024-01	3	66.7	33.3
2024-02	2	100.0	50.0

Model Answer ▼

WITH base AS (
  SELECT
    cohort_month, user_id,
    (EXTRACT(YEAR FROM (active_month || '-01')::date) - EXTRACT(YEAR FROM (cohort_month || '-01')::date)) * 12
      + (EXTRACT(MONTH FROM (active_month || '-01')::date) - EXTRACT(MONTH FROM (cohort_month || '-01')::date)) AS month_offset  -- elapsed month count
  FROM activity
),
matrix AS (
  SELECT
    cohort_month,
    COUNT(DISTINCT user_id) FILTER (WHERE month_offset = 0) AS m0,  -- initial month = denominator
    COUNT(DISTINCT user_id) FILTER (WHERE month_offset = 1) AS m1,  -- active one month later
    COUNT(DISTINCT user_id) FILTER (WHERE month_offset = 2) AS m2   -- active two months later
  FROM base
  GROUP BY cohort_month
)
SELECT
  cohort_month,
  m0 AS cohort_size,
  ROUND(m1 * 100.0 / NULLIF(m0, 0), 1) AS ret_m1,
  ROUND(m2 * 100.0 / NULLIF(m0, 0), 1) AS ret_m2
FROM matrix
ORDER BY cohort_month;

/*
  Execution order (SQL's logical evaluation order):
  1. FROM activity                           → read the rows
  2. base: calculate month_offset             → active month − registration month
  3. matrix: GROUP BY cohort_month + FILTER   → pivot elapsed months
  4. Outer SELECT                             → calculate retention against m0
  5. ORDER BY cohort_month                    → output from older to newer cohorts
  */

Explanation (table transitions & key points) ▼

WITH base AS (
  SELECT cohort_month, user_id,
    (EXTRACT(YEAR FROM (active_month || '-01')::date) - EXTRACT(YEAR FROM (cohort_month || '-01')::date)) * 12
      + (EXTRACT(MONTH FROM (active_month || '-01')::date) - EXTRACT(MONTH FROM (cohort_month || '-01')::date)) AS month_offset
  FROM activity
),
matrix AS (
  SELECT cohort_month,
    COUNT(DISTINCT user_id) FILTER (WHERE month_offset = 0) AS m0,
    COUNT(DISTINCT user_id) FILTER (WHERE month_offset = 1) AS m1,
    COUNT(DISTINCT user_id) FILTER (WHERE month_offset = 2) AS m2
  FROM base GROUP BY cohort_month
)
SELECT cohort_month,
  m0 AS cohort_size,
  ROUND(m1 * 100.0 / NULLIF(m0, 0), 1) AS ret_m1,
  ROUND(m2 * 100.0 / NULLIF(m0, 0), 1) AS ret_m2
FROM matrix
ORDER BY cohort_month;

LEGEND ▼

Rows read / loaded

① FROM activity (11 rows) ▼

FROM activityThese records show who registered in which cohort and was active in which month. The January cohort is U1,U2,U3; the February cohort is U4,U5. Re-group the records by elapsed month.

1 / 5

user_id	cohort_month	active_month
U1	2024-01	2024-01
U1	2024-01	2024-02
U1	2024-01	2024-03
U2	2024-01	2024-01
U2	2024-01	2024-02
U3	2024-01	2024-01
U4	2024-02	2024-02
U4	2024-02	2024-03
U4	2024-02	2024-04
U5	2024-02	2024-02
U5	2024-02	2024-03

11 rows read

LEARNING POINTS

The cohort-analysis framework is “month offset × conditional aggregation”: Group users by registration month, use month_offset as the horizontal axis, and pivot active-user counts with COUNT(DISTINCT) FILTER (WHERE month_offset = k). This applies the FILTER technique from the basic Q9 to a pivot.

Always fix the denominator at the initial month (m0): Retention is a proportion of the initial users in the same cohort. Recalculating the denominator at each elapsed month changes the meaning. Use mN / m0 consistently and prevent division by zero with NULLIF(m0, 0).

Prevent double-counting multiple activities in one month with DISTINCT: If one user is active multiple times in a month, that is still one retained user. Count unique users with COUNT(DISTINCT user_id); plain COUNT(*) counts activity events and breaks the rates.

ANTI-PATTERNS

Misread months outside the observation window as 0%: Newer cohorts have fewer observable elapsed months, and a month that has not arrived is unobserved (NULL), not 0%. Both cohorts in this question are observed through m2, but in practice include only observable rates with WHERE current_month >= cohort_month + k (which is why cohort matrices are triangular).

Leave the definition of “active” vague: Does one login count as active, or does a user become active only after a payment? The definition can move the numbers substantially. Fix the rule for which rows enter activity (the definition of active) within the team before aggregating.

Practical column: How to read a retention curve

Retention curves usually fall sharply at first and then flatten. The plateau is the product's “stickiness” and a higher plateau supports higher LTV. Place cohorts in rows and check whether newer cohorts are improving in early retention—a standard way to validate onboarding initiatives. Keep the relationship retention + churn (basic Q10) = 100% in mind and monitor both persistence and attrition.

QUESTION 10

Recursive CTE — Generate a Date Spine and Build a Continuous Time Series with Missing Days Filled as 0

WITH RECURSIVEUNION ALLDate SpineLEFT JOINCOALESCE

Background ▼

Daily sales and DAU often have no row at all on days without activity. If you feed this data directly into a line chart or moving average (basic set), missing days are compressed and both the chart and calculations shift. The solution is to generate a continuous date skeleton (a date spine), left join the actuals onto it, and fill missing days with 0. A recursive CTE is the classic way to generate the skeleton.

WITH RECURSIVE calendar AS (
  SELECT DATE '2024-03-01' AS d        -- ① anchor: one starting row (non-recursive term)
  UNION ALL                              -- ② stack the results vertically
  SELECT d + 1 FROM calendar          -- ③ recursive term: reference itself and add one day
  WHERE d < DATE '2024-03-05'           -- ④ stop condition: stop when the endpoint is reached
)

A recursive CTE repeats “current result → add one row → reference itself again”: The anchor creates one starting row, and the recursive term uses the row generated immediately before to create the next row. Without a WHERE stop condition, or with a loose one, the recursion loops forever and stops with a database recursion-limit error. Follow this build-up one step at a time in the visualization.

Problem ▼

Turn the sparse daily_sales data into a continuous time series where all five days from 2024-03-01 to 2024-03-05 appear and days without activity have sales 0. Return event_date, sales in ascending date order. Generate the date spine with a recursive CTE and fill it with LEFT JOIN + COALESCE.

Tables used

► daily_sales (3 sparse rows)

event_date	sales
2024-03-01	100
2024-03-03	150
2024-03-04	80

Expected Output ▼

event_date	sales
2024-03-01	100
2024-03-02	0
2024-03-03	150
2024-03-04	80
2024-03-05	0

Model Answer ▼

WITH RECURSIVE calendar AS (
  SELECT DATE '2024-03-01' AS d            -- anchor: one starting row
  UNION ALL
  SELECT d + 1                              -- recursive term: add one day to the previous date
  FROM calendar
  WHERE d < DATE '2024-03-05'               -- stop condition: continue only while before the endpoint
)
SELECT
  c.d AS event_date,
  COALESCE(s.sales, 0) AS sales             -- fill missing dates (NULL) with 0
FROM calendar c
LEFT JOIN daily_sales s ON s.event_date = c.d  -- left join actuals onto the spine
ORDER BY c.d;

/*
  Execution order (SQL's logical evaluation order):
  1. Recursive CTE calendar       → expand the dates one day at a time
  2. LEFT JOIN daily_sales         → join actuals to each date (missing = NULL)
  3. COALESCE(s.sales, 0)          → fill NULL with 0
  4. ORDER BY c.d                  → output in ascending date order
  */

Explanation (table transitions & key points) ▼

WITH RECURSIVE calendar AS (
  SELECT DATE '2024-03-01' AS d
  UNION ALL
  SELECT d + 1
  FROM calendar
  WHERE d < DATE '2024-03-05'
)
SELECT
  c.d AS event_date,
  COALESCE(s.sales, 0) AS sales
FROM calendar c
LEFT JOIN daily_sales s ON s.event_date = c.d
ORDER BY c.d;

LEGEND ▼

Columns / keys under evaluation

Excluded / hidden data

① Anchor — generate one starting row ▼

SELECT DATE '2024-03-01' AS dThis is the starting point of the recursion. calendar initially contains only the row for 03-01 (the current cumulative result). The recursive term then uses this row to create the next date.

1 / 7

calendar.d	State / WHERE d < '2024-03-05'
2024-03-01	● anchor (added now)
2024-03-02	next → (03-01 < 03-05 = true)

1 cumulative row

LEARNING POINTS

A recursive CTE is a three-part set: anchor + recursive term + stop condition: The anchor (non-recursive term) creates the starting point, the recursive term connected with UNION ALL references the immediately previous result to generate the next row, and the WHERE stop condition decides when to stop. Beyond date spines, the same pattern generates sequences, expands organization hierarchies, and traverses bills of materials (BOMs)—in short, repeated expansion.

Fill missing dates with “spine LEFT JOIN actuals → COALESCE”: Put the continuous date spine on the left and LEFT JOIN the actuals. An unmatched date has NULL on the right, so COALESCE(sales, 0) fills it with 0. The time series becomes gap-free, allowing the moving average from the basic Q6 and the previous-month LAG in practical Q6 to use the correct adjacency.

Use UNION ALL, not UNION: Recursive terms conventionally use UNION ALL. UNION performs duplicate elimination on every iteration, which is slower and unnecessary when each generated date is unique. ALL is correct for both performance and intent.

ANTI-PATTERNS

Omit the stop condition and create an infinite loop: If the WHERE stop condition is missing or unreachable, recursion never ends and hits the database recursion limit. Always state when and why it stops, and add a row-limit guard such as WHERE d < endpoint.

Remove missing dates with INNER JOIN: An INNER JOIN drops dates with no actuals and destroys the continuity you worked to create. Always use LEFT JOIN. If you also forget COALESCE, NULL remains and SUM/AVG/moving averages skip that day, producing incorrect values.

Practical column: Date dimensions and generate_series

In PostgreSQL, generate_series(DATE '2024-03-01', DATE '2024-03-05', INTERVAL '1 day') writes the same spine in one line and is convenient in practice. A recursive CTE, on the other hand, is portable standard SQL and a general technique for sequences, hierarchies, and path exploration, so understanding the mechanism is valuable. In a data warehouse, the standard approach is to maintain a permanent date-dimension table with holidays, weekdays, quarters, and other attributes, then left join KPIs to it. Think of spine generation as the small-scale version of that pattern.