SQL Window Functions

SQLWindowFunctions.md

SQL Window Functions Examples

This gist provides practical examples of SQL window functions to enhance your data analysis skills. Through these examples, you'll learn how to use various window functions to perform complex calculations like ranking, running totals, moving averages, and more.

1. Assign Row Numbers

SELECT id, salesperson, amount,
       ROW_NUMBER() OVER(ORDER BY amount DESC) AS row_num
FROM sales;

2. Rank Sales by Amount

SELECT id, salesperson, amount,
       RANK() OVER(ORDER BY amount DESC) AS rank,
       DENSE_RANK() OVER(ORDER BY amount DESC) AS dense_rank
FROM sales;

3. Previous and Next Sale Amount

SELECT id, salesperson, amount,
       LAG(amount) OVER(PARTITION BY salesperson ORDER BY date) AS previous_amount,
       LEAD(amount) OVER(PARTITION BY salesperson ORDER BY date) AS next_amount
FROM sales;

4. Running Total of Sales

SELECT id, date, salesperson, amount,
       SUM(amount) OVER(ORDER BY date) AS running_total
FROM sales;

5. Calculate 3-Day Moving Average

SELECT id, date, salesperson, amount,
       AVG(amount) OVER(ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg
FROM sales;

6. Divide Sales into Quartiles

SELECT id, salesperson, amount,
       NTILE(4) OVER(ORDER BY amount DESC) AS quartile
FROM sales;

Use these examples as a reference to incorporate SQL window functions into your data analysis workflows, allowing for more efficient and powerful queries.