Window function vs GROUP BY mental model in SQL - Hands-On Comparison

Understanding Window Functions vs GROUP BY in SQL

📖 Scenario: You work at a bookstore that tracks sales data. You want to analyze sales by each book and also see the total sales across all books. You will learn how to use GROUP BY to get totals per book and how to use window functions to get totals alongside each row.

🎯 Goal: Build SQL queries that first group sales by book using GROUP BY, then use a window function to show total sales per book and overall total sales in the same result.

📋 What You'll Learn

Create a table called sales with columns book (text) and copies_sold (integer) with given data

Write a GROUP BY query to get total copies sold per book

Add a variable to hold the overall total copies sold

Write a query using a window function SUM() OVER() to show total copies sold per book and overall total on each row

💡 Why This Matters

🌍 Real World

Bookstores and many businesses analyze sales data to understand product performance and overall revenue.

💼 Career

Knowing when to use GROUP BY versus window functions is key for data analysts and database developers to write efficient and insightful queries.

1

Create the sales table with data

Create a table called sales with columns book (text) and copies_sold (integer). Insert these rows exactly: ('Book A', 10), ('Book B', 5), ('Book A', 7), ('Book C', 3), ('Book B', 8).

SQL

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

Need a hint?

Use CREATE TABLE to define columns, then INSERT INTO with multiple rows.

2

Write a GROUP BY query to get total copies sold per book

Write a SQL query that selects book and the sum of copies_sold as total_copies from sales, grouping by book.

SQL

CREATE TABLE sales (book TEXT, copies_sold INTEGER);
INSERT INTO sales (book, copies_sold) VALUES
('Book A', 10),
('Book B', 5),
('Book A', 7),
('Book C', 3),
('Book B', 8);

-- Write GROUP BY query below
-- Your code here

Need a hint?

Use SUM() to add copies sold and GROUP BY book to group rows by book.

3

Calculate overall total copies sold

Create a variable called overall_total that holds the total copies sold across all books by summing copies_sold from sales.

SQL

CREATE TABLE sales (book TEXT, copies_sold INTEGER);
INSERT INTO sales (book, copies_sold) VALUES
('Book A', 10),
('Book B', 5),
('Book A', 7),
('Book C', 3),
('Book B', 8);

SELECT book, SUM(copies_sold) AS total_copies
FROM sales
GROUP BY book;

-- Create overall_total variable below
-- Your code here

Need a hint?

Use a WITH clause or a subquery to calculate the overall total.

4

Use a window function to show total copies per book and overall total

Write a SQL query that selects book, copies_sold, the sum of copies_sold over partition by book as total_per_book, and the sum of copies_sold over all rows as overall_total from sales.

SQL

CREATE TABLE sales (book TEXT, copies_sold INTEGER);
INSERT INTO sales (book, copies_sold) VALUES
('Book A', 10),
('Book B', 5),
('Book A', 7),
('Book C', 3),
('Book B', 8);

SELECT book, SUM(copies_sold) AS total_copies
FROM sales
GROUP BY book;

WITH overall AS (
  SELECT SUM(copies_sold) AS overall_total FROM sales
)
SELECT overall_total FROM overall;

-- Write window function query below
-- Your code here

Need a hint?

Use SUM() OVER (PARTITION BY book) for total per book and SUM() OVER () for overall total.