Why window functions are needed in SQL - See It in Action

Understanding Why Window Functions Are Needed in SQL

📖 Scenario: You work as a data analyst for a retail company. You have a sales table with daily sales data for different stores. You want to analyze sales trends and compare each day's sales to the average sales of the store without losing the detail of each row.

🎯 Goal: Build a simple SQL query using window functions to calculate the average sales per store alongside each day's sales.

📋 What You'll Learn

Create a table called sales with columns store_id, sale_date, and amount.

Insert sample data for at least two stores with sales on multiple dates.

Write a query that uses a window function to calculate the average sales per store.

Show each sale amount along with the average sales for that store.

💡 Why This Matters

🌍 Real World

Window functions are used in business reports to compare individual records with group statistics, like sales per day compared to average sales per store.

💼 Career

Data analysts and database developers use window functions to write efficient queries that provide detailed and summarized data together, improving insights without complex joins or subqueries.

1

Create the sales table and insert data

Create a table called sales with columns store_id (integer), sale_date (date), and amount (integer). Insert these exact rows: (1, '2024-01-01', 100), (1, '2024-01-02', 150), (2, '2024-01-01', 200), (2, '2024-01-02', 250).

SQL

-- Create the sales table and insert the specified rows
-- Your code here

Need a hint?

Use CREATE TABLE to define the table and INSERT INTO to add rows.

2

Write a basic SELECT query

Write a SELECT query to retrieve all columns from the sales table ordered by store_id and sale_date.

SQL

CREATE TABLE sales (
  store_id INTEGER,
  sale_date DATE,
  amount INTEGER
);

INSERT INTO sales (store_id, sale_date, amount) VALUES
(1, '2024-01-01', 100),
(1, '2024-01-02', 150),
(2, '2024-01-01', 200),
(2, '2024-01-02', 250);

-- Write a SELECT query to get all columns ordered by store_id and sale_date
-- Your code here

Need a hint?

Use ORDER BY store_id, sale_date to sort the results.

3

Add a window function to calculate average sales per store

Modify the SELECT query to add a column called avg_sales that shows the average amount per store_id using the window function AVG(amount) OVER (PARTITION BY store_id).

SQL

CREATE TABLE sales (
  store_id INTEGER,
  sale_date DATE,
  amount INTEGER
);

INSERT INTO sales (store_id, sale_date, amount) VALUES
(1, '2024-01-01', 100),
(1, '2024-01-02', 150),
(2, '2024-01-01', 200),
(2, '2024-01-02', 250);

-- Add a column avg_sales using AVG(amount) OVER (PARTITION BY store_id)
-- Your code here

Need a hint?

Use AVG(amount) OVER (PARTITION BY store_id) to calculate the average per store.

4

Explain why window functions are useful here

Add a comment explaining that window functions let you calculate aggregates like averages without losing the detail of each row, unlike GROUP BY which groups rows together.

SQL

CREATE TABLE sales (
  store_id INTEGER,
  sale_date DATE,
  amount INTEGER
);

INSERT INTO sales (store_id, sale_date, amount) VALUES
(1, '2024-01-01', 100),
(1, '2024-01-02', 150),
(2, '2024-01-01', 200),
(2, '2024-01-02', 250);

SELECT store_id, sale_date, amount,
       AVG(amount) OVER (PARTITION BY store_id) AS avg_sales
FROM sales
ORDER BY store_id, sale_date;

-- Add a comment explaining why window functions are needed
-- Your code here

Need a hint?

Explain that window functions keep row details while adding aggregate info.