SUM, AVG, COUNT as window functions in PostgreSQL - Mini Project: Build & Apply

Using SUM, AVG, and COUNT as Window Functions in PostgreSQL

📖 Scenario: You work for a small online store. You have a table of sales records showing each sale's sale_id, customer_id, and amount. You want to analyze sales data by calculating running totals, average sales, and counts of sales per customer.

🎯 Goal: Build a PostgreSQL query that uses window functions SUM(), AVG(), and COUNT() to calculate running totals, average sale amounts, and total sales count per customer.

📋 What You'll Learn

Create a table called sales with columns sale_id (integer), customer_id (integer), and amount (numeric).

Insert exactly 5 rows into sales with specified values.

Write a query that uses SUM(amount) OVER (PARTITION BY customer_id ORDER BY sale_id) to get running total per customer.

Use AVG(amount) OVER (PARTITION BY customer_id) to get average sale amount per customer.

Use COUNT(*) OVER (PARTITION BY customer_id) to get total number of sales per customer.

💡 Why This Matters

🌍 Real World

Window functions help analyze data trends over groups without collapsing rows, useful in sales, finance, and reporting.

💼 Career

Understanding window functions is essential for data analysts and database developers to write efficient and insightful SQL queries.

1

Create the sales table and insert data

Create a table called sales with columns sale_id (integer), customer_id (integer), and amount (numeric). Then insert these exact rows: (1, 101, 50.00), (2, 102, 75.00), (3, 101, 100.00), (4, 103, 20.00), (5, 102, 125.00).

PostgreSQL

-- Create the sales table and insert the 5 rows below
-- Your code here

Need a hint?

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

2

Set up the window partition and order

Write the beginning of a SELECT query from sales that selects sale_id, customer_id, and amount. Add a window clause that partitions by customer_id and orders by sale_id. Assign this window clause the name cust_win.

PostgreSQL

CREATE TABLE sales (
    sale_id INTEGER,
    customer_id INTEGER,
    amount NUMERIC
);

INSERT INTO sales (sale_id, customer_id, amount) VALUES
(1, 101, 50.00),
(2, 102, 75.00),
(3, 101, 100.00),
(4, 103, 20.00),
(5, 102, 125.00);

SELECT sale_id, customer_id, amount
FROM sales
WINDOW cust_win AS (PARTITION BY customer_id ORDER BY sale_id)
-- Your code here

Need a hint?

Use the WINDOW clause to define a named window cust_win with partition and order.

3

Add SUM, AVG, and COUNT window functions

Extend the SELECT query to include these columns: SUM(amount) OVER cust_win AS running_total, AVG(amount) OVER (PARTITION BY customer_id) AS avg_amount, and COUNT(*) OVER (PARTITION BY customer_id) AS sales_count.

PostgreSQL

CREATE TABLE sales (
    sale_id INTEGER,
    customer_id INTEGER,
    amount NUMERIC
);

INSERT INTO sales (sale_id, customer_id, amount) VALUES
(1, 101, 50.00),
(2, 102, 75.00),
(3, 101, 100.00),
(4, 103, 20.00),
(5, 102, 125.00);

SELECT sale_id, customer_id, amount,
-- Add SUM, AVG, and COUNT window functions here
SUM(amount) OVER cust_win AS running_total,
AVG(amount) OVER (PARTITION BY customer_id) AS avg_amount,
COUNT(*) OVER (PARTITION BY customer_id) AS sales_count
FROM sales
WINDOW cust_win AS (PARTITION BY customer_id ORDER BY sale_id);

Need a hint?

Use window functions with OVER and the named window cust_win or inline partition clauses.

4

Complete the query with ORDER BY

Add ORDER BY sale_id at the end of the SELECT query to sort the results by sale_id.

PostgreSQL

CREATE TABLE sales (
    sale_id INTEGER,
    customer_id INTEGER,
    amount NUMERIC
);

INSERT INTO sales (sale_id, customer_id, amount) VALUES
(1, 101, 50.00),
(2, 102, 75.00),
(3, 101, 100.00),
(4, 103, 20.00),
(5, 102, 125.00);

SELECT sale_id, customer_id, amount,
       SUM(amount) OVER cust_win AS running_total,
       AVG(amount) OVER (PARTITION BY customer_id) AS avg_amount,
       COUNT(*) OVER (PARTITION BY customer_id) AS sales_count
FROM sales
WINDOW cust_win AS (PARTITION BY customer_id ORDER BY sale_id)
-- Add ORDER BY sale_id here

Need a hint?

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