Week 45 - SQL Chips and Chunks Newsletter

1. SQL Chip: Introduction to Window Functions

Window functions are a game-changer for your career because they allow you to perform calculations across a set of rows while keeping individual row details intact. Unlike aggregate functions that collapse your data into single summary rows, window functions let you see the "forest" and the "trees" at the same time. This is essential for building reports where you need to compare an individual’s performance against a group average or calculate running totals.

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. 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 ranks employees across all departments, rather than within each department:

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

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: SQLPad

SQLPad — This is a premier platform specifically designed to help you crush technical interviews. It offers a massive library of "coding-style" SQL questions that mirror what you'll face at top-tier tech companies. It is particularly effective for mastering window functions and complex joins in a simulated interview environment.