Technical interview guide

Window Functions

Per-row calculations across a related set of rows — running totals, rankings, and row-over-row comparisons — without collapsing rows like GROUP BY does.

Read: 23 min
Practice MCQs: 25
Interview QA: 25
Edition: v3
Editorial status: Reviewed
Relevant for: AI EngineerBackend EngineerBusiness Intelligence DeveloperCloud ArchitectComputational BiologistCybersecurity EngineerData AnalystData ArchitectData EngineerData ScientistForward Deployed Engineer (FDE)Full-Stack DeveloperSoftware EngineerSolutions ArchitectTechnical Product Manager

Scope: SQL principles with PostgreSQL 18 examples; vendor-specific behavior must be verified.

Interview QA

Treat each question like a live interview question: answer out loud first (structure, assumptions, tradeoffs), then open the model answer to spot gaps and rehearse a tighter follow-up.

Curated: 2026-10-05 · Written: 2026-09-11 · Reviewed: 2026-10-03

QA-1

Explain what a window function is and when you would reach for one.

QA-2

Compare ROW_NUMBER, RANK and DENSE_RANK, and say how you choose.

QA-3

Write a query returning each customer's three most recent orders, and explain its shape.

QA-4

Explain window frames, and describe a bug the default frame causes.

QA-5

How would you compute each product's share of its category's revenue in one query?

QA-6

How do you deduplicate rows keeping a specific survivor per key?

QA-7

Explain how you would detect gaps in a sequence of events using window functions.

QA-8

A running total looks wrong — it jumps in steps instead of accumulating. Diagnose it.

QA-9

How do window functions perform, and what can you do about a slow one?

QA-10

When would you use a window function instead of a self-join, and when not?

QA-11

How would you compute a seven-day moving average correctly?

QA-12

Explain FIRST_VALUE and LAST_VALUE, including the trap.

QA-13

How does filtering interact with window functions, and what mistakes does that cause?

QA-14

Describe the gaps-and-islands problem and how window functions solve it.

QA-15

How would you review a colleague's query that uses window functions?

QA-16

Explain PERCENT_RANK, CUME_DIST and NTILE, and when each is appropriate.

QA-17

How would you compute a customer's first and most recent order in one query?

QA-18

What determinism problems can window functions introduce into a report?

QA-19

How do window functions behave with GROUP BY in the same query?

QA-20

Explain how you would test a query that uses window functions.

QA-21

When is a window function the wrong tool, and what would you use instead?

QA-22

How would you build a cohort retention report using window functions?

QA-23

Explain how a window function's ORDER BY differs from the query's ORDER BY.

QA-24

Describe how you would explain window functions to an engineer who only knows GROUP BY.

QA-25