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

Using the OVER Clause with ORDER BY in SQL

📖 Scenario: You work in a sales department and have a table of monthly sales data for different salespeople. You want to calculate a running total of sales for each salesperson ordered by month.

🎯 Goal: Build a SQL query that uses the OVER clause with ORDER BY to calculate a running total of sales per salesperson ordered by month.

📋 What You'll Learn

Create a table called sales with columns salesperson (text), month (integer), and amount (integer).

Insert the exact sales data for three salespeople over three months.

Write a query that selects salesperson, month, amount, and a running total of amount using SUM(amount) OVER (PARTITION BY salesperson ORDER BY month).

Order the final query result by salesperson and month.

💡 Why This Matters

🌍 Real World

Running totals are common in sales reports, financial statements, and time series analysis to track cumulative progress over time.

💼 Career

Knowing how to use window functions like OVER with ORDER BY is essential 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 salesperson (text), month (integer), and amount (integer). Insert these exact rows: ('Alice', 1, 100), ('Alice', 2, 150), ('Alice', 3, 200), ('Bob', 1, 80), ('Bob', 2, 120), ('Bob', 3, 160), ('Charlie', 1, 90), ('Charlie', 2, 110), ('Charlie', 3, 130).

SQL

-- Create the sales table and insert the exact data
-- Your code here

Hint

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

2

Add a helper variable for partitioning

Define a variable or alias called partition_column that will represent the salesperson column for use in the window function partitioning.

SQL

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

INSERT INTO sales (salesperson, month, amount) VALUES
('Alice', 1, 100),
('Alice', 2, 150),
('Alice', 3, 200),
('Bob', 1, 80),
('Bob', 2, 120),
('Bob', 3, 160),
('Charlie', 1, 90),
('Charlie', 2, 110),
('Charlie', 3, 130);

-- Define partition_column as salesperson
-- Your code here

Hint

In SQL, you don't create variables like in programming languages. Instead, you use the column name directly in the PARTITION BY clause.

3

Write the query with OVER clause and ORDER BY

Write a SQL query that selects salesperson, month, amount, and a running total called running_total which uses SUM(amount) OVER (PARTITION BY salesperson ORDER BY month).

SQL

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

INSERT INTO sales (salesperson, month, amount) VALUES
('Alice', 1, 100),
('Alice', 2, 150),
('Alice', 3, 200),
('Bob', 1, 80),
('Bob', 2, 120),
('Bob', 3, 160),
('Charlie', 1, 90),
('Charlie', 2, 110),
('Charlie', 3, 130);

-- Write the query selecting salesperson, month, amount, and running_total
-- Your code here

Hint

Use SUM(amount) OVER (PARTITION BY salesperson ORDER BY month) to calculate the running total.

4

Order the query result by salesperson and month

Add an ORDER BY clause to the query to sort the results by salesperson and then by month.

SQL

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

INSERT INTO sales (salesperson, month, amount) VALUES
('Alice', 1, 100),
('Alice', 2, 150),
('Alice', 3, 200),
('Bob', 1, 80),
('Bob', 2, 120),
('Bob', 3, 160),
('Charlie', 1, 90),
('Charlie', 2, 110),
('Charlie', 3, 130);

SELECT salesperson, month, amount,
       SUM(amount) OVER (PARTITION BY salesperson ORDER BY month) AS running_total
FROM sales
-- Add ORDER BY salesperson, month
-- Your code here

Hint

Use ORDER BY salesperson, month at the end of the query to sort the results.

Practice

1. What does the OVER clause with ORDER BY do in SQL?

easy

A. It creates a new table sorted by the specified column.

B. It performs calculations on rows in a specific order without grouping them.

C. It filters rows based on order conditions.

D. It groups rows and then orders the groups.

Solution

1. Step 1: Understand the OVER clause

   The OVER clause allows window functions to work on a set of rows related to the current row without grouping them.

2. Step 2: Role of ORDER BY inside OVER

   ORDER BY inside OVER defines the order in which rows are processed for functions like ROW_NUMBER() or SUM() running totals.

3. Final Answer:

   It performs calculations on rows in a specific order without grouping them. -> Option B

4. Quick Check:

   OVER with ORDER BY = ordered calculations without grouping [OK]

Hint: OVER with ORDER BY orders rows for calculations, no grouping needed [OK]

Common Mistakes:

• Thinking OVER groups rows like GROUP BY
• Confusing ORDER BY inside OVER with filtering
• Assuming it creates a new sorted table

2. Which of the following is the correct syntax to assign row numbers ordered by salary using the OVER clause?

easy

A. SELECT ROW_NUMBER(ORDER BY salary) AS rank FROM employees;

B. SELECT ROW_NUMBER() AS rank OVER ORDER BY salary FROM employees;

C. SELECT ROW_NUMBER() ORDER BY salary FROM employees;

D. SELECT ROW_NUMBER() OVER (ORDER BY salary) AS rank FROM employees;

Solution

1. Step 1: Identify correct ROW_NUMBER() syntax

   ROW_NUMBER() requires OVER() with ORDER BY inside parentheses to define ordering.

2. Step 2: Check each option

   SELECT ROW_NUMBER() OVER (ORDER BY salary) AS rank FROM employees; correctly uses ROW_NUMBER() OVER (ORDER BY salary). Others miss parentheses or misplace clauses.

3. Final Answer:

   SELECT ROW_NUMBER() OVER (ORDER BY salary) AS rank FROM employees; -> Option D

4. Quick Check:

   ROW_NUMBER() needs OVER(ORDER BY ...) [OK]

Hint: ROW_NUMBER() must be followed by OVER(ORDER BY column) [OK]

Common Mistakes:

• Omitting OVER() parentheses
• Placing ORDER BY outside OVER()
• Using ROW_NUMBER() without OVER

3. Given the table sales with columns id, amount, what is the output of this query?

SELECT id, amount, SUM(amount) OVER (ORDER BY id) AS running_total FROM sales ORDER BY id;

medium

A. The query returns an error due to missing GROUP BY.

B. Each row shows id, amount, and total sum of all amounts repeated.

C. Each row shows id, amount, and a running total of amounts ordered by id.

D. Rows are grouped by id with sum of amounts per group.

Solution

1. Step 1: Understand SUM() OVER (ORDER BY id)

   This calculates a running total of amount, adding amounts in order of id for each row.

2. Step 2: Analyze output per row

   Each row shows its id, amount, and the sum of amounts from first id up to current id.

3. Final Answer:

   Each row shows id, amount, and a running total of amounts ordered by id. -> Option C

4. Quick Check:

   SUM() OVER with ORDER BY = running total [OK]

Hint: SUM() OVER(ORDER BY) gives running totals, not grouped sums [OK]

Common Mistakes:

• Thinking it sums all rows for each row
• Expecting GROUP BY to be required
• Confusing running total with grouped sum

4. Identify the error in this query:

SELECT id, RANK() OVER ORDER BY score FROM players;

medium

A. Missing parentheses after OVER and ORDER BY inside them.

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

C. ORDER BY should be outside the OVER clause.

D. The query is correct and will run without errors.

Solution

1. Step 1: Check syntax of RANK() with OVER

   RANK() requires OVER() with parentheses enclosing ORDER BY clause.

2. Step 2: Identify missing parentheses

   The query misses parentheses after OVER and does not put ORDER BY inside them, causing syntax error.

3. Final Answer:

   Missing parentheses after OVER and ORDER BY inside them. -> Option A

4. Quick Check:

   OVER() must have parentheses with ORDER BY inside [OK]

Hint: Always use OVER(...) with parentheses enclosing ORDER BY [OK]

Common Mistakes:

• Writing OVER without parentheses
• Placing ORDER BY outside OVER()
• Assuming RANK() works without OVER

5. You want to assign a rank to employees based on their salary, but if two employees have the same salary, they should get the same rank and the next rank should skip accordingly. Which query correctly uses the OVER clause with ORDER BY to achieve this?

hard

A. SELECT employee_id, salary, RANK() OVER (ORDER BY salary DESC) AS salary_rank FROM employees;

B. SELECT employee_id, salary, ROW_NUMBER() OVER (ORDER BY salary DESC) AS salary_rank FROM employees;

C. SELECT employee_id, salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS salary_rank FROM employees;

D. SELECT employee_id, salary, RANK() OVER ORDER BY salary DESC AS salary_rank FROM employees;

Solution

1. Step 1: Understand ranking functions

   RANK() assigns same rank to ties and skips ranks after ties; DENSE_RANK() assigns same rank but no skips; ROW_NUMBER() assigns unique numbers.

2. Step 2: Check syntax and behavior

   SELECT employee_id, salary, RANK() OVER (ORDER BY salary DESC) AS salary_rank FROM employees; uses RANK() with correct OVER(ORDER BY salary DESC) syntax, matching requirement. SELECT employee_id, salary, RANK() OVER ORDER BY salary DESC AS salary_rank FROM employees; has syntax error. ROW_NUMBER() and DENSE_RANK() do not skip ranks after ties.

3. Final Answer:

   SELECT employee_id, salary, RANK() OVER (ORDER BY salary DESC) AS salary_rank FROM employees; -> Option A

4. Quick Check:

   RANK() with OVER(ORDER BY) skips ranks after ties [OK]

Hint: Use RANK() OVER(ORDER BY) for ranks with gaps on ties [OK]

Common Mistakes:

• Using ROW_NUMBER() which never skips ranks
• Using DENSE_RANK() which does not skip ranks
• Missing parentheses in OVER clause