SQL
Applying SQL Window Functions for Ranking and Analytical Queries
Master SQL window functions like `ROW_NUMBER()`, `RANK()`, or `AVG() OVER()` to perform advanced analytical tasks such as ranking, partitioning data, and calculating moving averages or cumulative sums.
SELECT
product_id,
category,
sales_amount,
ROW_NUMBER() OVER (PARTITION BY category ORDER BY sales_amount DESC) AS rank_in_category,
RANK() OVER (ORDER BY sales_amount DESC) AS overall_rank,
SUM(sales_amount) OVER (PARTITION BY category ORDER BY sales_amount) AS cumulative_category_sales
FROM
product_sales
ORDER BY
category, rank_in_category;
How it works: Window functions perform calculations across a set of table rows that are related to the current row. Unlike aggregate functions which group rows, window functions retain individual row details. The `OVER()` clause defines the "window" or set of rows, often using `PARTITION BY` to divide rows into groups and `ORDER BY` to specify the order within each group, enabling operations like ranking or cumulative sums.