SQL Window Functions: OVER, PARTITION BY, RANK and LAG

GROUP BY answers "what is the average salary per department?" with one row per department. A window function answers the same question but writes the answer on every employee's row, so you can compare each person with their department:

SELECT first_name, department, salary,
       ROUND(AVG(salary) OVER (PARTITION BY department)) AS dept_avg,
       salary - ROUND(AVG(salary) OVER (PARTITION BY department)) AS difference
FROM employees
ORDER BY department, salary DESC
LIMIT 8;

▶ Run this query in the SQL Playground

OVER (PARTITION BY department) is what makes AVG a window function. "Window" means the set of rows the function looks at for the current row; here that is all rows of the same department. All 120 employees stay in the result.

function_name(arguments) OVER (
  [PARTITION BY column, ...]   -- split rows into groups
  [ORDER BY column, ...]       -- order rows inside each group
  [ROWS BETWEEN ... AND ...]   -- which rows around the current one to use
)

An empty OVER () uses all rows. That makes "share of the total" a one-liner:

SELECT category,
       ROUND(SUM(price), 2) AS list_value,
       ROUND(100.0 * SUM(price) / SUM(SUM(price)) OVER (), 1) AS pct_of_total
FROM products
GROUP BY category
ORDER BY pct_of_total DESC;

▶ Run this query in the SQL Playground

SUM(SUM(price)) OVER () looks odd but is common: the inner SUM is the GROUP BY total per category, the outer SUM adds those totals across all categories. Window functions are calculated after GROUP BY, so they can use its results.

The three ranking functions differ only in how they handle ties. Product ratings have plenty of ties, so they show the difference well:

SELECT name, rating,
       ROW_NUMBER() OVER (ORDER BY rating DESC, name) AS row_num,
       RANK()       OVER (ORDER BY rating DESC) AS rnk,
       DENSE_RANK() OVER (ORDER BY rating DESC) AS dense_rnk
FROM products
ORDER BY rating DESC, name
LIMIT 10;

▶ Run this query in the SQL Playground

Add PARTITION BY to rank within groups. Each department's salaries, ranked from the top:

SELECT department, first_name, salary,
       RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank_in_dept
FROM employees
WHERE department IN ('Design', 'Product')
ORDER BY department, rank_in_dept;

▶ Run this query in the SQL Playground

NTILE: equal-sized buckets

NTILE(n) splits the ordered rows into n groups of (nearly) equal size, for quartiles, deciles and similar bands:

SELECT quartile,
       COUNT(*) AS employees,
       MIN(salary) AS lowest,
       MAX(salary) AS highest
FROM (SELECT salary, NTILE(4) OVER (ORDER BY salary DESC) AS quartile
      FROM employees) t
GROUP BY quartile
ORDER BY quartile;

▶ Run this query in the SQL Playground

Each ranking function has its own lesson with more examples: ROW_NUMBER and RANK and DENSE_RANK.

An aggregate with ORDER BY in its OVER clause becomes cumulative: each row sums everything up to and including itself. Here is revenue per month with a running total that restarts each year:

WITH monthly AS (
  SELECT strftime('%Y-%m', order_date) AS month,
         ROUND(SUM(total), 2) AS revenue
  FROM orders
  WHERE order_date >= '2023-01-01'
  GROUP BY month
)
SELECT month, revenue,
       ROUND(SUM(revenue) OVER (PARTITION BY substr(month, 1, 4)
                                ORDER BY month), 2) AS year_to_date
FROM monthly
ORDER BY month
LIMIT 8;

▶ Run this query in the SQL Playground

Moving average with a window frame

A frame limits the window to rows near the current one. ROWS BETWEEN 2 PRECEDING AND CURRENT ROW means "this month and the two before it", a three-month moving average:

WITH monthly AS (
  SELECT strftime('%Y-%m', order_date) AS month,
         ROUND(SUM(total), 2) AS revenue
  FROM orders
  WHERE order_date >= '2023-01-01'
  GROUP BY month
)
SELECT month, revenue,
       ROUND(AVG(revenue) OVER (ORDER BY month
                                ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 2) AS avg_3_months
FROM monthly
ORDER BY month
LIMIT 8;

▶ Run this query in the SQL Playground

The first two months average fewer than three values because there are not enough earlier rows yet.

LAG reads a value from an earlier row and LEAD from a later one. With LAG(revenue, 12) on monthly data you compare each month with the same month a year earlier:

WITH monthly AS (
  SELECT strftime('%Y-%m', order_date) AS month,
         ROUND(SUM(total), 2) AS revenue
  FROM orders
  WHERE order_date >= '2023-01-01'
  GROUP BY month
)
SELECT month, revenue,
       LAG(revenue)     OVER (ORDER BY month) AS previous_month,
       LAG(revenue, 12) OVER (ORDER BY month) AS same_month_last_year,
       ROUND(100.0 * (revenue - LAG(revenue, 12) OVER (ORDER BY month))
             / LAG(revenue, 12) OVER (ORDER BY month), 1) AS yoy_pct
FROM monthly
ORDER BY month DESC
LIMIT 6;

▶ Run this query in the SQL Playground

The LAG and LEAD lesson covers offsets, defaults and gaps between a customer's orders.

First and last value in a group

FIRST_VALUE returns the first value in the window. LAST_VALUE needs care: with ORDER BY the default frame ends at the current row, so it returns the current row unless you extend the frame to the end:

SELECT user_id, order_date, total,
       FIRST_VALUE(order_date) OVER (PARTITION BY user_id ORDER BY order_date, id) AS first_order,
       LAST_VALUE(order_date)  OVER (PARTITION BY user_id ORDER BY order_date, id
                                     ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_order
FROM orders
WHERE user_id IN (4, 77)
ORDER BY user_id, order_date;

▶ Run this query in the SQL Playground

Top N per group

Window functions cannot be used in WHERE, because WHERE runs before they are calculated. Number the rows in a subquery or CTE, then filter outside. The three newest orders per status:

SELECT status, id, order_date, total
FROM (SELECT status, id, order_date, total,
             ROW_NUMBER() OVER (PARTITION BY status ORDER BY order_date DESC, id DESC) AS rn
      FROM orders) t
WHERE rn <= 3
ORDER BY status, rn;

▶ Run this query in the SQL Playground

Finding duplicates

Number the rows within each group of identical values; every row numbered 2 or more is a duplicate:

SELECT id, name, rn
FROM (SELECT id, name,
             ROW_NUMBER() OVER (PARTITION BY name ORDER BY id) AS rn
      FROM products) t
WHERE rn > 1
ORDER BY name, rn
LIMIT 8;

▶ Run this query in the SQL Playground

Several windows in one query

Each window function has its own OVER clause, so different partitions can sit side by side:

SELECT first_name, department, salary,
       RANK() OVER (ORDER BY salary DESC) AS company_rank,
       RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank,
       COUNT(*) OVER (PARTITION BY department) AS dept_size
FROM employees
ORDER BY company_rank
LIMIT 6;

▶ Run this query in the SQL Playground

Two consequences follow. A window function cannot appear in WHERE, GROUP BY or HAVING; wrap the query and filter outside. And WHERE removes rows before the window is calculated, so filtering first changes the result: a LAG on a query filtered to one month has no previous row to read.

Most window functions need the rows sorted by the PARTITION BY and ORDER BY columns. That sort is usually the expensive part.

Six errors and how to avoid them.

SELECT *
FROM employees
WHERE ROW_NUMBER() OVER (ORDER BY salary DESC) <= 3;

SELECT *
FROM (SELECT e.*, ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn
      FROM employees e) t
WHERE rn <= 3;

ROW_NUMBER() OVER (ORDER BY rating DESC)

ROW_NUMBER() OVER (ORDER BY rating DESC, id)

LAST_VALUE(order_date) OVER (PARTITION BY user_id ORDER BY order_date)

LAST_VALUE(order_date) OVER (PARTITION BY user_id ORDER BY order_date
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)

RANK() OVER (ORDER BY salary DESC)

RANK() OVER (PARTITION BY department ORDER BY salary DESC)

SELECT department, AVG(salary) OVER (PARTITION BY department)
FROM employees;

SELECT department, AVG(salary)
FROM employees
GROUP BY department;

SELECT month, LAG(revenue) OVER (ORDER BY month) AS previous
FROM monthly
WHERE month = '2024-12';

SELECT * FROM (
  SELECT month, LAG(revenue) OVER (ORDER BY month) AS previous
  FROM monthly) t
WHERE month = '2024-12';

8 exercises on the real Playground tables.

Write each query yourself first. Every solution has been run against the Playground database.

1. 1Easy Show each product with the average price of its category next to it. Show 8, ordered by category and price. Table: products

   Show solution

   SQLite · runs in the Playground

   SELECT name, category, price,
          ROUND(AVG(price) OVER (PARTITION BY category), 2) AS category_avg
   FROM products
   ORDER BY category, price DESC
   LIMIT 8;

   ▶ Run this query in the SQL Playground

   PARTITION BY category computes one average per category and repeats it on each row.

2. 2Easy Number the employees from the most recently hired (1) to the earliest. Show the first 5. Table: employees

   Show solution

   SQLite · runs in the Playground

   SELECT ROW_NUMBER() OVER (ORDER BY hire_date DESC, id) AS n,
          first_name, last_name, hire_date
   FROM employees
   ORDER BY n
   LIMIT 5;

   ▶ Run this query in the SQL Playground

   id breaks ties between employees hired on the same day.

3. 3Medium For each customer's orders, show a running total of what they have spent so far. Use customers 4 and 77. Table: orders

   Show solution

   SQLite · runs in the Playground

   SELECT user_id, order_date, total,
          ROUND(SUM(total) OVER (PARTITION BY user_id ORDER BY order_date, id
                                 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 2) AS spent_so_far
   FROM orders
   WHERE user_id IN (4, 77)
   ORDER BY user_id, order_date;

   ▶ Run this query in the SQL Playground

   The running total restarts for each customer because of PARTITION BY user_id.

4. 4Medium Show the cheapest product in each category. Table: products

   Show solution

   SQLite · runs in the Playground

   SELECT category, name, price
   FROM (SELECT category, name, price,
                ROW_NUMBER() OVER (PARTITION BY category ORDER BY price, id) AS rn
         FROM products) t
   WHERE rn = 1
   ORDER BY price;

   ▶ Run this query in the SQL Playground

   Number within each category from the lowest price and keep number 1.

5. 5Medium Show each rating value with its number of reviews and its share of all reviews in percent. Table: reviews

   Show solution

   SQLite · runs in the Playground

   SELECT rating,
          COUNT(*) AS reviews,
          ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 1) AS pct
   FROM reviews
   GROUP BY rating
   ORDER BY rating DESC;

   ▶ Run this query in the SQL Playground

   SUM(COUNT(*)) OVER () adds up the grouped counts across all ratings.

6. 6Medium Show each Design employee with the salary just below and just above theirs in the same department. Table: employees

   Show solution

   SQLite · runs in the Playground

   SELECT first_name, salary,
          LAG(salary) OVER (PARTITION BY department ORDER BY salary) AS next_lower,
          LEAD(salary) OVER (PARTITION BY department ORDER BY salary) AS next_higher
   FROM employees
   WHERE department = 'Design'
   ORDER BY salary;

   ▶ Run this query in the SQL Playground

   Ordered by salary, LEAD looks one step up and LAG one step down.

7. 7Hard Find the month with the highest revenue in each year. Table: orders

   Show solution

   SQLite · runs in the Playground

   WITH monthly AS (
     SELECT strftime('%Y', order_date) AS yr,
            strftime('%Y-%m', order_date) AS month,
            ROUND(SUM(total), 2) AS revenue
     FROM orders
     GROUP BY yr, month
   )
   SELECT yr, month, revenue
   FROM (SELECT *, RANK() OVER (PARTITION BY yr ORDER BY revenue DESC) AS rnk
         FROM monthly) t
   WHERE rnk = 1
   ORDER BY yr;

   ▶ Run this query in the SQL Playground

   Group to months first, then rank the months within each year.

8. 8Hard Split customers who have ordered into four groups by total spend (1 = highest) and show each group's size and spend range. Table: users, orders

   Show solution

   SQLite · runs in the Playground

   SELECT spend_quartile,
          COUNT(*) AS customers,
          MIN(spent) AS lowest,
          MAX(spent) AS highest
   FROM (SELECT user_id, ROUND(SUM(total), 2) AS spent,
                NTILE(4) OVER (ORDER BY SUM(total) DESC) AS spend_quartile
         FROM orders
         GROUP BY user_id) t
   GROUP BY spend_quartile
   ORDER BY spend_quartile;

   ▶ Run this query in the SQL Playground

   NTILE runs after GROUP BY, so it can order by the grouped total.