Window Functions in SQL

🔔 Prelude:

Boss: “I need the average sales for 7 days.”

Me: “Sure! Which 7 days?”

Boss: “All the 7 days!”

Me: “Su…Sure…?”

🖋️ Window Functions

Window functions are generally applied after aggregate functions like MAX(), MIN(), AVG() to perform calculations across a set of table rows(days before, days after) that are related to the current row(current day).

Example Table 1: employees

Example 1: OVER() Without Partitioning

Calculate the average salary for all employees:

SELECT
    employee_id,
    department_id,
    salary,
    AVG(salary) OVER() AS avg_salary_all_departments
FROM
    employees;

Result:

Example 2: PARTITION BY

Calculate the average salary per department:

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

Result:

Example 3: ORDER BY with Ranking

Rank employees by salary within their departments:

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

Result:

Example Table 2: orders

Example 4: RANGE with Date Intervals

Sum the total orders in the last 7 days for each row: (INTERVAL 6 because included CURRENT ROW)

SELECT
    order_id,
    order_date,
    order_total,
    SUM(order_total) OVER(ORDER BY order_date RANGE BETWEEN INTERVAL '6' DAY PRECEDING AND CURRENT ROW) AS sum_last_7_days
FROM
    orders;

Result:

Example 5: ROWS Between Preceding and Following

Calculate the sum of order totals for the current row and the 2 preceding rows:

SELECT
    order_id,
    order_date,
    order_total,
    SUM(order_total) OVER(ORDER BY order_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS sum_last_3_orders
FROM
    orders;

Result:

References sheet

After max()…

• OVER(…): Applied over …
• PARTITION BY: Used to divide by …
• ORDER BY [DESC]
• ROWS [BETWEEN...AND]
  1. n PRECEDING
  2. n FOLLOWING
  3. CURRENT ROW
  4. UNBOUNDED PRECEDING All before
  5. UNBOUNDED FOLLOWING All after
• RANGE [BETWEEN...AND]
  1. INTERVAL 'n' DAY