A window function computes across a set of rows related to the current row but does not collapse them, so you keep every row and gain a calculated column. GROUP BY collapses rows into one per group. That means you can show each order alongside the customer's total spend in the same result, which with GROUP BY would need a subquery and a join back.
Why interviewers ask this
Window functions are the dividing line between basic and intermediate SQL, and most real analyst work (running totals, rankings, period comparisons, deduplication) depends on them. Interviewers want the no collapsing point, a correct grasp of PARTITION BY versus ORDER BY, and ideally awareness that the window frame defaults change the result.
How to structure your answer
- Lead with the key difference: rows are retained, not collapsed.
- Explain PARTITION BY as the grouping and ORDER BY as the sequence.
- Give two or three real uses you have written.
- Mention the frame clause and the default when ORDER BY is present.
Example answer
The core difference is that GROUP BY collapses and a window function does not. With a window I keep every row and add a column computed over a defined set of neighbors, so I can show each transaction next to that customer's running total without a self join. PARTITION BY says which rows belong to the same window, so partition by customer_id, and ORDER BY defines the sequence within it, which is what makes running totals and lag comparisons work. I use them constantly: row_number to pick the latest record per entity, lag to compute month over month change, sum over an ordered partition for cumulative revenue. The thing worth knowing is the frame. Once you add ORDER BY, the default frame is from the start of the partition to the current row, which is exactly what you want for a running total but not for a partition wide total. If I want the group total on every row, I leave ORDER BY off or set the frame explicitly.
Walking into this interview soon? GhostPilot listens to your live call, spots the question the moment it is asked, and puts a structured answer on your screen in real time. Try it on your next mock, or grab a $29 Session Pass, no subscription, for the real thing.
See how it worksFollow-up questions to expect
- What is the default window frame and when does it surprise people?
- How would you compute a seven day moving average in SQL?
- Can you filter on the result of a window function directly?
Related data analyst questions
Your interviewer will ask their own version of this. Paste your actual job description into the free Question Predictor and get the 20 questions that role is most likely to ask, with what each one is really probing.
Predict my questions