Advanced SQL Interview Questions and Answers (Window Functions and More)

All three number rows by an order. With ties:

ROW_NUMBER never ties; RANK ties and leaves gaps; DENSE_RANK ties without gaps. Use ROW_NUMBER for exactly one row per position, DENSE_RANK for "Nth highest value". See RANK.

DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC), filtered in an outer query.

SELECT department, name, salary
FROM (
  SELECT department, name, salary,
         DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS d
  FROM employees
) t
WHERE d <= 2
ORDER BY department, salary DESC;

department  | name  | salary
------------+-------+-------
Engineering | Meera | 81000
Engineering | Ravi  | 78000
Marketing   | John  | 46000
Sales       | Priya | 61000
Sales       | Asha  | 52000

Explain why the filter cannot go in WHERE directly: window functions are computed after WHERE.

SELECT name, salary,
       SUM(salary) OVER (ORDER BY salary) AS running
FROM employees;

name  | salary | running
------+--------+--------
John  | 46000  | 46000
Asha  | 52000  | 98000
Priya | 61000  | 159000
Ravi  | 78000  | 237000
Meera | 81000  | 318000

Be ready to explain the default frame: with ORDER BY, it is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, so tied values are added together on the same row. Use ROWS BETWEEN ... to avoid that.

SELECT region, month, revenue,
       ROUND(AVG(revenue) OVER (
         PARTITION BY region
         ORDER BY month
         ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
       ), 1) AS moving_avg
FROM monthly_sales
ORDER BY region, month;

region | month   | revenue | moving_avg
-------+---------+---------+-----------
North  | 2026-01 | 120     | 120
North  | 2026-02 | 150     | 135
North  | 2026-03 | 110     | 126.7
North  | 2026-04 | 170     | 143.3
South  | 2026-01 | 90      | 90
South  | 2026-02 | 90      | 90
South  | 2026-03 | 130     | 103.3
South  | 2026-04 | 125     | 115

Point out that the first two months average fewer than three values; you might exclude them or require COUNT(*) OVER (...) = 3.

SELECT region, month, revenue
FROM (
  SELECT region, month, revenue,
         RANK() OVER (PARTITION BY region ORDER BY revenue DESC) AS r
  FROM monthly_sales
) t
WHERE r = 1
ORDER BY region;

region | month   | revenue
-------+---------+--------
North  | 2026-04 | 170
South  | 2026-03 | 130

RANK keeps both months if two tie for best; ROW_NUMBER would pick one arbitrarily.

For consecutive dates, date - ROW_NUMBER() is constant. Group by that difference.

Given a logins (user_id, login_date) table, where user 1 logged in on 1, 2, 3 and 5 March, and user 2 on 1, 3 and 4 March:

-- SQLite date arithmetic (julianday). In PostgreSQL, with a DATE column:
-- login_date - (ROW_NUMBER() OVER (...))::int
WITH numbered AS (
  SELECT user_id, login_date,
         julianday(login_date)
           - ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS grp
  FROM logins
)
SELECT user_id,
       MIN(login_date) AS streak_start,
       MAX(login_date) AS streak_end,
       COUNT(*)        AS days
FROM numbered
GROUP BY user_id, grp
HAVING COUNT(*) >= 2
ORDER BY user_id, streak_start;

user_id | streak_start | streak_end | days
--------+--------------+------------+-----
1       | 2026-03-01   | 2026-03-03 | 3
2       | 2026-03-03   | 2026-03-04 | 2

Within a run of consecutive days, the date and the row number both increase by one each step, so their difference stays the same. A gap changes the difference, starting a new "island". This is a classic senior-level question.

With a recursive CTE: an anchor query selects the starting rows (for example, the CEO), and a recursive query joins the table to the CTE to find the next level, repeating until no rows are found. Mention a depth limit to protect against cycles. See Recursive CTEs.

1. Run EXPLAIN (or EXPLAIN ANALYZE) to see the plan: look for full table scans on large tables, and large differences between estimated and actual rows.
2. Check that filter and join columns are indexed, and that composite indexes start with the filtered columns.
3. Look for functions on indexed columns (WHERE YEAR(order_date) = 2026) and rewrite them as ranges.
4. Check for fan-out joins producing far more rows than needed, and aggregate earlier.
5. Select only the columns you need, and make sure statistics are up to date.

See Indexes.

They control what a transaction can see of other concurrent transactions: read uncommitted (dirty reads possible), read committed (the default in PostgreSQL, SQL Server and Oracle), repeatable read (MySQL InnoDB's default), and serializable (transactions behave as if run one at a time). Stricter levels prevent more anomalies but reduce concurrency and can cause transactions to be aborted and retried. See Transactions.

OLTP systems run applications: many small, fast reads and writes, normalized schemas, and indexes on lookup columns. OLAP systems (data warehouses) run large analytical queries over many rows, often with denormalized "star" schemas and column-oriented storage. SQL written for OLAP leans heavily on aggregation and window functions; SQL for OLTP focuses on precise, index-friendly lookups and short transactions.