SQL PARTITION BY Explained

PARTITION BY defines where the ranking restarts. Without it, ROW_NUMBER counts across the entire result set. With PARTITION BY department, each department gets its own sequence starting at 1. This is the foundation of every top-N per group query, which accounts for roughly 30% of SQL interview questions in data engineering rounds.

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

SUM with PARTITION BY and an ORDER BY clause produces a running total that resets for each group. The frame defaults to ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW when ORDER BY is present. This pattern powers cumulative revenue tracking, balance calculations, and progressive aggregation in pipeline output tables.

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

PARTITION BY without ORDER BY computes a single aggregate value for the entire group and attaches it to every row. This lets you compare each row to its group average, max, or total without a self-join or subquery. The query stays flat and readable.

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;

LAG and LEAD look at adjacent rows, but only within the partition. PARTITION BY customer_id with LAG means you compare each order to that customer's previous order, not the previous row in the table. Without the partition, LAG would cross customer boundaries and return meaningless comparisons.

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;

You can partition by more than one column. PARTITION BY region, product_category creates a separate window for each region-category combination. Ten regions and five categories produce fifty independent windows. Each window function call can use a different PARTITION BY clause in the same SELECT statement.

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;

GROUP BY collapses rows. PARTITION BY does not. If you write GROUP BY department and SELECT employee_name, you get an error because the query does not know which employee name to return. PARTITION BY department in a window function keeps every row and just scopes the calculation to each department independently. Mixing up these two concepts in an interview signals a fundamental gap in SQL understanding.

Without ORDER BY, the default frame for SUM/AVG/etc. is the entire partition. With ORDER BY, the default shrinks to UNBOUNDED PRECEDING to CURRENT ROW. This means SUM(amount) OVER (PARTITION BY customer_id) gives the total, but SUM(amount) OVER (PARTITION BY customer_id ORDER BY order_date) gives a running total. Many candidates add ORDER BY for 'cleanliness' without realizing it changes the computation entirely.

Partitioning by a high-cardinality combination (like user_id, timestamp) can create partitions with a single row each, making the window function meaningless. RANK over a single-row partition always returns 1. SUM over a single-row partition returns the row's own value. The query runs without errors but the results carry no analytical value.