SQL Window Functions — Basics of NTILE and PERCENT

QUESTION 6

NTILE() — Divide Data Evenly into N Groups

NTILEORDER BYRFM Analysis / Ranking

Background ▼

NTILE(N) follows the order defined by ORDER BY, divides the rows as evenly as possible into up to N buckets, and assigns each row a number from 1 through N. It is useful for classifying customers by purchase amount into top, middle, and bottom tiers.

NTILE(3) OVER(
  ORDER BY total_amount DESC
) AS tier

When the rows do not divide evenly: Extra rows are assigned one at a time to the lower-numbered buckets. Five rows divided into three buckets therefore produce bucket sizes of 2, 2, and 1. If there are fewer rows than N, only as many buckets as rows are created.

Problem ▼

From the customer_sales table below, divide customers into three groups (1, 2, 3) in descending order of sales (total_amount) and output the group number as tier.

Source Table

▸ customer_sales

customer_id	total_amount
C1	50000
C2	40000
C3	30000
C4	20000
C5	10000

Expected Output ▼

customer_id	total_amount	tier
C1	50000	1
C2	40000	1
C3	30000	2
C4	20000	2
C5	10000	3

Model Answer ▼

SELECT
  customer_id,
  total_amount,
  NTILE(3) OVER(ORDER BY total_amount DESC) AS tier
FROM customer_sales                                  -- Sort by sales descending and divide all rows evenly into three groups
ORDER BY total_amount DESC;

/*
  Execution order:
  1. FROM customer_sales (all 5 rows)
  2. ORDER BY total_amount DESC sorts rows by sales descending
  3. NTILE(3) divides the rows into three buckets
  4. SELECT returns the result
  */

Explanation (table transitions & key points) ▼

SELECT
  customer_id, total_amount,
  NTILE(3) OVER(ORDER BY total_amount DESC) AS tier
FROM customer_sales
ORDER BY total_amount DESC;

LEGEND ▼

Rows read / loaded

① FROM ▼

FROM customer_salesRead the table. It contains data for five customers. Because NTILE() assigns buckets according to row order, the next step must sort the rows with ORDER BY.

1 / 4

customer_id	total_amount
C1	50000
C2	40000
C3	30000
C4	20000
C5	10000

5 rows read

LEARNING POINTS

Use in analysis: NTILE(10) instantly creates groups for decile analysis, while NTILE(4) creates quartiles. It is often used to build datasets that compare behavior between the top 10% and bottom 10% of customers.

ANTI-PATTERNS

Ties crossing a boundary: NTILE divides mechanically by row count, so customers with the same sales can fall on opposite sides of a boundary and end up in different groups. If equal values must always share a group, use PERCENT_RANK or RANK and implement the grouping logic explicitly instead of using NTILE.

Example of a tie crossing a boundary (splitting 4 rows into 2 groups separates equal amounts)

NTILE(3) — 5 rows, 3 groups (no issue)

customer_id	total_amount	tier
C1	50000	1
C2	40000	1
C3	30000	2
C4	20000	2
C5	10000	3

All values differ, so no tie crosses a boundary.

✗ NTILE(2) — Equal amounts of 30,000 split across groups

customer_id	total_amount	tier
C1	50000	1
C2	30000	1
C3	30000	2
C4	10000	2

C2 and C3 both have 30,000 but are split between tiers 1 and 2.

QUESTION 7

PERCENT_RANK() — Measure Relative Rank within the Whole Set

PERCENT_RANKORDER BYPercentiles

Background ▼

PERCENT_RANK() calculates a row's relative position from 0.0 (first place) to 1.0 (last place).
Its formula is (the row's RANK - 1) / (total row count - 1). It is useful for requirements such as selecting only high-performing rows in the top 20%.

SELECT id_col,
       PERCENT_RANK() OVER (ORDER BY num_col DESC) AS pct_rank  -- 0.0 (first) through 1.0 (last)
FROM   table_name;

The endpoints and the denominator: The denominator is the row count minus one, so the two end rows land exactly on 0.0 and 1.0. A group with a single row returns 0.0. Tied rows share a rank and therefore share the same value.

Problem ▼

From the exam_scores table below, calculate each student's score and the relative position of that score within the whole set as pct_rank.

Source Table

▸ exam_scores

student_id	score
S1	95
S2	88
S3	75
S4	60

Expected Output ▼

student_id	score	pct_rank
S1	95	0
S2	88	0.3333
S3	75	0.6667
S4	60	1

Model Answer ▼

SELECT
  student_id,
  score,
  ROUND((PERCENT_RANK() OVER(ORDER BY score DESC))::numeric, 4) AS pct_rank
FROM exam_scores
ORDER BY score DESC;

/*
  Execution order:
  1. FROM exam_scores (all 4 rows)
  2. ORDER BY score DESC → Sort descending and establish ranks
  3. PERCENT_RANK() calculates (RANK - 1) / (4 - 1) for each row
  4. SELECT returns the result
  */

Explanation (table transitions & key points) ▼

SELECT
  student_id, score,
  PERCENT_RANK() OVER(ORDER BY score DESC) AS pct_rank
FROM exam_scores
ORDER BY score DESC;

LEGEND ▼

Rows read / loaded

① FROM ▼

FROM exam_scoresRead the table. It contains scores for four students.

1 / 4

student_id	score
S1	95
S2	88
S3	75
S4	60

4 rows read

LEARNING POINTS

Filtering in production: To select users in the top 20%, combine this result with a subquery (or CTE) and specify WHERE pct_rank <= 0.2. Whether the population has 100 or 10,000 users, the same query dynamically selects the top 20%.

Difference from CUME_DIST(): A similar function is CUME_DIST(), which calculates cumulative distribution. PERCENT_RANK assigns 0 to rank 1, whereas CUME_DIST starts at 1/N for the first-ranked row and always ends at 1. Choose according to the requirement.

☷ Numerical comparison of PERCENT_RANK and CUME_DIST (four students)

student_id	score	RANK	PERCENT_RANK (RANK−1) / (N−1)	CUME_DIST RANK / N
S1	95	1st	0.000　← always starts at 0	0.250
S2	88	2nd	0.333	0.500
S3	75	3rd	0.667	0.750
S4	60	4th	1.000　← always ends at 1	1.000

PERCENT_RANK is intuitive for filtering the top N%: because it starts at 0, <= 0.2 means the top 20%. CUME_DIST is better for expressing the cumulative proportion of rows at or below a value.

QUESTION 8

Advanced ROWS BETWEEN — Prior Running Total Excluding the Current Row

Frame SpecificationPRECEDINGPrior Running Total

Background ▼

A ROWS BETWEEN frame can define flexible aggregation ranges, including not only “from the first row through the current row,” but also “from the first row through the preceding row,” which excludes the current row.

SUM(sales) OVER(
  ORDER BY month
  ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
)

A frame that excludes the current row: Ending the frame at 1 PRECEDING leaves the current row out of the aggregate. On the very first row the frame contains no rows at all, so the result is NULL rather than 0.

Problem ▼

From the monthly_targets table below, calculate each month's sales and the running total through the previous month as prev_running_total.

For the first month (2026-01), no prior-month total exists, so the result is NULL.

Source Table

▸ monthly_targets

month	sales
2026-01	10
2026-02	20
2026-03	30
2026-04	40

Expected Output ▼

month	sales	prev_running_total
2026-01	10	NULL
2026-02	20	10
2026-03	30	30
2026-04	40	60

Model Answer ▼

SELECT
  month,
  sales,
  SUM(sales) OVER(
    ORDER BY month
    ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
  ) AS prev_running_total  -- From the first row through the preceding row; exclude the current row
FROM monthly_targets
ORDER BY month;

/*
  Frame evaluation:
  2026-01: no preceding row, so the frame is empty → NULL
  2026-02: sum of [2026-01 only] → 10
  2026-03: sum of [2026-01, 2026-02] → 10 + 20 = 30
  2026-04: sum of [2026-01, 2026-02, 2026-03] → 10 + 20 + 30 = 60
*/

Explanation (table transitions & key points) ▼

SELECT
  month, sales,
  SUM(sales) OVER(
    ORDER BY month
    ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
  ) AS prev_running_total
FROM monthly_targets
ORDER BY month;

LEGEND ▼

Rows read / loaded

① FROM ▼

FROM monthly_targetsRead the table containing four months of sales data.

1 / 4

month	sales
2026-01	10
2026-02	20
2026-03	30
2026-04	40

4 rows read

LEARNING POINTS

Production use case: This frame is useful in complex plan-versus-actual dashboards—for example, recalculating the current month's target by combining current sales with the shortfall carried forward from prior months.

Shifting a frame: An end of CURRENT ROW (the default) includes the current row; 1 PRECEDING totals through the prior row; and 1 FOLLOWING includes the next row. The ability to move both frame boundaries freely is the core power of frame specifications.

☷ Common frame patterns at a glance

ROWS BETWEEN … AND …	Aggregation range	Typical use
`UNBOUNDED PRECEDING` to `CURRENT ROW`	First row through current row (inclusive)	Running sales or count
`UNBOUNDED PRECEDING` to `1 PRECEDING`	First row through prior row (current excluded)	Total through prior period
`1 PRECEDING` to `1 FOLLOWING`	One row before and after (3 rows total)	Three-period moving average
`UNBOUNDED PRECEDING` to `UNBOUNDED FOLLOWING`	Entire partition	Attach group total to every row (like OVER without ORDER BY)

QUESTION 9

RANGE vs ROWS — The Running-Total Trap with Duplicate Dates

RANGEROWSHandling TiesCritical

Background ▼

When ORDER BY is specified and the frame clause is omitted, RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW applies by default.
ROWS operates on physical rows, while RANGE operates on logical values. Their behavior therefore differs decisively when the sort key contains ties, such as duplicate dates.

SELECT date_col,
       SUM(num_col) OVER (ORDER BY date_col)                    -- Omitting the frame defaults to RANGE
         AS default_frame,
       SUM(num_col) OVER (ORDER BY date_col
             RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) -- Includes all rows sharing a value
         AS range_frame,
       SUM(num_col) OVER (ORDER BY date_col
             ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)  -- Includes rows physically
         AS rows_frame
FROM   table_name;

They differ only when values tie: ROWS bounds the frame by a physical row count, while RANGE bounds it by the ORDER BY value. Rows sharing an ordering value are peers and fall into the same RANGE frame, so the two forms diverge only where such duplicates exist.

Problem ▼

The sales_records table below contains two rows dated 04-02.
Output both the default running total with only ORDER BY (equivalent to RANGE) and a running total with ROWS BETWEEN... specified explicitly, then compare their behavior for rows on the same date.

Source Table

▸ sales_records

sale_date	amount
04-01	10
04-02	20
04-02	30
04-03	40

Expected Output ▼

sale_date	amount	range_total	rows_total
04-01	10	10	10
04-02	20	60	30
04-02	30	60	60
04-03	40	100	100

Model Answer ▼

SELECT
  sale_date,
  amount,
  SUM(amount) OVER(                                   -- Omitted frame (implicit RANGE BETWEEN...)
    ORDER BY sale_date
  ) AS range_total,
  SUM(amount) OVER(                                   -- Specify ROWS explicitly
    ORDER BY sale_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS rows_total

FROM sales_records
ORDER BY sale_date, amount;

/*
  [RANGE] Evaluate by value.
  At a 04-02 row, sum every row whose date is at or before 04-02.
  The two 04-02 rows form a peer group,
  (10 + 20 + 30 = 60) and the result is assigned to both rows.

  [ROWS] Evaluate by physical row.
  04-02(20) row: [10, 20] = 30
  04-02(30) row: [10, 20, 30] = 60
*/

Explanation (table transitions & key points) ▼

SELECT
  sale_date, amount,
  SUM(amount) OVER(ORDER BY sale_date) AS range_total,
  SUM(amount) OVER(ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS rows_total
FROM sales_records
ORDER BY sale_date, amount;

LEGEND ▼

Rows read / loaded

① FROM ▼

FROM sales_recordsRead the table. Notice that two rows have the date 04-02. Multiple rows sharing the same value is what distinguishes RANGE behavior from ROWS behavior.

1 / 4

sale_date	amount
04-01	10
04-02	20
04-02	30
04-03	40

4 rows read (two dated 04-02)

LEARNING POINTS

Production choice:Use default RANGE when you want cumulative sales as of the end of each day, because it groups same-day sales. For an exact running amount after every order, add a unique key such as ORDER BY date, id or specify ROWS explicitly.

❖ Peer groups: how RANGE handles ties

RANGE: when evaluating a 04-02 row, group same-date rows as peers

sale_date	amount	Rows in frame	range_total
04-01	10	[04-01 only]	10
04-02	20	[04-01, 04-02 × 2 rows] ← peer group	60
04-02	30	[04-01, 04-02 × 2 rows] ← peer group	60
04-03	40	[04-01 through 04-03all]	100

The two same-date rows are peers, so their combined total of 60 is assigned to both.

ROWS: process physical row positions independently even for the same date

sale_date	amount	Rows in frame	rows_total
04-01	10	[04-01 only]	10
04-02	20	[04-01, current row(20)]	30
04-02	30	[04-01, 20, current row(30)]	60
04-03	40	[04-01 through 04-03all]	100

Counting each row independently gives the same-date rows different totals: 30 and 60.

ANTI-PATTERNS

Accidentally using RANGE: Writing only ORDER BY date for a moving average implicitly selects RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, producing a puzzling cumulative average through that date instead. Always choose the frame deliberately.

✓ Decision guide

Goal	Recommended expression	Reason
Running total at end of day	ORDER BY date only (default RANGE)	Naturally groups same-day rows
Exact total after each row	Explicit ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW	Guarantees independent row processing
Moving average	ROWS BETWEEN N-1 PRECEDING AND CURRENT ROW	Ties cannot expand the frame unexpectedly

QUESTION 10

Practical Capstone — Track Customer Purchases (Count, Total, and Previous Purchase)

CapstoneData MartProduction ReportingCombined

Background ▼

In production data platforms, building a data mart often means enriching each event row with the customer's status at that point in time.
Combine ROW_NUMBER(), SUM(), and LAG() to build a powerful tracking query.

SELECT t.*,
       ROW_NUMBER() OVER (PARTITION BY key_col ORDER BY ts_col) AS seq,      -- Which occurrence this is
       SUM(num_col) OVER (PARTITION BY key_col ORDER BY ts_col) AS running,  -- Running total at that point
       LAG(num_col) OVER (PARTITION BY key_col ORDER BY ts_col) AS prev_val  -- Previous value
FROM   table_name t;

One window shared by three metrics: All three metrics can be evaluated over the same window definition—per user, in chronological order. Matching PARTITION BY and ORDER BY means the same frame is used. Where rows can tie on the ordering, add a tie-breaker to ORDER BY to make the result deterministic.

Problem ▼

For every order row in the order_history table below, add these three metrics.

1. order_num: which numbered order this is for the user (ROW_NUMBER)
2. running_amt: the user's cumulative purchase amount through that point (SUM)
3. prev_date: the user's previous purchase date (LAG; NULL for the first order)

Source Table

▸ order_history

user_id	order_date	amount
U1	04-01	1000
U2	04-02	1500
U1	04-05	2000
U1	04-10	3000

Expected Output ▼

user_id	order_date	amount	order_num	running_amt	prev_date
U1	04-01	1000	1	1000	NULL
U1	04-05	2000	2	3000	04-01
U1	04-10	3000	3	6000	04-05
U2	04-02	1500	1	1500	NULL

Model Answer ▼

SELECT
  user_id,
  order_date,
  amount,
  ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY order_date) AS order_num,    -- ① Order number (sequence by date)
  SUM(amount) OVER(PARTITION BY user_id ORDER BY order_date) AS running_amt,   -- ② Cumulative purchase amount through this point
  LAG(order_date) OVER(PARTITION BY user_id ORDER BY order_date) AS prev_date  -- ③ Previous purchase date

FROM order_history
ORDER BY user_id, order_date;

/*
  Execution order:
  1. FROM order_history (U1 and U2 rows are interleaved)
  2. PARTITION BY user_id divides rows into U1 and U2 groups
  3. The three window functions are evaluated over the same partition and order
  4. ORDER BY user_id, order_date sorts the final output
  */

Explanation (table transitions & key points) ▼

SELECT
  user_id, order_date, amount,
  ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY order_date) AS order_num,
  SUM(amount) OVER(PARTITION BY user_id ORDER BY order_date) AS running_amt,
  LAG(order_date) OVER(PARTITION BY user_id ORDER BY order_date) AS prev_date
FROM order_history
ORDER BY user_id, order_date;

LEGEND ▼

Rows read / loaded

① FROM ▼

FROM order_historyRead four rows in insertion order, with U1 and U2 interleaved. No grouping or order has been established yet.

1 / 6

user_id	order_date	amount
U1	04-01	1000
U2	04-02	1500
U1	04-05	2000
U1	04-10	3000

4 rows read (insertion order, interleaved)

LEARNING POINTS

Remove duplication with WINDOW:Modern SQL can define WINDOW w AS (PARTITION BY user_id ORDER BY order_date) once and use SUM(amount) OVER w in SELECT, avoiding repeated window specifications under the DRY principle.

A foundation of data engineering:For CRM analysis, this table makes advanced questions—such as average days to a second purchase or churn after exceeding 5000 in cumulative spend—answerable with a simple GROUP BY. Window functions are powerful tools for untangling business logic.

☷ Roles and behavior of the three functions

Function	Metric added	First-row value	Frame-dependent
`ROW_NUMBER()`	order_num (order number)	Always starts at 1	No (row number only)
`SUM(amount)`	running_amt (cumulative purchase amount)	First order amount only	Yes (implicit default from first through current row)
`LAG(order_date)`	prev_date (previous purchase date)	NULL (no preceding row)	No (row reference only)

❖ Further analysis enabled by this data mart (using only GROUP BY)

Analysis	Additional query
Average days to second purchase	`WHERE order_num = 2 → AVG(DATEDIFF(order_date, prev_date))`
Order number that first exceeds 5,000 cumulative	`WHERE running_amt >= 5000 → MIN(order_num) per user`
Select first purchases only	`WHERE order_num = 1`
Average repurchase interval by user	`AVG(DATEDIFF(order_date, prev_date)) GROUP BY user_id`

A data mart created by one window-function query reduces later complex analysis to simple GROUP BY and WHERE operations. This is why window functions are foundational to data engineering.