SQL Window Functions Interview Questions (2026): 6 Real Questions With Runnable Answers

Overview

Window functions are the single highest-signal SQL topic in interviews — analysts and data engineers who can use them fluently get hired faster. The questions below cover the six window-function patterns interviewers test most: top-N per group, ranking ties, running totals, lag/lead comparisons, percent of total, and gap detection. Each answer is runnable on PostgreSQL; MySQL 8+ and BigQuery support the same syntax with minor changes.

Questions & Answers

Q1. Top 3 products per category by revenue Common · Medium

Prompt: Given orders(product_id, total) and products(id, name, category), return the top 3 products in each category by total revenue.

SELECT category, name, total_revenue
FROM (
  SELECT
    p.category,
    p.name,
    SUM(o.total) AS total_revenue,
    ROW_NUMBER() OVER (
      PARTITION BY p.category
      ORDER BY SUM(o.total) DESC
    ) AS rn
  FROM orders o
  JOIN products p ON p.id = o.product_id
  GROUP BY p.category, p.name
) ranked
WHERE rn <= 3
ORDER BY category, total_revenue DESC;

Why this matters: PARTITION BY divides the result into per-category windows. ROW_NUMBER assigns 1..N within each window. The outer WHERE rn <= 3 keeps only the top three. This is the canonical "top-N per group" pattern — practice it until you can write it from muscle memory.

Q2. RANK vs DENSE_RANK vs ROW_NUMBER Common · Medium

Prompt: For employees(id, salary), explain the difference between ROW_NUMBER, RANK, and DENSE_RANK by ranking salaries. What happens with ties?

SELECT
  id,
  salary,
  ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num,
  RANK()       OVER (ORDER BY salary DESC) AS rank_,
  DENSE_RANK() OVER (ORDER BY salary DESC) AS dense
FROM employees;

Why this matters: When two employees tie on salary: ROW_NUMBER assigns 1, 2 (arbitrary tiebreak); RANK assigns 1, 1, 3 (skips 2); DENSE_RANK assigns 1, 1, 2 (no skip). Pick DENSE_RANK for "second-highest distinct value" questions, ROW_NUMBER for "exactly N rows" questions.

Q3. Running total of revenue by day Common · Medium

Prompt: Given orders(order_date, total), compute the cumulative revenue total at each day.

SELECT
  order_date,
  daily_revenue,
  SUM(daily_revenue) OVER (
    ORDER BY order_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_total
FROM (
  SELECT order_date, SUM(total) AS daily_revenue
  FROM orders
  GROUP BY order_date
) daily
ORDER BY order_date;

Why this matters: The frame clause `ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW` is what makes it a running total instead of a grand total. Default frames vary by dialect, so always specify the frame explicitly in interviews.

Q4. Month-over-month revenue change Common · Medium

Prompt: Compute month-over-month revenue change as both absolute delta and percent.

WITH monthly AS (
  SELECT
    DATE_TRUNC('month', order_date) AS month,
    SUM(total) AS revenue
  FROM orders
  GROUP BY 1
)
SELECT
  month,
  revenue,
  LAG(revenue) OVER (ORDER BY month) AS prev_month_revenue,
  revenue - LAG(revenue) OVER (ORDER BY month) AS mom_delta,
  ROUND(
    100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
      / NULLIF(LAG(revenue) OVER (ORDER BY month), 0),
    2
  ) AS mom_pct_change
FROM monthly
ORDER BY month;

Why this matters: LAG returns the previous row's value. NULLIF(..., 0) prevents division-by-zero when the prior month had zero revenue. The first row will have NULL for prev_month_revenue, which is correct.

Q5. Percent of total per category Common · Medium

Prompt: For each product, show its revenue and what percent it contributes to its category total.

SELECT
  category,
  product_id,
  product_revenue,
  ROUND(
    100.0 * product_revenue
      / SUM(product_revenue) OVER (PARTITION BY category),
    2
  ) AS pct_of_category
FROM (
  SELECT
    p.category,
    p.id AS product_id,
    SUM(o.total) AS product_revenue
  FROM orders o
  JOIN products p ON p.id = o.product_id
  GROUP BY p.category, p.id
) per_product
ORDER BY category, pct_of_category DESC;

Why this matters: SUM(...) OVER (PARTITION BY category) gives the category total on every row, so each row can be divided by it without a self-join. This pattern appears in dashboards constantly.

Q6. Detect gaps in a sequence Common · Hard

Prompt: Given event_log(event_date), find dates where there is a gap of 2+ days from the previous event.

SELECT
  event_date AS gap_end,
  prev_event AS gap_start,
  event_date - prev_event AS gap_days
FROM (
  SELECT
    event_date,
    LAG(event_date) OVER (ORDER BY event_date) AS prev_event
  FROM (SELECT DISTINCT event_date FROM event_log) e
) gaps
WHERE event_date - prev_event >= 2
ORDER BY event_date;

Why this matters: LAG combined with date arithmetic lets you compare each row to its predecessor without a self-join. Always DISTINCT first when the underlying table has duplicates per date — otherwise LAG compares within duplicates and the "gap" calculation is off.

Common Pitfalls

• Forgetting the frame clause — default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW for ordered windows but UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING for unordered ones. Always specify.
• Using GROUP BY when you want a window function — GROUP BY collapses rows; window functions preserve them. If you need both row-level detail and an aggregate side by side, you want a window function.
• Filtering on a window function in WHERE — WHERE runs before window functions. Wrap in a subquery or CTE and filter outside.
• Tied results with ROW_NUMBER — ROW_NUMBER picks an arbitrary winner among ties. Use DENSE_RANK for stable distinct-value ranking.
• PARTITION BY on a column that allows NULL — NULL becomes its own partition. Often unintended; coalesce or filter first.

Practice With AI2SQL

The fastest way to lock in these patterns is to write them by hand, then check against AI2SQL output. Generate variations of every question above with different schemas, rewrite them from scratch, and you will have these patterns memorized within a week.

Frequently Asked Questions