SQL Window Functions — Basics of LAG/LEAD, Moving Averages

QUESTION 6

LAG() / LEAD() — Retrieve Values from Adjacent Rows and Calculate Day-over-Day Change

LAGLEADDay-over-Day Comparison

Background ▼

LAG() (the previous row) and LEAD() (the next row) let you bring a value from another row alongside the current row and easily calculate a subtraction or other difference. This is another signature strength of window functions.

SELECT
  sort_col, num_col,
  LAG(num_col) OVER(ORDER BY sort_col ASC) AS prev_val
FROM table_name;

LAG(column, offset, default): The arguments let you choose how many rows back to look (one row by default). The first row has no previous row, so it returns NULL.

Problem ▼

From the daily_sales table below, calculate each date's sales (amount), the previous day's sales, and the change from the previous day (diff).

Input table

▸ daily_sales

date	amount
04-01	100
04-02	120
04-03	90
04-04	150

Expected Output ▼

date	amount	prev_amount	diff
04-01	100	NULL	NULL
04-02	120	100	20
04-03	90	120	-30
04-04	150	90	60

Model Answer ▼

SELECT
  date,
  amount,
  LAG(amount) OVER(ORDER BY date) AS prev_amount,  -- amount from the previous row in date order
  amount - LAG(amount) OVER(ORDER BY date) AS diff
FROM daily_sales                                    -- subtract the previous sale from the current sale
ORDER BY date;

/*
  Execution order:
  1. FROM daily_sales
  2. The window function evaluates rows in date order
  3. SELECT produces the output
  */

Explanation (table transitions & key points) ▼

SELECT
  date, amount,
  LAG(amount) OVER(ORDER BY date) AS prev_amount,
  amount - LAG(amount) OVER(ORDER BY date) AS diff
FROM daily_sales
ORDER BY date;

LEGEND ▼

Rows read / loaded

① FROM ▼

FROM daily_salesRead the table.

1 / 3

date	amount
04-01	100
04-02	120
04-03	90
04-04	150

4 rows read

LEARNING POINTS

Eliminate self joins: Before LAG, a day-over-day comparison required a heavy, complex query that joined a table to itself with the date shifted by one day. LAG dramatically improved the ergonomics of analytical SQL.

Combine it with PARTITION BY: With LAG(amount) OVER(PARTITION BY store_id ORDER BY date), LAG resets to NULL when the store changes. This prevents accidentally subtracting the previous day's sales from a different store.

▤ How LAG() Works — Each Row References the Previous Row's Value

date	amount	LAG(amount) — source	prev_amount	diff (subtraction)
04-01	100	No previous row → NULL	NULL	NULL
04-02	120	↑ amount from 04-01	100	+20
04-03	90	↑ amount from 04-02	120	−30
04-04	150	↑ amount from 04-03	90	+60

↑ Arrow = the row referenced by LAG. Once rows are ordered by date, each row takes the value from the row immediately above it. The first row (04-01) has no source row, so the result is NULL.

ANTI-PATTERNS

Forgetting NULL handling: LAG always returns NULL for the first row. If you feed the day-over-day difference into later arithmetic or an aggregate whose denominator depends on non-NULL rows, that NULL may change the intended result. When the business definition requires it, protect the value with something like COALESCE(LAG(amount) OVER(...), 0).

QUESTION 7

ROWS BETWEEN — Specify a Frame to Calculate a Moving Average

Frame SpecificationROWS BETWEENMoving Average

Background ▼

Window functions let you precisely specify the range of rows to aggregate, called the frame. Here, you will use a frame to calculate the moving average familiar from stock-price charts and other time-series analysis.

AVG(amount) OVER(
  ORDER BY date
  ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
)

How to read the frame: From 2 PRECEDING (two rows back) through CURRENT ROW (the current row), AVG covers at most three rows: the previous two rows and the current row. Because this table has one row per day, that is the latest three days.

Problem ▼

Using the daily_sales table, return each date's sales and the moving average over the latest three rows (from two rows back through the current row).

Input table

▸ daily_sales

date	amount
04-01	100
04-02	140
04-03	120
04-04	190
04-05	170

Expected Output ▼

date	amount	moving_avg_3d
04-01	100	100
04-02	140	120
04-03	120	120
04-04	190	150
04-05	170	160

Model Answer ▼

SELECT
  date,
  amount,
  AVG(amount) OVER(  -- moving average without collapsing rows
    ORDER BY date
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW  -- latest 3 rows: current row + previous 2
  ) AS moving_avg_3d
FROM daily_sales
ORDER BY date;

/*
  Calculation:
  04-01: no previous row, so average [04-01] → 100
  04-02: one previous row, so average [04-01, 04-02] → (100+140)/2 = 120
  04-03: two previous rows, so average [04-01, 04-02, 04-03] → 360/3 = 120
  04-04: average [04-02, 04-03, 04-04] → (140+120+190)/3 = 150
*/

Explanation (table transitions & key points) ▼

SELECT
  date, amount,
  AVG(amount) OVER(
    ORDER BY date
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ) AS moving_avg_3d
FROM daily_sales
ORDER BY date;

LEGEND ▼

Rows read / loaded

① FROM ▼

FROM daily_salesRead the table.

1 / 3

date	amount
04-01	100
04-02	140
04-03	120
04-04	190
04-05	170

5 rows read

LEARNING POINTS

Frame keyword quick reference: The five basics are PRECEDING (an earlier row), FOLLOWING (a later row), UNBOUNDED PRECEDING (the first row), UNBOUNDED FOLLOWING (the last row), and CURRENT ROW (the current row).

Why moving averages matter in practice: Daily sales and active-user counts can vary sharply by day of the week, such as rising only on weekends. A seven-row moving average (6 PRECEDING AND CURRENT ROW) reduces that noise and reveals the real upward or downward trend. When there is exactly one row per day, this is a seven-day moving average. It is a fundamental analytical technique.

▤ ROWS BETWEEN ... — How the Frame Moves

date	amount	04-01 100	04-02 140	04-03 120	04-04 190	04-05 170	moving_avg
04-01	100	★					100
04-02	140	○	★				120
04-03	120	○	○	★			120
04-04	190	—	○	○	★		150
04-05	170	—	—	○	○	★	160

★ CURRENT ROW ○ PRECEDING (earlier row in the frame) — Outside the frame

For the 04-04 row, the frame runs from two rows back (04-02) through the current row (04-04): (140+120+190)÷3 = 150. The 04-01 row is outside the frame.

ANTI-PATTERNS

Misunderstanding the default frame: If you specify only ORDER BY and omit ROWS BETWEEN, the implicit frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, from the first row through the current row. That produces a running aggregate rather than a moving average. Always specify the frame explicitly when you need a moving average.

QUESTION 8

Filter Through a Subquery — Use a Window-Function Result in WHERE

SubqueryROW_NUMBERLatest-Row ExtractionVery Common

Background ▼

Because of SQL's logical execution order, you cannot put a window function directly in a WHERE clause. To filter rows by its result, run the window function inside a subquery (or CTE), then filter in the outer query's WHERE clause.

-- A highly reusable pattern for retrieving the latest history row
SELECT * FROM (
  SELECT
    *,
    ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY created_at DESC, log_id DESC) AS rn
  FROM logs
) AS tmp
WHERE rn = 1;

Why WHERE cannot reference it: WHERE is evaluated before window functions, so a window result cannot be used as a condition in the same SELECT. Materialize it as a column in a subquery or CTE first, then filter in the outer query. When rows can tie on the ordering, add a tie-breaker to ORDER BY or the row selected is undefined.

Problem ▼

From the orders table below, retrieve every column from only the latest order row for each user: the row with the newest order_date. If dates tie, choose the row with the greatest order_id.

Input table

▸ orders

order_id	user_id	order_date	item
1	U1	2024-04-01	Mouse
2	U2	2024-04-02	Desk
3	U1	2024-04-05	Laptop
4	U3	2024-04-06	Chair
5	U2	2024-04-08	Monitor

Expected Output ▼

order_id	user_id	order_date	item
3	U1	2024-04-05	Laptop
5	U2	2024-04-08	Monitor
4	U3	2024-04-06	Chair

Model Answer ▼

SELECT
  order_id,
  user_id,
  order_date,
  item
FROM (
  -- Subquery: number each user's orders from newest to oldest
  SELECT
    order_id,
    user_id,
    order_date,
    item,
    ROW_NUMBER() OVER(
      PARTITION BY user_id
      ORDER BY order_date DESC, order_id DESC
    ) AS rn
  FROM orders
) AS tmp    -- a subquery requires an alias (temporary name)
WHERE rn = 1 -- outer query keeps only the single latest row
ORDER BY user_id;

/*
  Execution order:
  1. FROM orders (inner query)
  2. Window function (inner query): build a temporary table (tmp) with ROW_NUMBER
     U1: Laptop(rn=1), Mouse(rn=2)
     U2: Monitor(rn=1), Desk(rn=2)
     U3: Chair(rn=1)
  3. FROM tmp (outer query)
  4. WHERE rn = 1: keep only matching rows in the outer query
  5. SELECT produces the output
*/

Explanation (table transitions & key points) ▼

SELECT
  order_id, user_id, order_date, item
FROM (
  SELECT
    order_id, user_id, order_date, item,
    ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY order_date DESC, order_id DESC) AS rn
  FROM orders
) AS tmp
WHERE rn = 1
ORDER BY user_id;

LEGEND ▼

Grouping keys / aggregation targets

Group classification

① Execute the Subquery ▼

Assign ROW_NUMBER inside the subqueryThe inner query runs first and creates a virtual table (tmp) with rn numbering each user's orders from newest to oldest: order_date DESC, then order_id DESC for a date tie.

1 / 3

order_id	user_id	order_date	item	rn
3	U1	04-05	Laptop	1
1	U1	04-01	Mouse	2
5	U2	04-08	Monitor	1
2	U2	04-02	Desk	2
4	U3	04-06	Chair	1

5 rows with sequence numbers

LEARNING POINTS

Why GROUP BY cannot replace this pattern: A common mistake is SELECT user_id, MAX(order_date), item FROM orders GROUP BY user_id. In MySQL and other systems, item is either rejected or is not guaranteed to come from the row with the maximum date. To retrieve every column from the latest row, use the ROW_NUMBER + subquery pattern.

▤ Two-Layer Execution Flow — Subquery, Then Outer WHERE

① Run the inner subquery — Number every row with ROW_NUMBER

order_id	user_id	order_date	item	rn (newest first per user)
3	U1	04-05	Laptop	1 ← latest
1	U1	04-01	Mouse	2
5	U2	04-08	Monitor	1 ← latest
2	U2	04-02	Desk	2
4	U3	04-06	Chair	1 ← latest

↓ WHERE rn = 1

② Outer query keeps only rn=1 — One latest row remains per user

order_id	user_id	order_date	item
3	U1	04-05	Laptop
5	U2	04-08	Monitor
4	U3	04-06	Chair

5 rows → 3 rows (the user count: one latest row per user). The outer SELECT excludes the rn column.

ANTI-PATTERNS

Using RANK(), or omitting a tie-breaker: If a user places two orders at the same time, RANK() gives both rows rn=1, so the result has more rows than users. Even ROW_NUMBER() chooses nondeterministically when equal timestamps have no tie-breaker. Extend ORDER BY through a unique column, such as order_id DESC.

QUESTION 9

Top N Extraction — Retrieve the Top Two Ranks in Each Category

Top NRANK()Ranking by Category

Background ▼

Extending latest-row pattern, you can change the filter to something like rn <= 3 to retrieve the top N rows in each group. When ties should share a rank, use RANK and filter the top N ranks instead. This is another powerful window-function operation that GROUP BY cannot perform.

WITH ranked AS (                   -- 1. Assign the ranking inside the CTE
  SELECT t.*,
         RANK() OVER (PARTITION BY group_col ORDER BY num_col DESC) AS rnk
  FROM   table_name t
)
SELECT *
FROM   ranked
WHERE  rnk <= 3;                     -- 2. Filter to the top N ranks in the outer WHERE

ROW_NUMBER or RANK: Use ROW_NUMBER() when you want exactly N rows per group, and RANK() when you want the top N ranks and every tied row with them. Whether ties are kept changes how many rows come back.

Problem ▼

From the products table below, retrieve the products in the top two price ranks within each category.

Products with the same price must share a rank and all be included. Output the rank (rnk) as well, so a tie can produce more than two products in a category.

Input table

▸ products

product_id	category	name	price
P1	Appliances	TV	80000
P2	Appliances	Refrigerator	120000
P3	Appliances	Washing Machine	90000
P4	Furniture	Bed	50000
P5	Furniture	Sofa	70000
P6	Furniture	Table	30000

Expected Output ▼

category	name	price	rnk
Appliances	Refrigerator	120000	1
Appliances	Washing Machine	90000	2
Furniture	Sofa	70000	1
Furniture	Bed	50000	2

Model Answer ▼

SELECT
  category,
  name,
  price,
  rnk
FROM (
  SELECT
    category,
    name,
    price,
    RANK() OVER(  -- rank by descending price within each category
      PARTITION BY category
      ORDER BY price DESC
    ) AS rnk
  FROM products
) AS tmp
WHERE rnk <= 2  -- keep top 2 ranks; ties may yield more than 2 rows
ORDER BY CASE category
  WHEN 'Appliances' THEN 1
  WHEN 'Furniture' THEN 2
  END, rnk, price DESC, name;

/*
  Execution order:
  1. In the subquery, assign RANK by descending price within each category
  2. In the outer query, filter on rnk <= 2 (the top two ranks)
  */

Explanation (table transitions & key points) ▼

SELECT
  category, name, price, rnk
FROM (
  SELECT
    category, name, price,
    RANK() OVER(PARTITION BY category ORDER BY price DESC) AS rnk
  FROM products
) AS tmp
WHERE rnk <= 2
ORDER BY CASE category WHEN 'Appliances' THEN 1 WHEN 'Furniture' THEN 2 END, rnk, price DESC, name;

LEGEND ▼

Grouping keys / aggregation targets

Group classification

① Assign RANK() in the Subquery ▼

PARTITION BY category ORDER BY price DESCAssign ranks independently within each category.

1 / 3

category	name	price	rnk
Appliances	Refrigerator	120000	1
Appliances	Washing Machine	90000	2
Appliances	TV	80000	3
Furniture	Sofa	70000	1
Furniture	Bed	50000	2
Furniture	Table	30000	3

Ranking complete

LEARNING POINTS

Rewrite with a WITH clause (CTE): If a nested subquery is hard to read, write WITH ranked AS ( SELECT ... RANK() ... ) SELECT * FROM ranked WHERE rnk <= 2;. This gives you modern SQL that reads naturally from top to bottom.

▤ Top N by Category — Combining Partitions with RANK

category	name	price	RANK() PARTITION BY category ORDER BY price DESC	WHERE rnk <= 2
Appliances	Refrigerator	120,000	1	✓ Included
Appliances	Washing Machine	90,000	2	✓ Included
Appliances	TV	80,000	3	✗ Excluded
Furniture	Sofa	70,000	1 ← reset by partition	✓ Included
Furniture	Bed	50,000	2	✓ Included
Furniture	Table	30,000	3	✗ Excluded

Assign ranks 1–3 inside the Appliances partition, then restart at 1 in the Furniture partition.
PARTITION BY creates a rank within each category, not one overall rank across categories.

ANTI-PATTERNS

Forcing an alternative in an environment without window functions: MySQL 5.7 and earlier do not support window functions. Replacing this pattern with correlated subqueries makes the query extremely complex and expensive. Use window functions actively in a modern RDBMS environment.

QUESTION 10

Practical Review — Build a Monthly Trend Report with Window Functions

Running TotalMonth-over-Month ChangePractical ReportComprehensive Review

Background ▼

Production dashboards often place several aggregate metrics side by side in one query.
Bring together everything you have learned and use one SELECT to calculate current-month sales, running sales, previous-month sales, and the change from the previous month.

SELECT month_col,
       SUM(num_col)                                AS monthly,     -- Aggregate for the month
       SUM(SUM(num_col)) OVER (ORDER BY month_col) AS cumulative,  -- Running total of the aggregate
       LAG(SUM(num_col)) OVER (ORDER BY month_col) AS prev_month   -- Previous month's aggregate
FROM   table_name
GROUP BY month_col
ORDER BY month_col;

Aggregation runs first: Window functions are evaluated after GROUP BY aggregation, which is why an aggregate can be nested inside one, as in SUM(SUM(...)) OVER (...). The ORDER BY inside OVER can likewise only reference columns that exist after aggregation.

Problem ▼

From the monthly_sales table below, output these four values for each month:
1. amount (current-month sales)
2. running_total (running sales from January)
3. prev_amount (previous-month sales; January may be NULL)
4. diff_from_prev (change in sales from the previous month).

Input table

▸ monthly_sales

month	amount
Jan	100
Feb	120
Mar	150
Apr	130
May	180

Expected Output ▼

month	amount	running_total	prev_amount	diff_from_prev
Jan	100	100	NULL	NULL
Feb	120	220	100	20
Mar	150	370	120	30
Apr	130	500	150	-20
May	180	680	130	50

Model Answer ▼

SELECT
  month,
  amount,
  SUM(amount) OVER(ORDER BY CASE month WHEN 'Jan' THEN 1 WHEN 'Feb' THEN 2 WHEN 'Mar' THEN 3 WHEN 'Apr' THEN 4 WHEN 'May' THEN 5 END) AS running_total,
  LAG(amount) OVER(ORDER BY CASE month WHEN 'Jan' THEN 1 WHEN 'Feb' THEN 2 WHEN 'Mar' THEN 3 WHEN 'Apr' THEN 4 WHEN 'May' THEN 5 END) AS prev_amount,
  amount - LAG(amount) OVER(ORDER BY CASE month WHEN 'Jan' THEN 1 WHEN 'Feb' THEN 2 WHEN 'Mar' THEN 3 WHEN 'Apr' THEN 4 WHEN 'May' THEN 5 END) AS diff_from_prev

FROM monthly_sales
ORDER BY CASE month WHEN 'Jan' THEN 1 WHEN 'Feb' THEN 2 WHEN 'Mar' THEN 3 WHEN 'Apr' THEN 4 WHEN 'May' THEN 5 END;

/*
  Execution order:
  1. FROM monthly_sales
  2. Evaluate the window-function expressions in SELECT
  3. ORDER BY sorts the output
  */

Explanation (table transitions & key points) ▼

SELECT
  month, amount,
  SUM(amount) OVER(ORDER BY CASE month WHEN 'Jan' THEN 1 WHEN 'Feb' THEN 2 WHEN 'Mar' THEN 3 WHEN 'Apr' THEN 4 WHEN 'May' THEN 5 END) AS running_total,
  LAG(amount) OVER(ORDER BY CASE month WHEN 'Jan' THEN 1 WHEN 'Feb' THEN 2 WHEN 'Mar' THEN 3 WHEN 'Apr' THEN 4 WHEN 'May' THEN 5 END) AS prev_amount,
  amount - LAG(amount) OVER(ORDER BY CASE month WHEN 'Jan' THEN 1 WHEN 'Feb' THEN 2 WHEN 'Mar' THEN 3 WHEN 'Apr' THEN 4 WHEN 'May' THEN 5 END) AS diff_from_prev
FROM monthly_sales
ORDER BY CASE month WHEN 'Jan' THEN 1 WHEN 'Feb' THEN 2 WHEN 'Mar' THEN 3 WHEN 'Apr' THEN 4 WHEN 'May' THEN 5 END;

LEGEND ▼

Rows read / loaded

① Base Data ▼

FROM monthly_salesRead the table.

1 / 3

month	amount
Jan	100
Feb	120
Mar	150
Apr	130
May	180

5 rows read

LEARNING POINTS

Window functions are an exceptionally powerful tool: Their ability to reference the surrounding context—adjacent rows or an entire group—without collapsing rows greatly expands SQL's expressive power. SQL for production dashboard data marts almost always uses window functions. Mastering this mental model is one of the biggest steps from intermediate to advanced SQL.

Analytical engineering in practice: To offer perspectives such as “120% of last month's result, but perhaps still short of the cumulative target,” data engineers need to combine metrics from several angles—single-period values, running totals, moving averages, and more—in one table.

▤ Three Window Expressions per Row — Calculation Breakdown

month	amount	SUM OVER(logical month order) = running_total	LAG(amount) OVER(...) = prev_amount	amount − LAG() = diff_from_prev
Jan	100	100 [100]	NULL (first row)	NULL
Feb	120	220 [100+120]	100 ↑ Jan value	+20 120−100
Mar	150	370 [100+120+150]	120 ↑ Feb value	+30 150−120
Apr	130	500 [100+120+150+130]	150 ↑ Mar value	−20 130−150
May	180	680 [100+120+150+130+180]	130 ↑ Apr value	+50 180−130

The three window expressions are logically evaluated over their own windows for each row. The database optimizer decides the physical execution strategy.