Week 11 - SQL Chips and Chunks Newsletter

1. SQL Chip: Introduction to Aggregate Window Functions (SUM() OVER, AVG() OVER) Window functions like SUM() and AVG() can also be used as aggregate functions that calculate totals or averages across rows—without grouping everything into one row. Unlike GROUP BY, they keep your rows visible and just add the calculated value next to each one.

Example: Calculate the total sales per region over time, ordered by date.

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

Each row still shows its own sale, but now includes a growing total sales value for that region, sorted by date.

2. SQL Chunk: Using AVG() OVER to See the Average Salary per Department (by hire date) Let’s say you want to show every employee’s salary along with the average salary for their department over time based on hire date.

SELECT 
    department, 
    name, 
    salary, 
    AVG(salary) OVER (PARTITION BY department ORDER BY hire_date) AS dept_avg_salary
FROM employees;

This adds an evolving average based on hiring sequence, great for historical analysis.

3. SQL Challenge: Compare Each Sale to the Region's Average Over Time Challenge: Write a query that shows each sale and how it compares to the average sales amount in that region over time.

SELECT 
    region, 
    sale_date, 
    sales_amount, 
    AVG(sales_amount) OVER (PARTITION BY region ORDER BY sale_date) AS avg_region_sale
FROM sales;

This shows each sale next to the growing average for that region, in sale order.

4. Common SQL Mistake: Skipping ORDER BY in OVER() A common mistake is to omit the ORDER BY in your window function. Without it, the database won’t know the order in which to calculate the running values.

Wrong:

SELECT 
    name, 
    salary,
    AVG(salary) OVER (PARTITION BY department) AS avg_salary
FROM employees;

Right:

SELECT 
    name, 
    salary,
    AVG(salary) OVER (PARTITION BY department ORDER BY hire_date) AS avg_salary
FROM employees;

Ordering matters when analyzing trends or patterns over time.

5. SQL in Practice: Add Running Total Sales to Each Transaction Here’s a practical way to enrich each row of data with a running total:

SELECT 
    store_id, 
    sale_date, 
    sales_amount, 
    SUM(sales_amount) OVER (PARTITION BY store_id ORDER BY sale_date) AS store_running_sales
FROM sales;

This helps you track progress and patterns within each store as time goes on.

6. SQL Resource: Data Engineering Academy If you’re ready to go deeper into SQL and data pipelines, the Data Engineering Academy is a top-tier, project-based program that teaches real-world SQL—including window functions, analytics, and much more.

It’s perfect for anyone looking to level up from beginner to job-ready data roles like data analyst or data engineer.

Start here with my referral link: Data Engineering Academy