SQL Interview Prep · Lesson

OVER, PARTITION BY and ORDER BY

The anatomy of a window specification and how partitions reset the calculation.

Why Interviewers Reach for Window Functions

A window function performs a calculation across a set of rows related to the current row, without collapsing them like GROUP BY does. That single property is why interviewers love them: you keep every detail row and still get an aggregate, rank, or running total alongside it.

GROUP BY returns one row per group.
Window function returns every input row, with an extra computed column.

When an interviewer says "show each employee and their department's average salary on the same row," they are testing whether you reach for a window function instead of a self-join.

The OVER Clause Anatomy

Every window function is followed by an OVER (...) clause. The clause has three optional parts, and naming them precisely impresses interviewers:

PARTITION BY — divides rows into groups; the function restarts in each.
ORDER BY — orders rows inside each partition (needed for ranking and running totals).
frame — limits which rows feed the calculation (ROWS/RANGE).

An empty OVER () treats the whole result set as one partition.

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

All lessons in this course

← Back to SQL Interview Prep