What a window function does
A window function computes a value across a set of rows related to the current row, without collapsing them like GROUP BY does. You keep every detail row and add a column such as a rank or a running total. The OVER() clause defines the window: PARTITION BY splits it into groups and ORDER BY sets the order inside each group.
Running total
Cumulative revenue per month is a single line with SUM() OVER (ORDER BY month).
SELECT month, revenue,
SUM(revenue) OVER (ORDER BY month) AS cumulative
FROM monthly_sales;Top N per group
"Best 3 products per category" is the classic question GROUP BY cannot answer. Number the rows inside each category, then filter.
SELECT * FROM (
SELECT category, product, revenue,
ROW_NUMBER() OVER (PARTITION BY category ORDER BY revenue DESC) AS rn
FROM product_sales
) t
WHERE rn <= 3;ROW_NUMBER vs RANK vs DENSE_RANK
With two products tied at the same revenue, ROW_NUMBER gives 1 and 2 arbitrarily, RANK gives 1, 1, 3 and DENSE_RANK gives 1, 1, 2. Pick RANK for leaderboards where ties matter, ROW_NUMBER when you need exactly N rows.
Month-over-month growth with LAG
LAG reads the previous row's value. Divide by it to get growth, and protect against division by zero with NULLIF.
SELECT month, revenue,
ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
/ NULLIF(LAG(revenue) OVER (ORDER BY month), 0), 1) AS growth_pct
FROM monthly_sales;Compatibility notes
Window functions are supported by PostgreSQL, SQL Server, Oracle, SQLite 3.25+, MySQL 8+ and BigQuery. On MySQL 5.7 they do not exist and must be rewritten with self-joins or variables. Mention your database engine when you ask FormulAI so the generated syntax matches.