Window functions

Window functions compute values across related rows without collapsing the result set like GROUP BY. Rankings, running totals, and row-over-row comparisons are typical uses in analytics and reporting.

ROW_NUMBER and RANK

SELECT customer_id,
       total,
       ordered_at,
       ROW_NUMBER() OVER (
         PARTITION BY customer_id
         ORDER BY ordered_at DESC
       ) AS rn
FROM orders;

Practice: Advanced features vary by engine—SQLite 3.25+ supports window functions; test your version with a simple query.

Running sum

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

OVER defines the window; PARTITION BY resets per group.

Window vs GROUP BY

GROUP BY returns one row per group; window functions keep detail rows while adding aggregate columns—common in Data Science feature engineering SQL.

Important interview questions and answers

1. Q: PARTITION BY does what?
   A: Splits rows into groups over which the window function runs separately.
2. Q: ROW_NUMBER vs RANK?
   A: ROW_NUMBER is unique per row; RANK leaves gaps after ties.

Self-check

1. Which clause defines the window for a window function?
2. When choose window functions over GROUP BY?

Tip: Check SQLite version (SELECT sqlite_version();)—window functions need 3.25+.