How do window functions work in SQL and when should analysts use them?

Window functions compute values across a set of rows without collapsing results like GROUP BY. Use them for running totals, ranking, cohort retention, and “latest per group” patterns. Common functions: ROW_NUMBER, RANK, LAG/LEAD, SUM() OVER. Interview tip: explain partitioning (PARTITION BY) and ordering (ORDER BY) and how they change the result.