Explain 'Window Functions' in SQL and provide examples of how they are used.

Instruction: Describe 'Window Functions' and their application in data analysis.

Context: This question evaluates the candidate's ability to leverage Window Functions for sophisticated data analysis tasks within SQL.

Updated

Official answer

A window function computes across related rows while retaining their row-level detail. OVER specifies partitioning, ordering and, for applicable functions, the frame. GROUP BY usually reduces rows to groups instead.

This self-contained PostgreSQL example orders individual sales, including a timestamp tie:

WITH sales(id, sale_date, amount) AS (
  VALUES (1, DATE '2026-01-01', 10),
         (2, DATE '2026-01-01', 20),
         (3, DATE '2026-01-02', 5)
)
SELECT id, amount,
       SUM(amount) OVER (
         ORDER BY sale_date, id
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_total
FROM sales
ORDER BY sale_date, id;

Totals are 10, 30 and 35. The unique tie-breaker and explicit ROWS frame make the intended row-by-row calculation clear. An ordered default frame can include peers, producing different results when ordering only by date.

ROW_NUMBER assigns row positions; RANK leaves gaps after ties; DENSE_RANK does not. LAG/LEAD access other rows under the specified order. An outer ORDER BY controls display order; a window order alone does not guarantee it. Use a subquery to filter window results where the dialect requires it.

Related Questions