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%.

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.

The top row (rank 1) is 0, and the bottom row (rank 4) is 1. For S2, (rank 2 - 1) / (4 students - 1) = 1/3 = 0.333....

SELECT
  student_id,
  score,
  PERCENT_RANK() OVER(ORDER BY score DESC) AS pct_rank  -- Relative rank from the top (0 to 1)
FROM exam_scores
ORDER BY score DESC;

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

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
)

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.

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
*/

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.

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.

With implicit RANGE, the values 20 and 30 on 04-02 are summed together and 60 is assigned to both rows. ROWS accumulates each physical row separately: 10+20=30, then 10+20+30=60.

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
*/

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.

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

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
  */