How to Master SQL Window Functions: The Best Interview Guide

If you are preparing for a data engineering or data analyst interview, there is one SQL topic you simply cannot skip: SQL window functions. Questions like “find the second highest salary in each department” or “remove duplicate rows but keep the latest one” show up again and again, and almost all of them are solved with a window function.

In this guide, let’s break down SQL window functions in simple words, with small examples you can run yourself, the most common interview questions, and the PySpark version of the same logic.

What Are SQL Window Functions?

For example, a normal aggregate like SUM() or COUNT() with GROUP BY squeezes many rows into one row. A window function does a calculation across a group of related rows, but keeps every row in the result.

In other words, think of it like this: each row looks through a “window” at its neighbors, does some math, and writes the answer next to itself.

In fact, all SQL window functions share the same basic shape:

function_name() OVER (
    PARTITION BY column   -- split rows into groups (optional)
    ORDER BY column       -- order rows inside each group (optional)
    frame_clause          -- which rows to include (optional)
)

• PARTITION BY works like GROUP BY, but rows are not collapsed.
• ORDER BY decides the order inside each partition. Ranking, LAG and LEAD need it.
• Frame clause (for example ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) controls which rows the function uses for running totals and moving averages.

Our Sample Table for SQL Window Functions

Next, we will use this small employees table in all examples:

ROW_NUMBER vs RANK vs DENSE_RANK in SQL Window Functions

This is the most asked question about SQL window functions. All three give a position number, but they behave differently when there is a tie.

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

As a result, here is the output for the IT department:

• ROW_NUMBER always gives unique numbers: 1, 2, 3, 4. For tied rows, the order is not guaranteed unless you add a tie-breaker column to ORDER BY.
• RANK gives tied rows the same number, then skips: 1, 2, 2, 4.
• DENSE_RANK gives tied rows the same number with no gap: 1, 2, 2, 3.

LAG and LEAD: Look at the Previous or Next Row

Among SQL window functions, LAG() reads a value from an earlier row, and LEAD() reads from a later row. They are perfect for month-on-month comparisons.

-- monthly_sales(sale_month DATE, amount INT)
-- 2026-01-01: 100, 2026-02-01: 120, 2026-03-01: 90

SELECT sale_month,
       amount,
       LAG(amount)  OVER (ORDER BY sale_month) AS prev_month,
       amount - LAG(amount) OVER (ORDER BY sale_month) AS change,
       LEAD(amount) OVER (ORDER BY sale_month) AS next_month
FROM monthly_sales;

Running Totals and Moving Averages with SQL Window Functions

Add ORDER BY inside OVER() to an aggregate like SUM, and it becomes a running total.

SELECT sale_month,
       amount,
       SUM(amount) OVER (ORDER BY sale_month
                         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total,
       AVG(amount) OVER (ORDER BY sale_month
                         ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg_3
FROM monthly_sales;

As a result, the running total here is 100, 220, 310.

5 Classic SQL Window Functions Interview Questions (With Answers)

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

-- customers(customer_id, email, updated_at)
WITH dedup AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY updated_at DESC) AS rn
    FROM customers
)
SELECT *
FROM dedup
WHERE rn = 1;

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

SELECT name, dept, salary,
       ROUND(100.0 * salary / SUM(salary) OVER (PARTITION BY dept), 2) AS pct_of_dept
FROM employees;

SQL Window Functions in PySpark

Similarly, data engineering interviews often ask you to do the same thing in PySpark. The idea is identical: define a window, then apply a function over it.

from pyspark.sql import Window
from pyspark.sql import functions as F

w = Window.partitionBy("dept").orderBy(F.col("salary").desc())

top2 = (df
        .withColumn("rnk", F.dense_rank().over(w))
        .filter(F.col("rnk") <= 2))

# Running total by month
w_run = Window.orderBy("sale_month").rowsBetween(Window.unboundedPreceding, Window.currentRow)
sales = sales_df.withColumn("running_total", F.sum("amount").over(w_run))

Key Takeaways: SQL Window Functions

FAQ: SQL Window Functions

Sources

• PostgreSQL Documentation: Window Functions tutorial
• MySQL 8.0 Reference Manual: Window Functions
• Apache Spark SQL: Window Functions