NTILE(n) divides data sorted by ORDER BY into n nearly equal buckets and assigns each row a bucket number from 1 through n.

NTILE(4) OVER(ORDER BY amount DESC) AS quartile
-- sort every row by amount descending and assign a bucket number (1–4)

Using the customers table below, which contains eight customers and their total purchase amounts, divide the rows into four equal groups in descending total_amount order with NTILE(4), and assign a quartile number.

quartile=1 is the highest-spending group (top 25%), and quartile=4 is the lowest-spending group.

SELECT
  customer_id,
  name,
  total_amount,
  NTILE(4) OVER(ORDER BY total_amount DESC) AS quartile  -- four descending buckets (2 rows each; 1 = highest-spending VIP)
FROM customers
ORDER BY total_amount DESC;

/*
  Logical evaluation order:
  1. FROM customers              → read 8 rows
  2. Window function NTILE(4)    → divide by total_amount DESC and assign bucket numbers
  3. SELECT                      → project the columns
  4. ORDER BY total_amount DESC  → perform the final sort
  */

Writing ROWS BETWEEN start AND end inside OVER precisely defines which range of rows forms the aggregation frame. This is one of the most powerful features of window functions.

AVG(temp) OVER(
  ORDER BY date
  ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  -- from 2 rows before through the current row = latest 3 rows
)

From the daily_temperature table below, calculate the temperature's three-day moving average including the current day as moving_avg_3d, rounded to one decimal place. The data contains exactly one row per consecutive calendar day, so the latest three rows represent the latest three days.

The first two rows do not yet have three days of data, so average only the rows available in their shortened frames, as shown in the output.

SELECT
  measured_date,
  temp,
  ROUND(
    AVG(temp) OVER(
      ORDER BY measured_date
      ROWS BETWEEN 2 PRECEDING AND CURRENT ROW  -- 3-row frame from 2 rows before to current (shortened at the start)
    )
  , 1) AS moving_avg_3d   -- ROUND(..., 1): round to one decimal place
FROM daily_temperature
ORDER BY measured_date;

/*
  Logical evaluation order:
  1. FROM daily_temperature → read 6 rows
  2. Window function        → calculate the moving average over each frame
  3. ROUND                  → round to one decimal place
  4. SELECT                 → project the columns
  */

FIRST_VALUE(column) returns the value from the first row in the window frame, while LAST_VALUE(column) returns the value from the last row.

FIRST_VALUE(price) OVER(
  PARTITION BY stock
  ORDER BY trade_date
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS first_price

From the stock_prices table below, attach the period's opening price (first_price) and closing price (last_price) for each stock to every row.

When using LAST_VALUE, explicitly specify ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING so the frame reaches the partition's final row.

SELECT
  stock,
  trade_date,
  price,
  FIRST_VALUE(price) OVER(
    PARTITION BY stock
    ORDER BY     trade_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING  -- whole-partition frame, explicit for symmetry with LAST_VALUE
  ) AS first_price,
  LAST_VALUE(price) OVER(
    PARTITION BY stock
    ORDER BY     trade_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING  -- required whole-partition frame; omission stops at the current peer group
  ) AS last_price

FROM stock_prices
ORDER BY stock, trade_date;

/*
  Logical evaluation order:
  1. FROM stock_prices → read 6 rows
  2. PARTITION BY stock → split into TYK (3 rows) and OSK (3 rows)
  3. ORDER BY trade_date within each partition
  4. FIRST_VALUE → attach the partition's first price to every row
  5. LAST_VALUE  → UNBOUNDED FOLLOWING makes the partition's final
     price available on every row
  6. SELECT → project the columns
  7. ORDER BY stock, trade_date → perform the final sort
*/

A window-function result cannot be filtered directly in WHERE because WHERE is evaluated before window functions. In production, calculate the window function first in a CTE (Common Table Expression) or subquery, then filter its result in the outer WHERE.

WITH cte_name AS (
  -- calculate the window function here
  SELECT *, ROW_NUMBER() OVER(...) AS rn FROM table
)
SELECT * FROM cte_name
WHERE rn <= 2;   -- filter by the column created in the CTE

From the emp_sales table below, return the top two employees in each department (dept) by descending sales. Implement the query with a CTE.

WITH ranked AS (
  -- calculate the window function in the CTE (it cannot be filtered here with WHERE)
  SELECT
    dept,
    emp_name,
    sales,
    ROW_NUMBER() OVER(
      PARTITION BY dept                  -- reset numbering per department
      ORDER BY     sales DESC, emp_name  -- rank by sales; break ties by name
    ) AS rn
  FROM emp_sales
)
SELECT
  dept,
  emp_name,
  sales
FROM ranked
WHERE rn <= 2                  -- keep the top two per department
ORDER BY
  CASE dept WHEN 'Sales' THEN 1 WHEN 'Engineering' THEN 2 END,
  sales DESC;

/*
  Logical evaluation order:
  1. Inner CTE query → assign ranks with PARTITION + ORDER + ROW_NUMBER
  2. CTE result      → expose the result as a virtual table
  3. Outer SELECT    → filter with WHERE rn and sort
  */