Percent of total with window functions in SQL - Mini Project: Build & Apply

Calculate Percent of Total Sales Using Window Functions

📖 Scenario: You work in a retail company database. You have a table of sales data showing how much each product sold. You want to find out what percentage of the total sales each product represents.

🎯 Goal: Create a SQL query that calculates the percent of total sales for each product using window functions.

📋 What You'll Learn

Create a table called sales with columns product (text) and amount (integer).

Insert the exact sales data for 5 products: 'Apples' 100, 'Bananas' 150, 'Cherries' 50, 'Dates' 200, 'Elderberries' 100.

Write a SQL query that selects product, amount, and a new column percent_of_total which shows the sales amount as a percentage of the total sales.

Use a window function with SUM(amount) OVER () to calculate the total sales.

Format the percent_of_total as a decimal number between 0 and 100 with two decimal places.

💡 Why This Matters

🌍 Real World

Retail and sales databases often need to show how each product contributes to total sales. This helps managers understand product performance.

💼 Career

Knowing how to use window functions to calculate percentages and totals is a key skill for data analysts and database developers.

1

Create the sales table and insert data

Create a table called sales with columns product as text and amount as integer. Then insert these exact rows: ('Apples', 100), ('Bananas', 150), ('Cherries', 50), ('Dates', 200), ('Elderberries', 100).

SQL

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

Need a hint?

Use CREATE TABLE to make the table and INSERT INTO to add rows.

2

Add a query to select product and amount

Write a SQL SELECT statement to get the product and amount columns from the sales table.

SQL

CREATE TABLE sales (
    product TEXT,
    amount INTEGER
);

INSERT INTO sales (product, amount) VALUES
('Apples', 100),
('Bananas', 150),
('Cherries', 50),
('Dates', 200),
('Elderberries', 100);

-- Select product and amount from sales
-- Your code here

Need a hint?

Use SELECT product, amount FROM sales; to get the data.

3

Calculate total sales using a window function

Modify the SELECT statement to add a new column called total_sales that uses the window function SUM(amount) OVER () to calculate the total sales amount for all products.

SQL

CREATE TABLE sales (
    product TEXT,
    amount INTEGER
);

INSERT INTO sales (product, amount) VALUES
('Apples', 100),
('Bananas', 150),
('Cherries', 50),
('Dates', 200),
('Elderberries', 100);

-- Select product, amount, and total sales using window function
-- Your code here

Need a hint?

Use SUM(amount) OVER () AS total_sales to get the total sales for all rows.

4

Calculate percent of total sales

Update the SELECT statement to add a new column called percent_of_total. Calculate it by dividing amount by SUM(amount) OVER () and multiplying by 100. Use ROUND(..., 2) to round the result to two decimal places.

SQL

CREATE TABLE sales (
    product TEXT,
    amount INTEGER
);

INSERT INTO sales (product, amount) VALUES
('Apples', 100),
('Bananas', 150),
('Cherries', 50),
('Dates', 200),
('Elderberries', 100);

-- Select product, amount, and percent of total sales
-- Your code here

Need a hint?

Calculate percent by dividing amount by total sales and multiply by 100. Use ROUND(..., 2) to keep two decimals.