Mid LevelAdvanced SQL4 Interview QuestionsFREE

SQL Window Functions Interview Questions

What Interviewers Are Testing

Window function interview questions assess your ability to perform complex analytics — ranking, running totals, moving averages — without collapsing rows the way GROUP BY does.

Interview Questions & Model Answers

Q1

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

A

All three assign a number to rows in a partition ordered by a sort column, but they handle ties differently. ROW_NUMBER: always assigns unique sequential numbers, no ties (arbitrary order for tied values). RANK: gives tied rows the same number but skips the next rank(s). DENSE_RANK: gives tied rows the same number but does NOT skip the next rank.

Example
-- Given salaries: 90k, 80k, 80k, 70k
-- ROW_NUMBER: 1, 2, 3, 4 (arbitrary for ties)
-- RANK:       1, 2, 2, 4 (skips 3)
-- DENSE_RANK: 1, 2, 2, 3 (no skip)

SELECT name, 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;
Try in Playground

Potential Follow-up Questions:

  • When would you use DENSE_RANK over RANK?
  • How do you get the top N records per group?
Q2

How do you calculate a running total in SQL?

A

Use SUM() as a window function with ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. This accumulates the sum from the first row to the current row in the specified order.

Example
SELECT order_date, amount,
  SUM(amount) OVER (
    ORDER BY order_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_total
FROM orders;
Try in Playground

Potential Follow-up Questions:

  • What is the difference between ROWS and RANGE in window frame?
Q3

How do you compare a row to its previous row using SQL?

A

Use the LAG() window function. LAG(col, n) returns the value of col from n rows before the current row in the window order. LEAD(col, n) does the same for rows ahead. This is ideal for calculating period-over-period changes.

Example
SELECT month, revenue,
  LAG(revenue) OVER (ORDER BY month) AS prev_month_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), 2) AS pct_change
FROM monthly_sales;
Try in Playground

Potential Follow-up Questions:

  • What does LAG return for the first row where there is no previous row?
Q4

How do you get the top 1 row per group in SQL?

A

Use ROW_NUMBER() with PARTITION BY in a CTE or subquery, then filter WHERE rn = 1. This is one of the most common window function interview patterns.

Example
-- Top earner per department
WITH ranked AS (
  SELECT name, department, salary,
    ROW_NUMBER() OVER (
      PARTITION BY department
      ORDER BY salary DESC
    ) AS rn
  FROM employees
)
SELECT name, department, salary
FROM ranked
WHERE rn = 1;
Try in Playground

Potential Follow-up Questions:

  • What if there are ties for the top position?
  • How would you get the top 3 per group instead?

Frequently Asked Questions

Are window functions commonly asked in SQL interviews?
Yes, extensively at mid-level and senior positions. They are a key differentiator between junior and senior SQL skills. Expect questions on: ROW_NUMBER vs RANK vs DENSE_RANK, LAG/LEAD for period comparison, running totals with SUM OVER, PARTITION BY, and the top-N-per-group pattern (CTE + ROW_NUMBER + WHERE rn <= N).
What is PARTITION BY in SQL window functions?
PARTITION BY in a window function is like GROUP BY but without collapsing rows. It divides the rows into groups (partitions), and the window function is applied independently to each partition. Without PARTITION BY, the window function applies across all rows. With PARTITION BY department, ROW_NUMBER() restarts at 1 for each department.

Related Interview Topics

Sql JoinsIndexingQuery OptimizationDatabase Normalization

Practice Answering Live

Use our Interview Arena to practice SQL challenges under real interview conditions. Free.

Open Interview Arena SQL Playground