PARTITION BY for grouping windows in PostgreSQL - Mini Project: Build & Apply

Using PARTITION BY for Grouping Windows in PostgreSQL

📖 Scenario: You work at a bookstore that tracks sales data. You want to analyze sales by each book category to see how each sale compares within its category.

🎯 Goal: Build a PostgreSQL query that uses PARTITION BY to group sales by book category and calculate the running total of sales within each category.

📋 What You'll Learn

Create a table called book_sales with columns sale_id, category, and amount.

Insert the exact sales data provided into book_sales.

Write a query that uses SUM(amount) OVER (PARTITION BY category ORDER BY sale_id) to calculate running totals per category.

Select sale_id, category, amount, and the running total as running_total.

💡 Why This Matters

🌍 Real World

Analyzing sales data by category helps businesses understand performance trends within each group.

💼 Career

Using window functions with PARTITION BY is a common skill for data analysts and database developers to perform advanced data analysis.

1

Create the book_sales table and insert data

Create a table called book_sales with columns sale_id as integer, category as text, and amount as numeric. Then insert these exact rows: (1, 'Fiction', 10), (2, 'Fiction', 15), (3, 'Non-Fiction', 20), (4, 'Fiction', 5), (5, 'Non-Fiction', 10).

PostgreSQL

-- Create table book_sales and insert data
-- Your code here

Need a hint?

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

2

Add a variable for ordering sales

Define a variable or alias called order_col that will be used to order sales by sale_id in the query. This will help organize sales in the running total calculation.

PostgreSQL

CREATE TABLE book_sales (
    sale_id INTEGER,
    category TEXT,
    amount NUMERIC
);

INSERT INTO book_sales (sale_id, category, amount) VALUES
(1, 'Fiction', 10),
(2, 'Fiction', 15),
(3, 'Non-Fiction', 20),
(4, 'Fiction', 5),
(5, 'Non-Fiction', 10);

-- Define order_col as sale_id for ordering
-- Your code here

Need a hint?

In SQL, you don't create variables like in programming languages. Instead, you use column names directly in the query's ORDER BY clause.

3

Write the query using PARTITION BY to calculate running totals

Write a SELECT query that retrieves sale_id, category, amount, and a running total of amount called running_total. Use SUM(amount) OVER (PARTITION BY category ORDER BY sale_id) to calculate the running total grouped by category and ordered by sale_id.

PostgreSQL

CREATE TABLE book_sales (
    sale_id INTEGER,
    category TEXT,
    amount NUMERIC
);

INSERT INTO book_sales (sale_id, category, amount) VALUES
(1, 'Fiction', 10),
(2, 'Fiction', 15),
(3, 'Non-Fiction', 20),
(4, 'Fiction', 5),
(5, 'Non-Fiction', 10);

SELECT sale_id, category, amount,
       -- Calculate running total here
       -- Your code here
FROM book_sales;

Need a hint?

Use the window function SUM() OVER (PARTITION BY category ORDER BY sale_id) to get running totals per category.

4

Complete the query with ordering by sale_id

Add an ORDER BY sale_id clause at the end of the query to display the results sorted by sale_id.

PostgreSQL

CREATE TABLE book_sales (
    sale_id INTEGER,
    category TEXT,
    amount NUMERIC
);

INSERT INTO book_sales (sale_id, category, amount) VALUES
(1, 'Fiction', 10),
(2, 'Fiction', 15),
(3, 'Non-Fiction', 20),
(4, 'Fiction', 5),
(5, 'Non-Fiction', 10);

SELECT sale_id, category, amount,
       SUM(amount) OVER (PARTITION BY category ORDER BY sale_id) AS running_total
FROM book_sales
-- Add ORDER BY sale_id here
-- Your code here

Need a hint?

Use ORDER BY sale_id at the end of the query to sort the results by sale ID.