CTE as readable subquery replacement in SQL - Mini Project: Build & Apply

Using CTE as a Readable Subquery Replacement

📖 Scenario: You work for a small bookstore that keeps track of book sales and authors in a database. You want to find the total sales for each author in a clear and readable way.

🎯 Goal: Build a SQL query using a Common Table Expression (CTE) to calculate total sales per author, replacing a subquery for better readability.

📋 What You'll Learn

Create a CTE named AuthorSales that sums sales per author from the sales table.

Use the CTE to select each author's name and their total sales.

Order the results by total sales in descending order.

💡 Why This Matters

🌍 Real World

CTEs help make complex queries easier to read and maintain, especially when working with aggregated data like sales summaries.

💼 Career

Database developers and analysts often use CTEs to organize queries for reports and data analysis in business environments.

1

Create the authors and sales tables

Write SQL statements to create two tables: authors with columns author_id (integer, primary key) and author_name (text), and sales with columns sale_id (integer, primary key), author_id (integer), and amount (integer).

SQL

-- Your code here to create authors and sales tables

Need a hint?

Use CREATE TABLE statements with the specified columns and data types.

2

Insert sample data into authors and sales

Insert these exact rows into authors: (1, 'Alice'), (2, 'Bob'), (3, 'Charlie'). Insert these exact rows into sales: (1, 1, 100), (2, 2, 150), (3, 1, 200), (4, 3, 50).

SQL

CREATE TABLE authors (
    author_id INTEGER PRIMARY KEY,
    author_name TEXT
);

CREATE TABLE sales (
    sale_id INTEGER PRIMARY KEY,
    author_id INTEGER,
    amount INTEGER
);
-- Your code here to insert data into authors and sales

Need a hint?

Use INSERT INTO with multiple rows for both tables.

3

Write a CTE named AuthorSales to sum sales per author

Write a SQL query that starts with WITH AuthorSales AS and defines a CTE that selects author_id and the sum of amount as total_sales from sales, grouped by author_id.

SQL

CREATE TABLE authors (
    author_id INTEGER PRIMARY KEY,
    author_name TEXT
);

CREATE TABLE sales (
    sale_id INTEGER PRIMARY KEY,
    author_id INTEGER,
    amount INTEGER
);

INSERT INTO authors (author_id, author_name) VALUES
(1, 'Alice'),
(2, 'Bob'),
(3, 'Charlie');

INSERT INTO sales (sale_id, author_id, amount) VALUES
(1, 1, 100),
(2, 2, 150),
(3, 1, 200),
(4, 3, 50);

WITH AuthorSales AS (
    -- Your code here to sum sales per author
)

Need a hint?

Use WITH AuthorSales AS (SELECT author_id, SUM(amount) AS total_sales FROM sales GROUP BY author_id).

4

Select author names and total sales using the CTE

Write a SQL query that selects author_name and total_sales by joining authors with the AuthorSales CTE on author_id. Order the results by total_sales in descending order.

SQL

CREATE TABLE authors (
    author_id INTEGER PRIMARY KEY,
    author_name TEXT
);

CREATE TABLE sales (
    sale_id INTEGER PRIMARY KEY,
    author_id INTEGER,
    amount INTEGER
);

INSERT INTO authors (author_id, author_name) VALUES
(1, 'Alice'),
(2, 'Bob'),
(3, 'Charlie');

INSERT INTO sales (sale_id, author_id, amount) VALUES
(1, 1, 100),
(2, 2, 150),
(3, 1, 200),
(4, 3, 50);

WITH AuthorSales AS (
    SELECT author_id, SUM(amount) AS total_sales
    FROM sales
    GROUP BY author_id
)
-- Your code here to select author_name and total_sales joining authors and AuthorSales

Need a hint?

Join authors with AuthorSales on author_id and order by total_sales descending.