Menu
CoddyTech
Start learning
Learn SQL step by stepOnline SQL compiler

SQL Window Functions Interview Questions

ROW_NUMBER, RANK, DENSE_RANK, running totals, LAG and LEAD, and the frame rules that catch experienced developers.

By Kevin Spektor, Co-founder & CTO

10 questions

  1. What is a window function, and how is it different from GROUP BY?

    A window function computes a value over related rows but keeps every row; GROUP BY collapses each group into one row.

    SQL
    CREATE TABLE employees (id INTEGER PRIMARY KEY, name TEXT, dept TEXT, salary INTEGER, manager_id INTEGER);
INSERT INTO employees VALUES
  (1, 'Asha', 'Engineering', 120000, NULL),
  (2, 'Ravi', 'Engineering', 90000, 1),
  (3, 'Meera', 'Engineering', 90000, 1),
  (4, 'John', 'Sales', 70000, 1),
  (5, 'Priya', 'Sales', 82000, 4),
  (6, 'Karan', 'HR', 60000, 1);
SELECT name, dept, salary,
       AVG(salary) OVER (PARTITION BY dept) AS dept_avg,
       salary - AVG(salary) OVER (PARTITION BY dept) AS diff
FROM employees
ORDER BY dept, salary DESC, name;

    Each employee keeps a row with the department average beside it. Inside OVER, PARTITION BY splits the groups, ORDER BY orders rows within them, and an optional frame (ROWS BETWEEN ...) limits which rows count. See window functions.

  2. How do ROW_NUMBER, RANK and DENSE_RANK number tied rows?

    SQL
    CREATE TABLE scores (name TEXT, score INTEGER);
INSERT INTO scores VALUES ('Asha', 100), ('Ravi', 90), ('Meera', 90), ('John', 80);
SELECT rn, rnk, drnk FROM (
  SELECT name,
         ROW_NUMBER() OVER (ORDER BY score DESC) AS rn,
         RANK()       OVER (ORDER BY score DESC) AS rnk,
         DENSE_RANK() OVER (ORDER BY score DESC) AS drnk
  FROM scores
) AS t
WHERE name = 'John';

    They differ only on ties. ROW_NUMBER always counts 1, 2, 3, 4; RANK gives tied rows the same number and then skips; DENSE_RANK repeats the number without skipping. So John gets 4 | 4 | 3.

    SQL
    CREATE TABLE scores (name TEXT, score INTEGER);
INSERT INTO scores VALUES ('Asha', 100), ('Ravi', 90), ('Meera', 90), ('John', 80);
SELECT name, score,
       ROW_NUMBER() OVER (ORDER BY score DESC, name) AS row_number,
       RANK()       OVER (ORDER BY score DESC) AS rank,
       DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank
FROM scores
ORDER BY score DESC, name;

    Give ROW_NUMBER a tiebreaker, or which tied row gets 2 is arbitrary. Use ROW_NUMBER to pick one row per group and DENSE_RANK for the Nth distinct value.

    Practice it: Kth Largest Element in an Array

  3. How do you calculate a running total in SQL?

    Use SUM(...) OVER (ORDER BY ...): each row gets the sum of all rows up to and including itself.

    SQL
    CREATE TABLE daily_sales (day TEXT, amount INTEGER);
INSERT INTO daily_sales VALUES
  ('2026-01-01', 100), ('2026-01-02', 250), ('2026-01-03', 50), ('2026-01-04', 300);
SELECT day, amount,
       SUM(amount) OVER (ORDER BY day) AS running_total,
       MIN(amount) OVER (ORDER BY day) AS lowest_so_far
FROM daily_sales
ORDER BY day;

    The total ends at 700. The same pattern with MIN gives the lowest value so far, the core of the "best time to buy and sell a stock" problem. Add PARTITION BY customer_id to restart the total per customer.

    Practice it: Running Sum of an Array, Best Time to Buy and Sell Stock

  4. What do LAG and LEAD do? Show the change from the previous day.

    LAG(col) returns the previous row's value in the window order and LEAD(col) the next row's, so you compare neighbors without a self join.

    SQL
    CREATE TABLE daily_sales (day TEXT, amount INTEGER);
INSERT INTO daily_sales VALUES
  ('2026-01-01', 100), ('2026-01-02', 250), ('2026-01-03', 50), ('2026-01-04', 300);
SELECT day, amount,
       amount - LAG(amount) OVER (ORDER BY day) AS change,
       LEAD(amount) OVER (ORDER BY day) AS next_day
FROM daily_sales
ORDER BY day;

    The first row's change is NULL because it has no previous day; LAG(amount, 1, 0) returns 0 instead. Typical uses are month-over-month growth and time between sessions.

  5. What does a running SUM() OVER (ORDER BY day) do with tied days?

    SQL
    CREATE TABLE sales (id INTEGER, day INTEGER, amount INTEGER);
INSERT INTO sales VALUES (1, 1, 10), (2, 2, 20), (3, 2, 30), (4, 3, 40);
SELECT running FROM (
  SELECT id, SUM(amount) OVER (ORDER BY day) AS running FROM sales
) AS t
WHERE id = 2;

    It prints 60, not 30. With ORDER BY and no frame, the default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which includes every row with the same day. Rows 2 and 3 share day 2, so both get 10 + 20 + 30. For a row-by-row total, order by a unique key (ORDER BY day, id) or use ROWS.

  6. How do you calculate a moving average, for example over the last 3 days?

    Use a ROWS frame: AVG(amount) OVER (ORDER BY day ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) averages the current row and the two before it.

    SQL
    CREATE TABLE daily_sales (day TEXT, amount INTEGER);
INSERT INTO daily_sales VALUES
  ('2026-01-01', 100), ('2026-01-02', 250), ('2026-01-03', 50), ('2026-01-04', 300);
SELECT day, amount,
       ROUND(AVG(amount) OVER (
         ORDER BY day ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
       ), 1) AS avg_3_days
FROM daily_sales
ORDER BY day;

    The first two rows average fewer values (100.0, then 175.0). ROWS counts rows, so a missing day widens the window; fill gaps with a calendar table first.

    Practice it: Maximum Sum Subarray of Size K

  7. Why does LAST_VALUE return the current row instead of the last row?

    The default frame ends at the current row, so LAST_VALUE returns the current row's value. Extend the frame to the end of the partition:

    SQL
    CREATE TABLE scores (name TEXT, score INTEGER);
INSERT INTO scores VALUES ('Asha', 100), ('Ravi', 90), ('John', 80);
SELECT name, score,
       LAST_VALUE(name) OVER (ORDER BY score) AS default_frame,
       LAST_VALUE(name) OVER (
         ORDER BY score ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
       ) AS whole_partition
FROM scores
ORDER BY score;

    default_frame repeats each row's own name; whole_partition shows Asha on every row. Simpler: FIRST_VALUE(name) OVER (ORDER BY score DESC), since the default frame starts at the first row.

  8. Can you use a window function in a WHERE clause?

    No. Window functions run after WHERE, GROUP BY and HAVING, so compute the window in a CTE or subquery and filter outside it.

    SQL
    CREATE TABLE employees (id INTEGER PRIMARY KEY, name TEXT, dept TEXT, salary INTEGER, manager_id INTEGER);
INSERT INTO employees VALUES
  (1, 'Asha', 'Engineering', 120000, NULL),
  (2, 'Ravi', 'Engineering', 90000, 1),
  (3, 'Meera', 'Engineering', 90000, 1),
  (4, 'John', 'Sales', 70000, 1),
  (5, 'Priya', 'Sales', 82000, 4),
  (6, 'Karan', 'HR', 60000, 1);
WITH t AS (
  SELECT name, salary, AVG(salary) OVER (PARTITION BY dept) AS dept_avg
  FROM employees
)
SELECT name, salary, dept_avg
FROM t
WHERE salary > dept_avg
ORDER BY salary DESC;

    Snowflake, BigQuery, Databricks and DuckDB have a QUALIFY clause for this; PostgreSQL, MySQL, SQL Server and SQLite do not.

  9. What does NTILE(n) do, and what happens when the rows do not divide evenly?

    NTILE(n) splits the ordered rows into n buckets of nearly equal size and returns each row's bucket number, so NTILE(4) gives quartiles.

    SQL
    CREATE TABLE scores (name TEXT, score INTEGER);
INSERT INTO scores VALUES
  ('Asha', 95), ('Ravi', 88), ('Meera', 76), ('John', 64),
  ('Priya', 59), ('Karan', 41), ('Dev', 30);
SELECT name, score, NTILE(3) OVER (ORDER BY score DESC) AS band
FROM scores
ORDER BY score DESC;

    Seven rows in three buckets gives sizes 3, 2 and 2: the first buckets take the extra rows. Tied values can land in different buckets, because NTILE counts rows, not values. For a position based on values, use PERCENT_RANK() or CUME_DIST().

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

    ROWS counts physical rows around the current one; RANGE takes every row whose ORDER BY value lies within an offset of the current value. They differ when the values have gaps or ties.

    SQL
    CREATE TABLE readings (day INTEGER, amount INTEGER);
INSERT INTO readings VALUES (1, 10), (2, 20), (4, 40), (5, 50);
SELECT day, amount,
       SUM(amount) OVER (ORDER BY day ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS last_2_rows,
       SUM(amount) OVER (ORDER BY day RANGE BETWEEN 1 PRECEDING AND CURRENT ROW) AS last_2_days
FROM readings
ORDER BY day;

    On day 4, last_2_rows adds days 2 and 4 (60), while last_2_days covers days 3 and 4 and finds only 40, because day 3 is missing. RANGE with a numeric offset works in PostgreSQL 11+, MySQL 8 and SQLite 3.28+; SQL Server allows RANGE only with UNBOUNDED and CURRENT ROW.

Coddy programming languages illustration

Learn to code with Coddy

Get started