What Does PARTITION BY Do in SQL?
Learn how PARTITION BY groups rows for window functions in SQL, and how it differs from GROUP BY with a worked example.
Expected Interview Answer
PARTITION BY divides the rows visible to a window function into independent groups, so the function's calculation restarts from scratch for each group instead of running across the entire result set.
PARTITION BY is written inside the OVER() clause alongside an optional ORDER BY. Unlike GROUP BY, it never reduces the number of output rows — every row keeps its place in the result set, but the window function evaluates it only against the other rows sharing its partition key. For example, RANK() OVER (PARTITION BY department ORDER BY salary DESC) ranks each employee only among coworkers in the same department, resetting to rank 1 at the start of every new department. Omitting PARTITION BY treats the whole result set as one single partition.
- Resets window calculations independently per group
- Never collapses rows, unlike GROUP BY
- Can combine with ORDER BY for per-group ranking or running totals
- Multiple partition keys can be combined for finer grouping
AI Mentor Explanation
Think of ranking batters by runs scored, but only within their own team rather than across the whole tournament. PARTITION BY team means the ranking calculation restarts at one for each team's roster independently, so a batter's rank reflects only their teammates, not players from other sides. Without partitioning, the ranking would run across every player in the tournament as if they were one giant team.
Step-by-Step Explanation
Step 1
Identify the grouping column
Pick the column that should define independent groups, such as department or region.
Step 2
Write PARTITION BY inside OVER()
Add PARTITION BY <column> before any ORDER BY within the window function's OVER clause.
Step 3
Add ORDER BY if the function needs sequencing
Ranking and offset functions like RANK or LAG also need ORDER BY inside the same OVER clause.
Step 4
Verify the row count stays the same
Confirm the query still returns one row per input row, since PARTITION BY never collapses rows like GROUP BY does.
What Interviewer Expects
- Clear statement that PARTITION BY groups rows without reducing row count
- Correct distinction between PARTITION BY and GROUP BY
- A concrete example combining PARTITION BY with ORDER BY
- Awareness that omitting PARTITION BY treats all rows as one partition
Common Mistakes
- Believing PARTITION BY reduces the number of returned rows like GROUP BY
- Forgetting to combine PARTITION BY with ORDER BY when ranking is needed
- Assuming PARTITION BY requires a GROUP BY clause to work
- Using PARTITION BY on a column with no meaningful grouping purpose
Best Answer (HR Friendly)
“PARTITION BY splits the rows a window function looks at into separate groups, like per department or per region, so the calculation restarts for each group instead of running across everything at once. It is different from GROUP BY because it never reduces the number of rows returned.”
Code Example
SELECT
employee_id,
department,
salary,
RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
FROM Employees;
-- Rank resets to 1 at the start of every new department,
-- and every employee row is still returned individually.Follow-up Questions
- How does PARTITION BY differ from GROUP BY?
- Can you PARTITION BY more than one column at once?
- What happens to a window function if PARTITION BY is omitted entirely?
- How does PARTITION BY interact with the frame clause in a window function?
MCQ Practice
1. What is the primary effect of adding PARTITION BY to a window function?
PARTITION BY divides rows into groups so the window function computes separately within each group, without reducing row count.
2. How does PARTITION BY differ from GROUP BY?
PARTITION BY preserves every input row while grouping the window calculation, whereas GROUP BY reduces rows to one per group.
3. What happens if PARTITION BY is omitted from a window function's OVER clause?
Without PARTITION BY, the window function treats all rows in the result set as one single partition.
Flash Cards
What does PARTITION BY do? — Divides rows into independent groups so a window function's calculation restarts for each group.
Does PARTITION BY reduce row count? — No, every row is preserved, unlike GROUP BY which collapses rows.
Where is PARTITION BY written? — Inside the OVER() clause of a window function, before any ORDER BY.
What happens without PARTITION BY? — The entire result set is treated as a single partition for the window function.