Advanced SQL: Window Functions, CTEs and Interview Problems

What this lesson covers

SQL fundamentals taught you to filter, group, join and nest queries. This lesson adds the tools that turn awkward, multi-step queries into short readable ones: window functions, common table expressions (including recursive ones), CASE expressions, views and materialized views, and the code that lives inside the database: stored procedures, functions and triggers.

The second half is a set of 15 classic interview problems: the Nth highest salary several ways, finding and deleting duplicates, employees earning more than their managers, top three per department, consecutive-day streaks, running totals, pivots, and more. These appear again and again in SQL rounds and online assessments. Each solution was run in SQLite 3.51 and the output shown is the real result. Where syntax differs in PostgreSQL, MySQL or SQL Server, the text says so.

Interviewers rarely want only "a" query that works. They ask follow-ups: "What if two people tie?", "What if there is no second-highest salary?", "Can you do it without window functions?", "How does this behave with NULLs?". The solutions below answer those questions too.

The sample data

CREATE TABLE employees (
  emp_id     INTEGER PRIMARY KEY,
  name       TEXT    NOT NULL,
  dept       TEXT    NOT NULL,
  salary     INTEGER NOT NULL,
  manager_id INTEGER REFERENCES employees(emp_id)
);
INSERT INTO employees VALUES
  (1,  'Asha',   'Engineering', 120000, NULL),
  (2,  'Ravi',   'Engineering',  95000, 1),
  (3,  'Meera',  'Engineering',  95000, 1),
  (4,  'Kiran',  'Engineering',  80000, 2),
  (5,  'Farah',  'Engineering', 100000, 2),
  (6,  'Dev',    'Sales',        70000, 1),
  (7,  'Priya',  'Sales',        85000, 6),
  (8,  'Arjun',  'Sales',        60000, 6),
  (9,  'Neha',   'HR',           65000, 1),
  (10, 'Sameer', 'HR',           50000, 9);

CREATE TABLE customers (
  customer_id INTEGER PRIMARY KEY,
  name        TEXT NOT NULL
);
INSERT INTO customers VALUES (1, 'Anil'), (2, 'Bina'), (3, 'Chetan'), (4, 'Divya');

CREATE TABLE orders (
  order_id    INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers(customer_id),
  order_date  TEXT    NOT NULL,
  amount      INTEGER NOT NULL
);
INSERT INTO orders VALUES
  (101, 1, '2026-01-05',  500),
  (102, 2, '2026-01-10', 1200),
  (103, 1, '2026-01-20',  300),
  (104, 3, '2026-02-02',  800),
  (105, 1, '2026-02-14',  700),
  (106, 2, '2026-03-01',  400),
  (107, 3, '2026-03-15', 1500);

Note two deliberate details: Ravi and Meera both earn 95000 (a tie), and Divya has never ordered. The org chart is: Asha at the top; Ravi, Meera, Dev and Neha report to her; Kiran and Farah report to Ravi; Priya and Arjun report to Dev; Sameer reports to Neha.

Window functions

The idea

An aggregate with GROUP BY collapses rows: ten employees become three department rows. Often you want the aggregate and the detail on the same row: each employee's salary next to the department average, or each order next to the running total so far.

A window function computes a value over a set of rows related to the current row (its window) without collapsing them. Every input row stays in the output.

 GROUP BY dept                 window: AVG(salary) OVER (PARTITION BY dept)
 +-------------+---------+     +-------+-------------+--------+----------+
 | dept        | avg     |     | name  | dept        | salary | dept_avg |
 +-------------+---------+     +-------+-------------+--------+----------+
 | Engineering | 98000   |     | Asha  | Engineering | 120000 | 98000    |
 | HR          | 57500   |     | Ravi  | Engineering |  95000 | 98000    |
 | Sales       | 71666.7 |     | ...   | ...         |  ...   | ...      |
 +-------------+---------+     +-------+-------------+--------+----------+
   3 rows                        10 rows, each keeps its detail

Syntax

function_name(args) OVER (
    PARTITION BY  columns      -- split rows into independent groups
    ORDER BY      columns      -- order rows inside each partition
    frame_clause               -- which rows around the current one
)

• PARTITION BY divides rows into partitions; the function restarts in each. Without it, the whole result is one partition.
• ORDER BY inside OVER orders rows within the partition. Ranking functions and LAG/LEAD need it; for aggregates it turns the result into a running calculation.
• The frame narrows the window further, for example "the current row and the two before it".

Window functions are evaluated after WHERE, GROUP BY and HAVING, at the SELECT step. Therefore you cannot use a window function in WHERE. To filter on one, compute it in a subquery or CTE and filter outside. This rule comes up in almost every problem below.

Support: PostgreSQL, SQL Server, Oracle, SQLite 3.25+ and MySQL 8.0+ all support window functions. MySQL 5.7 does not, which is why interviewers sometimes ask for solutions without them.

ROW_NUMBER, RANK and DENSE_RANK

All three number rows in the window's order. They differ only in how they treat ties.

SELECT name, dept, salary,
       ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num,
       RANK()       OVER (ORDER BY salary DESC) AS rnk,
       DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rnk
FROM employees;

name   | dept        | salary | row_num | rnk | dense_rnk
-------+-------------+--------+---------+-----+----------
Asha   | Engineering | 120000 | 1       | 1   | 1
Farah  | Engineering | 100000 | 2       | 2   | 2
Ravi   | Engineering |  95000 | 3       | 3   | 3
Meera  | Engineering |  95000 | 4       | 3   | 3
Priya  | Sales       |  85000 | 5       | 5   | 4
Kiran  | Engineering |  80000 | 6       | 6   | 5
Dev    | Sales       |  70000 | 7       | 7   | 6
Neha   | HR          |  65000 | 8       | 8   | 7
Arjun  | Sales       |  60000 | 9       | 9   | 8
Sameer | HR          |  50000 | 10      | 10  | 9

Look at the tie between Ravi and Meera:

• ROW_NUMBER gives every row a unique number: 3 and 4. Which of the two gets 3 is arbitrary unless you add a tie-breaker, such as ORDER BY salary DESC, emp_id.
• RANK gives ties the same number and then skips: 3, 3, then 5. Like sports rankings: two people in third place means no fourth place.
• DENSE_RANK gives ties the same number and does not skip: 3, 3, then 4.

PARTITION BY: ranking and averages per group

SELECT name, dept, salary,
       RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS dept_rank,
       ROUND(AVG(salary) OVER (PARTITION BY dept), 2)       AS dept_avg
FROM employees
ORDER BY dept, dept_rank;

name   | dept        | salary | dept_rank | dept_avg
-------+-------------+--------+-----------+---------
Asha   | Engineering | 120000 | 1         | 98000.0
Farah  | Engineering | 100000 | 2         | 98000.0
Ravi   | Engineering |  95000 | 3         | 98000.0
Meera  | Engineering |  95000 | 3         | 98000.0
Kiran  | Engineering |  80000 | 5         | 98000.0
Neha   | HR          |  65000 | 1         | 57500.0
Sameer | HR          |  50000 | 2         | 57500.0
Priya  | Sales       |  85000 | 1         | 71666.67
Dev    | Sales       |  70000 | 2         | 71666.67
Arjun  | Sales       |  60000 | 3         | 71666.67

Ranking restarts at 1 in each department. Check the Engineering average: (120000 + 100000 + 95000 + 95000 + 80000) / 5 = 490000 / 5 = 98000. Sales: 215000 / 3 = 71666.67.

Running totals and the frame

Adding ORDER BY to an aggregate window makes it cumulative.

SELECT order_id, order_date, amount,
       SUM(amount) OVER (ORDER BY order_date) AS running_total,
       SUM(amount) OVER (PARTITION BY customer_id
                         ORDER BY order_date)  AS customer_running
FROM orders
ORDER BY order_date;

order_id | order_date | amount | running_total | customer_running
---------+------------+--------+---------------+-----------------
101      | 2026-01-05 |  500   |  500          |  500
102      | 2026-01-10 | 1200   | 1700          | 1200
103      | 2026-01-20 |  300   | 2000          |  800
104      | 2026-02-02 |  800   | 2800          |  800
105      | 2026-02-14 |  700   | 3500          | 1500
106      | 2026-03-01 |  400   | 3900          | 1600
107      | 2026-03-15 | 1500   | 5400          | 2300

customer_running restarts per customer: Anil (orders 101, 103, 105) goes 500, 800, 1500.

The frame clause says exactly which rows count:

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW   start to here
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW           last 3 rows
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING           neighbors
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING   here to end

A three-order moving average:

SELECT order_id, amount,
       ROUND(AVG(amount) OVER (ORDER BY order_date
             ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 2) AS moving_avg_3
FROM orders
ORDER BY order_date;

order_id | amount | moving_avg_3
---------+--------+-------------
101      |  500   | 500.0
102      | 1200   | 850.0
103      |  300   | 666.67
104      |  800   | 766.67
105      |  700   | 600.0
106      |  400   | 633.33
107      | 1500   | 866.67

Order 104: (1200 + 300 + 800) / 3 = 766.67. The first two rows average over fewer rows because there is nothing before them.

ROWS versus RANGE: the tie trap

When you write ORDER BY without a frame, the default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. RANGE treats all rows with the same ORDER BY value as one peer group. With ties, a "running total" jumps:

SELECT name, salary,
       SUM(salary) OVER (ORDER BY salary DESC) AS range_default,
       SUM(salary) OVER (ORDER BY salary DESC
                         ROWS UNBOUNDED PRECEDING) AS rows_frame
FROM employees
WHERE dept = 'Engineering'
ORDER BY salary DESC;

name  | salary | range_default | rows_frame
------+--------+---------------+-----------
Asha  | 120000 | 120000        | 120000
Farah | 100000 | 220000        | 220000
Ravi  |  95000 | 410000        | 315000
Meera |  95000 | 410000        | 410000
Kiran |  80000 | 490000        | 490000

With RANGE, Ravi already includes Meera's 95000, because they are peers. With ROWS, the total grows one row at a time. Use ROWS (and a unique tie-breaker in ORDER BY) when you want a true row-by-row running total.

LAG and LEAD

LAG(col, n, default) reads the value from n rows before the current row in the window order; LEAD reads n rows after. n defaults to 1. They replace self-joins for "compare with previous row" questions.

SELECT order_id, customer_id, order_date, amount,
       LAG(amount)      OVER (PARTITION BY customer_id ORDER BY order_date) AS prev_amount,
       LEAD(order_date) OVER (PARTITION BY customer_id ORDER BY order_date) AS next_date
FROM orders
ORDER BY customer_id, order_date;

order_id | cust | order_date | amount | prev_amount | next_date
---------+------+------------+--------+-------------+-----------
101      | 1    | 2026-01-05 |  500   | NULL        | 2026-01-20
103      | 1    | 2026-01-20 |  300   | 500         | 2026-02-14
105      | 1    | 2026-02-14 |  700   | 300         | NULL
102      | 2    | 2026-01-10 | 1200   | NULL        | 2026-03-01
106      | 2    | 2026-03-01 |  400   | 1200        | NULL
104      | 3    | 2026-02-02 |  800   | NULL        | 2026-03-15
107      | 3    | 2026-03-15 | 1500   | 800         | NULL

The first row of each partition has no previous row, so LAG returns NULL (or the default if you pass one: LAG(amount, 1, 0)).

Other window functions

LAST_VALUE is a classic trap: with the default frame it returns the current row's value. Write ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING to get the real last value of the partition.

Common table expressions (CTEs)

A CTE is a named temporary result defined with WITH at the start of a query and usable like a table in the main query. It exists only for that one statement.

WITH dept_avg AS (
  SELECT dept, AVG(salary) AS avg_sal
  FROM employees
  GROUP BY dept
)
SELECT dept, avg_sal
FROM dept_avg
WHERE avg_sal > (SELECT AVG(avg_sal) FROM dept_avg);

Result: Engineering, 98000.0. The three department averages are 98000, 57500 and 71666.67; their mean is 75722.22, and only Engineering is above it.

Why CTEs help:

• Readability: you build the query in named steps, top to bottom, instead of nesting subqueries inside out.
• Reuse: one CTE can be referenced several times in the main query (as dept_avg is, twice).
• Recursion: only CTEs can be recursive.

You can chain several: WITH a AS (...), b AS (SELECT ... FROM a) SELECT ... FROM b.

On performance: a CTE is not automatically cached. PostgreSQL before version 12 always computed CTEs separately (an "optimization fence"); from 12 on it can inline them like subqueries, and you can force either with MATERIALIZED / NOT MATERIALIZED. Other databases mostly inline them. Treat CTEs as a readability tool, not a speed tool.

Recursive CTEs

A recursive CTE refers to itself. It is how SQL walks hierarchies (org charts, category trees, bill of materials) and graphs, and generates series.

It always has two parts joined by UNION ALL:

1. The anchor member: a normal query that produces the starting rows.
2. The recursive member: a query that references the CTE itself and produces new rows from the rows produced in the previous step.

The database runs the anchor, then repeatedly runs the recursive member on the newest rows until it produces no new rows.

 iteration 0 (anchor):     rows R0
 iteration 1:              f(R0) = R1
 iteration 2:              f(R1) = R2
 ...                       stop when Rk is empty
 result = R0 UNION ALL R1 UNION ALL ... UNION ALL Rk-1

Use WITH RECURSIVE in PostgreSQL, MySQL 8 and SQLite; SQL Server and Oracle use plain WITH.

Number series

WITH RECURSIVE nums(n) AS (
  SELECT 1                              -- anchor
  UNION ALL
  SELECT n + 1 FROM nums WHERE n < 5    -- recursive step
)
SELECT n FROM nums;

Result: 1, 2, 3, 4, 5. The WHERE n < 5 is the termination condition. Without it the query runs forever (SQL Server stops at 100 levels by default, MAXRECURSION; MySQL at 1000 by default, cte_max_recursion_depth; SQLite and PostgreSQL will keep going).

The same trick generates a calendar, useful for filling in days with no data:

WITH RECURSIVE days(d) AS (
  SELECT '2026-01-01'
  UNION ALL
  SELECT date(d, '+1 day') FROM days WHERE d < '2026-01-05'
)
SELECT d FROM days;

Result: 2026-01-01 through 2026-01-05. (date(d, '+1 day') is SQLite; PostgreSQL has generate_series('2026-01-01'::date, '2026-01-05', '1 day').)

Org chart

Walk down from the CEO, tracking each person's level and path:

WITH RECURSIVE org AS (
  SELECT emp_id, name, manager_id, 0 AS level, name AS path
  FROM employees
  WHERE manager_id IS NULL                       -- anchor: the top
  UNION ALL
  SELECT e.emp_id, e.name, e.manager_id,
         o.level + 1, o.path || ' > ' || e.name  -- one level down
  FROM employees e
  JOIN org o ON e.manager_id = o.emp_id
)
SELECT level, name, path FROM org ORDER BY path;

level | name   | path
------+--------+------------------------
0     | Asha   | Asha
1     | Dev    | Asha > Dev
2     | Arjun  | Asha > Dev > Arjun
2     | Priya  | Asha > Dev > Priya
1     | Meera  | Asha > Meera
1     | Neha   | Asha > Neha
2     | Sameer | Asha > Neha > Sameer
1     | Ravi   | Asha > Ravi
2     | Farah  | Asha > Ravi > Farah
2     | Kiran  | Asha > Ravi > Kiran

Trace it: the anchor returns Asha (level 0). Iteration 1 finds everyone whose manager is Asha: Ravi, Meera, Dev, Neha (level 1). Iteration 2 finds their reports: Kiran, Farah, Priya, Arjun, Sameer (level 2). Iteration 3 finds nobody, so recursion stops. (|| concatenates strings in SQLite, PostgreSQL and Oracle; MySQL uses CONCAT.)

"Everyone under Ravi, at any depth" just changes the anchor:

WITH RECURSIVE reports AS (
  SELECT emp_id, name FROM employees WHERE manager_id = 2
  UNION ALL
  SELECT e.emp_id, e.name
  FROM employees e JOIN reports r ON e.manager_id = r.emp_id
)
SELECT name FROM reports;

Result: Kiran, Farah. Neither has reports, so it stops after one level.

CASE expressions

CASE returns a value based on conditions, and can be used anywhere an expression is allowed. It has two forms.

-- Searched CASE: any conditions, checked top to bottom
SELECT name, salary,
       CASE
         WHEN salary >= 100000 THEN 'Band A'
         WHEN salary >=  75000 THEN 'Band B'
         ELSE 'Band C'
       END AS band
FROM employees
ORDER BY salary DESC;

Asha and Farah are Band A; Ravi, Meera, Priya and Kiran are Band B; the rest are Band C. The first true WHEN wins, so put the strictest condition first. Without ELSE, unmatched rows get NULL.

The simple form compares one expression to values: CASE dept WHEN 'HR' THEN 1 WHEN 'Sales' THEN 2 ELSE 3 END. That is handy for a custom sort order:

SELECT name, dept FROM employees
ORDER BY CASE dept WHEN 'HR' THEN 1 WHEN 'Sales' THEN 2 ELSE 3 END, name;

Conditional aggregation puts CASE inside an aggregate to count or sum subsets in one pass:

SELECT dept,
       COUNT(CASE WHEN salary >= 80000 THEN 1 END) AS high_paid,
       COUNT(*) AS total
FROM employees
GROUP BY dept;

dept        | high_paid | total
------------+-----------+------
Engineering | 5         | 5
HR          | 0         | 2
Sales       | 1         | 3

COUNT skips the NULLs that CASE returns when the condition fails. This pattern is also how you pivot rows into columns (problem 11). PostgreSQL offers the shorter COUNT(*) FILTER (WHERE salary >= 80000).

Views and materialized views

Views

A view is a named, stored SELECT. It holds no data of its own; each time you query it, the database runs (or merges) the underlying query.

CREATE VIEW dept_summary AS
SELECT dept, COUNT(*) AS headcount, SUM(salary) AS payroll
FROM employees
GROUP BY dept;

SELECT * FROM dept_summary WHERE headcount >= 3;

dept        | headcount | payroll
------------+-----------+--------
Engineering | 5         | 490000
Sales       | 3         | 215000

Uses:

• Simplicity: hide a complex join behind a simple name.
• Security: grant users access to a view that exposes only some columns or rows (for example, without salary), not to the base table.
• Logical data independence: if you restructure tables, redefine the view so old queries still work (see DBMS introduction).

Updatable views. You can INSERT, UPDATE or DELETE through a simple view, one that selects from a single table without aggregates, DISTINCT, GROUP BY or set operations, in PostgreSQL, MySQL and SQL Server. Views with joins or aggregates generally are not updatable unless you write an INSTEAD OF trigger. SQLite views are read-only unless you add INSTEAD OF triggers. WITH CHECK OPTION stops inserts or updates through a view that would produce rows the view itself cannot see.

Materialized views

A materialized view stores the query's result physically, like a table. Reads are fast because nothing is recomputed, but the data is a snapshot that goes stale until refreshed.

PostgreSQL syntax (not available in SQLite or MySQL):

-- PostgreSQL
CREATE MATERIALIZED VIEW dept_summary_mv AS
SELECT dept, COUNT(*) AS headcount, SUM(salary) AS payroll
FROM employees
GROUP BY dept;

REFRESH MATERIALIZED VIEW dept_summary_mv;
-- Allows reads during refresh; needs a unique index on the view
REFRESH MATERIALIZED VIEW CONCURRENTLY dept_summary_mv;

Oracle supports materialized views with automatic or on-commit refresh; SQL Server has indexed views, which it keeps up to date automatically; MySQL has none, so people maintain a summary table with scheduled jobs or triggers.

Stored procedures, functions and triggers

These put logic inside the database. SQLite supports triggers but not stored procedures or SQL-defined functions, so the procedure and function examples below use PostgreSQL syntax and were not run here.

Stored procedures

A stored procedure is a named block of SQL and procedural code (variables, IF, loops) stored in the database and run with CALL. It can run several statements, manage transactions (in PostgreSQL 11+ procedures, and in MySQL), and return results through output parameters.

-- PostgreSQL 11+
CREATE PROCEDURE give_raise(p_dept TEXT, p_pct NUMERIC)
LANGUAGE plpgsql
AS $$
BEGIN
  UPDATE employees
  SET salary = salary * (1 + p_pct / 100)
  WHERE dept = p_dept;
END;
$$;

CALL give_raise('HR', 10);

Functions

A function (user-defined function) takes arguments and returns a value (or a table), so it can be used inside queries.

-- PostgreSQL
CREATE FUNCTION annual_ctc(monthly INTEGER)
RETURNS INTEGER
LANGUAGE sql IMMUTABLE
AS $$ SELECT monthly * 12 $$;

SELECT name, annual_ctc(salary / 12) FROM employees;

The exact rules differ by database (for example, SQL Server functions cannot modify data at all), so mention which one you mean.

Pros of database-side code: fewer network round trips, logic close to data, one implementation shared by many apps, permissions can be granted on the procedure rather than the tables. Cons: harder to version, test and debug than application code; ties you to one vendor's language; can make the database a bottleneck that is harder to scale than stateless app servers. Many modern teams keep business logic in the application and use procedures sparingly.

Triggers

A trigger is code the database runs automatically when a specified event happens on a table: INSERT, UPDATE or DELETE, BEFORE or AFTER the change (or INSTEAD OF for views), once per affected row (FOR EACH ROW) or once per statement. Inside a row trigger, OLD is the row before the change and NEW the row after.

An audit trail, tested in SQLite:

CREATE TABLE salary_audit (
  emp_id     INTEGER NOT NULL,
  old_salary INTEGER NOT NULL,
  new_salary INTEGER NOT NULL,
  changed_at TEXT    NOT NULL DEFAULT (datetime('now'))
);

CREATE TRIGGER trg_salary_audit
AFTER UPDATE OF salary ON employees
FOR EACH ROW
WHEN NEW.salary <> OLD.salary
BEGIN
  INSERT INTO salary_audit (emp_id, old_salary, new_salary)
  VALUES (OLD.emp_id, OLD.salary, NEW.salary);
END;

CREATE TRIGGER trg_no_pay_cut
BEFORE UPDATE OF salary ON employees
FOR EACH ROW
WHEN NEW.salary < OLD.salary
BEGIN
  SELECT RAISE(ABORT, 'salary cannot decrease');
END;

Try it inside a transaction and roll back, so the sample data stays unchanged for the problems below:

BEGIN;
UPDATE employees SET salary = salary + 5000 WHERE dept = 'HR';
SELECT emp_id, old_salary, new_salary FROM salary_audit;
ROLLBACK;

emp_id | old_salary | new_salary
-------+------------+-----------
9      | 65000      | 70000
10     | 50000      | 55000

And UPDATE employees SET salary = 1000 WHERE emp_id = 1; fails with salary cannot decrease.

In PostgreSQL, a trigger calls a separate trigger function: CREATE FUNCTION ... RETURNS trigger then CREATE TRIGGER ... EXECUTE FUNCTION fn(). MySQL uses CREATE TRIGGER ... FOR EACH ROW BEGIN ... END similar to SQLite, but supports only row-level triggers.

Uses: audit logs, enforcing rules that constraints cannot express, keeping a derived or summary column in sync, and INSTEAD OF triggers for updatable views.

15 classic SQL interview problems

Every problem uses the sample data above. Where it helps, the solution is shown more than one way, because interviewers often ask for an alternative.

Problem 1. Second highest salary

Distinct salaries in descending order are 120000, 100000, 95000, 85000, 80000, 70000, 65000, 60000, 50000. The answer is 100000.

-- Way 1: the maximum below the maximum
SELECT MAX(salary) AS second_highest
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

-- Way 2: skip the top distinct value; wrapping in a scalar
-- subquery returns NULL (not an empty result) if there is none
SELECT (SELECT DISTINCT salary FROM employees
        ORDER BY salary DESC
        LIMIT 1 OFFSET 1) AS second_highest;

Both return 100000. Follow-ups: without DISTINCT, way 2 would break if the top salary were tied. If every employee earned the same, way 1 returns NULL (MAX over no rows) and way 2 also returns NULL thanks to the outer SELECT; a bare LIMIT ... OFFSET query would return no row at all.

Problem 2. Nth highest salary (N = 3)

Expected: 95000 (third distinct value).

-- Way 1: LIMIT/OFFSET, offset = N - 1
SELECT DISTINCT salary FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 2;

-- Way 2: DENSE_RANK (ties share a rank, no gaps)
SELECT DISTINCT salary
FROM (SELECT salary,
             DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
      FROM employees) AS ranked
WHERE rnk = 3;

-- Way 3: correlated subquery, works without window functions or LIMIT:
-- a salary is Nth highest if exactly N-1 distinct salaries are greater
SELECT DISTINCT e1.salary
FROM employees e1
WHERE 3 - 1 = (SELECT COUNT(DISTINCT e2.salary)
               FROM employees e2
               WHERE e2.salary > e1.salary);

All three return 95000. Way 3 is O(n²) without an index, but it works in any SQL dialect. In SQL Server, way 1 becomes OFFSET 2 ROWS FETCH NEXT 1 ROWS ONLY or TOP. If you used RANK instead of DENSE_RANK, rank 3 would still be 95000 here, but for N = 4 RANK would find nothing (ranks go 3, 3, 5), while DENSE_RANK gives 85000.

Problem 3. Find duplicate emails

CREATE TABLE person (id INTEGER PRIMARY KEY, email TEXT NOT NULL);
INSERT INTO person VALUES
  (1, 'a@x.com'), (2, 'b@x.com'), (3, 'a@x.com'),
  (4, 'c@x.com'), (5, 'b@x.com'), (6, 'a@x.com');

SELECT email, COUNT(*) AS copies
FROM person
GROUP BY email
HAVING COUNT(*) > 1;

email   | copies
--------+-------
a@x.com | 3
b@x.com | 2

This is the textbook HAVING use: the filter is on a group count, so it cannot go in WHERE. To see every duplicate row with its id, use COUNT(*) OVER (PARTITION BY email) in a subquery and keep rows where it is greater than 1.

Problem 4. Delete duplicates, keeping one row (the lowest id)

-- Way 1: keep the minimum id of each email
DELETE FROM person
WHERE id NOT IN (SELECT MIN(id) FROM person GROUP BY email);

SELECT * FROM person;

id | email
---+--------
1  | a@x.com
2  | b@x.com
4  | c@x.com

-- Reset the data, then Way 2: ROW_NUMBER marks every copy after the first
DELETE FROM person;
INSERT INTO person VALUES
  (1, 'a@x.com'), (2, 'b@x.com'), (3, 'a@x.com'),
  (4, 'c@x.com'), (5, 'b@x.com'), (6, 'a@x.com');

DELETE FROM person
WHERE id IN (
  SELECT id FROM (
    SELECT id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn
    FROM person
  ) AS numbered
  WHERE rn > 1
);

Same result: ids 3, 5 and 6 are removed. NOT IN is safe here because MIN(id) of a primary key is never NULL.

MySQL refuses a DELETE whose subquery reads the same table directly (error 1093), so either wrap the subquery in another derived table, as in way 2, or use a self-join delete: DELETE p1 FROM person p1 JOIN person p2 ON p1.email = p2.email AND p1.id > p2.id;. Finally, add UNIQUE (email) so duplicates cannot come back.

Problem 5. Employees who earn more than their managers

SELECT e.name AS employee, e.salary,
       m.name AS manager, m.salary AS manager_salary
FROM employees e
JOIN employees m ON e.manager_id = m.emp_id
WHERE e.salary > m.salary;

employee | salary | manager | manager_salary
---------+--------+---------+---------------
Farah    | 100000 | Ravi    | 95000
Priya    |  85000 | Dev     | 70000

A self-join: alias e is the employee row, m the manager row. An inner join is correct because Asha (no manager) cannot satisfy the condition anyway.

Problem 6. Top three salaries in each department

"Top three" usually means the three highest distinct salaries, so ties are all included. Use DENSE_RANK.

SELECT dept, name, salary
FROM (
  SELECT dept, name, salary,
         DENSE_RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS rnk
  FROM employees
) AS ranked
WHERE rnk <= 3
ORDER BY dept, salary DESC, name;

dept        | name   | salary
------------+--------+-------
Engineering | Asha   | 120000
Engineering | Farah  | 100000
Engineering | Meera  |  95000
Engineering | Ravi   |  95000
HR          | Neha   |  65000
HR          | Sameer |  50000
Sales       | Priya  |  85000
Sales       | Dev    |  70000
Sales       | Arjun  |  60000

Engineering returns four people because Ravi and Meera tie for the third distinct salary; Kiran (80000, dense rank 4) is excluded. HR has only two employees, so it returns two. If the requirement is "exactly three people per department", use ROW_NUMBER with a tie-breaker instead.

Without window functions (MySQL 5.7), count the distinct higher salaries in the same department:

SELECT e1.dept, e1.name, e1.salary
FROM employees e1
WHERE (SELECT COUNT(DISTINCT e2.salary)
       FROM employees e2
       WHERE e2.dept = e1.dept AND e2.salary > e1.salary) < 3
ORDER BY e1.dept, e1.salary DESC, e1.name;

Same nine rows.

Problem 7. Second highest salary in each department

SELECT dept, name, salary
FROM (
  SELECT dept, name, salary,
         DENSE_RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS rnk
  FROM employees
) AS ranked
WHERE rnk = 2
ORDER BY dept;

dept        | name   | salary
------------+--------+-------
Engineering | Farah  | 100000
HR          | Sameer |  50000
Sales       | Dev    |  70000

A department with only one distinct salary simply does not appear. If the interviewer wants a row with NULL for such departments, start from the list of departments and LEFT JOIN this result.

Problem 8. Customers who never placed an order

-- Way 1: anti-join
SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL;

-- Way 2: NOT EXISTS
SELECT name FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id);

Both return Divya. Avoid WHERE customer_id NOT IN (SELECT customer_id FROM orders) unless the column is NOT NULL: one NULL in the subquery makes NOT IN return no rows at all.

Problem 9. Users who logged in on three or more consecutive days

This is the gaps and islands problem: find runs ("islands") of consecutive values.

CREATE TABLE logins (user_id INTEGER NOT NULL, login_date TEXT NOT NULL);
INSERT INTO logins VALUES
  (1, '2026-01-01'), (1, '2026-01-02'), (1, '2026-01-02'),
  (1, '2026-01-03'), (1, '2026-01-05'),
  (2, '2026-01-01'), (2, '2026-01-03'), (2, '2026-01-04'),
  (3, '2026-01-10'), (3, '2026-01-11'), (3, '2026-01-12'), (3, '2026-01-13');

User 1 has a run of three days (1st to 3rd; note the duplicate login on the 2nd), user 2's longest run is two, and user 3 has four.

The key trick: within a run of consecutive dates, date minus row number is constant. When a gap appears, the difference jumps.

user 1   login_date   rn   login_date - rn days
         2026-01-01   1    2025-12-31
         2026-01-02   2    2025-12-31   same group
         2026-01-03   3    2025-12-31
         2026-01-05   4    2026-01-01   new group (gap on the 4th)

WITH d AS (                      -- remove duplicate logins per day first
  SELECT DISTINCT user_id, login_date FROM logins
),
g AS (
  SELECT user_id, login_date,
         julianday(login_date)
           - ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS grp
  FROM d
)
SELECT user_id,
       MIN(login_date) AS streak_start,
       MAX(login_date) AS streak_end,
       COUNT(*)        AS days
FROM g
GROUP BY user_id, grp
HAVING COUNT(*) >= 3;

user_id | streak_start | streak_end | days
--------+--------------+------------+-----
1       | 2026-01-01   | 2026-01-03 | 3
3       | 2026-01-10   | 2026-01-13 | 4

julianday turns a date into a day number in SQLite. In PostgreSQL write login_date - ROW_NUMBER() OVER (...) * INTERVAL '1 day' (or subtract an integer from a date); in MySQL, DATE_SUB(login_date, INTERVAL rn DAY). Forgetting the DISTINCT step is the classic bug: the duplicate login would get its own row number and break the run.

A shorter alternative when you only need "who has a run of at least 3": compare each date with the date two rows earlier.

WITH d AS (SELECT DISTINCT user_id, login_date FROM logins)
SELECT DISTINCT user_id
FROM (
  SELECT user_id, login_date,
         LAG(login_date, 2) OVER (PARTITION BY user_id ORDER BY login_date) AS two_before
  FROM d
) AS t
WHERE julianday(login_date) - julianday(two_before) = 2;

Result: users 1 and 3. Since dates are distinct and sorted, a two-day difference across two rows means three consecutive days.

Problem 10. Running total of monthly revenue

SELECT month, monthly_total,
       SUM(monthly_total) OVER (ORDER BY month) AS running_total
FROM (
  SELECT strftime('%Y-%m', order_date) AS month,
         SUM(amount) AS monthly_total
  FROM orders
  GROUP BY month
) AS m;

month   | monthly_total | running_total
--------+---------------+--------------
2026-01 | 2000          | 2000
2026-02 | 1500          | 3500
2026-03 | 1900          | 5400

January is 500 + 1200 + 300 = 2000; February 800 + 700 = 1500; March 400 + 1500 = 1900. (strftime is SQLite; use DATE_TRUNC('month', order_date) in PostgreSQL or DATE_FORMAT(order_date, '%Y-%m') in MySQL.)

Without window functions, a self-join sums every earlier-or-equal row:

SELECT o1.order_id, o1.amount, SUM(o2.amount) AS running_total
FROM orders o1
JOIN orders o2 ON o2.order_date <= o1.order_date
GROUP BY o1.order_id, o1.amount
ORDER BY o1.order_date;

This gives 500, 1700, 2000, 2800, 3500, 3900, 5400, matching the window version earlier, but it is O(n²); the window version is a single pass over sorted rows.

Problem 11. Pivot rows into columns

Quarterly sales stored one row per region and quarter should be shown one row per region, one column per quarter.

CREATE TABLE sales (region TEXT NOT NULL, quarter TEXT NOT NULL, amount INTEGER NOT NULL);
INSERT INTO sales VALUES
  ('North', 'Q1', 100), ('North', 'Q2', 150), ('North', 'Q3', 120), ('North', 'Q4', 130),
  ('South', 'Q1', 200), ('South', 'Q3', 180), ('South', 'Q4', 90);

SELECT region,
       SUM(CASE WHEN quarter = 'Q1' THEN amount ELSE 0 END) AS q1,
       SUM(CASE WHEN quarter = 'Q2' THEN amount ELSE 0 END) AS q2,
       SUM(CASE WHEN quarter = 'Q3' THEN amount ELSE 0 END) AS q3,
       SUM(CASE WHEN quarter = 'Q4' THEN amount ELSE 0 END) AS q4,
       SUM(amount) AS total
FROM sales
GROUP BY region
ORDER BY region;

region | q1  | q2  | q3  | q4  | total
-------+-----+-----+-----+-----+------
North  | 100 | 150 | 120 | 130 | 500
South  | 200 | 0   | 180 | 90  | 470

South has no Q2 row, so it shows 0 (because of ELSE 0; without it you would get NULL, which is arguably more honest). SQL Server and Oracle have a PIVOT operator, but conditional aggregation works everywhere. Columns must be known when the query is written; a truly dynamic pivot needs the query text built in code.

Problem 12. Department with the highest average salary

-- Way 1: sort and take one (drops ties)
SELECT dept, AVG(salary) AS avg_salary
FROM employees
GROUP BY dept
ORDER BY avg_salary DESC
LIMIT 1;

-- Way 2: keeps all departments tied for the top
WITH a AS (
  SELECT dept, AVG(salary) AS avg_salary FROM employees GROUP BY dept
)
SELECT dept, avg_salary FROM a
WHERE avg_salary = (SELECT MAX(avg_salary) FROM a);

Both return Engineering, 98000.0. Mention the tie behavior: way 1 silently picks one of several tied departments.

Problem 13. Month-over-month revenue change

WITH m AS (
  SELECT strftime('%Y-%m', order_date) AS month, SUM(amount) AS revenue
  FROM orders
  GROUP BY month
)
SELECT month, revenue,
       LAG(revenue) OVER (ORDER BY month) AS prev_revenue,
       revenue - LAG(revenue) OVER (ORDER BY month) AS change,
       ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
             / LAG(revenue) OVER (ORDER BY month), 1) AS pct_change
FROM m;

month   | revenue | prev_revenue | change | pct_change
--------+---------+--------------+--------+-----------
2026-01 | 2000    | NULL         | NULL   | NULL
2026-02 | 1500    | 2000         | -500   | -25.0
2026-03 | 1900    | 1500         | 400    | 26.7

Check: −500 / 2000 = −25%; 400 / 1500 = 26.67%. The 100.0 forces decimal division; with two integers, SQLite, PostgreSQL and SQL Server do integer division (MySQL does not). A month with zero revenue would cause division by zero; guard with NULLIF(LAG(...), 0). Months with no orders at all are missing from m; join to a generated calendar (the recursive CTE above) to show them.

Problem 14. Each customer's first order

SELECT customer_id, order_id, order_date, amount
FROM (
  SELECT o.*,
         ROW_NUMBER() OVER (PARTITION BY customer_id
                            ORDER BY order_date, order_id) AS rn
  FROM orders o
) AS t
WHERE rn = 1;

customer_id | order_id | order_date | amount
------------+----------+------------+-------
1           | 101      | 2026-01-05 | 500
2           | 102      | 2026-01-10 | 1200
3           | 104      | 2026-02-02 | 800

This "latest/first row per group" pattern is everywhere: latest status per ticket, most recent price per product. ROW_NUMBER guarantees exactly one row per customer; order_id breaks ties if two orders share a date. A GROUP BY customer_id with MIN(order_date) finds the date but not the rest of the row without another join. PostgreSQL also offers SELECT DISTINCT ON (customer_id) ... ORDER BY customer_id, order_date.

Problem 15. Employees earning above their department's average, and each department's share of payroll

SELECT name, dept, salary, ROUND(dept_avg, 2) AS dept_avg
FROM (
  SELECT name, dept, salary,
         AVG(salary) OVER (PARTITION BY dept) AS dept_avg
  FROM employees
) AS t
WHERE salary > dept_avg
ORDER BY dept, salary DESC;

name  | dept        | salary | dept_avg
------+-------------+--------+---------
Asha  | Engineering | 120000 | 98000.0
Farah | Engineering | 100000 | 98000.0
Neha  | HR          |  65000 | 57500.0
Priya | Sales       |  85000 | 71666.67

The window version reads the table once; the correlated-subquery version from SQL fundamentals recomputes the average per row unless the optimizer rewrites it.

A related follow-up, "each department's percentage of the total payroll", combines GROUP BY with a window over the grouped rows:

SELECT dept, SUM(salary) AS payroll,
       ROUND(100.0 * SUM(salary) / SUM(SUM(salary)) OVER (), 1) AS pct
FROM employees
GROUP BY dept
ORDER BY payroll DESC;

dept        | payroll | pct
------------+---------+-----
Engineering | 490000  | 59.8
Sales       | 215000  | 26.2
HR          | 115000  | 14.0

SUM(SUM(salary)) OVER () looks odd but is legal: the inner SUM is the group aggregate, the outer is a window over all groups (an empty OVER () means the whole result). Total payroll is 820000; 490000 / 820000 = 59.8%.

Interview questions

Q1. What is the difference between ROW_NUMBER, RANK and DENSE_RANK?

All number rows in a given order. ROW_NUMBER gives unique numbers even for ties. RANK gives tied rows the same number and leaves gaps after them (1, 2, 2, 4). DENSE_RANK gives ties the same number without gaps (1, 2, 2, 3). Use DENSE_RANK for "Nth highest distinct value" and ROW_NUMBER to pick exactly one row per group.

Q2. What is the difference between a window function and GROUP BY?

GROUP BY collapses each group into one output row. A window function computes over a set of related rows but keeps every input row, so you can show detail and aggregate side by side, such as each salary next to its department average. Window functions run after GROUP BY and HAVING, at the SELECT step.

Q3. Why can you not use a window function in WHERE?

WHERE is evaluated before SELECT, and window functions are computed during SELECT, so their values do not exist yet when WHERE runs. Compute the window function in a subquery or CTE and filter in the outer query. Some databases (Snowflake, BigQuery, Teradata, and DuckDB) add a QUALIFY clause for this, but it is not standard and not in PostgreSQL, MySQL or SQLite.

Q4. What does PARTITION BY do?

It splits the rows into independent groups for a window function, which restarts its calculation in each partition. RANK() OVER (PARTITION BY dept ORDER BY salary DESC) ranks employees within each department separately. Without PARTITION BY, the whole result set is one partition.

Q5. What is the difference between ROWS and RANGE in a window frame?

ROWS counts physical rows relative to the current row. RANGE works on values, so all rows with the same ORDER BY value (peers) are included together. The default frame with ORDER BY is RANGE UNBOUNDED PRECEDING, which makes running totals jump at ties; specify ROWS for a row-by-row running total.

Q6. What is a CTE and why use one?

A common table expression is a named temporary result defined with WITH and used by the following statement. It makes complex queries readable by breaking them into named steps, can be referenced several times, and is the only way to write recursive queries. It is not automatically faster than a subquery.

Q7. How does a recursive CTE work?

It has an anchor query that produces starting rows and a recursive query that references the CTE, joined by UNION ALL. The database runs the anchor, then repeatedly runs the recursive part on the rows produced in the previous iteration until no new rows appear. It is used for hierarchies like org charts, graph traversal and generating series, and needs a termination condition or cycle guard.

Q8. View versus materialized view?

A view stores only a query and is recomputed each time it is read, so it is always current. A materialized view stores the query's result as data, so reads are fast and it can be indexed, but it becomes stale until refreshed. Use views for simplification and access control, and materialized views for expensive aggregates read often.

Q9. What is the difference between a stored procedure and a function?

A function returns a value or table and can be used inside a query, such as in SELECT or WHERE. A procedure is invoked with CALL, may return nothing or return results through parameters, and in many databases can control transactions. Exact rules vary: SQL Server functions cannot modify data, while PostgreSQL functions can.

Q10. What is a trigger? Give a use case and a risk.

A trigger is code the database runs automatically before or after an insert, update or delete on a table, per row or per statement. A common use is an audit table that records old and new salaries on every update. The risk is hidden behavior: triggers slow every write, can chain into other triggers, and surprise developers who do not know they exist.

Q11. How do you find the Nth highest salary?

Three standard ways: SELECT DISTINCT salary ... ORDER BY salary DESC LIMIT 1 OFFSET N-1; DENSE_RANK() OVER (ORDER BY salary DESC) in a subquery filtered to rank N; or a correlated subquery that keeps a salary when exactly N−1 distinct salaries are greater. Handle ties with DISTINCT or DENSE_RANK, and return NULL when there is no Nth value by wrapping in a scalar subquery.

Q12. How do you delete duplicate rows but keep one?

Keep one row per duplicate group, usually the lowest id, and delete the rest: DELETE FROM t WHERE id NOT IN (SELECT MIN(id) FROM t GROUP BY dup_cols), or delete rows whose ROW_NUMBER() OVER (PARTITION BY dup_cols ORDER BY id) is greater than 1. In MySQL, use a self-join delete or wrap the subquery, because MySQL cannot delete from a table it reads in a direct subquery. Then add a unique constraint so duplicates cannot return.

Q13. How do you find consecutive-day streaks in SQL?

Deduplicate to one row per user per day, number each user's dates with ROW_NUMBER, and subtract that number (as days) from the date. Dates in the same consecutive run give the same result, so you group by user and that value and keep groups with the required count. Alternatively, LAG(date, k-1) checks whether the date k−1 rows back is exactly k−1 days earlier.

Q14. How do you pivot rows into columns without a PIVOT operator?

Use conditional aggregation: group by the row key and compute one column per category with SUM(CASE WHEN category = 'X' THEN value ELSE 0 END). It works in every SQL database. The categories must be known in advance; dynamic pivots require building the SQL in application code.

Q15. Why might LAST_VALUE return the current row's value?

With ORDER BY in the window and no explicit frame, the default frame ends at the current row (and its peers), so the "last" row of the frame is the current one. Specify ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING to make the frame the whole partition.

Key takeaways

• Window functions compute across related rows without collapsing them; PARTITION BY groups, ORDER BY orders, the frame bounds.
• ROW_NUMBER, RANK and DENSE_RANK differ only in ties; ask how ties should count.
• Window functions cannot appear in WHERE; wrap them in a subquery or CTE.
• Use ROWS frames for running totals with ties, and LAG/LEAD instead of self-joins to compare neighboring rows.
• CTEs make queries readable; recursive CTEs walk hierarchies and generate series, and need a stop condition.
• Views store queries; materialized views store results and need refreshing.
• Procedures, functions and triggers move logic into the database; triggers are powerful but invisible.
• Most interview problems reduce to a few patterns: rank-then-filter, anti-join, self-join, gaps-and-islands, conditional aggregation and running windows.

Next lesson

Continue with Normalization.