Ranking, Lead and Lag

So far you have computed totals and averages across windows, but sometimes the question is about position: who is first, who is second, and how does each row compare to the one before it? Ranking functions assign a numbered position to every row, while offset functions let you peek at the previous or next row without writing a self-join. These are the tools behind leaderboards, period-over-period reports, and 'show me the highest paid person in each team' questions. In this lesson you will number rows in order, handle ties correctly instead of pretending they do not exist, and look backward and forward across sorted data to spot trends and gaps.

flowchart TD
  T["Tied salaries: 910000, 910000, 980000"]
  T --> R1["ROW_NUMBER: 1, 2, 3"]
  T --> R2["RANK: 1, 1, 3"]
  style R1 fill:#0e7490,color:#fff
  style R2 fill:#1e293b,color:#fff

ROW_NUMBER gives every row a unique number. RANK gives tied rows the same number and skips the next slot.

Ranking with ROW_NUMBER and RANK

There are three ranking functions, and they disagree on what to do with ties. ROW_NUMBER() is ruthless: it hands out 1, 2, 3, 4 in the specified order, and if two rows are identical it still picks an arbitrary winner based on whatever internal order the engine found them in. RANK() is more honest: it gives tied rows the same number, then skips the next integers. Two rows tied for first both get 1, and the next row gets 3. DENSE_RANK() is a compromise: ties share a number, but the next row gets the very next integer, so the sequence runs 1, 1, 2.

Which one you choose depends on the business rule. If you are awarding three bonuses and two people tie for second, ROW_NUMBER might quietly exclude the wrong person because it invents an artificial order. RANK makes the tie visible and fair. If you need contiguous bands for a report, DENSE_RANK keeps the sequence tight without hidden gaps. Always pick the function that matches the policy you are implementing, not the one that happens to return the number you expected.

See how the three functions handle ties in the salary list.

SELECT name, salary,
  ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num,
  RANK() OVER (ORDER BY salary DESC) AS rank_num,
  DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_num
FROM employees;

Peeking at neighbors with LAG and LEAD

LAG(column, offset) looks back that many rows in the ordered window; LEAD looks forward by the same distance. They are perfect for questions like 'how much more does this employee earn than the previous hire?' or 'what is the next project deadline after this one?' Both functions require an ORDER BY inside OVER, because 'previous' and 'next' only make sense once the rows are arranged in a sequence.

The first row in any window has no previous row, so LAG returns NULL there. You can provide a default value as the third argument: LAG(salary, 1, 0) returns 0 instead of NULL for the first row. Without PARTITION BY the window is the whole table; with it, LAG looks back only within the same group, so the first employee in each department starts fresh with NULL.

flowchart LR
  R1["Row 1: Ada"] --> R2["Row 2: Bo"]
  R2 --> R3["Row 3: Chen"]
  R3 --> R4["Row 4: Dina"]
  R2 -.->|LAG| L["Ada"]
  R3 -.->|LAG| L2["Bo"]
  R3 -.->|LEAD| N["Dina"]
  style R2 fill:#0e7490,color:#fff
  style R3 fill:#0e7490,color:#fff

LAG looks back; LEAD looks forward within the ordered window.

Compare each employee's salary to the previous and next in salary order.

SELECT name, salary,
  LAG(salary, 1, 0) OVER (ORDER BY salary) AS prev_salary,
  LEAD(salary, 1, 0) OVER (ORDER BY salary) AS next_salary
FROM employees;

Resetting rank and offset within groups

Ranking the whole company is useful for a global leaderboard, but managers usually ask about relative standing inside their own team. Add PARTITION BY department_id and the rank resets to 1 for each department. The same rule applies to LAG: with PARTITION BY it looks back only within the same group, so the first employee in Marketing has no previous Marketing employee and gets NULL, even if there are earlier rows in Engineering.

Combine PARTITION BY with ORDER BY and you can answer questions like 'who is the highest paid in each department?' without collapsing rows. Use RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) and every department gets its own 1, 2, 3 sequence, independent of every other team. You can even use ranking and offset functions together in the same SELECT list, and the database computes both windows in a single pass.

flowchart TD
  subgraph D1["Engineering"]
    E1["Dina: 1"]
    E2["Chen: 2"]
  end
  subgraph D2["Marketing"]
    M1["Farah: 1"]
    M2["Eli: 2"]
  end
  style D1 fill:#0e7490,color:#fff
  style D2 fill:#1e293b,color:#fff

PARTITION BY starts a new ranking sequence for each department.

Rank employees by salary within each department.

SELECT name, department_id, salary,
  RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dept_rank
FROM employees;

Practice: rank and compare

The exercises below start with simple global ranking, move into partitioned leaderboards, then force you to fix a query that forgets ORDER BY inside OVER and therefore returns random offsets. The transfer task asks you to combine LAG with PARTITION BY to compare each employee to the next hire in their own department — a pattern that appears in succession planning and team-budget forecasting.

Exercise

Assign a global rank to every employee based on salary from highest to lowest. Use RANK() and return name, salary, and global_rank.

SELECT name, salary,
  -- add ranking here
  AS global_rank
FROM employees;

SELECT name, salary, RANK() OVER (ORDER BY salary DESC) AS global_rank FROM employees;

Exercise

Return name, salary, and prev_salary for every employee, where prev_salary is the salary of the employee who was hired immediately before them (by hired_on). Order the result by hired_on.

SELECT name, salary,
  -- look back one row by hire date
  AS prev_salary
FROM employees
ORDER BY hired_on;

SELECT name, salary, LAG(salary) OVER (ORDER BY hired_on) AS prev_salary FROM employees ORDER BY hired_on;

Exercise

This query is meant to show each employee's salary and the salary of the next higher-paid employee. It uses LEAD but forgets the ORDER BY inside OVER, so the 'next' row is random. Fix it by adding the correct ORDER BY.

SELECT name, salary,
  LEAD(salary) OVER () AS next_higher
FROM employees;

SELECT name, salary, LEAD(salary) OVER (ORDER BY salary DESC) AS next_higher FROM employees;

Exercise

For each employee, show their name, department_id, salary, and the salary of the person hired immediately after them in the same department. Call the new column next_in_dept. Use LEAD with PARTITION BY.

SELECT name, department_id, salary,
  -- compute next hire's salary in this department
  AS next_in_dept
FROM employees;

SELECT name, department_id, salary, LEAD(salary) OVER (PARTITION BY department_id ORDER BY hired_on) AS next_in_dept FROM employees;

Exercise

Two employees tie for the highest salary. What does RANK() assign to the next highest-paid employee?

Recap

ROW_NUMBER gives every row a unique number, even when ties exist.
RANK gives tied rows the same number and skips the next integers; DENSE_RANK avoids gaps.
LAG looks back; LEAD looks forward; both need ORDER BY inside OVER to be meaningful.
PARTITION BY resets the window per group, so rankings and offsets stay local.
Add a unique tie-breaker to ORDER BY when reproducibility matters.

Next you will reshape row-level data into summary columns using conditional aggregation — turning vertical lists into horizontal reports.

Ranking with ROW_NUMBER and RANK

Peeking at neighbors with LAG and LEAD

Resetting rank and offset within groups

Practice: rank and compare

Recap

Checkpoint quiz

Go deeper — technical resources