SQL Batch Processing — Applied Running Totals and Pivots

QUESTION 6

Multiple CTEs — Classify Users into Segments with RFM Scores

Multiple CTEsWITHRFMSegment Classification

Background ▼

Listing multiple CTEs separated by commas creates a pipeline in which each later CTE can reference an earlier one. This organizes complex batch processing as a sequence of named steps.

WITH
step1 AS (
  SELECT user_id, COUNT(*) AS order_count
  FROM orders
  GROUP BY user_id
),
step2 AS (               -- can reference step1
  SELECT *,
    CASE WHEN order_count >= 5 THEN 'Loyal' ELSE 'General' END AS segment
  FROM step1
)
SELECT * FROM step2;

What is RFM:a method for scoring users on three metrics: Recency (most recent purchase date), Frequency (purchase frequency), and Monetary (purchase amount). It is extremely common in marketing batch processing.

Problem ▼

From the orders table below, calculate each user’s R (days since the most recent purchase), F (purchase count), and M (total purchase amount), assign each metric a score from 1 to 3, and classify the final segment.

Reference date: 2024-05-01. R: within 30 days = 3, within 60 days = 2, otherwise = 1. F: 3 or more purchases = 3, 2 purchases = 2, 1 purchase = 1. M: at least 100000 = 3, at least 50000 = 2, otherwise = 1.
Classify the final segment as “VIP” when total_score is at least 8, “Loyal” when it is at least 6, and “General” otherwise.

Source table

▸ orders

order_id	user_id	order_date	amount
1	U01	2024-04-20	50000
2	U01	2024-04-28	70000
3	U01	2024-04-30	30000
4	U02	2024-03-10	120000
5	U02	2024-03-25	40000
6	U03	2024-01-15	30000
7	U04	2024-04-25	200000
8	U04	2024-04-29	150000

Expected Output ▼

user_id	r_score	f_score	m_score	total_score	segment
U01	3	3	3	9	VIP
U04	3	2	3	8	VIP
U02	2	2	3	7	Loyal
U03	1	1	1	3	General

Model Answer ▼

-- Define multiple CTEs (comma-separated)
WITH
rfm_raw AS (                                                   -- ① raw per-user aggregation (calculate the source data for R/F/M)
  SELECT
    user_id,
    '2024-05-01'::date - MAX(order_date) AS recency_days,      -- R: reference date − most recent purchase date
    COUNT(*)                             AS frequency,         -- F: purchase count
    SUM(amount)                          AS monetary           -- M: total purchase amount
  FROM orders
  GROUP BY user_id
),
rfm_scored AS (                                                -- ② convert each metric to a score from 1 to 3
  SELECT
    user_id,
    CASE WHEN recency_days <= 30 THEN 3
         WHEN recency_days <= 60 THEN 2 ELSE 1 END AS r_score,   -- daysis fewerhigher score
    CASE WHEN frequency >= 3 THEN 3
         WHEN frequency =  2 THEN 2 ELSE 1 END AS f_score,     -- purchase countis morehigher score
    CASE WHEN monetary >= 100000 THEN 3
         WHEN monetary >=  50000 THEN 2 ELSE 1 END AS m_score  -- amountis higher score
  FROM rfm_raw                                                 -- reference the previous CTE
)

-- ③ classify segments by total score
SELECT
  user_id,
  r_score, f_score, m_score,
  r_score + f_score + m_score AS total_score,
  CASE WHEN r_score + f_score + m_score >= 8 THEN 'VIP'
       WHEN r_score + f_score + m_score >= 6 THEN 'Loyal'
       ELSE 'General' END AS segment
FROM rfm_scored
ORDER BY total_score DESC;

/*
  【Evaluation order】
  1. rfm_raw    → define the CTE(aggregation)
  2. rfm_scored → define the CTE(scoring)
  3. outer query       → evaluate the outer query(sort and output)
*/

Explanation (table transitions & key points) ▼

WITH

rfm_raw AS (
  SELECT
    user_id,
    '2024-05-01'::date - MAX(order_date) AS recency_days,
    COUNT(*) AS frequency,
    SUM(amount) AS monetary
  FROM orders
  GROUP BY user_id
),

rfm_scored AS (
  SELECT
    user_id,
    CASE WHEN recency_days <= 30 THEN 3
         WHEN recency_days <= 60 THEN 2 ELSE 1 END AS r_score,
    CASE WHEN frequency >= 3 THEN 3
         WHEN frequency =  2 THEN 2 ELSE 1 END AS f_score,
    CASE WHEN monetary >= 100000 THEN 3
         WHEN monetary >=  50000 THEN 2 ELSE 1 END AS m_score
  FROM rfm_raw
)

SELECT
  user_id,
  r_score, f_score, m_score,
  r_score + f_score + m_score AS total_score,
  CASE WHEN r_score + f_score + m_score >= 8 THEN 'VIP'
       WHEN r_score + f_score + m_score >= 6 THEN 'Loyal'
       ELSE 'General' END AS segment
FROM rfm_scored
ORDER BY total_score DESC;

LEGEND ▼

Rows read / loaded

① FROM ▼

FROM ordersRead the orders table (8 rows). The rows are grouped by user_id in the next step.

1 / 4

order_id	user_id	order_date	amount
1	U01	2024-04-20	50,000
2	U01	2024-04-28	70,000
3	U01	2024-04-30	30,000
4	U02	2024-03-10	120,000
5	U02	2024-03-25	40,000
6	U03	2024-01-15	30,000
7	U04	2024-04-25	200,000
8	U04	2024-04-29	150,000

Read all 8 rows

LEARNING POINTS

Naming CTEs: Naming steps such as rfm_raw (raw data) → rfm_scored (scored data) makes the processing pipeline self-explanatory and useful during batch-code review.

Date subtraction: In PostgreSQL, DATE - DATE returns an integer day difference. '2024-05-01'::date - MAX(order_date) calculates the days from the reference date to the most recent purchase; BigQuery uses DATE_DIFF('2024-05-01', MAX(order_date), DAY).

ANTI-PATTERNS

Referencing CTEs in reverse order: A later CTE cannot be referenced by an earlier CTE. Define dependencies first; circular references are invalid except in recursive CTEs.

Practical note: Where this is used in practice

RFM customer scoring is central to CRM. A nightly batch can send these segments to a marketing-automation tool and trigger actions such as an exclusive early offer for VIPs or a retention coupon for users who may churn.

QUESTION 7

DENSE_RANK() — Rank Ties and Return the Top-Ranked Group

DENSE_RANKWindow FunctionsTie handlingRanking API

Background ▼

When tied scores must include everyone through the tied third place, DENSE_RANK() is the right choice. Understand how it differs from ROW_NUMBER.

SELECT player, score,
  ROW_NUMBER()  OVER (ORDER BY score DESC) AS rn,    -- always assign consecutive numbers, even for ties
  RANK()        OVER (ORDER BY score DESC) AS rnk,   -- ties share a rank; the next rank is skipped
  DENSE_RANK()  OVER (ORDER BY score DESC) AS dr;    -- ties share a rank; rank numbers do not skip

Choosing the right function:Use ROW_NUMBER for the top N rows with a fixed count, DENSE_RANK for everyone tied through the top N ranks, and RANK when a displayed ranking should skip numbers. Choose based on the API requirement.

Problem ▼

From the game_scores table below, rank players within each game using DENSE_RANK and return everyone ranked third or higher. Show every tied player at the same rank.

Source table

▸ game_scores

game_id	player_name	score
G01	Alice	9500
G01	Bob	8200
G01	Carol	8200
G01	Dave	7800
G01	Eve	7100
G02	Frank	6000
G02	Grace	6000
G02	Hank	5500
G02	Iris	4800

Expected Output ▼

game_id	player_name	score	rank
G01	Alice	9500	1
G01	Bob	8200	2
G01	Carol	8200	2
G01	Dave	7800	3
G02	Frank	6000	1
G02	Grace	6000	1
G02	Hank	5500	2
G02	Iris	4800	3

Model Answer ▼

WITH ranked_scores AS (
  -- ① Rank within each game using DENSE_RANK
  SELECT
    game_id,
    player_name,
    score,
    DENSE_RANK() OVER (
      PARTITION BY game_id  -- partition into groups by game
      ORDER BY score DESC   -- rank by score descending
    ) AS rank
  FROM game_scores
)

-- ② filter for players ranked 3 or higher
SELECT
  game_id,
  player_name,
  score,
  rank
FROM ranked_scores
WHERE rank <= 3               -- include ties through third place
ORDER BY game_id, rank, score DESC;

/*
  【Evaluation order】
  1. inside the CTE → evaluate the window function (DENSE_RANK; row count is preserved)
  2. outer query  → rows(rank in filter)
  3. outer query  → sort and output
*/

Explanation (table transitions & key points) ▼

WITH ranked_scores AS (
  SELECT
    game_id,
    player_name,
    score,
    DENSE_RANK() OVER (
      PARTITION BY game_id
      ORDER BY score DESC
    ) AS rank
  FROM game_scores
)

SELECT
  game_id,
  player_name,
  score,
  rank
FROM ranked_scores
WHERE rank <= 3
ORDER BY game_id, rank, score DESC;

LEGEND ▼

Rows read / loaded

① FROM ▼

FROM game_scoresRead game_scores (9 rows): G01 has five players, G02 has four, and Bob/Carol and Frank/Grace are tied.

1 / 4

game_id	player_name	score
G01	Alice	9,500
G01	Bob	8,200
G01	Carol	8,200
G01	Dave	7,800
G01	Eve	7,100
G02	Frank	6,000
G02	Grace	6,000
G02	Hank	5,500
G02	Iris	4,800

Read all 9 rows

LEARNING POINTS

When to choose DENSE_RANK: If three players tie for third, all three should be shown while fourth place is excluded. Using RANK would make Dave fourth after Bob and Carol tie for second, so WHERE rank <= 3 would remove Dave.

Omitting PARTITION BY: For one overall ranking across all data, omit PARTITION BY. For rankings within games or product categories, keep it.

ANTI-PATTERNS

Taking the top N rows with ROW_NUMBER without handling ties: This can show only one of two players tied for second. Use DENSE_RANK or RANK when everyone tied through rank N must be included.

Practical note: Where this is used in practice

DENSE_RANK is essential in social-game reward batches. Different rewards for users with the same score can trigger serious complaints, so confirm with stakeholders whether ties should receive consecutive ranks, skipped ranks, or shared ranks.

QUESTION 8

LEFT JOIN + COALESCE — Build a Complete Monthly Report Including Zero-Sales Months

LEFT JOINCOALESCEMissing-data fill-inbatch report

Background ▼

Months with no sales have no rows in the orders table. However, a monthly report may still need to include months with zero sales. LEFT JOIN + COALESCE is the standard pattern.

SELECT
  m.month,
  COALESCE(s.total_sales, 0) AS total_sales  -- replace NULL with 0
FROM       months        m        -- master list of all months
LEFT JOIN  sales_summary s        -- exists only for months with sales (some months become NULL)
        ON m.month = s.month;    -- join by month

LEFT JOIN vs INNER JOIN：INNER JOIN returns only matching rows from both tables, so zero-sales months disappear. LEFT JOIN preserves every row from the left table and returns NULL when the right side has no match. Converting that NULL to 0 with COALESCE is the standard zero-sales fill-in idiom.

Problem ▼

Create a monthly summary for every month from January through June 2024. For months with no sales, set total_sales = 0 and order_count = 0.

Source table

▸ orders (sales data)

order_id	order_month	amount
1	2024-01	30000
2	2024-01	50000
3	2024-03	80000
4	2024-05	40000
5	2024-05	60000
6	2024-06	90000

▸ month_master (month master)

month
2024-01
2024-02
2024-03
2024-04
2024-05
2024-06

Expected Output ▼

month	total_sales	order_count
2024-01	80000	2
2024-02	0	0
2024-03	80000	1
2024-04	0	0
2024-05	100000	2
2024-06	90000	1

Model Answer ▼

WITH
monthly_agg AS (                 -- ① salesis monthonlyaggregation
  SELECT
    order_month,
    SUM(amount) AS total_sales,
    COUNT(*)    AS order_count
  FROM orders
  GROUP BY order_month
)

-- ② allmonth masterto salesaggregation(LEFT JOIN)
SELECT
  m.month,
  COALESCE(a.total_sales, 0) AS total_sales, -- NULL0to 
  COALESCE(a.order_count, 0) AS order_count
FROM month_master m
LEFT JOIN monthly_agg a
  ON m.month = a.order_month                 -- salesis monthis is NULLto 
ORDER BY m.month;

/*
  【Evaluation order】
  1. inside the CTE → define the CTE(monthper aggregation)
  2. outer query  → (allrows)
  3. outer query  → (COALESCE)
  4. outer query  → sort and output
*/

Explanation (table transitions & key points) ▼

WITH
monthly_agg AS (
  SELECT
    order_month,
    SUM(amount) AS total_sales,
    COUNT(*) AS order_count
  FROM orders
  GROUP BY order_month
)

SELECT
  m.month,
  COALESCE(a.total_sales, 0) AS total_sales,
  COALESCE(a.order_count, 0) AS order_count
FROM month_master m
LEFT JOIN monthly_agg a
  ON m.month = a.order_month
ORDER BY m.month;

LEGEND ▼

Rows read / loaded

① FROM ▼

FROM ordersRead sales data from orders. At this point, February and April have no rows because they had no sales.

1 / 5

order_id	order_month	amount
1	2024-01	30,000
2	2024-01	50,000
3	2024-03	80,000
4	2024-05	40,000
5	2024-05	60,000
6	2024-06	90,000

Read all 6 rows

LEARNING POINTS

Use a month-master table: Create a table such as month_master, or generate months dynamically with generate_series in PostgreSQL or UNNEST in BigQuery. This is the basic pattern for filling missing data with zero.

Multiple COALESCE arguments: COALESCE(a, b, c) evaluates left to right and returns the first non-NULL value. COALESCE(a.total_sales, 0) means “return 0 when total_sales is NULL.”

ANTI-PATTERNS

INNER JOIN removes zero-sales months: If a report must include months with no sales, INNER JOIN removes those months. Use LEFT JOIN when the complete period is required.

Forgetting to convert NULL to 0: NULLs left after a LEFT JOIN can propagate through aggregation or arithmetic and reach the API. Always provide a default with COALESCE.

Practical note: Where this is used in practice

Returning a zero-sales month with no record can create date gaps in a frontend line chart. The backend should join a calendar master containing every date and provide a continuous time series.

QUESTION 9

FIRST_VALUE() / LAST_VALUE() — Put First and Latest Purchases on One Row

FIRST_VALUELAST_VALUEFirst/latestPurchase analysis

Background ▼

FIRST_VALUE() and LAST_VALUE() return values from the first and last rows in a window. They are useful for purchase-history analysis such as showing each user’s first and latest product on one row.

FIRST_VALUE(product) OVER (
  PARTITION BY user_id          -- independent for each user
  ORDER BY     order_date        --  days → of rowsis  timespurchase
) AS first_product

LAST_VALUE(product) OVER (
  PARTITION BY user_id
  ORDER BY     order_date
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING -- look at all rows — important
) AS last_product

LAST_VALUE pitfall:If ROWS BETWEEN is omitted, the default is “through the current row,” so rows before the last cannot see the latest value.ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING in “allrows”please。

Problem ▼

From the purchase_history table below, return each user’s first purchase date, first product, latest purchase date, and latest product on one row. Return exactly one row per user.

Source table

▸ purchase_history

user_id	order_date	product
U01	2024-01-10	Apple
U01	2024-02-20	Banana
U01	2024-04-05	Orange
U02	2024-01-15	Green tea
U02	2024-03-30	Coffee
U03	2024-02-01	Water

Expected Output ▼

user_id	first_date	first_product	last_date	last_product
U01	2024-01-10	Apple	2024-04-05	Orange
U02	2024-01-15	Green tea	2024-03-30	Coffee
U03	2024-02-01	Water	2024-02-01	Water

Model Answer ▼

WITH purchase_ranked AS (
  -- ① add first- and latest-purchase details to each row
  SELECT
    user_id,
    order_date,
    product,
    FIRST_VALUE(order_date) OVER (                              --  timespurchasedetails
      PARTITION BY user_id
      ORDER BY order_date
    ) AS first_date,
    FIRST_VALUE(product) OVER (
      PARTITION BY user_id
      ORDER BY order_date
    ) AS first_product,
    LAST_VALUE(order_date) OVER (                               -- latest-purchase details (ROWS BETWEEN covers all rows)
      PARTITION BY user_id
      ORDER BY order_date
      ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS last_date,
    LAST_VALUE(product) OVER (
      PARTITION BY user_id
      ORDER BY order_date
      ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS last_product
  FROM purchase_history
)

-- ② keep one row per user
SELECT DISTINCT
  user_id,
  first_date,
  first_product,
  last_date,
  last_product
FROM purchase_ranked
ORDER BY user_id;

/*
  【Evaluation order】
  1. inside the CTE → read the rows
  2. inside the CTE → Window Functions(FIRST/LAST_VALUE、rowsis )
  3. outer query  → remove duplicates (DISTINCT)
  4. outer query  → sort and output
*/

Explanation (table transitions & key points) ▼

WITH purchase_ranked AS (
  SELECT
    user_id,
    order_date,
    product,
    FIRST_VALUE(order_date) OVER (
      PARTITION BY user_id
      ORDER BY order_date
    ) AS first_date,
    FIRST_VALUE(product) OVER (
      PARTITION BY user_id
      ORDER BY order_date
    ) AS first_product,
    LAST_VALUE(order_date) OVER (
      PARTITION BY user_id
      ORDER BY order_date
      ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS last_date,
    LAST_VALUE(product) OVER (
      PARTITION BY user_id
      ORDER BY order_date
      ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS last_product
  FROM purchase_history
)

SELECT DISTINCT
  user_id,
  first_date,
  first_product,
  last_date,
  last_product
FROM purchase_ranked
ORDER BY user_id;

LEGEND ▼

Rows read / loaded

① FROM ▼

FROM purchase_historyRead purchase_history (6 rows): U01 has three purchases, U02 has two, and U03 has one.

1 / 4

user_id	order_date	product
U01	2024-01-10	Apple
U01	2024-02-20	Banana
U01	2024-04-05	Orange
U02	2024-01-15	Green tea
U02	2024-03-30	Coffee
U03	2024-02-01	Water

Read all 6 rows

LEARNING POINTS

FIRST_VALUE alternative: MIN(order_date) OVER (PARTITION BY user_id) also returns the first date. To retrieve the product from that same first row, use FIRST_VALUE or filter a CTE to ROW_NUMBER = 1.

SELECT DISTINCT vs. GROUP BY: Use DISTINCT when identical duplicate rows simply need to be compressed to one row. Use GROUP BY when the compression also performs aggregation such as SUM or COUNT.

ANTI-PATTERNS

Forgetting the full frame on LAST_VALUE: The default frame ends at the current row, so rows before the last cannot see the latest value. Always specify ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.

Practical note: Where this is used in practice

Comparing a user’s first and latest products helps analyze behavior among high-LTV customers. This FIRST_VALUE / LAST_VALUE combination can power a data mart measuring conversion from a trial set to a subscription delivery.

QUESTION 10

Recursive CTE (RECURSIVE) — Expand Hierarchical Categories Across All Levels

RECURSIVERecursive CTEHierarchyTree expansion

Background ▼

A recursive CTE (WITH RECURSIVE) is a self-referencing query. It expands data with hierarchical parent-child relationships, such as category trees, organization charts, and comment threads.

WITH RECURSIVE tree AS (

  -- Anchor: get the first row (root node)
  SELECT id, parent_id, name, 0 AS depth
  FROM   categories
  WHERE  parent_id IS NULL          -- no parent = root

  UNION ALL                          -- connect the anchor and recursive members (do not remove duplicates)

  -- Recursive member: use the previous step to get children
  SELECT c.id, c.parent_id, c.name, t.depth + 1
  FROM       categories c
  INNER JOIN tree       t  -- reference tree from the previous step (recursive)
          ON c.parent_id = t.id
)
SELECT * FROM tree;

Recursive termination:Recursion ends when the recursive member produces no new rows. A cycle such as A → B → A can loop forever, so production queries should enforce a maximum depth.

Problem ▼

The categories table below stores parent-child relationships for product categories. Expand every level with a recursive CTE and display each category’s depth and hierarchy path (for example: Electronics > Smartphone > Android).

Source table

▸ categories

id	parent_id	name
1	NULL	Electronics
2	1	Smartphone
3	1	PC
4	2	Android
5	2	iPhone
6	3	Notebook PC
7	3	Desktop

Expected Output ▼

id	name	depth	path
1	Electronics	0	Electronics
2	Smartphone	1	Electronics > Smartphone
3	PC	1	Electronics > PC
4	Android	2	Electronics > Smartphone > Android
5	iPhone	2	Electronics > Smartphone > iPhone
6	Notebook PC	2	Electronics > PC > Notebook PC
7	Desktop	2	Electronics > PC > Desktop

Model Answer ▼

WITH RECURSIVE category_tree AS (
  -- ═══ Anchor: get the root node ═══
  SELECT
    id,
    parent_id,
    name,
    0    AS depth,
    name AS path
  FROM categories
  WHERE parent_id IS NULL

  UNION ALL

  -- ═══ recursive member: repeatedly get children from each parent ═══
  SELECT
    c.id,
    c.parent_id,
    c.name,
    t.depth + 1,               -- go one level deeper
    t.path || ' > ' || c.name  -- append the child name to the parent path
  FROM categories c
  INNER JOIN category_tree t
    ON c.parent_id = t.id      -- join the child parent_id to the parent id
)

-- output all results after recursion completes
SELECT
  id,
  name,
  depth,
  path
FROM category_tree
ORDER BY id;

/*
  【Evaluation order(Recursive CTEof ー)】
  1. Anchor()
  2. first recursion(expand)
  3. second recursion(expand)
  4. stop when no rows are added
  5. evaluate the outer query   → with ORDER BY sort and output
*/

Explanation (table transitions & key points) ▼

WITH RECURSIVE category_tree AS (
  SELECT
    id,
    parent_id,
    name,
    0    AS depth,
    name AS path
  FROM categories
  WHERE parent_id IS NULL

  UNION ALL

  SELECT
    c.id,
    c.parent_id,
    c.name,
    t.depth + 1,
    t.path || ' > ' || c.name
  FROM categories c
  INNER JOIN category_tree t
    ON c.parent_id = t.id
)

SELECT
  id,
  name,
  depth,
  path
FROM category_tree
ORDER BY id;

LEGEND ▼

Rows read / loaded

① Anchor ▼

Get the root node (depth=0)Read the row whose parent_id is NULL (Electronics). This is the starting point for expanding the hierarchy.

1 / 4

id	parent_id	name	depth	path
1	NULL	Electronics	0	Electronics

Anchor: 1 row

LEARNING POINTS

Practical recursive-CTE examples: Recursive CTEs handle category trees, organization charts, comment threads, bills of materials, graph path searches, and any structure where children can have further children.

Depth limit: Add a limit such as WHERE t.depth < 10 in the recursive member to prevent infinite loops. It is also common to stop when an id already present in the path appears again.

ANTI-PATTERNS

Using UNION instead of UNION ALL: The recursive member should use UNION ALL. Duplicate elimination can hurt performance and may not work correctly for recursion in some databases.

Running cyclic data without a depth limit: A cycle such as A → B → A never terminates and may time out or error. For production data, always enforce depth < maximum_depth.

Practical note: Where this is used in practice

Use recursive CTEs to generate e-commerce breadcrumbs or list every employee under a selected division head. They are the practical choice when tree depth is variable.