Window Functions — Learn NTILE, PERCENT_RANK, CTEs, LAG + LEAD, and Moving Averages in Practice

QUESTION 6

NTILE() — Segment Users into Quartiles by Purchase Amount

NTILENumbering FunctionSegmentation

Background ▼

NTILE(N) divides all rows into up to N groups whose sizes differ by at most one row (buckets) and assigns each row a group number in the range 1 through N.
It belongs to the same family of numbering functions as ROW_NUMBER, RANK, and DENSE_RANK.

NTILE(4) OVER(ORDER BY total_purchase DESC)
-- Sort by purchase amount descending and divide into four groups
-- 1 = highest-value group (top 25%)
-- 4 = lowest-value group (bottom 25%)

When the rows do not divide evenly: If the row count is not divisible by N, the remainder is distributed one extra row at a time starting with the first group. For example, six rows divided into four groups gives two rows each to tiers 1 and 2, and one row each to tiers 3 and 4.

Problem ▼

Using the customers table below, classify every user into four tiers in descending order of total purchase amount (total_purchase).
Divide the rows so tier 1 is the highest-value group and tier 4 is the lowest-value group.

Input table

▸ customers

user_id	name	total_purchase
U1	Tanaka	48000
U2	Suzuki	12000
U3	Sato	95000
U4	Takahashi	33000
U5	Ito	71000
U6	Watanabe	8000

Expected Output ▼

user_id	name	total_purchase	tier
U3	Sato	95000	1
U5	Ito	71000	1
U1	Tanaka	48000	2
U4	Takahashi	33000	2
U2	Suzuki	12000	3
U6	Watanabe	8000	4

Six rows divided into four groups gives a quotient of 1 and a remainder of 2. The first two groups (tiers 1 and 2) receive two rows each; the remaining groups (tiers 3 and 4) receive one row each.

Model Answer ▼

SELECT
  user_id,
  name,
  total_purchase,
  NTILE(4) OVER(
    ORDER BY total_purchase DESC
  ) AS tier                       -- Sort by purchase amount descending and divide into four nearly equal groups
FROM customers
ORDER BY total_purchase DESC;

/*
  Execution order:
  1. FROM customers  → retrieve all six rows
  2. NTILE(4) OVER(ORDER BY total_purchase DESC) evaluate
  3. ORDER BY total_purchase DESC to sort the output
  */

Explanation (table transitions & key points) ▼

SELECT
  user_id, name, total_purchase,
  NTILE(4) OVER(
    ORDER BY total_purchase DESC
  ) AS tier
FROM customers
ORDER BY total_purchase DESC;

LEGEND ▼

Rows read / loaded

① FROM ▼

FROM customersRead the table in its original row order before ORDER BY.

1 / 3

user_id	name	total_purchase
U1	Tanaka	48000
U2	Suzuki	12000
U3	Sato	95000
U4	Takahashi	33000
U5	Ito	71000
U6	Watanabe	8000

All 6 rows loaded

LEARNING POINTS

Choosing between NTILE and RANK: RANK gives equal values the same rank, so the number of users associated with each rank may be uneven. NTILE instead distributes rows across up to N groups whose sizes differ by at most one row, making it appropriate when all users must be divided into four nearly equal groups.

Segmentation in practice: CRM systems use NTILE(4) to define purchase-value tiers 1 through 4 and automatically assign differentiated programs, such as dedicated support for VIP customers in tier 1 and discount coupons for tier 4. Because NTILE mechanically divides by row count, it is a convenient starting point for analytics dashboards.

Remainder distribution: When the row count is not divisible by N in NTILE(N), a remainder of r means only the first r groups receive one extra row. Dividing 10 rows into three groups gives tier 1 = 4 rows, tier 2 = 3 rows, and tier 3 = 3 rows: 10 ÷ 3 has a quotient of 3 and a remainder of 1, so only tier 1 gets one extra row.

NTILE(4) DISTRIBUTION — HOW SIX ROWS ARE DIVIDED INTO FOUR GROUPS

Temporary sort position	name	total_purchase	NTILE(4)	Rows in group
1st	Sato	95,000	1	← 6 ÷ 4 has remainder 2; first 2 groups get +1 row
2nd	Ito	71,000	1	tier 1 = 2 rows
3rd	Tanaka	48,000	2	← The second group also gets +1 row
4th	Takahashi	33,000	2	tier 2 = 2 rows
5th	Suzuki	12,000	3	tier 3 = 1 row (no remainder row)
6th	Watanabe	8,000	4	tier 4 = 1 row (no remainder row)

Calculation: 6 rows ÷ 4 groups = quotient 1, remainder 2 → the first two groups (tiers 1 and 2) have 1 + 1 = 2 rows; the remaining groups (tiers 3 and 4) have 1 row.

ANTI-PATTERNS

When N in NTILE(N) exceeds the row count: Applying NTILE(10) to a three-row table produces only tiers 1 through 3; tiers 4 through 10 are empty, and no error occurs. N in NTILE(N) is the maximum desired number of groups; the number of nonempty groups cannot exceed the number of data rows.

NTILE without ORDER BY: Without ORDER BY, the database engine may process rows in an arbitrary order, so group assignments can change between executions of the same query. Always specify a meaningful ORDER BY column.

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 7

PERCENT_RANK() — Calculate a Score's Position in the Overall Distribution

PERCENT_RANKRelative RankPercentile

Background ▼

PERCENT_RANK() returns each row's relative position within the full set as a decimal from 0.0 to 1.0.
For a set with multiple rows, the minimum row is 0.0 (0th percentile) and the maximum row is 1.0 (100th percentile). A single-row set returns 0.0.

PERCENT_RANK() OVER(ORDER BY score)
-- Formula: (rank - 1) / (total_rows - 1)
-- Example: rank 3 of 5 → (3-1)/(5-1) = 0.5 = 50th percentile

Difference from CUME_DIST: CUME_DIST() returns the proportion of rows whose value is less than or equal to the current row, producing a value greater than 0 and at most 1. PERCENT_RANK starts at 0 by normalizing rank as (rank - 1) / (total_rows - 1). Their values differ even without ties; when ties occur, the difference in how ranks are handled becomes even more important.

Problem ▼

Using the exam_scores table below, calculate the percentile position of each employee's test score within the full set.
Display the result as a percentage with one decimal place and order the output by score ascending.

Input table

▸ exam_scores

employee_id	name	score
E1	Tanaka	70
E2	Suzuki	90
E3	Sato	60
E4	Takahashi	85
E5	Ito	75

Expected Output ▼

name	score	percentile
Sato	60	0.0%
Tanaka	70	25.0%
Ito	75	50.0%
Takahashi	85	75.0%
Suzuki	90	100.0%

PERCENT_RANK = (rank-1)/(n-1). With five rows, the denominator is 4. Tanaka has rank 2, so (2-1)/4 = 0.25 → 25.0%.

Model Answer ▼

SELECT
  name,
  score,
  ROUND(
    PERCENT_RANK() OVER(ORDER BY score) * 100,  -- PERCENT_RANK() returns 0.0–1.0; multiply by 100 to convert to percent
    1                                           -- Round to one decimal place
  ) AS percentile
FROM exam_scores
ORDER BY score;                                 -- Output in ascending score order

/*
  Formula: PERCENT_RANK = (rank - 1) / (total_rows - 1)
  Per-row calculation:
    Sato score=60: rank=1 → (1-1)/(5-1) = 0/4 = 0.0  →  0.0%
    Tanaka score=70: rank=2 → (2-1)/(5-1) = 1/4 = 0.25 → 25.0%
    Ito score=75: rank=3 → (3-1)/(5-1) = 2/4 = 0.5  → 50.0%
    Takahashi score=85: rank=4 → (4-1)/(5-1) = 3/4 = 0.75 → 75.0%
    Suzuki score=90: rank=5 → (5-1)/(5-1) = 4/4 = 1.0  →100.0%

  Execution order:
  1. FROM exam_scores
  2. PERCENT_RANK() OVER(ORDER BY score): calculate relative positions in ascending score order
  3. × 100 → ROUND(..., 1) to convert to a percentage
  4. ORDER BY score for output
*/

Explanation (table transitions & key points) ▼

SELECT
  name, score,
  ROUND(
    PERCENT_RANK() OVER(ORDER BY score) * 100,
    1
  ) AS percentile
FROM exam_scores
ORDER BY score;

LEGEND ▼

Rows read / loaded

① FROM ▼

FROM exam_scoresLoad all five rows.

1 / 3

employee_id	name	score
E1	Tanaka	70
E2	Suzuki	90
E3	Sato	60
E4	Takahashi	85
E5	Ito	75

All 5 rows loaded

LEARNING POINTS

Choosing between PERCENT_RANK and CUME_DIST: PERCENT_RANK normalizes rank as (rank - 1) / (n - 1) (for multiple rows, minimum = 0 and maximum = 1). CUME_DIST reports the proportion of rows whose value is less than or equal to the current row (maximum = 1, minimum > 0). Even without ties, their denominators and reference points differ; ties make the distinction more pronounced. PERCENT_RANK expresses a rank's relative position, while CUME_DIST expresses what percentage of values are at or below the current value.

Practical use — standardized-score and percentile analysis: PERCENT_RANK and NTILE can be combined to produce database metrics analogous to standardized admissions scores. To extract products in the highest 10% of all products, a common pattern is WHERE PERCENT_RANK() OVER(...) >= 0.9, with the window value calculated in a subquery before filtering.

PERCENT_RANK VS CUME_DIST — COMPARISON WITHOUT TIES

name	score	PERCENT_RANK() = (rank-1)/(n-1)	CUME_DIST() = rank/n	Interpretation
Sato	60	0.0 (0%)	0.2 (20%)	CUME_DIST counts values ≤ 60: 1 row = 1/5
Tanaka	70	0.25 (25%)	0.4 (40%)	Values ≤ 70: 2 rows = 2/5
Ito	75	0.5 (50%)	0.6 (60%)	Values ≤ 75: 3 rows = 3/5
Takahashi	85	0.75 (75%)	0.8 (80%)
Suzuki	90	1.0 (100%)	1.0 (100%)	Both functions return 1.0 for the maximum value

Without ties, the ith row in ascending order has PERCENT_RANK (i-1)/(n-1) and CUME_DIST i/n. The difference varies by row: at the minimum, PERCENT_RANK = 0 and CUME_DIST = 1/n; at the maximum, both equal 1.

ANTI-PATTERNS

Using the wrong ORDER BY direction: With ORDER BY score ASC, the lowest score is 0.0; with ORDER BY score DESC, the highest score is 0.0. To express a top percentage, you may use PERCENT_RANK with ORDER BY DESC, or calculate an upper-tail percentile as 1 - PERCENT_RANK. Choose the direction deliberately for the intended meaning.

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 8

WITH Clause (CTE) — Modularize Window Functions for Running Totals and Shares

CTE / WITH ClauseSUM OVERRunning Total / ShareReadability

Background ▼

Writing a long query containing window functions as nested subqueries can make it difficult to read. A WITH clause (CTE: Common Table Expression) provides a modern top-to-bottom structure: first build an intermediate table with window functions, then use it for further calculations.

WITH cte_name AS (
  -- ① Build an intermediate table with a window function
  SELECT ..., SUM(col) OVER(...) AS running_total
  FROM tbl
)
-- ② Reference the intermediate table for further calculations
SELECT ..., running_total / total * 100 AS pct
FROM cte_name;

Meaning of an empty OVER(): When nothing is specified inside OVER, as in SUM(revenue) OVER(), the function aggregates across all rows, producing the table-wide total. This lets one CTE calculate both the grand total and the running total.

Problem ▼

Using the monthly_revenue table below and a WITH clause, output each month's revenue together with the following two calculated values.
1. running_total — cumulative revenue from January
2. monthly_pct — that month's share of total revenue (percent, one decimal place).

Input table

▸ monthly_revenue

month	revenue
Jan	200
Feb	250
Mar	180
Apr	320
May	290

Expected Output ▼

month	revenue	running_total	monthly_pct
Jan	200	200	16.1%
Feb	250	450	20.2%
Mar	180	630	14.5%
Apr	320	950	25.8%
May	290	1240	23.4%

Grand total = 200 + 250 + 180 + 320 + 290 = 1240. Jan: 200 ÷ 1240 × 100 = 16.1%.

Model Answer ▼

WITH monthly_agg AS (
  SELECT
    month,
    revenue,
    SUM(revenue) OVER(ORDER BY month) AS running_total,     -- ① Running revenue: add values in month order
    SUM(revenue) OVER() AS total_revenue                    -- ② Grand total: empty OVER() = all rows = table total
  FROM monthly_revenue
)
SELECT
  month,
  revenue,
  running_total,
  ROUND(revenue * 100.0 / total_revenue, 1) AS monthly_pct  -- Use total_revenue from the CTE to calculate the percentage share
FROM monthly_agg
ORDER BY month;

/*
  Execution order:
  1. Evaluate FROM monthly_revenue inside CTE monthly_agg
  2. Evaluate both window functions inside the CTE
  3. Use total_revenue in the outer SELECT to calculate monthly_pct, while omitting total_revenue itself from the output
  4. ORDER BY month for output
  */

Explanation (table transitions & key points) ▼

WITH monthly_agg AS (
  SELECT month, revenue,
    SUM(revenue) OVER(ORDER BY month) AS running_total,
    SUM(revenue) OVER() AS total_revenue
  FROM monthly_revenue
)
SELECT
  month, revenue,
  running_total,
  ROUND(revenue * 100.0 / total_revenue, 1)
  AS monthly_pct
FROM monthly_agg
ORDER BY month;

LEGEND ▼

Columns / keys under evaluation

① Define CTE (WITH Clause) ▼

WITH monthly_agg AS ( SELECT ... FROM monthly_revenue )A CTE is a named, temporary query result. Here, two window functions—SUM OVER(ORDER BY) and SUM OVER()—are calculated from monthly_revenue to build the intermediate table monthly_agg.

1 / 3

month	revenue	running_total (SUM OVER ORDER BY)	total_revenue (SUM OVER())
Jan	200	200	1240
Feb	250	450	1240
Mar	180	630	1240
Apr	320	950	1240
May	290	1240	1240

CTE intermediate table complete (5 rows)

LEARNING POINTS

Readability compared with subqueries: The same logic written as subqueries becomes deeply nested, such as SELECT ... FROM (SELECT ... FROM ...) t WHERE .... CTEs read from top to bottom and are a de facto standard for production queries with multiple aggregation steps. They are also easier to review.

Understand empty OVER(): SUM(col) OVER() means a partition containing all rows and a frame containing all rows. It attaches the same value to every row, a standard pattern for calculating ratios to a grand total. Unlike GROUP BY, it preserves the individual rows.

Chain multiple CTEs: Multiple CTEs can be defined in sequence, separated by commas: WITH cte1 AS (...), cte2 AS (SELECT ... FROM cte1). BI tools and data-mart pipelines often use three layers: preprocessing CTE → aggregation CTE → final output.

OVER() WITH AND WITHOUT ARGUMENTS — SIDE-BY-SIDE COMPARISON

Syntax	Meaning	Jan	Feb	Mar	Apr	May
SUM OVER()	Grand total (fixed)	1240	1240	1240	1240	1240
SUM OVER(ORDER BY month)	Running total (increases by row)	200	450	630	950	1240

On the final row (May), both values are 1240. Putting both values on the same CTE row also makes it possible to calculate running_total / total_revenue, the share of total revenue accumulated through May.

ANTI-PATTERNS

Using a window result in WHERE inside the same CTE: A CTE still cannot reference a window-function result directly in the WHERE clause of the same SELECT. WHERE SUM(revenue) OVER() > 1000 is invalid. Use two layers: calculate the window function in a CTE, then filter in the outer query, following the Q8 pattern.

Beware of integer division: Databases such as PostgreSQL truncate integer division, so revenue * 100 / total_revenue loses the fractional part. To make the intent clear across databases, use a decimal literal as in revenue * 100.0 / total_revenue, or convert to a decimal with CAST(revenue AS DECIMAL) before dividing.

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 9

LAG() + LEAD() Together — Automatically Detect Peak Days by Comparing Adjacent Values

LAG + LEADAdjacent ComparisonCASE WHENAnomaly Detection

Background ▼

Using LAG (the previous row) and LEAD (the next row) together enables a three-point comparison with adjacent values. This is a common time-series pattern for detecting peaks (local maxima), valleys (local minima), and sudden changes.

LAG(amount)  OVER(ORDER BY date)  AS prev_amount  -- Value from one row earlier
LEAD(amount) OVER(ORDER BY date)  AS next_amount  -- Value from one row later

-- Compare three points with CASE WHEN → detect a peak
CASE
  WHEN amount > prev_amount AND amount > next_amount THEN 'PEAK'
END

Handling the first and last rows: LAG returns NULL for the first row, and LEAD returns NULL for the last row. A comparison with NULL (> NULL) is UNKNOWN; because CASE WHEN does not treat UNKNOWN as true, the first and last rows are automatically excluded from peak detection. This is the intended behavior.

Problem ▼

Using the daily_sales table below, output the previous day's sales as prev_amount and the next day's sales as next_amount beside each date. In the is_peak column, output 'PEAK' when sales exceed both adjacent days; otherwise output the empty string ''.

Input table

▸ daily_sales

date	amount
04-01	80
04-02	160
04-03	110
04-04	190
04-05	130
04-06	175

Expected Output ▼

date	amount	prev_amount	next_amount	is_peak
04-01	80	NULL	160
04-02	160	80	110	PEAK
04-03	110	160	190
04-04	190	110	130	PEAK
04-05	130	190	175
04-06	175	130	NULL

Apr-02: 160 > 80 and 160 > 110 → PEAK. Apr-06: because LEAD is NULL, the comparison is UNKNOWN and does not satisfy CASE WHEN → not PEAK.

Model Answer ▼

SELECT
  date,
  amount,
  LAG(amount)  OVER(ORDER BY date) AS prev_amount,
  LEAD(amount) OVER(ORDER BY date) AS next_amount,   -- Previous row's sales (NULL on the first row)
  CASE                                               -- Next row's sales (NULL on the last row)
    WHEN amount > LAG(amount)  OVER(ORDER BY date)   -- Mark PEAK only when the value exceeds both adjacent days
     AND  amount > LEAD(amount) OVER(ORDER BY date)
    THEN 'PEAK'
    ELSE ''
  END AS is_peak

FROM daily_sales
ORDER BY date;

/*
  Execution order:
  1. FROM daily_sales   → retrieve all six rows
  2. Attach LAG and LEAD together → retrieve adjacent values
  3. Use CASE WHEN for a three-point comparison → detect peaks
  4. ORDER BY date      → output
  */

Explanation (table transitions & key points) ▼

SELECT
  date, amount,
  LAG(amount) OVER(ORDER BY date) AS prev_amount,
  LEAD(amount) OVER(ORDER BY date) AS next_amount,
  CASE
    WHEN amount > LAG(amount) OVER(ORDER BY date)
     AND amount > LEAD(amount) OVER(ORDER BY date)
    THEN 'PEAK'
    ELSE ''
  END AS is_peak
FROM daily_sales
ORDER BY date;

LEGEND ▼

Rows read / loaded

① FROM ▼

FROM daily_salesLoad all six rows.

1 / 3

date	amount
04-01	80
04-02	160
04-03	110
04-04	190
04-05	130
04-06	175

All 6 rows loaded

LEARNING POINTS

Comparisons with NULL are UNKNOWN—a useful design property: The first row has LAG = NULL, so amount > NULL is UNKNOWN. CASE WHEN does not treat it as true, automatically excluding that row from peak detection. This matches the intuitive rule that endpoints cannot be peaks, so no COALESCE adjustment is needed. If endpoints should be eligible, supply a default such as COALESCE(LAG(...), -1) so the comparison can succeed.

The LAG/LEAD offset argument: LAG(amount, 2) retrieves the value two rows earlier, while LEAD(amount, 3) retrieves the value three rows later. The third argument specifies a default: LAG(amount, 1, 0) returns 0 instead of NULL.

Practical use—KPI anomaly detection: In daily-sales or server-metric monitoring, this pattern detects rows that deviate from the average of adjacent values by at least X%. To identify periods that rise on consecutive days, chain comparisons such as CASE WHEN amount > LAG(amount)... AND LAG(amount) > LAG(amount,2)... THEN 'RISING'.

LAG + LEAD THREE-POINT COMPARISON — HOW EACH ROW REFERENCES BOTH NEIGHBORS

date	amount	prev (LAG)	next (LEAD)	prev comparison	next comparison	is_peak
04-01	80	NULL	160	80 > NULL → UNKNOWN		—
04-02	160	80	110	160 > 80 ✓	160 > 110 ✓	PEAK
04-03	110	160	190	110 > 160 ✗		—
04-04	190	110	130	190 > 110 ✓	190 > 130 ✓	PEAK
04-05	130	190	175	130 > 190 ✗		—
04-06	175	130	NULL	175 > 130 ✓	175 > NULL → UNKNOWN	—

PEAK requires both adjacent comparisons to be TRUE. Comparisons with NULL at the first and last rows are UNKNOWN and do not satisfy CASE WHEN, safely excluding the endpoints.

ANTI-PATTERNS

Inefficient repetition of window functions inside CASE WHEN: This query writes LAG(amount) OVER(ORDER BY date) and LEAD(amount) OVER(ORDER BY date) twice each: once as a SELECT column and once inside CASE WHEN. If performance matters, calculate the window functions first in a CTE and reference those columns in an outer WHERE or CASE to avoid duplicate window expressions.

Missing plateau peaks: Because amount > LAG is a strict comparison, a value equal to the previous day's sales is neither a PEAK nor a valley. To treat ties as peaks, change it to >=, with the tradeoff that every row in a run of equal values may become a PEAK. Match the operator to the requirement.

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 10

PARTITION BY × Frame Clause — Calculate an Independent Moving Average per Store

PARTITION BYROWS BETWEENMoving Average by GroupCommon in Practice

Background ▼

Combining PARTITION BY (group partitioning) with ROWS BETWEEN (frame specification) creates a window that moves only within each group. This is one of the most practical window-function patterns.

AVG(amount) OVER(
  PARTITION BY store_id       -- Separate the window by store
  ORDER BY date               -- Order by date within each partition
  ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
  -- One preceding row + current row = two-day average
)

The reset effect of PARTITION BY: Each time the partition changes, the frame always resets and starts from the first row of the new partition. This automatically prevents cross-partition contamination from accidentally including another store's rows.

Problem ▼

Using the store_daily_sales table below, calculate an independent moving average over the latest two rows (moving_avg_2d) for each store (store_id).
The window must reset whenever the store changes.

Input table

▸ store_daily_sales

store_id	date	amount
S1	04-01	100
S1	04-02	140
S1	04-03	120
S1	04-04	160
S2	04-01	200
S2	04-02	180
S2	04-03	220
S2	04-04	190

Expected Output ▼

store_id	date	amount	moving_avg_2d
S1	04-01	100	100.0
S1	04-02	140	120.0
S1	04-03	120	130.0
S1	04-04	160	140.0
S2	04-01	200	200.0
S2	04-02	180	190.0
S2	04-03	220	200.0
S2	04-04	190	205.0

S2 on Apr-01 is the first row in the S2 partition, so its average uses only one row and equals 200.0. The value from S1 on Apr-04 is not carried over.

Model Answer ▼

SELECT
  store_id,
  date,
  amount,
  AVG(amount) OVER(
    PARTITION BY store_id
    ORDER BY date                             -- Keep each store's window independent; rows from other stores cannot enter
    ROWS BETWEEN 1 PRECEDING AND CURRENT ROW  -- Sort by date within the partition
  ) AS moving_avg_2d                          -- Average the preceding row and current row, at most two rows
FROM store_daily_sales
ORDER BY store_id, date;

/*
  Execution order:
  1. FROM store_daily_sales        → retrieve all eight rows
  2. PARTITION BY store_id         → partition by store
  3. ORDER BY date + ROWS BETWEEN  → calculate the moving average within each partition
  4. ORDER BY store_id, date       → output
  */

Explanation (table transitions & key points) ▼

SELECT
  store_id, date, amount,
  AVG(amount) OVER(
    PARTITION BY store_id
    ORDER BY date
    ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
  ) AS moving_avg_2d
FROM store_daily_sales
ORDER BY store_id, date;

LEGEND ▼

Rows read / loaded

① FROM ▼

FROM store_daily_salesLoad all eight rows, containing both S1 and S2.

1 / 3

store_id	date	amount
S1	04-01	100
S1	04-02	140
S1	04-03	120
S1	04-04	160
S2	04-01	200
S2	04-02	180
S2	04-03	220
S2	04-04	190

All 8 rows loaded

LEARNING POINTS

What happens without PARTITION BY: If PARTITION BY is removed and only ORDER BY store_id, date ROWS BETWEEN 1 PRECEDING AND CURRENT ROW remains, S1 Apr-04 (160) and S2 Apr-01 (200) enter the same frame, producing an unintended cross-store average. With only ORDER BY date, store order within the same date is also nondeterministic. Always use PARTITION BY when one table contains multiple entities such as stores, products, or users.

The PARTITION BY × ORDER BY × ROWS BETWEEN trio: The complete form, OVER(PARTITION BY x ORDER BY y ROWS BETWEEN n PRECEDING AND CURRENT ROW), is the full window-function pattern. Once you can read it, you can understand most production window-function queries.

Use in BigQuery and Redshift: Data warehouses routinely calculate seven-day moving averages by store and product for dashboards. A clause such as PARTITION BY store_id, product_id ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW can efficiently process hundreds of thousands of rows.

WITH VS WITHOUT PARTITION BY — S1→S2 BOUNDARY WITH ORDER BY STORE_ID, DATE

store_id	date	amount	Without PARTITION BY ORDER BY store_id, date (all rows)	PARTITION BY store_id 1 PRECEDING (within store only)
S1	04-03	120	AVG(140, 120) = 130 ✓	AVG(140, 120) = 130 ✓
S1	04-04	160	AVG(120, 160) = 140 ✓	AVG(120, 160) = 140 ✓
S2	04-01	200	AVG(160, 200) = 180 ✗ ← Incorrectly references S1 Apr-04!	AVG(200) = 200 ✓ ← Reset; independent of S1
S2	04-02	180	AVG(200, 180) = 190 ✓	AVG(200, 180) = 190 ✓

For this comparison, ordering the full set by store_id and date makes S2 Apr-01 the boundary. Without PARTITION BY, S1 Apr-04's 160 is treated as the preceding row for S2 Apr-01, producing the incorrect average of 180. With PARTITION BY store_id, the frame correctly resets at the first S2 row.

ANTI-PATTERNS

Processing multiple groups without PARTITION BY: This is one of the most common bugs. Forgetting PARTITION BY in a table containing multiple stores, users, or products creates frames that cross group boundaries and silently produces incorrect results without an error. Unlike GROUP BY, filtering to one store_id with WHERE merely prevents analysis of the other stores.

Confusing RANGE BETWEEN with ROWS BETWEEN: RANGE in RANGE BETWEEN 1 PRECEDING AND CURRENT ROW defines the frame by value range. With a date ORDER BY, dialect-specific interval syntax and peer handling can cause same-day or nearby rows to be grouped unexpectedly. When the frame must contain a precise number of rows, use ROWS BETWEEN.

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.