SQL
Perform Advanced Analytics with SQL Window Functions
Master SQL window functions like ROW_NUMBER(), RANK(), and NTILE() to easily rank data, calculate moving averages, and analyze data partitions for insightful reports.
SELECT
product_category,
product_name,
sales_amount,
RANK() OVER (PARTITION BY product_category ORDER BY sales_amount DESC) as rank_in_category,
SUM(sales_amount) OVER (PARTITION BY product_category ORDER BY sales_amount ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as cumulative_sales,
AVG(sales_amount) OVER (PARTITION BY product_category ORDER BY sales_amount ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) as moving_avg_3_sales
FROM
products_sales
ORDER BY
product_category, rank_in_category;
How it works: SQL Window Functions perform calculations across a set of table rows that are somehow related to the current row. Unlike aggregate functions, window functions do not group rows and thus return a value for each row. They are perfect for tasks like ranking items within groups (`RANK()`, `ROW_NUMBER()`), calculating cumulative sums, moving averages, or lead/lag values over specified partitions of your data.