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

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

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

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

Hint

Use ORDER BY salesperson, sale_id to sort the results.

Practice

1. What does the PARTITION BY clause do when used with the OVER clause in SQL?

easy

A. It divides the result set into groups to perform calculations within each group.

B. It filters rows before aggregation happens.

C. It sorts the entire result set globally.

D. It removes duplicate rows from the result.

Solution

1. Step 1: Understand the role of PARTITION BY

   The PARTITION BY clause groups rows based on column values, similar to grouping in real life like sorting students by class.

2. Step 2: See how OVER uses PARTITION BY

   The OVER clause applies functions like SUM or ROW_NUMBER within each group separately, not on the whole table.

3. Final Answer:

   It divides the result set into groups to perform calculations within each group. -> Option A

4. Quick Check:

   PARTITION BY = group rows for calculations [OK]

Hint: Think of PARTITION BY as grouping rows inside a window function [OK]

Common Mistakes:

• Confusing PARTITION BY with WHERE filtering
• Thinking PARTITION BY sorts all rows globally
• Assuming PARTITION BY removes duplicates

2. Which of the following is the correct syntax to calculate a running total of sales per department using OVER with PARTITION BY?

easy

A. SELECT department, sales, SUM(sales) OVER ORDER BY date PARTITION BY department AS running_total FROM sales_data;

B. SELECT department, sales, SUM(sales) OVER (PARTITION BY department ORDER BY date) AS running_total FROM sales_data;

C. SELECT department, sales, SUM(sales) PARTITION BY department OVER ORDER BY date AS running_total FROM sales_data;

D. SELECT department, sales, SUM(sales) OVER (ORDER BY date PARTITION BY department) AS running_total FROM sales_data;

Solution

1. Step 1: Identify correct OVER clause order

   The correct syntax places PARTITION BY before ORDER BY inside the parentheses after OVER.

2. Step 2: Check each option's syntax

   SELECT department, sales, SUM(sales) OVER (PARTITION BY department ORDER BY date) AS running_total FROM sales_data; correctly uses SUM(sales) OVER (PARTITION BY department ORDER BY date). Others misplace PARTITION BY or OVER keywords.

3. Final Answer:

   SELECT department, sales, SUM(sales) OVER (PARTITION BY department ORDER BY date) AS running_total FROM sales_data; -> Option B

4. Quick Check:

   PARTITION BY before ORDER BY inside OVER [OK]

Hint: Remember: OVER (PARTITION BY ... ORDER BY ...) is the correct order [OK]

Common Mistakes:

• Swapping PARTITION BY and ORDER BY order
• Placing PARTITION BY outside OVER parentheses
• Using PARTITION BY without OVER

3. Given the table Employees with columns Department and Salary, what will the following query return?

SELECT Department, Salary, RANK() OVER (PARTITION BY Department ORDER BY Salary DESC) AS SalaryRank FROM Employees;

medium

A. Returns the salary and department without any ranking.

B. Ranks salaries across all employees ignoring departments.

C. Calculates the total salary per department.

D. Ranks salaries within each department from highest to lowest, starting at 1 for the highest salary.

Solution

1. Step 1: Understand RANK() with PARTITION BY

   The RANK() function assigns ranks starting at 1 within each partition (department), ordered by salary descending.

2. Step 2: Analyze the query output

   Each employee gets a rank based on salary within their department, highest salary ranked 1, ties get same rank.

3. Final Answer:

   Ranks salaries within each department from highest to lowest, starting at 1 for the highest salary. -> Option D

4. Quick Check:

   RANK() OVER PARTITION BY groups and ranks rows [OK]

Hint: RANK() with PARTITION BY ranks within groups, not whole table [OK]

Common Mistakes:

• Thinking RANK() ignores PARTITION BY
• Confusing RANK() with SUM()
• Assuming ranking is global, not per group

4. Identify the error in this SQL query:

SELECT employee_id, department, SUM(salary) OVER PARTITION BY department FROM employees;

medium

A. department column cannot be used in PARTITION BY.

B. SUM() cannot be used with OVER clause.

C. Missing parentheses around PARTITION BY clause inside OVER.

D. employee_id must be included in PARTITION BY.

Solution

1. Step 1: Check OVER clause syntax

   The OVER clause must have parentheses enclosing PARTITION BY and other clauses.

2. Step 2: Identify missing parentheses

   The query misses parentheses after OVER, it should be OVER (PARTITION BY department).

3. Final Answer:

   Missing parentheses around PARTITION BY clause inside OVER. -> Option C

4. Quick Check:

   OVER requires parentheses around PARTITION BY [OK]

Hint: Always put parentheses after OVER, even if only PARTITION BY used [OK]

Common Mistakes:

• Omitting parentheses after OVER
• Using aggregate without OVER for window function
• Including wrong columns in PARTITION BY

5. You want to find each student's score rank within their class and also show the average score of their class. Which SQL query correctly uses OVER with PARTITION BY to achieve this?

hard

A. SELECT student_id, class, score, RANK() OVER (PARTITION BY class ORDER BY score DESC) AS rank_in_class, AVG(score) OVER (PARTITION BY class) AS avg_class_score FROM student_scores;

B. SELECT student_id, class, score, RANK() OVER (ORDER BY score DESC PARTITION BY class) AS rank_in_class, AVG(score) OVER (PARTITION BY class) AS avg_class_score FROM student_scores;

C. SELECT student_id, class, score, RANK() PARTITION BY class ORDER BY score DESC AS rank_in_class, AVG(score) OVER (class) AS avg_class_score FROM student_scores;

D. SELECT student_id, class, score, RANK() OVER (PARTITION BY class ORDER BY score) AS rank_in_class, AVG(score) OVER (PARTITION BY class ORDER BY score) AS avg_class_score FROM student_scores;

Solution

1. Step 1: Use correct syntax for RANK() and AVG() with PARTITION BY

   Both functions must have OVER (PARTITION BY class ...). RANK() orders descending for highest score first; AVG() just partitions by class.

2. Step 2: Check each option's correctness

   SELECT student_id, class, score, RANK() OVER (PARTITION BY class ORDER BY score DESC) AS rank_in_class, AVG(score) OVER (PARTITION BY class) AS avg_class_score FROM student_scores; correctly uses RANK() OVER (PARTITION BY class ORDER BY score DESC) and AVG(score) OVER (PARTITION BY class). Others have syntax errors or wrong order.

3. Final Answer:

   SELECT student_id, class, score, RANK() OVER (PARTITION BY class ORDER BY score DESC) AS rank_in_class, AVG(score) OVER (PARTITION BY class) AS avg_class_score FROM student_scores; -> Option A

4. Quick Check:

   Use OVER (PARTITION BY ...) with correct order and parentheses [OK]

Hint: Use separate OVER clauses with PARTITION BY for each window function [OK]

Common Mistakes:

• Mixing ORDER BY and PARTITION BY order inside OVER
• Missing parentheses around PARTITION BY
• Using PARTITION BY without OVER