Batch Processing — Learn CTEs, Window Functions, Running Totals, and Pivots in Practice

QUESTION 1

CTE + GROUP BY — Aggregate a Daily Sales Summary for an API

WITHCTEGROUP BYDaily Batch

Background ▼

A CTE (Common Table Expression) is a temporary named query defined as WITH name AS (...). In batch processing, a standard pattern is to split the three stages—aggregation, filtering, and formatting—into CTEs.

WITH aggregate_name AS (
  -- ① Define the aggregation (GROUP BY) here
  SELECT col1, SUM(col2) AS total
  FROM   table_name
  GROUP BY col1         -- Aggregate rows for each col1 value
)
-- ② Filter and sort the aggregated result
SELECT *
FROM   aggregate_name
WHERE  total > 10000  -- A CTE lets WHERE reference the aggregated total
ORDER BY col1;          -- Sort using the default ASC order

Why use a CTE:You normally cannot write WHERE SUM(...) and must use HAVING. Moving the aggregation into a CTE lets you treat its result as an ordinary column. As batch SQL grows, naming and separating processing stages becomes increasingly valuable.

Problem ▼

From the orders table below, calculate the total sales, order count, and average order amount for each order_date.

Return only dates whose total sales are at least 50,000 yen, sorted by total sales in descending order.

Source table

▸ orders

order_id	order_date	amount
1	2024-04-01	20000
2	2024-04-01	35000
3	2024-04-02	60000
4	2024-04-02	15000
5	2024-04-03	80000
6	2024-04-03	40000
7	2024-04-04	12000
8	2024-04-04	18000

Expected Output ▼

order_date	total_sales	order_count	avg_order
2024-04-03	120000	2	60000
2024-04-02	75000	2	37500
2024-04-01	55000	2	27500

04-04 is excluded because its total is 30,000 yen.

Model Answer ▼

-- WITH: split processing into a CTE
WITH daily_stats AS (
  -- ① Aggregate orders by date
  SELECT
    order_date,
    SUM(amount)  AS total_sales,
    COUNT(*)     AS order_count,
    AVG(amount)  AS avg_order
  FROM orders
  GROUP BY order_date
)
-- ② Filter and sort the CTE result
SELECT
  order_date,
  total_sales,
  order_count,
  avg_order
FROM daily_stats
WHERE total_sales >= 50000
ORDER BY total_sales DESC;

/*
  Evaluation order:
  1. CTE daily_stats            → aggregate by date (SUM/COUNT/AVG)
  2. Outer WHERE                  → filter by the total_sales threshold
  3. ORDER BY total_sales DESC  → return rows in descending order
*/

Explanation (table transitions & key points) ▼

WITH daily_stats AS (
  SELECT
    order_date,
    SUM(amount) AS total_sales,
    COUNT(*) AS order_count,
    AVG(amount) AS avg_order
  FROM orders
  GROUP BY order_date
)
SELECT
  order_date, total_sales,
  order_count, avg_order
FROM daily_stats
WHERE total_sales >= 50000
ORDER BY total_sales DESC;

LEGEND ▼

Rows read / loaded

① FROM ▼

FROM ordersRead all eight rows from orders. In the next step, GROUP BY collects rows by date.

1 / 5

order_id	order_date	amount
1	2024-04-01	20,000
2	2024-04-01	35,000
3	2024-04-02	60,000
4	2024-04-02	15,000
5	2024-04-03	80,000
6	2024-04-03	40,000
7	2024-04-04	12,000
8	2024-04-04	18,000

Read all 8 rows

LEARNING POINTS

Evaluation order:SQL is evaluated in the order FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY. Splitting processing into a CTE and an outer query makes this order easier to see.

COUNT(*) vs. COUNT(col):COUNT(*) counts every row, including rows containing NULL, while COUNT(col) excludes NULL values in that column. Use the former when counting row existence itself.

ANTI-PATTERNS

Aggregate function directly in WHERE:WHERE SUM(...) fails because of SQL evaluation order. Use HAVING or an outer WHERE around a CTE.

ORDER BY inside a CTE:Sorting inside a CTE does not guarantee output order. Apply ORDER BY in the outer SELECT.

Practical note

Organizing CTEs as a pipeline—1. raw-data processing, 2. master-data joins, 3. aggregation, and 4. formatting—lets you run each stage independently and greatly improves operational maintainability.

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

LAG() Window Function — Calculate Prior-Month Differences and Growth Rates

LAGWindow FunctionsMonth-over-MonthBatch Report

Background ▼

LAG() is a window function that brings a value from one row earlier (or n rows earlier) onto the current row. It is essential for month-over-month and day-over-day calculations.

LAG(value_column, 1, 0) OVER (
  PARTITION BY group_column   -- Reference the previous row independently within each group
  ORDER BY     sort_column       -- This order determines which row is previous
) AS previous_value

The arguments are LAG(column, offset, default). The offset defaults to 1 (one row earlier), and the default value is used when no previous row exists, as on the first row.

OVER and PARTITION BY:OVER() lets a window function calculate values without collapsing rows. If PARTITION BY is omitted, all rows form one group.

Problem ▼

From the monthly_sales table below, calculate each month’s previous-month sales, difference from the previous month, and month-over-month growth rate (%).

Source table

▸ monthly_sales

month	sales
2024-01	100000
2024-02	130000
2024-03	120000
2024-04	160000
2024-05	145000

Expected Output ▼

month	sales	prev_sales	diff	growth_rate
2024-01	100000	0	NULL	NULL
2024-02	130000	100000	+30000	30.00
2024-03	120000	130000	-10000	-7.69
2024-04	160000	120000	+40000	33.33
2024-05	145000	160000	-15000	-9.38

Model Answer ▼

-- WITH: split processing into a CTE
WITH sales_with_prev AS (
  -- ① Use LAG to place previous-month sales on the current row
  SELECT
    month,
    sales,
    LAG(sales, 1, 0) OVER (
      ORDER BY month
    ) AS prev_sales
  FROM monthly_sales
)
-- ② Calculate the difference and growth rate
SELECT
  month,
  sales,
  prev_sales,
  CASE
    WHEN prev_sales = 0 THEN NULL
    ELSE sales - prev_sales
  END AS diff,
  CASE
    WHEN prev_sales = 0 THEN NULL
    ELSE ROUND((sales - prev_sales)::numeric / prev_sales * 100, 2)
  END AS growth_rate
FROM sales_with_prev
ORDER BY month;

/*
  Evaluation order:
  1. CTE sales_with_prev  → attach previous-month sales with LAG
  2. Outer CASE             → calculate the difference and growth rate
  3. ORDER BY month       → return rows in ascending month order
*/

Explanation (table transitions & key points) ▼

WITH sales_with_prev AS (
  SELECT
    month, sales,
    LAG(sales, 1, 0) OVER (
      ORDER BY month
    ) AS prev_sales
  FROM monthly_sales
)
SELECT
  month, sales, prev_sales,
  CASE
    WHEN prev_sales = 0 THEN NULL
    ELSE sales - prev_sales
  END AS diff,
  CASE
    WHEN prev_sales = 0 THEN NULL
    ELSE ROUND((sales - prev_sales)::numeric / prev_sales * 100, 2)
  END AS growth_rate
FROM sales_with_prev
ORDER BY month;

LEGEND ▼

Rows read / loaded

① FROM ▼

FROM monthly_salesRead all five rows from monthly_sales. In the next step, LAG adds the sales value from one row earlier to each row.

1 / 3

month	sales
2024-01	100,000
2024-02	130,000
2024-03	120,000
2024-04	160,000
2024-05	145,000

Read all 5 rows

LEARNING POINTS

LAG default value:The third argument of LAG(sales, 1, 0) prevents NULL on the first row. This is useful when an API response should avoid NULL.

Integer division:Many databases truncate integer division. Cast explicitly with ::numeric or an equivalent type before calculating.

ANTI-PATTERNS

LAG without ORDER BY:The row order becomes undefined. Always specify ORDER BY.

Division by a zero denominator:Guard with CASE WHEN prev_sales = 0 THEN NULL to prevent division-by-zero errors.

Practical note

Month-over-month and week-over-week comparisons are core dashboard metrics. LAG(sales, 7), which helps remove day-of-week noise, is another common pattern.

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

SUM() OVER (ROWS BETWEEN) — Calculate a Running Sales Total and Moving Average

SUM OVERROWS BETWEENRunning TotalMoving Average

Background ▼

The ROWS BETWEEN clause of a window function precisely specifies which range of rows to aggregate for each current row.

SUM(sales) OVER (
  ORDER BY month
  ROWS BETWEEN UNBOUNDED PRECEDING    -- From the first row
           AND CURRENT ROW            -- Through the current row → running total
) AS running_total

AVG(sales) OVER (
  ORDER BY month
  ROWS BETWEEN 2 PRECEDING           -- From two rows earlier
           AND CURRENT ROW            -- Through the current row → three-month moving average
) AS moving_avg_3m

Batch-processing use case:This pattern is common in time-series batches, such as identifying the month when cumulative sales exceeded a target. A window function is essential because it aggregates without removing rows.

Problem ▼

From the monthly_sales table below, calculate each month’s running sales total (running_total) and moving average for the latest three months (moving_avg_3m).

Source table

▸ monthly_sales

month	sales
2024-01	80000
2024-02	120000
2024-03	100000
2024-04	150000
2024-05	130000
2024-06	170000

Expected Output ▼

month	sales	running_total	moving_avg_3m
2024-01	80000	80000	80000.0
2024-02	120000	200000	100000.0
2024-03	100000	300000	100000.0
2024-04	150000	450000	123333.3
2024-05	130000	580000	126666.7
2024-06	170000	750000	150000.0

Model Answer ▼

SELECT
  month,
  sales,
  SUM(sales) OVER (                                   -- ① Running sales total: sum from the first row through the current row
    ORDER BY month
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_total,
  ROUND(                                              -- ② Three-month moving average: average from two rows earlier through the current row
    AVG(sales) OVER (
      ORDER BY month
      ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    )::numeric, 1
  ) AS moving_avg_3m
FROM monthly_sales
ORDER BY month;

/*
  Evaluation order:
  1. FROM monthly_sales  → read rows
  2. SUM() OVER (...)    → calculate the running sales total
  3. AVG() OVER (...)    → calculate the three-month moving average
  4. ORDER BY month      → return rows in ascending month order
*/

Explanation (table transitions & key points) ▼

SELECT
  month, sales,
  SUM(sales) OVER (
    ORDER BY month
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_total,
  ROUND(
    AVG(sales) OVER (
      ORDER BY month
      ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    )::numeric, 1
  ) AS moving_avg_3m
FROM monthly_sales
ORDER BY month;

LEGEND ▼

Rows read / loaded

① FROM ▼

FROM monthly_salesRead all six rows from monthly_sales. In the next step, SUM OVER adds the running total from the first row through the current row.

1 / 3

month	sales
2024-01	80,000
2024-02	120,000
2024-03	100,000
2024-04	150,000
2024-05	130,000
2024-06	170,000

Read all 6 rows

LEARNING POINTS

ROWS vs. RANGE:ROWS BETWEEN defines a window by physical row count, while RANGE BETWEEN uses a range of values. Prefer ROWS when its clearer and typically faster semantics match the requirement.

Practical use of moving averages:They smooth short-term trends and provide a foundation for anomaly detection.

ANTI-PATTERNS

Running total without ORDER BY:SUM() OVER() returns the total across all rows. A running total requires ORDER BY and ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

Practical note

Moving averages smooth noise from weekdays, campaigns, and similar effects to reveal trends. Seven-day (7MA) and 28-day (28MA) windows are standard choices.

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

ROW_NUMBER() + CTE — Build an API for Top-N Products by Category

ROW_NUMBERPARTITION BYTop NRanking API

Background ▼

APIs often need only the top N rows in each category. A standard pattern assigns each row a rank within its group using ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...), isolates that result in a CTE, and then filters with WHERE rn <= N.

WITH ranked AS (
  SELECT *,
    ROW_NUMBER() OVER (
      PARTITION BY category        -- Number rows independently within each category
      ORDER BY     sales DESC, product_id  -- Break ties by product_id ascending
    ) AS rn
  FROM products
)
SELECT *
FROM   ranked
WHERE  rn <= 2;   -- Keep only the top two rows in each category

ROW_NUMBER vs. RANK vs. DENSE_RANK:ROW_NUMBER always assigns a unique sequence even when values tie. RANK gives ties the same rank and skips the next number (1, 1, 3), while DENSE_RANK does not skip it (1, 1, 2). ROW_NUMBER is usually easiest for extracting exactly N rows.

Problem ▼

From the product_sales table below, extract the top two products by sales within each category.

Source table

▸ product_sales

product_id	category	product_name	sales
P01	Food	Apple	85000
P02	Food	Banana	62000
P03	Food	Mandarin	62000
P04	Food	Grape	41000
P05	Refreshments	Green tea	95000
P06	Refreshments	Coffee	78000
P07	Refreshments	Juice	78000
P08	Refreshments	Water	55000

Expected Output ▼

category	product_name	sales	rn
Food	Apple	85000	1
Food	Banana	62000	2
Refreshments	Green tea	95000	1
Refreshments	Coffee	78000	2

Ties are resolved deterministically by product_id in ascending order.

Model Answer ▼

-- WITH: split processing into a CTE
WITH ranked_products AS (
  -- ① Rank sales within each category
  SELECT
    category,
    product_name,
    sales,
    ROW_NUMBER() OVER (
      PARTITION BY category
      ORDER BY sales DESC, product_id
    ) AS rn
  FROM product_sales
)
-- ② Extract the top two rows in each category
SELECT
  category,
  product_name,
  sales,
  rn
FROM ranked_products
WHERE rn <= 2
ORDER BY category, rn;

/*
  Evaluation order:
  1. CTE ranked_products    → rank sales within each category
  2. Outer WHERE rn <= 2      → extract the top two rows per category
  3. ORDER BY category, rn  → sort and return the result
*/

Explanation (table transitions & key points) ▼

WITH ranked_products AS (
  SELECT
    category, product_name, sales,
    ROW_NUMBER() OVER (
      PARTITION BY category
      ORDER BY sales DESC, product_id
    ) AS rn
  FROM product_sales
)
SELECT
  category, product_name, sales, rn
FROM ranked_products
WHERE rn <= 2
ORDER BY category, rn;

LEGEND ▼

Rows read / loaded

① FROM ▼

FROM product_salesRead all eight rows from product_sales. In the next step, PARTITION BY forms one group for each category.

1 / 5

product_id	category	product_name	sales
P01	Food	Apple	85,000
P02	Food	Banana	62,000
P03	Food	Mandarin	62,000
P04	Food	Grape	41,000
P05	Refreshments	Green tea	95,000
P06	Refreshments	Coffee	78,000
P07	Refreshments	Juice	78,000
P08	Refreshments	Water	55,000

Read all 8 rows

LEARNING POINTS

Why the CTE is necessary:Window functions are evaluated after WHERE. Complete the window calculation in a CTE, then filter it in the outer query.

PARTITION BY:Without it, the rank covers the entire result. It is required for ranks within groups such as categories.

ANTI-PATTERNS

WHERE at the same query level:Directly using a window-function result in WHERE at the same SELECT level causes an error.

Practical note

Top N per category and selecting only the latest history row with ORDER BY created_at DESC are essential data-mart patterns.

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

CASE WHEN + SUM — Pivot Counts by Status

CASE WHENPivotSUM FILTERCross Tabulation

Background ▼

Pivot aggregation, which expands row-oriented data into columns, is common in dashboard APIs. Because many SQL dialects lack a dedicated PIVOT statement, it can be implemented with CASE WHEN + SUM.

SELECT
  category,
  SUM(CASE WHEN status = 'done' THEN 1 ELSE 0 END) AS completed,
  SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) AS pending
FROM   tasks
GROUP BY category;

PostgreSQL also supports FILTER:COUNT(*) FILTER (WHERE status = 'done') produces the same result. CASE WHEN is more portable because some databases, including BigQuery, do not support this FILTER syntax.

Problem ▼

From the tasks table below, return one row per assignee containing counts by status (done, in progress, and not started) plus the total count.

Source table

▸ tasks

task_id	assignee	status
T01	Baker	done
T02	Baker	done
T03	Baker	in progress
T04	Baker	not started
T05	Clark	done
T06	Clark	in progress
T07	Clark	in progress
T08	Adams	not started
T09	Adams	not started
T10	Adams	done

Expected Output ▼

assignee	done	in_progress	not_started	total
Adams	1	0	2	3
Baker	2	1	1	4
Clark	1	2	0	3

Model Answer ▼

SELECT
  assignee,
  SUM(CASE WHEN status = 'done' THEN 1 ELSE 0 END) AS done,          -- ① Count done tasks
  SUM(CASE WHEN status = 'in progress' THEN 1 ELSE 0 END) AS in_progress,  -- ② Count tasks in progress
  SUM(CASE WHEN status = 'not started' THEN 1 ELSE 0 END) AS not_started,  -- ③ Count tasks not started
  COUNT(*) AS total                                                -- ④ Count all tasks
FROM tasks
GROUP BY assignee
ORDER BY assignee;

/*
  Evaluation order:
  1. FROM tasks         → read rows
  2. GROUP BY assignee  → group by assignee
  3. SUM(CASE...)       → aggregate counts by status (pivot)
  4. ORDER BY assignee  → return rows in ascending order
*/

Explanation (table transitions & key points) ▼

SELECT
  assignee,
  SUM(CASE WHEN status = 'done' THEN 1 ELSE 0 END) AS done,
  SUM(CASE WHEN status = 'in progress' THEN 1 ELSE 0 END) AS in_progress,
  SUM(CASE WHEN status = 'not started' THEN 1 ELSE 0 END) AS not_started,
  COUNT(*) AS total
FROM tasks
GROUP BY assignee
ORDER BY assignee;

LEGEND ▼

Rows read / loaded

① FROM ▼

FROM tasksRead all ten rows from tasks. In the next step, GROUP BY groups the data by assignee.

1 / 5

task_id	assignee	status
T01	Baker	done
T02	Baker	done
T03	Baker	in progress
T04	Baker	not started
T05	Clark	done
T06	Clark	in progress
T07	Clark	in progress
T08	Adams	not started
T09	Adams	not started
T10	Adams	done

Read all 10 rows

LEARNING POINTS

Creating flags with CASE WHEN:SUM(CASE WHEN ... THEN 1 ELSE 0 END) is an idiom for counting rows that satisfy a condition.

Compression through pivoting:Expand row-oriented data into columns to build an aggregated API response table in one query.

ANTI-PATTERNS

Omitting ELSE:A nonmatching condition returns NULL. Specify ELSE 0 to prevent unintended behavior.

Practical note

Pivoting in the database can substantially reduce data transfer and application memory use. It is effective for dashboard aggregation over large logs.

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.