SQL Window Functions — Applied Frame Clauses and Top-N

QUESTION 1

NTILE() — Divide Rows into N Rank Groups

NTILEORDER BYQuantile GroupsEven Distribution

Background ▼

NTILE(N) divides all rows into N groups as evenly as possible and assigns each row a group number from 1 to N. It is useful for quantile-based groups such as “customers in the top 25%” or “products in the bottom 10% by score.”

SELECT
  name, score,
  NTILE(4) OVER(ORDER BY score DESC) AS quartile
  -- Sort by score descending and divide all rows into four groups; the top 25% has quartile=1
FROM students;

When the rows do not divide evenly: The remainder is distributed one extra row at a time starting with the lowest-numbered group. For example, NTILE(4) over six rows gives two rows each to groups 1 and 2, and one row each to groups 3 and 4.

Problem ▼

From the sales_scores table below, return each sales representative’s name, score, and group number (tier) after dividing the rows into three groups by descending score.

The highest-scoring group is tier=1, and the lowest-scoring group is tier=3.

Input Table

▸ sales_scores

sales_rep	score
Alex	95
Blair	80
Casey	65
Drew	50
Emery	35
Flynn	20

Expected Output ▼

sales_rep	score	tier
Alex	95	1
Blair	80	1
Casey	65	2
Drew	50	2
Emery	35	3
Flynn	20	3

Model Answer ▼

SELECT
  sales_rep,
  score,
  NTILE(3) OVER(ORDER BY score DESC) AS tier  -- Divide into three groups by descending score (top group = tier 1)
FROM sales_scores
ORDER BY tier, score DESC;                    -- Sort by tier, then by descending score within each group
/*
  Execution order:
  1. FROM sales_scores          → retrieve 6 rows
  2. OVER(ORDER BY score DESC)  → sort by score descending
  3. NTILE(3)                   → divide evenly into three groups
  4. SELECT output                  → sort by tier, then score DESC
  */

Explanation (table transitions & key points) ▼

SELECT
  sales_rep, score,
  NTILE(3) OVER(ORDER BY score DESC) AS tier
FROM sales_scores
ORDER BY tier, score DESC;

LEGEND ▼

Rows read / loaded

① FROM ▼

FROM sales_scoresLoad the table. It contains scores for six sales representatives; at this point, the rows are neither ordered nor grouped.

1 / 4

sales_rep	score
Alex	95
Blair	80
Casey	65
Drew	50
Emery	35
Flynn	20

All 6 rows loaded

LEARNING POINTS

Practical applications:A common use case is classifying customers in RFM analysis as “high value (tier=1),” “middle (tier=2),” or “dormant (tier=3)” and tailoring marketing actions to each group. Using NTILE(10) for decile analysis also makes it easy to identify the top 10% customer segment.

The key difference from RANK():RANK() assigns the same rank to tied scores, while NTILE() still distributes tied rows mechanically across groups. Even if three people tie for first, NTILE(3) can place one in each group. Add a unique tiebreaker such as ORDER BY score DESC, sales_rep when tied-row assignment must be reproducible.

Behavior when the row count is not divisible by N (NTILE(4) over 6 rows)

◿ NTILE(4) — Divide 6 rows into 4 groups (remainder 2)

sales_rep	score	tier	Rows
Alex	95	1	2 rows (remainder +1)
Blair	80	1
Casey	65	2	2 rows (remainder +1)
Drew	50	2
Emery	35	3	1 row (no remainder)
Flynn	20	4	1 row (no remainder)

6 ÷ 4 = 1 remainder 2 → the first groups (tiers 1 and 2) receive one extra row

≡ Distribution rules

For R rows and N groups:
• Base size: floor(R / N)
• The first R mod N groups receive one extra row
• The remainder is always assigned starting with the first group

Example: NTILE(3) over 10 rows → 4 rows, 3 rows, 3 rows

ANTI-PATTERNS

NTILE without ORDER BY is nondeterministic:If ORDER BY is omitted as in NTILE(3) OVER(), row order depends on internal database processing and is not deterministic, so the same data can be divided differently across executions. Always specify ORDER BY for NTILE.

QUESTION 2

ROWS BETWEEN — Calculate a Moving Average with a Window Frame

ROWS BETWEENORDER BYMoving AverageWindow Frame

Background ▼

A frame clause inside a window function’s OVER() defines which range of rows contributes to the calculation for each current row.

AVG(revenue) OVER(
  ORDER BY sale_date
  ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  -- Calculate over the current row and the two preceding rows (up to three rows total)
)

Common frame specifications:
• ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW: first row through current row (running aggregate)
• ROWS BETWEEN 2 PRECEDING AND CURRENT ROW: two preceding rows plus current row (three-row moving average)
• With ORDER BY but no frame clause, the default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW (running aggregate)

Problem ▼

From the daily_sales table below, return each date’s revenue and its three-day moving average (moving_avg_3d).

Use at most three rows—the current row and two preceding rows—and display the average to one decimal place.
For the opening rows with fewer than three days of data, average only the rows that exist.

Input Table

▸ daily_sales

sale_date	revenue
04-01	100
04-02	200
04-03	150
04-04	300
04-05	250

Expected Output ▼

sale_date	revenue	moving_avg_3d
04-01	100	100.0
04-02	200	150.0
04-03	150	150.0
04-04	300	216.7
04-05	250	233.3

Model Answer ▼

SELECT
  sale_date,
  revenue,
  ROUND(
    AVG(revenue) OVER(
      ORDER BY sale_date
      ROWS BETWEEN 2 PRECEDING AND CURRENT ROW  -- Establish row order and the frame’s reference order
    ), 1                                        -- Frame = current row plus two preceding rows (up to three); opening rows use only available rows
  ) AS moving_avg_3d                            -- Round to one decimal place
FROM daily_sales
ORDER BY sale_date;

/*
  Execution order:
  1. FROM daily_sales                          → retrieve 5 rows
  2. OVER(ORDER BY sale_date)                  → establish ascending date order
  3. ROWS BETWEEN 2 PRECEDING AND CURRENT ROW  → define the moving-average frame
  4. ROUND(..., 1)                             → round
  5. SELECT output                                 → return the columns
  */

Explanation (table transitions & key points) ▼

SELECT
  sale_date, revenue,
  ROUND(
    AVG(revenue) OVER(
      ORDER BY sale_date
      ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ), 1
  ) AS moving_avg_3d
FROM daily_sales
ORDER BY sale_date;

LEGEND ▼

Rows read / loaded

① FROM ▼

FROM daily_salesLoad five days of sales data. The rows have not yet been ordered, and no frame has been defined.

1 / 4

sale_date	revenue
04-01	100
04-02	200
04-03	150
04-04	300
04-05	250

All 5 rows loaded

LEARNING POINTS

The default-frame trap:When ORDER BY is present, the default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, a running frame from the first row through the current row. If ROWS BETWEEN is omitted when you intend a moving average, you get a cumulative average over the entire period to date.

ROWS versus RANGE:ROWS defines a frame by physical row count. If ORDER BY values can tie, add a unique tiebreaker to make that order deterministic. RANGE defines a frame by value range, so rows tied on the ORDER BY value share the same frame. Results often match for mostly unique values such as dates, but differ for values with many ties, such as scores or amounts.

⇄ Results: no frame clause (cumulative aggregate) vs ROWS BETWEEN (moving average)

✗ No frame clause → cumulative average (not a moving average)

sale_date	revenue	cumulative_avg
04-01	100	100.0
04-02	200	150.0
04-03	150	150.0
04-04	300	187.5
04-05	250	200.0

On 04-04, this averages all four days from the beginning; it is not a moving average

✓ ROWS BETWEEN 2 PRECEDING AND CURRENT ROW → three-day moving average

sale_date	revenue	moving_avg_3d
04-01	100	100.0
04-02	200	150.0
04-03	150	150.0
04-04	300	216.7
04-05	250	233.3

On 04-04, this averages only the latest three days (04-02 through 04-04); the frame slides

QUESTION 3

PERCENT_RANK() — Calculate Percentile Rank Within a Group

PERCENT_RANKPARTITION BYPercentile RankRelative Position

Background ▼

PERCENT_RANK() returns a value from 0.0 to 1.0 that represents a row’s relative rank within its group. The similar CUME_DIST() instead returns the number of rows at or below the current value divided by the total row count, so its minimum is not zero.

PERCENT_RANK() OVER(
  PARTITION BY subject
  ORDER BY score ASC
) AS pct_rank

How the value is calculated: For a group with multiple rows, the formula is (rank - 1) / (row count - 1). With ascending order, the lowest score is 0.00 and a unique highest score is 1.00. Tied highest scores can remain below 1.00 because they share a rank. A one-row group returns 0.00.

Problem ▼

From the test_scores table below, return each student’s name, subject, score, and percentile rank within the subject (pct_rank).

Calculate each subject independently in ascending score order and display the 0.0–1.0 result to two decimal places.

Input Table

▸ test_scores

student_name	subject	score
Alex	Mathematics	90
Blair	Mathematics	75
Casey	Mathematics	60
Drew	English	85
Emery	English	70

Expected Output ▼

student_name	subject	score	pct_rank
Casey	Mathematics	60	0.00
Blair	Mathematics	75	0.50
Alex	Mathematics	90	1.00
Emery	English	70	0.00
Drew	English	85	1.00

Model Answer ▼

SELECT
  student_name,
  subject,
  score,
  ROUND(
    PERCENT_RANK() OVER(
      PARTITION BY subject
      ORDER BY score ASC    -- Calculate percentile rank independently for each subject
    )::numeric, 2          -- Cast to numeric and round to two decimal places
  ) AS pct_rank             -- Round to two decimal places
FROM test_scores
ORDER BY
  CASE subject WHEN 'Mathematics' THEN 1 WHEN 'English' THEN 2 END,
  score;

/*
  Execution order:
  1. FROM test_scores      → retrieve 5 rows
  2. PARTITION BY subject  → partition by subject
  3. ORDER BY score ASC    → sort each subject by score ascending
  4. PERCENT_RANK()        → apply to every row
  5. ROUND(..., 2)         → round to two decimal places
  6. SELECT output             → return columns in the explicit subject order, then by score
  */

Explanation (table transitions & key points) ▼

SELECT
  student_name, subject, score,
  ROUND(
    PERCENT_RANK() OVER(
      PARTITION BY subject
      ORDER BY score ASC
    )::numeric, 2
  ) AS pct_rank
FROM test_scores
ORDER BY
  CASE subject WHEN 'Mathematics' THEN 1 WHEN 'English' THEN 2 END,
  score;

LEGEND ▼

Rows read / loaded

① FROM ▼

FROM test_scoresLoad five rows containing two subjects: Mathematics (three students) and English (two students).

1 / 4

student_name	subject	score
Alex	Mathematics	90
Blair	Mathematics	75
Casey	Mathematics	60
Drew	English	85
Emery	English	70

All 5 rows loaded

LEARNING POINTS

Filtering by a top-percent threshold:Wrap this query in a subquery or CTE and apply WHERE pct_rank >= 0.8 to select rows at or above the 80th-percentile rank within each subject. This is a rank threshold, so ties and small groups can return more or fewer than exactly 20% of the rows. Unlike NTILE’s even allocation, PERCENT_RANK reports a relative position based on rank.

Difference from CUME_DIST():CUME_DIST() returns “rows at or below the current value ÷ total rows,” so its minimum is 1/N, never zero. PERCENT_RANK always has a minimum of 0.00, and tied scores receive the same value. Both span 0 to 1, but their meanings differ.

⊞ PERCENT_RANK vs CUME_DIST — English group (2 rows)

student_name	score	PERCENT_RANK (rank-1)/(N-1)	CUME_DIST At or below / N	Meaning
Emery	70	0.00	0.50	PR: lowest score is fixed at 0 / CD: 50% are at or below
Drew	85	1.00	1.00	PR: unique highest score is 1 / CD: 100% are at or below

ANTI-PATTERNS

PERCENT_RANK() is fixed at 0.00 for a one-row group:When a partition has only one row, the denominator would be (count - 1) = 0, so the database returns 0.00 rather than raising a division-by-zero error. Consider checking COUNT(*) OVER(PARTITION BY ...) first when the analysis requires multiple rows.

QUESTION 4

LAG() + CTE — Calculate Month-over-Month Growth

LAGCTEGrowth RateMoM Analysis

Background ▼

Combining the preceding row returned by LAG() with the current value lets you calculate rates of change such as month-over-month growth.

Growth-rate formula: (current month − previous month) ÷ previous month × 100

Repeating LAG() several times in the SELECT list can duplicate window-expression work and can hurt performance. Computing LAG once in a CTE (WITH clause) and calculating the growth rate in the outer query keeps the expression reusable and readable.

WITH base AS (
  SELECT sort_col, num_col,
    LAG(num_col) OVER(ORDER BY sort_col) AS prev_val
    -- Evaluate LAG once in the CTE and reuse it in the outer query
  FROM table_name
)
SELECT *, (num_col - prev_val) * 100.0 / NULLIF(prev_val, 0) AS rate_pct
FROM base;

Division by zero and NULL: When the preceding value used as the denominator is 0, the division raises an error, so the usual guard is to turn the denominator into NULL with NULLIF. On the first row there is no preceding row, so LAG returns NULL and anything computed from it is NULL too.

Problem ▼

From the monthly_revenue table below, return each month, revenue, previous month’s revenue (prev_revenue), and month-over-month growth percentage (growth_rate_pct).

Display the growth rate to one decimal place. NULL is expected for the first row, which has no previous month.

Input Table

▸ monthly_revenue

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

Expected Output ▼

month	revenue	prev_revenue	growth_rate_pct
2024-01	1000	NULL	NULL
2024-02	1200	1000	20.0
2024-03	1100	1200	-8.3
2024-04	1500	1100	36.4

Model Answer ▼

-- Calculate LAG() once in the CTE and reuse it for growth in the outer query
WITH base AS (
  SELECT
    month,
    revenue,
    LAG(revenue) OVER(ORDER BY month) AS prev_revenue  -- Previous row’s revenue; the first row is NULL
  FROM monthly_revenue
)
SELECT
  month,
  revenue,
  prev_revenue,
  ROUND(
    (revenue - prev_revenue) * 100.0
      / NULLIF(prev_revenue, 0), 1  -- Decimal division; NULL when prev is NULL or zero
  ) AS growth_rate_pct
FROM base
ORDER BY month;

/*
  Execution order:
  1. CTE (WITH base)                   → read monthly_revenue
  2. OVER(ORDER BY month)              → sort by month
  3. LAG(revenue)                      → attach the previous month’s revenue
  4. Outer (revenue - prev) * 100.0 / NULLIF(prev, 0)  → calculate MoM growth safely
  5. ROUND(..., 1)                     → round and return
  */

Explanation (table transitions & key points) ▼

WITH base AS (
  SELECT
    month, revenue,
    LAG(revenue) OVER(ORDER BY month) AS prev_revenue
  FROM monthly_revenue
)
SELECT
  month, revenue, prev_revenue,
  ROUND(
    (revenue - prev_revenue) * 100.0
    / NULLIF(prev_revenue, 0), 1
  ) AS growth_rate_pct
FROM base
ORDER BY month;

LEGEND ▼

Rows read / loaded

① FROM (inside CTE) ▼

FROM monthly_revenue (inside CTE base)Load monthly_revenue in the inner query of the CTE (WITH base AS ...). It contains four months of revenue.

1 / 4

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

All 4 rows loaded

LEARNING POINTS

Reuse a window result through a CTE:Writing LAG directly wherever it is needed produces repeated expressions such as (revenue - LAG(...) OVER(...)) * 100.0 / LAG(...) OVER(...). Calculating it once in a CTE avoids repetition, improves readability, and can avoid duplicate work. A CTE or subquery is a reliable practical pattern here.

Adapting the query for year-over-year growth:For gap-free monthly data with exactly one row per month, LAG(revenue, 12) references the same month one year earlier. Likewise, LAG(revenue, 52) can compare gap-free weekly data with the corresponding week. If periods can be missing or duplicated, join by an explicit date key or a calendar instead, because LAG offsets count rows, not elapsed time.

Handling prev_revenue = 0 with NULLIF

✗ Division by zero when prev_revenue is 0

-- A zero prev_revenue causes a division-by-zero error without NULLIF
ROUND((revenue - prev_revenue)
      * 100.0 / prev_revenue, 1)

✓ Prevent division by zero with NULLIF

-- NULLIF(prev_revenue, 0) converts zero to NULL
-- Division by NULL returns NULL instead of raising an error
ROUND((revenue - prev_revenue)
  * 100.0 / NULLIF(prev_revenue, 0), 1)

QUESTION 5

ROW_NUMBER() + Subquery — Return the Top N Rows per Category

ROW_NUMBERPARTITION BYTop-NProduction Pattern

Background ▼

A window-function result such as a row number cannot be filtered in WHERE at the same query level. WHERE is evaluated before window functions, so use a subquery or CTE first.

-- ✗ Error: a window-function result cannot be used directly in WHERE
SELECT * FROM t
WHERE ROW_NUMBER() OVER(...) <= 2;  -- SQL Error!

-- ✓ Wrap the calculation in a subquery, then filter in WHERE
SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER(...) AS rn FROM t
) ranked
WHERE rn <= 2;

Why a tie-breaker is required: ROW_NUMBER() always assigns distinct numbers to tied rows, but which of them comes first is undefined while the ordering stays equal. Append a unique column to ORDER BY so that the rows selected are deterministic.

Problem ▼

From the product_sales table below, return only the top two products by sales amount in each category.

Use ROW_NUMBER(), not DENSE_RANK, to assign a unique row number when amounts tie within a category. Break ties by product name so the selected rows are deterministic.

Input Table

▸ product_sales

category	product	amount
Beverages	Coffee	4000
Beverages	Tea	2800
Beverages	Juice	1500
Food	Apple	3000
Food	Banana	2500
Food	Grapes	1800

Expected Output ▼

category	product	amount
Beverages	Coffee	4000
Beverages	Tea	2800
Food	Apple	3000
Food	Banana	2500

Model Answer ▼

SELECT
  category,
  product,
  amount
FROM (
  -- Evaluate the window function and assign row numbers inside the subquery
  SELECT
    category,
    product,
    amount,
    ROW_NUMBER() OVER(
      PARTITION BY category
      ORDER BY amount DESC, product ASC  -- Number rows deterministically within each category
    ) AS rn                  -- The tiebreaker makes row numbers deterministic even for equal amounts
  FROM product_sales
) ranked
WHERE rn <= 2                -- Filter by the already-calculated rn in the outer query
ORDER BY
  CASE category WHEN 'Beverages' THEN 1 WHEN 'Food' THEN 2 END,
  amount DESC, product ASC;
/*
  Execution order:
  1. Subquery FROM product_sales  → retrieve 6 rows
  2. PARTITION BY category     → partition by category
  3. ORDER BY amount DESC, product ASC  → sort by amount DESC, product ASC
  4. ROW_NUMBER()              → assign row numbers
  5. Outer WHERE rn <= 2         → keep the top two
  6. SELECT / ORDER BY         → return category, product, and amount in explicit order
  */

Explanation (table transitions & key points) ▼

SELECT
  category, product, amount
FROM (
  SELECT
    category, product, amount,
    ROW_NUMBER() OVER(
      PARTITION BY category
      ORDER BY amount DESC, product ASC
    ) AS rn
  FROM product_sales
) ranked
WHERE rn <= 2
ORDER BY
  CASE category WHEN 'Beverages' THEN 1 WHEN 'Food' THEN 2 END,
  amount DESC, product ASC;

LEGEND ▼

Rows read / loaded

① FROM (inside subquery) ▼

FROM product_sales (inside subquery)Load product_sales inside the subquery. It contains six rows in two categories: Beverages and Food.

1 / 5

category	product	amount
Beverages	Coffee	4000
Beverages	Tea	2800
Beverages	Juice	1500
Food	Apple	3000
Food	Banana	2500
Food	Grapes	1800

All 6 rows loaded

LEARNING POINTS

Top N per group is an essential pattern:Production examples include the top three products per store, five latest events per user, or two highest-paid employees per department. The tiebreaker must be unique; if product names can repeat, append a stable key such as product_id in production.

The QUALIFY clause in BigQuery and Snowflake:Some data warehouses support QUALIFY, which filters window results without a subquery: SELECT * FROM t QUALIFY ROW_NUMBER() OVER(PARTITION BY category ORDER BY amount DESC, product ASC) <= 2. MySQL and PostgreSQL do not support QUALIFY, so choose the form appropriate for your database.

⇄ ROW_NUMBER vs DENSE_RANK — behavior when amounts tie

ROW_NUMBER — unique numbers even for ties

product	amount	rn	rn <= 2?
Coffee	4000	1	✓ Kept
Tea	2800	2	✓ Kept
Tea (black)	2800	3	✗ Excluded

The tied rows receive rn=2 and rn=3 by product name → returns exactly N rows when enough rows exist

DENSE_RANK — ties share a number (the result can exceed N rows)

product	amount	dr	dr <= 2?
Coffee	4000	1	✓ Kept
Tea	2800	2	✓ Kept
Tea (black)	2800	2	✓ Kept (two rows tied)

When several rows tie for second, the result exceeds N rows. Choose ROW_NUMBER to cap the result at N rows, or DENSE_RANK to include every tied row.

ANTI-PATTERNS

Window functions are evaluated after WHERE:In SQL’s logical evaluation order, FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY, window functions are evaluated after WHERE as part of producing the SELECT result. Writing ROW_NUMBER() OVER(...) <= 2 directly in WHERE therefore raises an error. Wrap it in a subquery or CTE and reference the result from the outer query.