Window Functions

1What Are Window Functions?✨

Window functions allow you to perform calculations across a set of table rows that are related to the current row. They are powerful tools for analytics and can help you derive insights from your data without needing to group rows into a single output.

2Why Learn Window Functions?🎯

• ✓ Analyze data in context of other rows
• ✓ Calculate running totals and moving averages
• ✓ Perform ranking and percentile calculations
• ✓ Enhance reporting and data analysis capabilities

3Basic Syntax💡

The basic syntax of a window function includes the function name followed by an OVER clause, which defines the window of rows to consider for the calculation.

4Example:🚀

• SELECT employee_id, salary,
• AVG(salary) OVER (PARTITION BY department_id) AS avg_department_salary
• FROM employees;

5Understanding PARTITION BY🔍

The PARTITION BY clause divides the result set into partitions to which the window function is applied. You can think of it as grouping your data without collapsing it.

6Example:⭐

• SELECT employee_id, department_id, salary,
• RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS department_rank
• FROM employees;

7Common Window Functions🧩

• Some common window functions include:
• ROW_NUMBER(): Assigns a unique number to each row within a partition.
• RANK(): Similar to ROW_NUMBER but allows for ties.
• SUM(): Calculates cumulative sums across rows.

8Example:🎈

• SELECT employee_id, salary,
• SUM(salary) OVER (ORDER BY employee_id) AS running_total
• FROM employees;

9Capstone Project: Sales Analysis✨

For your capstone project, you'll analyze sales data using window functions. Calculate total sales per product, find top-selling products, and create a ranking of sales representatives.

10Example:🎯

WITH sales_data AS (
    SELECT product_id, sales_amount,
           SUM(sales_amount) OVER (PARTITION BY product_id) AS total_sales
    FROM sales
)
SELECT product_id, total_sales,
       RANK() OVER (ORDER BY total_sales DESC) AS sales_rank
FROM sales_data;