PARTITION BY

Hook: Interviewers love this because it looks simple, but it tests whether you understand the difference between keeping rows and grouping rows away.

Question: What does PARTITION BY do in SQL window functions?

Answer: PARTITION BY splits the result set into smaller groups called partitions, and the window function is computed separately inside each group. Unlike GROUP BY, it does not collapse rows, so you still see every original row. It is the standard way to ask for per-department totals, per-customer ranks, or running metrics while keeping row-level detail.

Interview-Ready Answer: I think of PARTITION BY as creating a temporary bucket for each group while leaving all the rows visible. For example, SUM(amount) OVER (PARTITION BY dept) gives me the department total on every row in that department, instead of returning one row per department like GROUP BY would. The key idea is that PARTITION BY defines the scope of the window function, and if I also need ranking or running totals, I usually add ORDER BY inside the window too.

What it is

Detailed Explanation: A window function is a function that looks at a set of related rows while still returning one output row per input row. PARTITION BY decides how those rows are split into buckets. If you omit it, the whole result set is one bucket.

1. The database builds the base row set from FROM, WHERE, joins, and filtering.
2. It splits that row set by the PARTITION BY keys, such as dept or customer_id.
3. If the window also has ORDER BY, the rows inside each partition are ordered for functions like ROW_NUMBER(), RANK(), or running totals.
4. The window function runs for each row using only rows in its own partition, then the original rows are returned unchanged.

Think of it this way: PARTITION BY answers “which rows are siblings?” It does not answer “which row comes first?” That is what ORDER BY inside the window is for.

Why it matters

You use PARTITION BY when you want both of these at the same time: group-level knowledge and row-level detail. For example, you can show each employee’s sales and also show the total sales for their department on the same row. That is why it is so common in dashboards, leaderboards, analytics, and audit queries.

PARTITION BY vs GROUP BY

How to reason about it under pressure

1. Ask: do I need every original row back? If yes, reach for a window function, not a grouped aggregate.
2. Choose the partition key: department, customer, month, region, or any business bucket.
3. Add ORDER BY only if the function depends on sequence, such as ranking or running totals.
4. Remember the difference between partition and frame: the partition is the bucket; the frame is the slice inside the bucket. A frame clause like ROWS BETWEEN is a narrower concept used by some window functions.

Performance and edge cases

The expensive part is usually not the function itself; it is the sort or grouping work the engine does to build partitions. In practical terms, a query over 1 million rows may be fast if the data is already ordered by the partition keys, but on wide 10 million row tables it can spill to temp storage if memory is small. In big systems, a matching index on the partition and order columns can reduce sorting work a lot.

• If there is no PARTITION BY, the entire table is one partition.
• Rows with the same partition key value belong to the same partition; in most engines, NULL partition values are grouped together.
• PARTITION BY alone does not guarantee a row order. If you need stable ranking, add a tie-breaker to ORDER BY.
• Do not confuse PARTITION BY with table partitioning. Table partitioning is storage layout; window partitioning is query-time logic.

Real-World Story: Imagine a subscription billing service that shows each invoice with the customer’s monthly total and their latest invoice rank. The analyst writes one query with SUM(amount) OVER (PARTITION BY customer_id) and ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY invoice_date DESC), so every invoice row still appears, but each row also carries the customer-level summary. This is perfect for finance reviews, support tooling, and fraud checks.

What goes wrong when someone misunderstands it? A developer forgets PARTITION BY customer_id and ranks invoices globally instead of per customer. Suddenly, only the newest invoice in the whole company gets rank 1, and downstream code that expects one “latest invoice” per customer starts showing missing rows. In logs, you might see suspiciously low counts per customer, confused support tickets, and dashboards where one department appears to dominate everything because all rows were compared as one giant bucket.

The symptom is subtle: the query still runs, the numbers still look “reasonable,” but the business meaning is wrong. That is why interviewers like this topic — it tests whether you can protect correctness, not just syntax.

Follow-up & Tricky Questions:

• What happens if you omit PARTITION BY? The entire result set becomes one partition, so the window function sees all rows together. That is why SUM(amount) OVER () returns the grand total on every row.
• How is PARTITION BY different from GROUP BY? GROUP BY collapses rows into one output row per group, while PARTITION BY keeps all rows and just adds window results beside them.
• Can you partition by more than one column? Yes. PARTITION BY country, city makes each unique combination its own bucket, which is common for reporting by region and subregion.
• Why do people add ORDER BY inside the window? Because functions like ROW_NUMBER(), RANK(), and running sums need a sequence inside each partition. Without a clear order, the result may be unstable or less meaningful.
• What is the difference between PARTITION BY and table partitioning? Window PARTITION BY is query logic over rows you already selected; table partitioning is how the database stores and manages data on disk.
• Does PARTITION BY remove duplicates? No. It does not change the number of rows at all; it only changes the scope of the window function.
• Is PARTITION BY the same as ORDER BY? No. PARTITION BY splits rows into groups; ORDER BY sorts rows inside each group or the final result.
• Does PARTITION BY always require ORDER BY? No. Aggregates like SUM() and COUNT() can use PARTITION BY without ORDER BY, but ranking and running calculations usually need it.

Common Mistakes:

• Using GROUP BY when they still need row detail. Correction: use a window function with PARTITION BY so the rows stay visible.
• Forgetting ORDER BY for ranking functions. Correction: add a clear sort key, and include a tie-breaker column when values can repeat.
• Thinking PARTITION BY changes the number of rows. Correction: it only changes the scope of the calculation; the output row count stays the same.
• Mixing up window partitions with storage partitions. Correction: one is query-time behavior, the other is physical data layout.

Memory Hook: “Partition is the bucket; order is the line inside the bucket.” If you remember that, you will almost always choose the right clause under interview pressure.

Cheat Sheet:

• PARTITION BY splits rows into groups for window functions.
• It does not collapse rows.
• Omit it, and the whole result set is one partition.
• Use ORDER BY inside the window for ranks and running totals.
• Use a tie-breaker column when sort values can repeat.
• Think “per-group on every row,” not “summary row only.”

Practice Tasks:

• Write a query that shows each order and the total order amount per customer using SUM(...) OVER (PARTITION BY customer_id).
• Add ROW_NUMBER() to rank orders within each customer by newest date first.
• Compare the result of a window query with an equivalent GROUP BY query and explain why the row counts differ.