SQL PARTITION BY Explained

Source: DataDriven analysis of 1,042 verified data engineering interview rounds.

PARTITION BY Syntax

You'll always find PARTITION BY tucked inside the OVER clause, never floating on its own. Pass it one or more columns and it carves the result set into bins along those values. Every row sharing the same partition key gets computed together, and the computation resets at each boundary. That reset is the thing to hold in your head.

-- General syntax
function_name() OVER (
  PARTITION BY column1, column2, ...
  ORDER BY column3
  ROWS BETWEEN ... AND ...
)

-- Example: running total per customer
SELECT
  customer_id,
  order_date,
  amount,
  SUM(amount) OVER (
    PARTITION BY customer_id
    ORDER BY order_date
  ) AS running_total
FROM orders;

The PARTITION BY clause is optional. Without it, the entire result set is treated as one partition. ORDER BY inside OVER is also optional, but its presence changes the default frame for aggregate window functions. Both of these defaults trip up candidates in interviews regularly.

PARTITION BY vs GROUP BY

This is one of the most frequently asked conceptual questions in SQL interviews. Both create groups. The difference is in what happens to the rows after grouping.

Here is the same question answered both ways. The goal: show each employee alongside their department average salary.

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

-- Returns every employee row
-- dept_avg is the same value for all rows in the same department

SELECT
  e.department,
  e.employee_name,
  e.salary,
  d.dept_avg
FROM employees e
JOIN (
  SELECT department, AVG(salary) AS dept_avg
  FROM employees
  GROUP BY department
) d ON e.department = d.department;

-- Same result, more code, extra join
-- PARTITION BY does this in a single pass

5 PARTITION BY Patterns

These five patterns cover the vast majority of PARTITION BY usage in interviews and production SQL. Each shows a different window function type and explains how PARTITION BY scopes it.

SELECT
  department, employee_name, salary,
  ROW_NUMBER() OVER (
    PARTITION BY department
    ORDER BY salary DESC
  ) AS dept_rank
FROM employees;

-- Engineering: 1, 2, 3, 4...
-- Marketing:   1, 2, 3, 4...
-- Each partition is independent

SELECT
  customer_id,
  order_date,
  amount,
  SUM(amount) OVER (
    PARTITION BY customer_id
    ORDER BY order_date
  ) AS running_total
FROM orders;

SELECT
  department,
  employee_name,
  salary,
  AVG(salary) OVER (PARTITION BY department) AS dept_avg,
  salary - AVG(salary) OVER (PARTITION BY department) AS diff_from_avg
FROM employees;

SELECT
  customer_id,
  order_date,
  amount,
  LAG(amount) OVER (
    PARTITION BY customer_id
    ORDER BY order_date
  ) AS prev_amount,
  amount - LAG(amount) OVER (
    PARTITION BY customer_id
    ORDER BY order_date
  ) AS change
FROM orders;

SELECT
  region,
  product_category,
  month,
  revenue,
  SUM(revenue) OVER (
    PARTITION BY region, product_category
    ORDER BY month
  ) AS cumulative_rev,
  RANK() OVER (
    PARTITION BY region
    ORDER BY revenue DESC
  ) AS region_rank
FROM monthly_sales;

PARTITION BY and Frame Clauses

The frame clause (ROWS BETWEEN or RANGE BETWEEN) controls which rows within a partition contribute to the calculation. PARTITION BY defines the outer boundary; the frame defines the inner boundary. Understanding their interaction is critical for moving averages, cumulative sums, and bounded lookbacks.

-- 3-row moving average per store
SELECT
  store_id,
  sale_date,
  daily_revenue,
  AVG(daily_revenue) OVER (
    PARTITION BY store_id
    ORDER BY sale_date
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ) AS moving_avg_3
FROM daily_sales;

-- Without ORDER BY: frame is the entire partition (grand avg)
-- With ORDER BY, no frame: default is UNBOUNDED PRECEDING to CURRENT ROW
-- With explicit ROWS: exactly the rows you specify

ROWS counts physical rows. RANGE counts logical value ranges. With dates, ROWS BETWEEN 6 PRECEDING AND CURRENT ROW grabs the previous 6 rows by position, which might not be 6 calendar days if data has gaps. RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW grabs all rows within 6 calendar days regardless of how many rows that represents. PostgreSQL supports both; MySQL 8+ supports ROWS but not RANGE with intervals.

-- ROWS vs RANGE with date gaps
-- Day 1, Day 2, Day 5 (days 3 and 4 missing)

-- ROWS BETWEEN 2 PRECEDING: Day 5 sees Day 2 and Day 1
-- RANGE BETWEEN INTERVAL '2 days' PRECEDING: Day 5 sees only itself
--   (Day 3 and Day 4 do not exist)

5 PARTITION BY Interview Questions

These questions test different aspects of PARTITION BY: basic scoping, frame interaction, multi-column partitions, and the GROUP BY comparison. Each includes the interviewer's intent and a recommended approach.

Common PARTITION BY Mistakes

Three mistakes account for most PARTITION BY bugs in interviews. Each produces a query that compiles and runs but returns wrong results.

PARTITION BY FAQ

Related Guides