0Pricing
SQL Interview Prep · Lesson

OVER, PARTITION BY and ORDER BY

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

OVER, PARTITION BY and ORDER BY is a free SQL Interview Prep lesson on CoddyKit — lesson 1 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Interview Prep learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

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;

Window vs Aggregate: Same Function, Different Result

The exact same aggregate function behaves differently as a window function. Compare the two queries below conceptually.

  • AVG(salary) with GROUP BY department returns one row per department.
  • AVG(salary) OVER (PARTITION BY department) returns every employee, each tagged with the department average.

Interview tip: stress that the window version does not require GROUP BY and does not remove duplicate detail rows.

-- Aggregate: collapses
SELECT department, AVG(salary)
FROM employees
GROUP BY department;

-- Window: preserves every row
SELECT department, name, AVG(salary) OVER (PARTITION BY department) AS dept_avg
FROM employees;

PARTITION BY: Resetting the Calculation

PARTITION BY is to window functions what GROUP BY is to aggregates, except it does not collapse rows. Each distinct partition value gets its own independent calculation.

In the example, the row number restarts at 1 for every department. Without PARTITION BY, numbering would run continuously across all employees.

  • You can partition by one column or several.
  • No PARTITION BY means one giant partition (the whole set).
SELECT
  department,
  name,
  ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
FROM employees;

ORDER BY Inside OVER

The ORDER BY inside OVER is not the same as the query's final ORDER BY. It only defines the row sequence within each partition for the function to operate on.

  • Ranking functions (ROW_NUMBER, RANK) require it — they need an order to rank by.
  • Plain aggregates over a partition do not need it unless you want a running calculation.

A common interview slip is confusing the window's ORDER BY with the presentation order of the output.

SELECT
  name,
  hire_date,
  ROW_NUMBER() OVER (ORDER BY hire_date) AS seniority_rank
FROM employees
ORDER BY name;  -- output order is independent of the window order

Combining PARTITION BY and ORDER BY

The classic ranking window combines both: PARTITION BY groups, then ORDER BY sequences inside each group.

Read the spec below as: "Within each department, order employees by salary descending, and number them." The highest-paid person in every department gets row number 1.

This single specification is the backbone of the most common window-function interview problems, including top-N-per-group.

SELECT
  department,
  name,
  salary,
  ROW_NUMBER() OVER (
    PARTITION BY department
    ORDER BY salary DESC
  ) AS dept_salary_rank
FROM employees;

ORDER BY Changes Aggregate Behavior

Here is a subtle point interviewers probe: adding ORDER BY to an aggregate window turns it into a running calculation, because an implicit frame ("from start of partition to current row") kicks in.

  • SUM(x) OVER (PARTITION BY g) → the same group total on every row.
  • SUM(x) OVER (PARTITION BY g ORDER BY d) → a running total up to the current row.

Knowing that ORDER BY implicitly adds a frame separates mid-level from junior candidates.

SELECT
  account_id,
  txn_date,
  amount,
  SUM(amount) OVER (
    PARTITION BY account_id
    ORDER BY txn_date
  ) AS running_balance
FROM transactions;

Where Window Functions Are Allowed

Window functions can only appear in the SELECT list and the ORDER BY clause. They are not allowed in WHERE, GROUP BY, or HAVING.

The reason ties back to logical execution order: window functions are evaluated after WHERE, GROUP BY, and HAVING have run. The rows are already chosen before the window even sees them.

This is why filtering on a ranking requires a subquery or CTE — a point covered fully in a later lesson.

-- This FAILS: window function in WHERE
-- SELECT name FROM employees
-- WHERE ROW_NUMBER() OVER (ORDER BY salary) = 1;

-- This works: window in SELECT, filter outside
SELECT * FROM (
  SELECT name, ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn
  FROM employees
) t
WHERE rn = 1;

Multiple Window Functions in One Query

You can use several window functions in the same SELECT, each with its own or a shared specification. The database computes them in one pass over the partitioned data.

This is handy in interviews when you need a rank and a department average together. If two functions share a spec, some dialects let you name it with a WINDOW clause to avoid repetition.

SELECT
  name,
  department,
  salary,
  ROW_NUMBER() OVER w  AS rn,
  AVG(salary) OVER (PARTITION BY department) AS dept_avg
FROM employees
WINDOW w AS (PARTITION BY department ORDER BY salary DESC);

Worked Example: Salary vs Department Average

A frequent analyst question: "List every employee with their salary, the department average, and the difference." One window expression does the heavy lifting; arithmetic does the rest.

Notice there is no GROUP BY and every employee row survives. The dept_avg repeats for everyone in the same department, exactly what makes the comparison possible row by row.

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

Common Mistakes Interviewers Watch For

Avoid these traps when window functions come up:

  • Putting a window function in WHERE or HAVING — illegal; use a subquery.
  • Forgetting ORDER BY on a ranking function — results become arbitrary.
  • Assuming PARTITION BY reduces row count — it never does.
  • Confusing the window's ORDER BY with the final output order.
  • Adding ORDER BY to an aggregate window and not realizing it became a running total.

Quick Check

Test your grasp of the window specification.

Recap: The Window Specification

You now own the anatomy of OVER (...):

  • Window functions keep every row while computing across related rows.
  • PARTITION BY groups and restarts the calculation; it never removes rows.
  • ORDER BY sequences rows inside a partition; ranking functions require it, and it turns aggregates into running calculations.
  • Window functions are legal only in SELECT and ORDER BY — never in WHERE/HAVING.

Next, you will assign deterministic sequence numbers with ROW_NUMBER.

Frequently asked questions

Is the “OVER, PARTITION BY and ORDER BY” lesson free?

Yes — the full text of “OVER, PARTITION BY and ORDER BY” is free to read here on the web, and the SQL Interview Prep course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Interview Prep course, upgrade to CoddyKit PRO.

What will I learn in “OVER, PARTITION BY and ORDER BY”?

The anatomy of a window specification and how partitions reset the calculation. You practise SQL Interview Prep with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Interview Prep?

No prior experience is required. SQL Interview Prep on CoddyKit is structured for beginners through advanced learners; this is — lesson 1 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “OVER, PARTITION BY and ORDER BY” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Interview Prep lesson?

Yes. Every SQL Interview Prep lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

  1. OVER, PARTITION BY and ORDER BY
  2. ROW_NUMBER for Unique Sequencing
  3. RANK vs DENSE_RANK on Ties
  4. Filtering on a Window Result
← Back to SQL Interview Prep