Write a query to simulate the LEAD() function using a SELF JOIN.

Instruction: Assume a table 'employee' with columns 'id', 'name', and 'salary'. Write a SQL query to simulate the LEAD() function to get the salary of the next highest-paid employee without using window functions.

Context: This question challenges the candidate to think creatively and demonstrate their ability to perform advanced SQL operations without relying on built-in functions.

Updated

Example Answer

LEAD means the next row in a defined order, not the next distinct salary. I would order non-null salaries by salary and then unique ID so ties are deterministic. The self join finds later rows, and a NOT EXISTS condition removes any candidate with another row between it and the current row. The last row remains in the result with a null next salary. This demonstrates the semantics, but the native window function is usually clearer and should be preferred when allowed.

Example

Assume employees.id is unique and salary is non-null.

SELECT a.id, a.salary, b.salary AS next_salary
FROM employees AS a
LEFT JOIN employees AS b
  ON (b.salary > a.salary OR (b.salary = a.salary AND b.id > a.id))
 AND NOT EXISTS (
   SELECT 1 FROM employees AS c
   WHERE (c.salary > a.salary OR (c.salary = a.salary AND c.id > a.id))
     AND (c.salary < b.salary OR (c.salary = b.salary AND c.id < b.id))
 )
ORDER BY a.salary, a.id;

For salaries 90, 90, 100, next salaries are 90, 100, NULL. A MIN(salary) comparison using only > would skip the equal-salary row.

Practice follow-up

How would you reproduce LEAD with offset 2, or partition by department?

Sources

PostgreSQL window function semantics.

Related Questions