OVER clause with PARTITION BY in SQL - Mini Project: Build & Apply

Using the OVER Clause with PARTITION BY in SQL

📖 Scenario: You work for a retail company that wants to analyze sales data. The company wants to see the total sales amount for each salesperson, but also wants to keep the details of each sale.

🎯 Goal: Build an SQL query that uses the OVER clause with PARTITION BY to calculate the total sales amount for each salesperson alongside each individual sale record.

📋 What You'll Learn

Create a table called sales with columns sale_id, salesperson, and amount.

Insert the exact sales data provided.

Write a query that selects all columns and adds a new column total_sales which shows the total sales amount per salesperson using SUM(amount) OVER (PARTITION BY salesperson).

💡 Why This Matters

🌍 Real World

Sales teams often need to see individual sales along with total sales per person to understand performance.

💼 Career

Knowing how to use window functions like OVER with PARTITION BY is valuable for data analysts and database developers to write efficient and insightful queries.

1

Create the sales table and insert data

Create a table called sales with columns sale_id (integer), salesperson (text), and amount (integer). Then insert these exact rows: (1, 'Alice', 100), (2, 'Bob', 150), (3, 'Alice', 200), (4, 'Bob', 100), (5, 'Charlie', 300).

SQL

-- Create the sales table and insert the data
-- 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 query to select all columns from the sales table.

SQL

CREATE TABLE sales (
  sale_id INTEGER,
  salesperson TEXT,
  amount INTEGER
);

INSERT INTO sales (sale_id, salesperson, amount) VALUES
  (1, 'Alice', 100),
  (2, 'Bob', 150),
  (3, 'Alice', 200),
  (4, 'Bob', 100),
  (5, 'Charlie', 300);

-- Select all columns from sales
-- Your code here

Need a hint?

Use SELECT * FROM sales; to get all rows and columns.

3

Add total sales per salesperson using OVER with PARTITION BY

Modify the SELECT query to add a new column called total_sales that shows the total sales amount for each salesperson. Use SUM(amount) OVER (PARTITION BY salesperson) to calculate this.

SQL

CREATE TABLE sales (
  sale_id INTEGER,
  salesperson TEXT,
  amount INTEGER
);

INSERT INTO sales (sale_id, salesperson, amount) VALUES
  (1, 'Alice', 100),
  (2, 'Bob', 150),
  (3, 'Alice', 200),
  (4, 'Bob', 100),
  (5, 'Charlie', 300);

-- Select all columns and add total_sales per salesperson
-- Your code here

Need a hint?

Use SUM(amount) OVER (PARTITION BY salesperson) to get total sales per salesperson.

4

Complete the query with ordering

Add an ORDER BY salesperson, sale_id clause to the query to sort the results by salesperson name and then by sale ID.

SQL

CREATE TABLE sales (
  sale_id INTEGER,
  salesperson TEXT,
  amount INTEGER
);

INSERT INTO sales (sale_id, salesperson, amount) VALUES
  (1, 'Alice', 100),
  (2, 'Bob', 150),
  (3, 'Alice', 200),
  (4, 'Bob', 100),
  (5, 'Charlie', 300);

SELECT sale_id, salesperson, amount, SUM(amount) OVER (PARTITION BY salesperson) AS total_sales FROM sales
-- Add ORDER BY salesperson, sale_id below
-- Your code here

Need a hint?

Use ORDER BY salesperson, sale_id to sort the results.