Window functions: SQL patterns and examples
Running totals, rankings, moving averages and row-to-row comparisons. Window functions calculate across related rows without collapsing them, which is why they replace most self-joins.
- Running TotalA running total adds each row's value to the sum of all earlier rows, so you can see how a figure builds up over time.
- Moving AverageA moving average smooths a noisy series by averaging each row with the rows just before it, such as a 7-day average of daily sales.
- Rank Within a GroupRanking within a group numbers rows from best to worst inside each category, using ROW_NUMBER, RANK or DENSE_RANK.
- Top N Per GroupTop N per group returns the best few rows in each category, such as the three best-selling products in every department.
- Compare to the Previous RowComparing with the previous row uses LAG to show how a value changed since the last period, such as revenue against last month.
- Percent of TotalPercent of total shows each group's share of the overall figure, such as each category's share of sales.
- Cumulative Percentage (Pareto)A cumulative percentage ranks items from largest to smallest and shows how much of the total the top items add up to, as in a Pareto or 80/20 analysis.
- First Row Per GroupFirst row per group picks one row from each group, such as each customer's first order or each user's latest login.
- MedianThe median is the middle value of a set, and unlike the average it is not pulled around by a few very large or very small values.
- Quartiles with NTILENTILE splits rows into equal-sized buckets such as quartiles or deciles, so you can label customers or products by where they fall in the distribution.