Why window functions are powerful in PostgreSQL - See It in Action

Why Window Functions Are Powerful

📖 Scenario: Imagine you work at a company that tracks sales data for different products across multiple stores. You want to analyze sales trends without losing the details of each sale.

🎯 Goal: You will build a simple sales table and use a window function to calculate running totals of sales per product. This will show how window functions let you perform calculations across rows related to the current row, without grouping and losing row details.

📋 What You'll Learn

Create a table called sales with columns id, product, and amount

Insert 5 rows of sales data with specific products and amounts

Write a query that uses the SUM() OVER (PARTITION BY product ORDER BY id) window function to calculate running totals per product

Select id, product, amount, and the running total as running_total

💡 Why This Matters

🌍 Real World

Window functions are used in business reports, analytics, and dashboards to calculate running totals, rankings, moving averages, and more without losing row-level details.

💼 Career

Understanding window functions is essential for data analysts, database developers, and anyone working with SQL to create advanced reports and insights.

1

Create the sales table

Create a table called sales with columns id as integer primary key, product as text, and amount as integer.

PostgreSQL

-- Create the sales table with id, product, and amount columns
-- Your code here

Need a hint?

Use CREATE TABLE with the specified columns and types.

2

Insert sales data

Insert these 5 rows into the sales table: (1, 'Apple', 10), (2, 'Banana', 5), (3, 'Apple', 15), (4, 'Banana', 10), (5, 'Apple', 5).

PostgreSQL

CREATE TABLE sales (
  id INTEGER PRIMARY KEY,
  product TEXT,
  amount INTEGER
);
-- Insert the 5 sales rows below
-- Your code here

Need a hint?

Use a single INSERT INTO statement with multiple rows.

3

Write the window function query

Write a SELECT query on sales that selects id, product, amount, and a running total of amount per product ordered by id. Use SUM(amount) OVER (PARTITION BY product ORDER BY id) as running_total.

PostgreSQL

CREATE TABLE sales (
  id INTEGER PRIMARY KEY,
  product TEXT,
  amount INTEGER
);

INSERT INTO sales (id, product, amount) VALUES
  (1, 'Apple', 10),
  (2, 'Banana', 5),
  (3, 'Apple', 15),
  (4, 'Banana', 10),
  (5, 'Apple', 5);

-- Write the SELECT query with the window function below
-- Your code here

Need a hint?

Use SUM() OVER (PARTITION BY product ORDER BY id) to get running totals per product.

4

Complete the query with ordering

Add an ORDER BY id clause at the end of the SELECT query to show results sorted by id.

PostgreSQL

CREATE TABLE sales (
  id INTEGER PRIMARY KEY,
  product TEXT,
  amount INTEGER
);

INSERT INTO sales (id, product, amount) VALUES
  (1, 'Apple', 10),
  (2, 'Banana', 5),
  (3, 'Apple', 15),
  (4, 'Banana', 10),
  (5, 'Apple', 5);

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

Need a hint?

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