Week 6 - SQL Chips and Chunks Newsletter

Author

Jose Vazquez
March 07, 2025

1. SQL Chip: Introduction to Window Functions

Window functions are incredibly powerful in SQL because they allow you to perform calculations across a set of table rows related to the current row. Unlike aggregate functions that group data, window functions retain individual row details while still allowing you to perform operations like sums, averages, and ranking. Here’s a simple example of a window function that calculates the running total of sales:

SELECT
sale_date,
sales_amount,
SUM(sales_amount) OVER (ORDER BY sale_date) AS running_total
FROM sales;

In this example, SUM(sales_amount) OVER (ORDER BY sale_date) computes the cumulative total of sales ordered by date. Window functions provide a more flexible and powerful way to analyze data compared to traditional aggregation.

2. SQL Chunk: Understanding PARTITION BY

The PARTITION BY clause allows you to break your window function into smaller partitions, or groups, so that the calculation is applied to each group independently. For example, if you want to calculate the running total of sales for each region separately, you can use PARTITION BY:

SELECT
region,
sale_date,
sales_amount,
SUM(sales_amount) OVER (PARTITION BY region ORDER BY sale_date) AS running_total
FROM sales;

Here, the sales are partitioned by region, so the running total is reset for each region. The PARTITION BY clause is great when you need to perform calculations within specific groups of your data, such as regions, departments, or product categories.

3. SQL Challenge: Ranking Employees by Salary

This week’s challenge: Write a query to rank employees by salary within each department. Use the RANK() window function to assign a rank to each employee based on their salary, and use PARTITION BY to rank employees within their department.

Hint: You can use RANK() with PARTITION BY department and ORDER BY salary DESC to rank employees by salary in descending order.

SELECT
department,
name,
salary,
RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS salary_rank
FROM employees;

This query will give each employee a rank within their department, with the highest salary ranked 1. Employees with the same salary will receive the same rank, and the next rank will be skipped.

4. Common SQL Mistake: Misunderstanding the Role of PARTITION BY

A common mistake is using window functions without fully understanding how PARTITION BY works. If you omit PARTITION BY, the window function will operate over the entire result set, rather than within specific partitions (e.g., by department or region). Always ensure you’re using PARTITION BY when you want to break the data into groups.

For example, without PARTITION BY, this query:

SELECT
department,
name,
salary,
RANK() OVER (ORDER BY salary DESC) AS salary_rank
FROM employees;

Ranks employees across all departments, rather than within each department. This can lead to incorrect results if you want ranks specific to departments.

5. SQL in Practice: Calculating Moving Averages

A practical use of window functions is calculating moving averages. For instance, let’s say you want to compute a 3-day moving average of sales. You can use the AVG() window function with PARTITION BY and ORDER BY to do this:

SELECT
sale_date,
sales_amount,
AVG(sales_amount) OVER (ORDER BY sale_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg
FROM sales;

This query calculates the moving average of sales for each day, considering the previous two days (including the current day). This technique is useful in trend analysis or smoothing out fluctuations in data over time.

6. SQL Resource: SQL Play App (iPhone & Android)

If you want to practice SQL and window functions on the go, SQL Play is an excellent app available for both iPhone and Android. The app offers a variety of SQL challenges, including exercises that focus on window functions like PARTITION BY and ORDER BY. It’s a great tool for both beginners and advanced learners to practice anytime, anywhere.

You can download the app here: