Practical window function patterns in PostgreSQL - Mini Project: Build & Apply

Practical Window Function Patterns in PostgreSQL

📖 Scenario: You are working as a data analyst for a retail company. You have a table called sales that records each sale transaction with columns for sale_id, store_id, sale_date, and amount. Your manager wants to analyze sales trends by store and over time.

🎯 Goal: Build a PostgreSQL query using window functions to calculate the running total of sales amount per store ordered by sale date, and also rank each sale within its store by amount.

📋 What You'll Learn

Use the sales table with columns sale_id, store_id, sale_date, and amount

Calculate a running total of amount per store_id ordered by sale_date

Rank each sale within its store_id by amount in descending order

Use window functions SUM() OVER() and RANK() OVER()

💡 Why This Matters

🌍 Real World

Retail analysts often need to track cumulative sales and rank transactions to identify top-performing stores or sales days.

💼 Career

Understanding window functions is essential for data analysts and database professionals to perform advanced data analysis and reporting.

1

Create the sales table and insert sample data

Write SQL statements to create a table called sales with columns sale_id (integer), store_id (integer), sale_date (date), and amount (numeric). Then insert these exact rows: (1, 101, '2024-01-01', 100.00), (2, 101, '2024-01-02', 150.00), (3, 102, '2024-01-01', 200.00), (4, 101, '2024-01-03', 50.00), (5, 102, '2024-01-02', 300.00).

PostgreSQL

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

Need a hint?

Use CREATE TABLE to define the table and INSERT INTO to add rows with exact values.

2

Define the window partition and order for running total

Write a SQL query selecting all columns from sales. Add a window function SUM(amount) OVER (PARTITION BY store_id ORDER BY sale_date) and alias it as running_total to calculate the running total of sales amount per store ordered by sale date.

PostgreSQL

SELECT sale_id, store_id, sale_date, amount,
       -- Add running total calculation here
       SUM(amount) OVER (PARTITION BY store_id ORDER BY sale_date) AS running_total
FROM sales;
-- Your code here

Need a hint?

Use SUM(amount) OVER (PARTITION BY store_id ORDER BY sale_date) to get the running total per store.

3

Add ranking of sales by amount within each store

Extend the previous query by adding a new column that uses the window function RANK() OVER (PARTITION BY store_id ORDER BY amount DESC) and alias it as sale_rank. This ranks sales within each store by amount from highest to lowest.

PostgreSQL

SELECT sale_id, store_id, sale_date, amount,
       SUM(amount) OVER (PARTITION BY store_id ORDER BY sale_date) AS running_total,
       -- Add ranking by amount per store here
       RANK() OVER (PARTITION BY store_id ORDER BY amount DESC) AS sale_rank
FROM sales;
-- Your code here

Need a hint?

Use RANK() OVER (PARTITION BY store_id ORDER BY amount DESC) to rank sales by amount within each store.

4

Complete the query with ordering by store and sale date

Add an ORDER BY store_id, sale_date clause at the end of the query to display results sorted by store and then by sale date.

PostgreSQL

SELECT sale_id, store_id, sale_date, amount,
       SUM(amount) OVER (PARTITION BY store_id ORDER BY sale_date) AS running_total,
       RANK() OVER (PARTITION BY store_id ORDER BY amount DESC) AS sale_rank
FROM sales
ORDER BY store_id, sale_date;
-- Your code here

Need a hint?

Use ORDER BY store_id, sale_date to sort the final output by store and date.