SQL Window Functions: What DE Interviewers Actually Ask

Window functions appear in roughly 80% of senior data engineering interviews. Most candidates know ROW_NUMBER(), RANK(), and LAG(). The ones who get offers know the edge cases.

This guide covers what interviewers actually probe — with real question patterns and the reasoning behind model answers.

Why Window Functions Are High-Signal

They test two things simultaneously:

SQL depth — do you understand execution order, framing, and performance implications?
Data intuition — can you translate a business problem into the right window logic?

A candidate who reaches for a subquery where a window function is cleaner is showing a gap. So is someone who uses OVER() everywhere without understanding the cost.

The 4 Question Patterns

Pattern 1: "Write a query that..."

Classic retrieval practice. Common variants:

"Find the second-highest salary per department"
"Calculate 7-day rolling average of daily revenue"
"Flag all rows where the value exceeds the previous row by more than 20%"

What interviewers look for:

Do you partition correctly? (PARTITION BY department_id vs PARTITION BY department_name)
Do you handle ties? (RANK() vs DENSE_RANK() vs ROW_NUMBER())
Do you define the frame explicitly when it matters?

Model answer — rolling 7-day average:

SELECT
  order_date,
  daily_revenue,
  AVG(daily_revenue) OVER (
    ORDER BY order_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS rolling_7d_avg
FROM daily_revenue_summary
ORDER BY order_date;

Notice ROWS BETWEEN 6 PRECEDING AND CURRENT ROW — not RANGE. For date-based windows, RANGE uses logical boundaries (all rows with the same date value) while ROWS uses physical row count. With gaps in the date sequence, they produce different results. Stating this distinction unprompted signals depth.

Pattern 2: "What's wrong with this query?"

Spot-the-bug variant. The interviewer shares a query with a subtle issue.

Common bugs planted:

Incorrect frame on a running total:

-- This is wrong for a running total
SUM(amount) OVER (ORDER BY created_at RANGE UNBOUNDED PRECEDING)

The default frame for ORDER BY without explicit framing is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which includes all rows with the same created_at value. If multiple rows share a timestamp, they all get the same "running total" — which is the sum up to and including all tied rows. Usually not what you want. Use ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW instead.

Partitioning on the wrong column: Partitioning on a high-cardinality column (e.g., user UUID) when the business question is about departments. The query runs but answers the wrong question.

Pattern 3: "How would you design this?"

"You have a sessions table with session_start and session_end. How do you identify sessions that overlap for the same user?"

This requires translating a spatial/temporal problem into window logic. Strong answer:

WITH ordered AS (
  SELECT
    user_id,
    session_start,
    session_end,
    MAX(session_end) OVER (
      PARTITION BY user_id
      ORDER BY session_start
      ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
    ) AS prev_max_end
  FROM sessions
)
SELECT *,
  CASE WHEN session_start < prev_max_end THEN 1 ELSE 0 END AS is_overlapping
FROM ordered;

The key insight: you don't compare adjacent rows — you compare against the running maximum of all previous end times. Demonstrating that distinction shows production intuition.

Pattern 4: "What are the performance implications?"

Senior-level signal. They want to know if you think about execution.

What to say:

Window functions require a sort step for each unique PARTITION BY + ORDER BY combination. In query engines like BigQuery, Spark SQL, and Redshift:

Multiple windows with the same partition/order can be computed in one pass
Different partitions on the same query require separate sort operations
ROWS framing is generally cheaper than RANGE framing for large partitions
Window functions cannot be pushed down past a WHERE clause — the full table is scanned before filtering

Practical implication: if you have 5 different window functions all over the same data, think about whether they share a PARTITION BY. Consolidate where possible.

The Frame Clause Cheat Sheet

Frame spec	Meaning
`ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW`	All rows from start of partition to current row
`ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING`	Previous, current, and next row
`ROWS BETWEEN 6 PRECEDING AND CURRENT ROW`	Rolling 7-row window
`RANGE BETWEEN INTERVAL '7' DAY PRECEDING AND CURRENT ROW`	Logical 7-day window (handles gaps)
`ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING`	Entire partition (same as no frame)

The Three Functions Most Candidates Get Wrong

`NTILE(n)`

Splits the partition into n equal-ish buckets. Common mistake: candidates forget that with non-divisible row counts, the earlier buckets get the extra rows (not later ones). Interviewers catch this by asking about 10 rows with NTILE(3).

`PERCENT_RANK()` vs `CUME_DIST()`

PERCENT_RANK(): (rank - 1) / (total rows - 1) — relative position
CUME_DIST(): fraction of rows ≤ current row — cumulative distribution

For "what percentile is this user?" questions, CUME_DIST() is usually the right choice. Candidates who use PERCENT_RANK() for percentile questions are showing a gap.

`LEAD()` and `LAG()` with NULLs

The default when there's no preceding/following row is NULL. The IGNORE NULLS option (available in BigQuery, Spark SQL) changes this. In Postgres, you handle it differently — with a COALESCE or a filtered CTE. Knowing the dialect differences is a senior signal.

What a Strong Answer Sounds Like

Interviewers aren't just evaluating correctness — they're evaluating how you think. Strong SQL answers:

State the approach before writing code ("I'd use DENSE_RANK() here because there may be ties and I don't want gaps in the ranking")
Flag assumptions ("I'm assuming created_at has no duplicate timestamps — if it does, the frame behaviour changes")
Name alternatives considered ("I could do this with a self-join, but the window function is cleaner and avoids the N² join")

These habits come from experience with production data, where the edge cases always matter.

Practice makes the difference. polydomainai has 30+ SQL window function questions with AI-scored feedback. You'll know exactly where your answer was strong and where it fell short — before the interview.

Practice SQL window functions →