E-Commerce Analytics with MySQL

Design a normalized database and write advanced SQL queries with window functions.

Introduction

30 Second Summary

Every time you browse an online store, your clicks, purchases, and wishlists generate mountains of data. Businesses rely on analysts who can slice through that data to answer questions like "Who are our top customers?" and "Which products are trending this month?"

In this project, you will build an e-commerce analytics database from scratch, write progressively complex SQL queries to answer real business questions, and prove your optimization skills by measuring query performance before and after adding indexes.

What You'll Build

A fully functional e-commerce analytics database in MySQL with sample data, advanced analytical queries, and measurable performance optimizations.

By the end of this project, you'll have:

  • A normalized 4-table e-commerce schema with customers, products, orders, and order items demonstrating proper relational architecture.
  • A collection of analytical queries using subqueries, window functions (RANK, DENSE_RANK, ROW_NUMBER), and CTEs that answer real business questions.
  • Measurable proof of query optimization by comparing EXPLAIN output before and after adding strategic indexes.
  • Secret Mission: Build a recursive CTE that generates a monthly sales trend report with running totals and month-over-month growth percentages.

Are there any prerequisites?

You should be comfortable with basic SQL concepts like CREATE TABLE, INSERT, SELECT with WHERE, and JOINs. This project builds on those foundations to teach intermediate and advanced techniques.

Install MySQL and Set Up Your Environment

To build an analytics database, you need a database server running on your machine. MySQL is one of the most widely used relational databases in production environments.

In this step, you will install MySQL 8.0 using Homebrew, start the server, and create a dedicated database for your analytics project.

In this step, get ready to:
  • Install MySQL 8.0 and start the database server.
  • Connect to MySQL and verify the version.
  • Create and select the project database.
Install MySQL 8.0 via Homebrew and start the server

MySQL 8.0 introduced window functions, CTEs, and other advanced features you will use throughout this project. You will install it through Homebrew, the macOS package manager.

  • Open your terminal by pressing Cmd+Space to open Spotlight, then typing Terminal and pressing Enter.
  • Check if MySQL is already installed by running this command:
mysql --version

✔️ I see version 8.0.x

MySQL 8.0 is already on your machine. Make sure the server is running by executing:

brew services start mysql@8.0

Skip ahead to the "Connect and verify" instructions below the tabs.

ⓧ I see a different version number

You have a different MySQL version installed (likely 9.x from the default Homebrew formula). You need MySQL 8.0 specifically because earlier versions lack window functions and CTEs.

  • Stop the existing MySQL service by running:
brew services stop mysql
  • Install MySQL 8.0 alongside your existing version by running:
brew install mysql@8.0
  • Add MySQL 8.0 to your PATH so your terminal finds the correct version:
export PATH="$(brew --prefix mysql@8.0)/bin:$PATH"
  • Start the MySQL 8.0 server by running:
brew services start mysql@8.0

Continue with the "Connect and verify" instructions below.

ⓧ Command not found

MySQL is not installed yet. You will install it fresh through Homebrew.

  • Install MySQL 8.0 by running this command:
brew install mysql@8.0

This may take a minute or two as Homebrew downloads dependencies.

What does this command do?

Homebrew downloads and installs the MySQL 8.0.46 database server and client tools. This version includes all the advanced SQL features you need for this project.

Because mysql@8.0 is a keg-only formula, Homebrew does not add its binaries to your PATH automatically. You need to tell your terminal where to find the mysql command.

  • Add MySQL 8.0 to your PATH for this terminal session by running:
export PATH="$(brew --prefix mysql@8.0)/bin:$PATH"

Why do I need to set the PATH?

Homebrew installs versioned formulas in an isolated location so they do not conflict with other software. The export PATH command tells your terminal to look in the MySQL 8.0 bin folder first when you type mysql.

This setting lasts only for the current terminal session. If you close and reopen your terminal later, you will need to run it again.

  • Start the MySQL server by running:
brew services start mysql@8.0

Continue with the "Connect and verify" instructions below.

Homebrew not found?

If you see "command not found: brew", you need to install Homebrew first. Visit brew.sh and follow the one-line install command, then return here.

help me install Homebrew on my Mac

  • Connect to the MySQL server by running:
mysql -u root

You should see a welcome message and a mysql> prompt. This means you are inside the MySQL client and can run SQL commands.

  • Verify the version supports window functions by running this SQL command:
SELECT VERSION();

The output should show 8.0.46 or another 8.0.x number. This confirms window functions and CTEs are available.

Still cannot connect?

Make sure you ran the export PATH command in the same terminal window where you are running mysql -u root. Opening a new terminal tab resets the PATH.

If you see "Can't connect to local MySQL server", wait a few seconds after starting the service, then try again. The server needs a moment to initialize.

help me troubleshoot my MySQL connection

Create the project database

Now that you are connected to MySQL, you will create a dedicated database to hold all the tables for this project. Keeping project data in its own database prevents it from mixing with anything else on your server.

  • Create the database by running this SQL command at the mysql> prompt:
CREATE DATABASE ecommerce_analytics;
  • Switch into the new database by running:
USE ecommerce_analytics;
  • Confirm you are using the correct database by running:
SELECT DATABASE();

You should see ecommerce_analytics in the output. This confirms all your future SQL commands will run against this database.

What did these commands do?

CREATE DATABASE creates a new, empty database on your MySQL server. Think of it as creating a new folder to organize related tables.

USE tells MySQL which database to work with for the rest of your session. Without it, MySQL would not know where to create your tables.

Seeing an error when creating the database?

If you see a message that the database already exists, that is fine. Run USE ecommerce_analytics; to select it and continue.

If you see a permission error, make sure you connected with mysql -u root (not a different user).

help me fix my CREATE DATABASE error

Your database server is running and your project database is ready. Next up, you will design a normalized schema with four tables that model a real e-commerce system.

Design and Build the Normalized Schema

Your MySQL server is running and the ecommerce_analytics database is ready. Now you need a structure to store actual data in.

A normalized database splits data into focused tables that reference each other through relationships. Instead of one giant spreadsheet with repeated customer names on every order row, you store each customer once and link orders to them by ID.

In this step, get ready to:
  • Create a customers table with data types and constraints.
  • Create a products table for your store inventory.
  • Create orders and order_items tables linked by foreign keys.
Create the customers table with proper data types and constraints

Every table needs columns with specific data types that match the kind of information being stored. Constraints like NOT NULL and UNIQUE prevent bad data from sneaking in at the database level.

  • In your MySQL client, create the customers table by running this SQL:
-- Customers table
CREATE TABLE customers (
    customer_id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    city VARCHAR(50),
    registered_at DATE NOT NULL
);

What does this code do?

This creates a table where each row represents one customer in your store.

  • AUTO_INCREMENT PRIMARY KEY gives each customer a unique ID that MySQL assigns automatically.
  • VARCHAR(50) stores text up to 50 characters. The number sets the maximum length.
  • UNIQUE NOT NULL on email means every customer must have an email address, and no two customers can share the same one.
  • city has no NOT NULL constraint, so it is optional. A customer can exist without a city on record.
  • You should see Query OK, 0 rows affected in your terminal. This confirms the table was created successfully.

Seeing an error?

  • If you see No database selected, run USE ecommerce_analytics; first.
  • If you see Table already exists, you may have run the statement twice. That is fine. The table is already there.

Help me debug my CREATE TABLE error.

Create the products table

The products table stores your store's inventory. Each product belongs to a category and has a fixed price.

  • Create the products table by running:
-- Products table
CREATE TABLE products (
    product_id INT AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    category VARCHAR(50) NOT NULL,
    price DECIMAL(10,2) NOT NULL
);

What does this code do?

  • DECIMAL(10,2) stores precise monetary values. The 10 is the total number of digits and 2 is the number of decimal places. This avoids the rounding errors you get with floating-point types.
  • category is a simple text field. In a production system you might normalize this into its own table, but for analytics practice this keeps things focused.
  • Confirm you see Query OK, 0 rows affected in your terminal.
Create the orders and order_items tables with foreign key relationships

The orders and order_items tables are where relationships come in. A foreign key is a column in one table that references the primary key of another table. It ensures you cannot create an order for a customer that does not exist.

  • Create the orders table by running:
-- Orders table
CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date DATE NOT NULL,
    status VARCHAR(20) DEFAULT 'completed',
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

What does this code do?

  • FOREIGN KEY (customer_id) REFERENCES customers(customer_id) links each order to a specific customer. MySQL will reject any INSERT that tries to use a customer_id that does not exist in the customers table.
  • DEFAULT 'completed' means if you insert an order without specifying a status, MySQL fills in completed automatically.
  • Confirm you see Query OK, 0 rows affected, then create the order_items table by running:
-- Order items table
CREATE TABLE order_items (
    item_id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (order_id) REFERENCES orders(order_id),
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

Why does order_items have its own unit_price?

You might wonder why unit_price exists here when the products table already has a price. Prices change over time. Storing the price at the moment of purchase means your historical analytics stay accurate even if the product price updates later.

This table has two foreign keys. One links to the order it belongs to, and the other links to the product that was purchased.

  • Verify all four tables exist by running:
SHOW TABLES;
  • You should see all four tables listed: customers, orders, order_items, and products.
  • Inspect the structure of your most complex table by running:
DESCRIBE order_items;
  • You should see five columns listed with their types, plus a Key column showing PRI for the primary key and MUL for the foreign keys.

What does MUL mean?

MUL stands for "multiple." It means the column is the first column of an index where multiple rows can have the same value. MySQL automatically creates an index on foreign key columns so it can efficiently look up related records.

Don't see all four tables?

  • If a table is missing, check that you ran all four CREATE TABLE statements. You can re-run any that failed.
  • If you see an error about a foreign key constraint, make sure you created the tables in order. The orders table must come after customers, and order_items must come after both orders and products.

Help me fix a foreign key error when creating my tables.

✔️ Awesome, I've got everything!

Great. Double check you see all four tables in your SHOW TABLES; output before moving on.

ⓧ I'd like to double check the full code

Here is the complete schema. You can save this as schema.sql for reference.

-- Create database
CREATE DATABASE IF NOT EXISTS ecommerce_analytics;
USE ecommerce_analytics;

-- Customers table
CREATE TABLE customers (
    customer_id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    city VARCHAR(50),
    registered_at DATE NOT NULL
);

-- Products table
CREATE TABLE products (
    product_id INT AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    category VARCHAR(50) NOT NULL,
    price DECIMAL(10,2) NOT NULL
);

-- Orders table
CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date DATE NOT NULL,
    status VARCHAR(20) DEFAULT 'completed',
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

-- Order items table
CREATE TABLE order_items (
    item_id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (order_id) REFERENCES orders(order_id),
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

Why normalize instead of using one big table?

With normalization, updating a customer's email means changing one row in the customers table. Without it, you would need to find and update that email in every single order row. Fewer duplicates means fewer inconsistencies and faster updates.

Your database skeleton is in place. Next up, you will fill these tables with sample data and start writing analytical queries against them.

Populate Data and Write Subqueries

Your schema is built and the relationships between tables are locked in with foreign keys. Empty tables don't answer business questions though.

In this step, you'll populate your database with realistic e-commerce data, then write three types of subqueries to extract insights. Subqueries are queries nested inside other queries, and they're one of the most powerful tools for answering multi-layered business questions.

In this step, get ready to:
  • Populate all four tables with sample e-commerce data using multi-row INSERT statements.
  • Write scalar, correlated, and derived table subqueries that answer real business questions.
Insert sample data into all four tables

Multi-row INSERT statements let you add many records in a single command instead of writing a separate INSERT for each row. This is more efficient and easier to read.

  • In your text editor, create a new file called data.sql in the same location as your schema.sql file.
  • Paste the following INSERT statements for your customers and products tables:
USE ecommerce_analytics;

-- Insert customers
INSERT INTO customers (first_name, last_name, email, city, registered_at) VALUES
('Alice', 'Johnson', 'alice@email.com', 'New York', '2024-01-15'),
('Bob', 'Smith', 'bob@email.com', 'Los Angeles', '2024-02-20'),
('Carol', 'Davis', 'carol@email.com', 'Chicago', '2024-01-10'),
('David', 'Wilson', 'david@email.com', 'Houston', '2024-03-05'),
('Eva', 'Brown', 'eva@email.com', 'Phoenix', '2024-02-28'),
('Frank', 'Taylor', 'frank@email.com', 'New York', '2024-04-12'),
('Grace', 'Anderson', 'grace@email.com', 'Chicago', '2024-03-20'),
('Henry', 'Thomas', 'henry@email.com', 'Los Angeles', '2024-05-01'),
('Ivy', 'Martinez', 'ivy@email.com', 'Houston', '2024-04-18'),
('Jack', 'Garcia', 'jack@email.com', 'Phoenix', '2024-06-01');

-- Insert products (3 categories: Electronics, Clothing, Books)
INSERT INTO products (product_name, category, price) VALUES
('Wireless Headphones', 'Electronics', 79.99),
('Smartphone Case', 'Electronics', 29.99),
('USB-C Cable', 'Electronics', 14.99),
('Winter Jacket', 'Clothing', 129.99),
('Running Shoes', 'Clothing', 89.99),
('Cotton T-Shirt', 'Clothing', 24.99),
('SQL Cookbook', 'Books', 39.99),
('Data Science Handbook', 'Books', 49.99);

What does this code do?

The USE statement ensures all statements target the correct database.

Each multi-row INSERT adds all records in one command. The 10 customers span 5 cities, and the 8 products are spread across 3 categories (Electronics, Clothing, Books) with prices ranging from $14.99 to $129.99.

  • Now add the orders and order items below the products INSERT in the same file:
-- Insert orders
INSERT INTO orders (customer_id, order_date, status) VALUES
(1, '2024-03-01', 'completed'),
(1, '2024-04-15', 'completed'),
(2, '2024-03-10', 'completed'),
(3, '2024-03-20', 'completed'),
(3, '2024-05-01', 'completed'),
(4, '2024-04-05', 'completed'),
(5, '2024-04-20', 'completed'),
(5, '2024-06-10', 'completed'),
(6, '2024-05-15', 'completed'),
(7, '2024-05-25', 'completed'),
(7, '2024-06-20', 'completed'),
(8, '2024-06-01', 'completed'),
(9, '2024-06-15', 'completed'),
(10, '2024-07-01', 'completed'),
(1, '2024-07-10', 'completed');

-- Insert order items
INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES
(1, 1, 1, 79.99),
(1, 7, 2, 39.99),
(2, 4, 1, 129.99),
(2, 3, 3, 14.99),
(3, 2, 2, 29.99),
(3, 6, 1, 24.99),
(4, 1, 1, 79.99),
(4, 5, 1, 89.99),
(5, 8, 1, 49.99),
(5, 3, 2, 14.99),
(6, 4, 1, 129.99),
(6, 7, 1, 39.99),
(7, 5, 2, 89.99),
(7, 6, 3, 24.99),
(8, 1, 1, 79.99),
(8, 8, 2, 49.99),
(9, 2, 1, 29.99),
(9, 4, 1, 129.99),
(10, 7, 1, 39.99),
(10, 3, 1, 14.99),
(11, 5, 1, 89.99),
(11, 1, 1, 79.99),
(12, 8, 1, 49.99),
(12, 6, 2, 24.99),
(13, 4, 1, 129.99),
(13, 2, 1, 29.99),
(14, 1, 2, 79.99),
(14, 7, 1, 39.99),
(15, 5, 1, 89.99),
(15, 8, 1, 49.99);

What's happening here?

The 15 orders link back to customers via customer_id. Some customers have multiple orders (Alice has 3, Carol has 2, Eva has 2).

The 30 order items connect orders to products via order_id and product_id. Each item records the quantity purchased and the unit price at the time of purchase.

  • Save data.sql.
  • Switch back to your MySQL client and run the INSERT statements by pasting the entire contents of data.sql into the terminal.
  • Verify the data loaded correctly by running:
SELECT 'customers' AS table_name, COUNT(*) AS row_count FROM customers
UNION ALL
SELECT 'products', COUNT(*) FROM products
UNION ALL
SELECT 'orders', COUNT(*) FROM orders
UNION ALL
SELECT 'order_items', COUNT(*) FROM order_items;

You should see 10 customers, 8 products, 15 orders, and 30 order_items.

Seeing a foreign key error?

If you get an error like Cannot add or update a child row: a foreign key constraint fails, the INSERT statements ran out of order. Customers and products must be inserted before orders, and orders before order_items.

Run DELETE FROM order_items; then DELETE FROM orders; to clear partial data, then paste the statements again starting from the customers INSERT.

Help me debug my foreign key constraint error.

✔️ Awesome, I've got everything!

Great. Make sure data.sql is saved with all four INSERT blocks.

ⓧ I'd like to double check the full code

USE ecommerce_analytics;

-- Insert customers
INSERT INTO customers (first_name, last_name, email, city, registered_at) VALUES
('Alice', 'Johnson', 'alice@email.com', 'New York', '2024-01-15'),
('Bob', 'Smith', 'bob@email.com', 'Los Angeles', '2024-02-20'),
('Carol', 'Davis', 'carol@email.com', 'Chicago', '2024-01-10'),
('David', 'Wilson', 'david@email.com', 'Houston', '2024-03-05'),
('Eva', 'Brown', 'eva@email.com', 'Phoenix', '2024-02-28'),
('Frank', 'Taylor', 'frank@email.com', 'New York', '2024-04-12'),
('Grace', 'Anderson', 'grace@email.com', 'Chicago', '2024-03-20'),
('Henry', 'Thomas', 'henry@email.com', 'Los Angeles', '2024-05-01'),
('Ivy', 'Martinez', 'ivy@email.com', 'Houston', '2024-04-18'),
('Jack', 'Garcia', 'jack@email.com', 'Phoenix', '2024-06-01');

-- Insert products (3 categories: Electronics, Clothing, Books)
INSERT INTO products (product_name, category, price) VALUES
('Wireless Headphones', 'Electronics', 79.99),
('Smartphone Case', 'Electronics', 29.99),
('USB-C Cable', 'Electronics', 14.99),
('Winter Jacket', 'Clothing', 129.99),
('Running Shoes', 'Clothing', 89.99),
('Cotton T-Shirt', 'Clothing', 24.99),
('SQL Cookbook', 'Books', 39.99),
('Data Science Handbook', 'Books', 49.99);

-- Insert orders
INSERT INTO orders (customer_id, order_date, status) VALUES
(1, '2024-03-01', 'completed'),
(1, '2024-04-15', 'completed'),
(2, '2024-03-10', 'completed'),
(3, '2024-03-20', 'completed'),
(3, '2024-05-01', 'completed'),
(4, '2024-04-05', 'completed'),
(5, '2024-04-20', 'completed'),
(5, '2024-06-10', 'completed'),
(6, '2024-05-15', 'completed'),
(7, '2024-05-25', 'completed'),
(7, '2024-06-20', 'completed'),
(8, '2024-06-01', 'completed'),
(9, '2024-06-15', 'completed'),
(10, '2024-07-01', 'completed'),
(1, '2024-07-10', 'completed');

-- Insert order items
INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES
(1, 1, 1, 79.99),
(1, 7, 2, 39.99),
(2, 4, 1, 129.99),
(2, 3, 3, 14.99),
(3, 2, 2, 29.99),
(3, 6, 1, 24.99),
(4, 1, 1, 79.99),
(4, 5, 1, 89.99),
(5, 8, 1, 49.99),
(5, 3, 2, 14.99),
(6, 4, 1, 129.99),
(6, 7, 1, 39.99),
(7, 5, 2, 89.99),
(7, 6, 3, 24.99),
(8, 1, 1, 79.99),
(8, 8, 2, 49.99),
(9, 2, 1, 29.99),
(9, 4, 1, 129.99),
(10, 7, 1, 39.99),
(10, 3, 1, 14.99),
(11, 5, 1, 89.99),
(11, 1, 1, 79.99),
(12, 8, 1, 49.99),
(12, 6, 2, 24.99),
(13, 4, 1, 129.99),
(13, 2, 1, 29.99),
(14, 1, 2, 79.99),
(14, 7, 1, 39.99),
(15, 5, 1, 89.99),
(15, 8, 1, 49.99);
Write scalar and correlated subqueries to answer business questions

A subquery is a SELECT statement embedded inside another query. It lets you answer layered questions like "which products are priced above average?" where you need to calculate the average first, then filter by it.

  • In your text editor, create a new file called queries.sql in the same location as your other SQL files.
  • Add the following scalar subquery that finds all products priced above the average price:
USE ecommerce_analytics;

-- ============================================
-- SUBQUERIES
-- ============================================

-- Scalar subquery: Products priced above average
SELECT product_name, category, price
FROM products
WHERE price > (SELECT AVG(price) FROM products);

What does this code do?

The inner query (SELECT AVG(price) FROM products) calculates the average product price across all 8 products. MySQL resolves this to a single number first.

The outer query then filters products where price exceeds that number. This is called a scalar subquery because it returns exactly one value.

  • Save queries.sql.
  • Copy the SELECT statement (not the USE or comment headers) and paste it into your MySQL client to run it.

You should see 3 products returned: Wireless Headphones, Winter Jacket, and Running Shoes.

Getting 0 rows returned?

Make sure you inserted the products data first. Run SELECT COUNT(*) FROM products; to confirm you have 8 rows.

Why is my scalar subquery returning no results?

  • Now add the correlated subquery below your scalar subquery in queries.sql. This finds customers who spent more than the overall average customer spending:
-- Correlated subquery: Customers who spent more than average customer spending
SELECT c.first_name, c.last_name,
    (SELECT SUM(oi.quantity * oi.unit_price)
     FROM orders o
     JOIN order_items oi ON o.order_id = oi.order_id
     WHERE o.customer_id = c.customer_id) AS total_spent
FROM customers c
WHERE (SELECT SUM(oi.quantity * oi.unit_price)
       FROM orders o
       JOIN order_items oi ON o.order_id = oi.order_id
       WHERE o.customer_id = c.customer_id) >
      (SELECT AVG(customer_total) FROM
        (SELECT SUM(oi.quantity * oi.unit_price) AS customer_total
         FROM orders o
         JOIN order_items oi ON o.order_id = oi.order_id
         GROUP BY o.customer_id) AS avg_calc);

What does this code do?

A correlated subquery references the outer query's row (c.customer_id) inside the inner query. This means the inner query re-executes for each customer row.

The SELECT list uses a subquery to calculate each customer's total spending. The WHERE clause compares that total against the average customer spending (calculated with a derived table subquery inside the comparison).

  • Save queries.sql.
  • Copy this SELECT statement and paste it into your MySQL client to run it.

You should see a few customer names with their total spending amounts. Only customers whose spending exceeds the group average appear.

Correlated vs scalar subqueries

The scalar subquery runs once and produces a single value. The correlated subquery runs once per row in the outer query because it depends on the current row's data. Correlated subqueries are more powerful but can be slower on large datasets.

  • Add the final subquery to queries.sql. This derived table subquery finds the top 5 spenders:
-- Derived table subquery: Top spenders
SELECT c.first_name, c.last_name, spending.total_spent
FROM customers c
JOIN (
    SELECT o.customer_id, SUM(oi.quantity * oi.unit_price) AS total_spent
    FROM orders o
    JOIN order_items oi ON o.order_id = oi.order_id
    GROUP BY o.customer_id
) AS spending ON c.customer_id = spending.customer_id
ORDER BY spending.total_spent DESC
LIMIT 5;

What does this code do?

The subquery in the FROM clause creates a temporary "table" (called a derived table) that calculates each customer's total spending. You give it an alias (spending) so the outer query can reference its columns.

The outer query JOINs this derived table back to customers to get the names, then sorts by total spending and limits to the top 5.

  • Save queries.sql.
  • Copy this SELECT statement and paste it into your MySQL client to run it.

You should see 5 rows showing customer names and their total spending, ordered from highest to lowest.

Seeing NULL values in total_spent?

NULL appears when a customer has no matching orders. Confirm your orders data loaded by running SELECT COUNT(*) FROM orders; (should show 15).

Also check that order_items has 30 rows. If either table is empty, re-paste the INSERT statements from data.sql.

Help me debug NULL values in my derived table subquery.

✔️ Awesome, I've got everything!

Great. Make sure queries.sql is saved with all three subqueries.

ⓧ I'd like to double check the full code

USE ecommerce_analytics;

-- ============================================
-- SUBQUERIES
-- ============================================

-- Scalar subquery: Products priced above average
SELECT product_name, category, price
FROM products
WHERE price > (SELECT AVG(price) FROM products);

-- Correlated subquery: Customers who spent more than average customer spending
SELECT c.first_name, c.last_name,
    (SELECT SUM(oi.quantity * oi.unit_price)
     FROM orders o
     JOIN order_items oi ON o.order_id = oi.order_id
     WHERE o.customer_id = c.customer_id) AS total_spent
FROM customers c
WHERE (SELECT SUM(oi.quantity * oi.unit_price)
       FROM orders o
       JOIN order_items oi ON o.order_id = oi.order_id
       WHERE o.customer_id = c.customer_id) >
      (SELECT AVG(customer_total) FROM
        (SELECT SUM(oi.quantity * oi.unit_price) AS customer_total
         FROM orders o
         JOIN order_items oi ON o.order_id = oi.order_id
         GROUP BY o.customer_id) AS avg_calc);

-- Derived table subquery: Top spenders
SELECT c.first_name, c.last_name, spending.total_spent
FROM customers c
JOIN (
    SELECT o.customer_id, SUM(oi.quantity * oi.unit_price) AS total_spent
    FROM orders o
    JOIN order_items oi ON o.order_id = oi.order_id
    GROUP BY o.customer_id
) AS spending ON c.customer_id = spending.customer_id
ORDER BY spending.total_sent DESC
LIMIT 5;

You now have a populated database and three working subqueries that answer real business questions. Next up, you'll use window functions to rank products and calculate running totals without the limitations of GROUP BY.

Use Window Functions for Ranking and Analytics

Your subqueries from the previous step answered targeted questions about individual products and customers. But what if you want to rank every product within its category, or track how a customer's spending accumulates over time?

Window functions let you compute across a set of rows related to the current row without collapsing them into a single result. You keep every row in your output while adding calculated columns like ranks, running totals, and comparisons to previous rows.

In this step, get ready to:
  • Rank products by revenue within each category using ROW_NUMBER, RANK, and DENSE_RANK.
  • Calculate running totals and compare orders over time using SUM() OVER and LAG().
Rank products by revenue within each category

A common business question is "which products perform best in each category?" Window functions answer this by assigning a rank to each row within a partition of your data.

MySQL 8.0 provides three ranking functions that each handle ties differently. You will run all three in a single query to compare their behavior side by side.

  • Add a window functions section header to queries.sql by pasting this comment block below your existing subqueries:
-- ============================================
-- WINDOW FUNCTIONS
-- ============================================
  • Add the ranking query below the section header in queries.sql:
-- RANK, DENSE_RANK, ROW_NUMBER: Products ranked by revenue within category
SELECT
    p.product_name,
    p.category,
    SUM(oi.quantity * oi.unit_price) AS revenue,
    ROW_NUMBER() OVER (PARTITION BY p.category ORDER BY SUM(oi.quantity * oi.unit_price) DESC) AS row_num,
    RANK() OVER (PARTITION BY p.category ORDER BY SUM(oi.quantity * oi.unit_price) DESC) AS rank_val,
    DENSE_RANK() OVER (PARTITION BY p.category ORDER BY SUM(oi.quantity * oi.unit_price) DESC) AS dense_rank_val
FROM products p
JOIN order_items oi ON p.product_id = oi.product_id
GROUP BY p.product_id, p.product_name, p.category;

What does this query do?

This query calculates total revenue per product, then applies three ranking functions within each product category.

  • PARTITION BY p.category restarts the ranking for each category. Electronics products are ranked separately from Clothing and Books.
  • ORDER BY SUM(...) DESC determines the ranking order. Highest revenue gets rank 1.
  • The GROUP BY aggregates order items into product-level revenue totals before the window functions apply.
  • Copy the SELECT query (without the comment) and paste it into your MySQL client to run it.

You should see a table with 8 rows (one per product), showing each product's revenue and three ranking columns.

How do ROW_NUMBER, RANK, and DENSE_RANK differ?

With your results on screen, compare the three columns. The difference shows when two products have the same revenue (a tie).

  • ROW_NUMBER() always assigns unique consecutive numbers. Ties get arbitrary ordering.
  • RANK() gives ties the same rank but skips the next number. Two products at rank 1 means the next gets rank 3.
  • DENSE_RANK() gives ties the same rank without skipping. Two products at rank 1 means the next gets rank 2.

Seeing an error about OVER clause?

  • Make sure your OVER keyword comes immediately after the function call with no comma in between.
  • Check that your MySQL version is 8.0 or higher. Window functions are not available in MySQL 5.7.
  • Verify the GROUP BY includes all three columns: p.product_id, p.product_name, p.category.

Help me debug my window function query

Calculate running totals and compare orders over time

Ranking is just one use of window functions. You can also use them to calculate running totals across time-ordered rows, or compare each row to the one before it.

  • Add the cumulative spending query below your ranking query in queries.sql:
-- Running total: Cumulative spending per customer over time
SELECT
    c.first_name,
    c.last_name,
    o.order_date,
    SUM(oi.quantity * oi.unit_price) AS order_total,
    SUM(SUM(oi.quantity * oi.unit_price)) OVER (
        PARTITION BY c.customer_id
        ORDER BY o.order_date
    ) AS cumulative_spent
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY c.customer_id, c.first_name, c.last_name, o.order_id, o.order_date;

What does this query do?

  • SUM(SUM(...)) looks unusual. The inner SUM aggregates order items into an order total. The outer SUM ... OVER is the window function that accumulates those totals across rows.
  • PARTITION BY c.customer_id resets the running total for each customer.
  • ORDER BY o.order_date determines the accumulation order. Each row shows the customer's total spending up to that order date.
  • Copy and run the SELECT query in your MySQL client.

You should see rows for each customer's orders, with the cumulative_spent column growing as you read down each customer's entries.

Getting a GROUP BY error?

  • Ensure your GROUP BY includes o.order_id in addition to the customer and date columns. Each order needs its own row.
  • Check that you have both c.first_name and c.last_name in the GROUP BY. MySQL's strict mode requires all non-aggregated SELECT columns to appear in GROUP BY.

Help me fix my cumulative spending query

  • Now add the LAG() query below the cumulative spending query in queries.sql:
-- LAG: Compare each order to the customer's previous order
SELECT
    c.first_name,
    o.order_date,
    SUM(oi.quantity * oi.unit_price) AS order_total,
    LAG(SUM(oi.quantity * oi.unit_price)) OVER (
        PARTITION BY c.customer_id
        ORDER BY o.order_date
    ) AS previous_order_total
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY c.customer_id, c.first_name, o.order_id, o.order_date;

What does LAG() do?

LAG() reaches back to the previous row in the window and pulls a value from it. This lets you compare each order to the customer's prior order without a self-join.

The first order for each customer shows NULL in previous_order_total because there is no earlier row to look back at.

  • Copy and run the LAG() query in your MySQL client.

You should see each customer's orders with a previous_order_total column. Customers with multiple orders show their prior order's total alongside the current one. First orders display NULL.

  • Save queries.sql.

All previous_order_total values are NULL?

  • This is expected for customers with only one order. Look at customers like Alice (customer_id 1) who has three orders. Her second and third rows should show non-NULL values.
  • If every row is NULL, check that PARTITION BY c.customer_id is present. Without it, LAG() looks at the previous row across all customers instead of within each customer's orders.

Help me understand my LAG() query results

✔️ Awesome, I've got everything!

Great. Double check you have saved queries.sql with all three window function queries below the subqueries section.

ⓧ I'd like to double check the full code

Here is the full queries.sql file as it should look after this step:

USE ecommerce_analytics;

-- ============================================
-- SUBQUERIES
-- ============================================

-- Scalar subquery: Products priced above average
SELECT product_name, category, price
FROM products
WHERE price > (SELECT AVG(price) FROM products);

-- Correlated subquery: Customers who spent more than average customer spending
SELECT c.first_name, c.last_name,
    (SELECT SUM(oi.quantity * oi.unit_price)
     FROM orders o
     JOIN order_items oi ON o.order_id = oi.order_id
     WHERE o.customer_id = c.customer_id) AS total_spent
FROM customers c
WHERE (SELECT SUM(oi.quantity * oi.unit_price)
       FROM orders o
       JOIN order_items oi ON o.order_id = oi.order_id
       WHERE o.customer_id = c.customer_id) >
      (SELECT AVG(customer_total) FROM
        (SELECT SUM(oi.quantity * oi.unit_price) AS customer_total
         FROM orders o
         JOIN order_items oi ON o.order_id = oi.order_id
         GROUP BY o.customer_id) AS avg_calc);

-- Derived table subquery: Top spenders
SELECT c.first_name, c.last_name, spending.total_spent
FROM customers c
JOIN (
    SELECT o.customer_id, SUM(oi.quantity * oi.unit_price) AS total_spent
    FROM orders o
    JOIN order_items oi ON o.order_id = oi.order_id
    GROUP BY o.customer_id
) AS spending ON c.customer_id = spending.customer_id
ORDER BY spending.total_spent DESC
LIMIT 5;

-- ============================================
-- WINDOW FUNCTIONS
-- ============================================

-- RANK, DENSE_RANK, ROW_NUMBER: Products ranked by revenue within category
SELECT
    p.product_name,
    p.category,
    SUM(oi.quantity * oi.unit_price) AS revenue,
    ROW_NUMBER() OVER (PARTITION BY p.category ORDER BY SUM(oi.quantity * oi.unit_price) DESC) AS row_num,
    RANK() OVER (PARTITION BY p.category ORDER BY SUM(oi.quantity * oi.unit_price) DESC) AS rank_val,
    DENSE_RANK() OVER (PARTITION BY p.category ORDER BY SUM(oi.quantity * oi.unit_price) DESC) AS dense_rank_val
FROM products p
JOIN order_items oi ON p.product_id = oi.product_id
GROUP BY p.product_id, p.product_name, p.category;

-- Running total: Cumulative spending per customer over time
SELECT
    c.first_name,
    c.last_name,
    o.order_date,
    SUM(oi.quantity * oi.unit_price) AS order_total,
    SUM(SUM(oi.quantity * oi.unit_price)) OVER (
        PARTITION BY c.customer_id
        ORDER BY o.order_date
    ) AS cumulative_spent
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY c.customer_id, c.first_name, c.last_name, o.order_id, o.order_date;

-- LAG: Compare each order to the customer's previous order
SELECT
    c.first_name,
    o.order_date,
    SUM(oi.quantity * oi.unit_price) AS order_total,
    LAG(SUM(oi.quantity * oi.unit_price)) OVER (
        PARTITION BY c.customer_id
        ORDER BY o.order_date
    ) AS previous_order_total
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY c.customer_id, c.first_name, o.order_id, o.order_date;

You now have three powerful window function patterns in your toolkit: ranking, running totals, and row-to-row comparisons. Next up, you will use Common Table Expressions to chain multiple query stages together and answer even more complex business questions.

Write Common Table Expressions for Complex Analytics

Your window functions already answer powerful questions about rankings and running totals. But what happens when you need to build an answer in stages, where each stage feeds into the next?

Common Table Expressions (CTEs) let you name intermediate result sets and chain them together. Think of them as temporary named views that exist only for the duration of a single query. In this step, you will write CTEs that combine ranking logic with filtering to answer multi-layered business questions.

In this step, get ready to:
  • Write a non-recursive CTE that finds the top 3 products per category by revenue.
  • Write a multi-CTE query that identifies high-value customers who also buy premium products.
Write a non-recursive CTE to find top products per category

A real business question like "What are our top 3 products in each category?" requires two stages of logic. First, calculate each product's revenue. Then, rank them within their category and filter.

A CTE lets you define each stage as a named block using the WITH keyword. You can chain multiple CTEs together, with each one referencing the previous.

  • In your MySQL client, run this query to find the top 3 products per category by revenue:
-- Non-recursive CTE: Top 3 products per category by revenue
WITH product_revenue AS (
    SELECT
        p.product_id,
        p.product_name,
        p.category,
        SUM(oi.quantity * oi.unit_price) AS revenue
    FROM products p
    JOIN order_items oi ON p.product_id = oi.product_id
    GROUP BY p.product_id, p.product_name, p.category
),
ranked_products AS (
    SELECT
        product_name,
        category,
        revenue,
        RANK() OVER (PARTITION BY category ORDER BY revenue DESC) AS category_rank
    FROM product_revenue
)
SELECT product_name, category, revenue, category_rank
FROM ranked_products
WHERE category_rank <= 3
ORDER BY category, category_rank;

What does this query do?

This query works in three stages, each building on the last:

  • The first CTE product_revenue calculates the total revenue for every product by multiplying quantity by unit price.
  • The second CTE ranked_products applies RANK() to sort products within each category by their revenue.
  • The final SELECT filters for only products with a rank of 3 or less, giving you the top performers per category.

Why CTEs instead of nested subqueries?

You could write this as deeply nested subqueries, but CTEs read top-to-bottom like a recipe. Each named step is self-contained and reusable within the same query.

This makes debugging easier too. You can run just the first CTE to check its output before adding the ranking logic on top.

  • Verify you see a results table with columns for product_name, category, revenue, and category_rank.
  • You should see up to 3 products listed for each of the 3 categories (Books, Clothing, Electronics).
  • Add this query to your queries.sql file under a new section header for CTEs.

Getting a syntax error near WITH?

  • Make sure the previous statement ended with a semicolon. MySQL interprets WITH as part of the prior query if it is not properly terminated.
  • Check that the comma between the two CTE definitions (after the closing parenthesis of product_revenue) is present. Missing it causes a syntax error.

Help me debug my CTE syntax error in MySQL.

Write a multi-CTE query to identify high-value premium buyers

Now imagine a marketing team asks: "Which customers have spent over $200 AND bought at least one premium product (priced $100+)?" This requires two independent filters combined at the end.

  • Run this multi-CTE query in your MySQL client:
-- Multi-CTE: High-value customers who buy premium products
WITH customer_spending AS (
    SELECT
        o.customer_id,
        SUM(oi.quantity * oi.unit_price) AS total_spent
    FROM orders o
    JOIN order_items oi ON o.order_id = oi.order_id
    GROUP BY o.customer_id
    HAVING total_spent > 200
),
premium_buyers AS (
    SELECT DISTINCT o.customer_id
    FROM orders o
    JOIN order_items oi ON o.order_id = oi.order_id
    JOIN products p ON oi.product_id = p.product_id
    WHERE p.price >= 100
)
SELECT
    c.first_name,
    c.last_name,
    cs.total_spent
FROM customer_spending cs
JOIN premium_buyers pb ON cs.customer_id = pb.customer_id
JOIN customers c ON cs.customer_id = c.customer_id
ORDER BY cs.total_spent DESC;

What does this query do?

This query uses two independent CTEs that each answer a separate question:

  • The customer_spending CTE finds all customers who have spent more than $200 total. The HAVING clause filters after the GROUP BY aggregation.
  • The premium_buyers CTE finds all customers who purchased at least one product priced at $100 or more.
  • The final SELECT joins both CTEs together. Only customers who appear in BOTH sets make it to the results.
  • Verify your results return 2-4 customers with their names and total spending amounts.
  • Add this query to your queries.sql file below the previous CTE query.
  • Save queries.sql.

Query returns zero rows?

  • Check that you inserted all 30 order items from the data script. Run SELECT COUNT(*) FROM order_items; to confirm you have 30 rows.
  • Verify the HAVING total_spent > 200 threshold. If your data differs, try lowering it to 100 temporarily to check the logic works.

Help me figure out why my multi-CTE query returns empty results.

✔️ Awesome, I've got everything!

Great. Double check you have saved queries.sql with both CTE queries added.

ⓧ I'd like to double check the full code

Here is what your queries.sql should look like at this point:

USE ecommerce_analytics;

-- ============================================
-- SUBQUERIES
-- ============================================

-- Scalar subquery: Products priced above average
SELECT product_name, category, price
FROM products
WHERE price > (SELECT AVG(price) FROM products);

-- Correlated subquery: Customers who spent more than average customer spending
SELECT c.first_name, c.last_name,
    (SELECT SUM(oi.quantity * oi.unit_price)
     FROM orders o
     JOIN order_items oi ON o.order_id = oi.order_id
     WHERE o.customer_id = c.customer_id) AS total_spent
FROM customers c
WHERE (SELECT SUM(oi.quantity * oi.unit_price)
       FROM orders o
       JOIN order_items oi ON o.order_id = oi.order_id
       WHERE o.customer_id = c.customer_id) >
      (SELECT AVG(customer_total) FROM
        (SELECT SUM(oi.quantity * oi.unit_price) AS customer_total
         FROM orders o
         JOIN order_items oi ON o.order_id = oi.order_id
         GROUP BY o.customer_id) AS avg_calc);

-- Derived table subquery: Top spenders
SELECT c.first_name, c.last_name, spending.total_spent
FROM customers c
JOIN (
    SELECT o.customer_id, SUM(oi.quantity * oi.unit_price) AS total_spent
    FROM orders o
    JOIN order_items oi ON o.order_id = oi.order_id
    GROUP BY o.customer_id
) AS spending ON c.customer_id = spending.customer_id
ORDER BY spending.total_spent DESC
LIMIT 5;

-- ============================================
-- WINDOW FUNCTIONS
-- ============================================

-- RANK, DENSE_RANK, ROW_NUMBER: Products ranked by revenue within category
SELECT
    p.product_name,
    p.category,
    SUM(oi.quantity * oi.unit_price) AS revenue,
    ROW_NUMBER() OVER (PARTITION BY p.category ORDER BY SUM(oi.quantity * oi.unit_price) DESC) AS row_num,
    RANK() OVER (PARTITION BY p.category ORDER BY SUM(oi.quantity * oi.unit_price) DESC) AS rank_val,
    DENSE_RANK() OVER (PARTITION BY p.category ORDER BY SUM(oi.quantity * oi.unit_price) DESC) AS dense_rank_val
FROM products p
JOIN order_items oi ON p.product_id = oi.product_id
GROUP BY p.product_id, p.product_name, p.category;

-- Running total: Cumulative spending per customer over time
SELECT
    c.first_name,
    c.last_name,
    o.order_date,
    SUM(oi.quantity * oi.unit_price) AS order_total,
    SUM(SUM(oi.quantity * oi.unit_price)) OVER (
        PARTITION BY c.customer_id
        ORDER BY o.order_date
    ) AS cumulative_spent
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY c.customer_id, c.first_name, c.last_name, o.order_id, o.order_date;

-- LAG: Compare each order to the customer's previous order
SELECT
    c.first_name,
    o.order_date,
    SUM(oi.quantity * oi.unit_price) AS order_total,
    LAG(SUM(oi.quantity * oi.unit_price)) OVER (
        PARTITION BY c.customer_id
        ORDER BY o.order_date
    ) AS previous_order_total
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY c.customer_id, c.first_name, o.order_id, o.order_date;

-- ============================================
-- COMMON TABLE EXPRESSIONS (CTEs)
-- ============================================

-- Non-recursive CTE: Top 3 products per category by revenue
WITH product_revenue AS (
    SELECT
        p.product_id,
        p.product_name,
        p.category,
        SUM(oi.quantity * oi.unit_price) AS revenue
    FROM products p
    JOIN order_items oi ON p.product_id = oi.product_id
    GROUP BY p.product_id, p.product_name, p.category
),
ranked_products AS (
    SELECT
        product_name,
        category,
        revenue,
        RANK() OVER (PARTITION BY category ORDER BY revenue DESC) AS category_rank
    FROM product_revenue
)
SELECT product_name, category, revenue, category_rank
FROM ranked_products
WHERE category_rank <= 3
ORDER BY category, category_rank;

-- Multi-CTE: High-value customers who buy premium products
WITH customer_spending AS (
    SELECT
        o.customer_id,
        SUM(oi.quantity * oi.unit_price) AS total_spent
    FROM orders o
    JOIN order_items oi ON o.order_id = oi.order_id
    GROUP BY o.customer_id
    HAVING total_spent > 200
),
premium_buyers AS (
    SELECT DISTINCT o.customer_id
    FROM orders o
    JOIN order_items oi ON o.order_id = oi.order_id
    JOIN products p ON oi.product_id = p.product_id
    WHERE p.price >= 100
)
SELECT
    c.first_name,
    c.last_name,
    cs.total_spent
FROM customer_spending cs
JOIN premium_buyers pb ON cs.customer_id = pb.customer_id
JOIN customers c ON cs.customer_id = c.customer_id
ORDER BY cs.total_spent DESC;

You now have a toolkit of CTEs that break complex analytics into readable, chainable steps. Next up, you will optimize your queries with indexes and prove the improvement using EXPLAIN.

Optimize Queries with Indexes and EXPLAIN

Your queries work, but how do you know they're efficient? Writing correct SQL is one skill. Writing fast SQL is another.

In this step, you'll use EXPLAIN to see how MySQL actually executes your queries. Then you'll add strategic indexes to speed things up and prove the improvement with hard numbers.

In this step, get ready to:
  • Run EXPLAIN to establish a baseline for query performance.
  • Add strategic indexes and measure the improvement.
  • Understand the trade-offs of indexing.
Run EXPLAIN to establish a baseline

Before adding indexes, you need a baseline. EXPLAIN shows you MySQL's execution plan for a query without actually running it.

  • Run this EXPLAIN on the product revenue query from the previous step:
-- Before indexes: EXPLAIN the ranking query
EXPLAIN SELECT
    p.product_name,
    p.category,
    SUM(oi.quantity * oi.unit_price) AS revenue
FROM products p
JOIN order_items oi ON p.product_id = oi.product_id
GROUP BY p.product_id, p.product_name, p.category;

What does EXPLAIN show you?

EXPLAIN returns a row for each table involved in the query. The most important columns to look at are:

  • type: how MySQL accesses the table. ALL means a full table scan (reads every row). ref or range means it uses an index.
  • rows: MySQL's estimate of how many rows it needs to examine. Lower is better.
  • key: which index (if any) MySQL chose. NULL means no index was used.
  • Look at the output table. Find the type column for the order_items row.

You should see type=ALL for the order_items table. This means MySQL is scanning every single row to find matches.

Why is a full table scan bad?

With 30 rows, a full scan is instant. But imagine a production table with 10 million order items. Scanning every row for each JOIN would take minutes instead of milliseconds.

The ranking from best to worst access type is: system, const, eq_ref, ref, range, index, ALL. Your goal is to move queries away from ALL toward ref or range.

Seeing an error instead of a table?

Make sure you're still connected to the ecommerce_analytics database. Run SELECT DATABASE(); to check. If it returns NULL, run USE ecommerce_analytics; first.

Help me troubleshoot my EXPLAIN query

Add strategic indexes and measure the improvement

An index is like a book's table of contents. Instead of flipping through every page, MySQL can jump straight to the data it needs.

  • Create four strategic indexes by running these statements:
-- Add indexes
CREATE INDEX idx_order_items_order_product ON order_items (order_id, product_id);
CREATE INDEX idx_products_category ON products (category);
CREATE INDEX idx_orders_date ON orders (order_date);
CREATE INDEX idx_orders_customer ON orders (customer_id);

What does each index do?

  • idx_order_items_order_product is a composite index on two columns. It speeds up JOINs that look up order_items by order_id and product_id together. The leftmost prefix rule means it also helps queries that filter on order_id alone.
  • idx_products_category helps queries that filter or group by product category.
  • idx_orders_date speeds up date-range queries like monthly reports.
  • idx_orders_customer helps JOINs between customers and orders.

You should see Query OK, 30 rows affected (or similar) for each statement. The row count matches the number of rows MySQL indexed in that table.

  • Now re-run the same EXPLAIN query to see the difference:
-- After indexes: Re-run EXPLAIN
EXPLAIN SELECT
    p.product_name,
    p.category,
    SUM(oi.quantity * oi.unit_price) AS revenue
FROM products p
JOIN order_items oi ON p.product_id = oi.product_id
GROUP BY p.product_id, p.product_name, p.category;

What should I see?

Compare the type column to your earlier output. It should improve from ALL to ref or range. The rows estimate should also decrease, meaning MySQL examines fewer rows.

  • Verify your indexes were created by running:
-- Verify indexes
SHOW INDEX FROM order_items;
SHOW INDEX FROM products;
SHOW INDEX FROM orders;

You should see your new indexes listed alongside the primary key indexes that MySQL created automatically.

Seeing 'Duplicate key name' error?

This means you've already created that index. Each index name must be unique per table. If you need to start fresh, run DROP INDEX idx_order_items_order_product ON order_items; and then re-create it.

Help me fix my index creation error

Understand when NOT to index

Indexes are not free. Every index you add comes with a cost on the write side of your database.

What is the trade-off with indexes?

Indexes speed up reads (SELECT queries) but slow down writes (INSERT, UPDATE, DELETE). Every time you add or change a row, MySQL must also update every index on that table.

With only 30 rows in order_items, the optimizer may still choose a full table scan because reading 30 rows is essentially free. The real performance impact of indexes appears at 1000+ rows, where the difference between scanning everything and jumping to the right spot becomes dramatic.

Index guidelines for production

  • Index columns you frequently use in WHERE, JOIN, and ORDER BY clauses.
  • Avoid indexing columns with very few unique values (like a boolean status column).
  • Use composite indexes when queries frequently filter on the same combination of columns.

✔️ Awesome, I've got everything!

Great. Make sure you've saved your queries.sql file with all the EXPLAIN and index statements included.

ⓧ I'd like to double check the full code

Here is the complete queries.sql file with everything from this project so far:

USE ecommerce_analytics;

-- ============================================
-- SUBQUERIES
-- ============================================

-- Scalar subquery: Products priced above average
SELECT product_name, category, price
FROM products
WHERE price > (SELECT AVG(price) FROM products);

-- Correlated subquery: Customers who spent more than average customer spending
SELECT c.first_name, c.last_name,
    (SELECT SUM(oi.quantity * oi.unit_price)
     FROM orders o
     JOIN order_items oi ON o.order_id = oi.order_id
     WHERE o.customer_id = c.customer_id) AS total_spent
FROM customers c
WHERE (SELECT SUM(oi.quantity * oi.unit_price)
       FROM orders o
       JOIN order_items oi ON o.order_id = oi.order_id
       WHERE o.customer_id = c.customer_id) >
      (SELECT AVG(customer_total) FROM
        (SELECT SUM(oi.quantity * oi.unit_price) AS customer_total
         FROM orders o
         JOIN order_items oi ON o.order_id = oi.order_id
         GROUP BY o.customer_id) AS avg_calc);

-- Derived table subquery: Top spenders
SELECT c.first_name, c.last_name, spending.total_spent
FROM customers c
JOIN (
    SELECT o.customer_id, SUM(oi.quantity * oi.unit_price) AS total_spent
    FROM orders o
    JOIN order_items oi ON o.order_id = oi.order_id
    GROUP BY o.customer_id
) AS spending ON c.customer_id = spending.customer_id
ORDER BY spending.total_spent DESC
LIMIT 5;

-- ============================================
-- WINDOW FUNCTIONS
-- ============================================

-- RANK, DENSE_RANK, ROW_NUMBER: Products ranked by revenue within category
SELECT
    p.product_name,
    p.category,
    SUM(oi.quantity * oi.unit_price) AS revenue,
    ROW_NUMBER() OVER (PARTITION BY p.category ORDER BY SUM(oi.quantity * oi.unit_price) DESC) AS row_num,
    RANK() OVER (PARTITION BY p.category ORDER BY SUM(oi.quantity * oi.unit_price) DESC) AS rank_val,
    DENSE_RANK() OVER (PARTITION BY p.category ORDER BY SUM(oi.quantity * oi.unit_price) DESC) AS dense_rank_val
FROM products p
JOIN order_items oi ON p.product_id = oi.product_id
GROUP BY p.product_id, p.product_name, p.category;

-- Running total: Cumulative spending per customer over time
SELECT
    c.first_name,
    c.last_name,
    o.order_date,
    SUM(oi.quantity * oi.unit_price) AS order_total,
    SUM(SUM(oi.quantity * oi.unit_price)) OVER (
        PARTITION BY c.customer_id
        ORDER BY o.order_date
    ) AS cumulative_spent
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY c.customer_id, c.first_name, c.last_name, o.order_id, o.order_date;

-- LAG: Compare each order to the customer's previous order
SELECT
    c.first_name,
    o.order_date,
    SUM(oi.quantity * oi.unit_price) AS order_total,
    LAG(SUM(oi.quantity * oi.unit_price)) OVER (
        PARTITION BY c.customer_id
        ORDER BY o.order_date
    ) AS previous_order_total
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY c.customer_id, c.first_name, o.order_id, o.order_date;

-- ============================================
-- COMMON TABLE EXPRESSIONS (CTEs)
-- ============================================

-- Non-recursive CTE: Top 3 products per category by revenue
WITH product_revenue AS (
    SELECT
        p.product_id,
        p.product_name,
        p.category,
        SUM(oi.quantity * oi.unit_price) AS revenue
    FROM products p
    JOIN order_items oi ON p.product_id = oi.product_id
    GROUP BY p.product_id, p.product_name, p.category
),
ranked_products AS (
    SELECT
        product_name,
        category,
        revenue,
        RANK() OVER (PARTITION BY category ORDER BY revenue DESC) AS category_rank
    FROM product_revenue
)
SELECT product_name, category, revenue, category_rank
FROM ranked_products
WHERE category_rank <= 3
ORDER BY category, category_rank;

-- Multi-CTE: High-value customers who buy premium products
WITH customer_spending AS (
    SELECT
        o.customer_id,
        SUM(oi.quantity * oi.unit_price) AS total_spent
    FROM orders o
    JOIN order_items oi ON o.order_id = oi.order_id
    GROUP BY o.customer_id
    HAVING total_spent > 200
),
premium_buyers AS (
    SELECT DISTINCT o.customer_id
    FROM orders o
    JOIN order_items oi ON o.order_id = oi.order_id
    JOIN products p ON oi.product_id = p.product_id
    WHERE p.price >= 100
)
SELECT
    c.first_name,
    c.last_name,
    cs.total_spent
FROM customer_spending cs
JOIN premium_buyers pb ON cs.customer_id = pb.customer_id
JOIN customers c ON cs.customer_id = c.customer_id
ORDER BY cs.total_spent DESC;

-- ============================================
-- INDEXING AND OPTIMIZATION
-- ============================================

-- Before indexes: EXPLAIN the ranking query
EXPLAIN SELECT
    p.product_name,
    p.category,
    SUM(oi.quantity * oi.unit_price) AS revenue
FROM products p
JOIN order_items oi ON p.product_id = oi.product_id
GROUP BY p.product_id, p.product_name, p.category;

-- Add indexes
CREATE INDEX idx_order_items_order_product ON order_items (order_id, product_id);
CREATE INDEX idx_products_category ON products (category);
CREATE INDEX idx_orders_date ON orders (order_date);
CREATE INDEX idx_orders_customer ON orders (customer_id);

-- After indexes: Re-run EXPLAIN
EXPLAIN SELECT
    p.product_name,
    p.category,
    SUM(oi.quantity * oi.unit_price) AS revenue
FROM products p
JOIN order_items oi ON p.product_id = oi.product_id
GROUP BY p.product_id, p.product_name, p.category;

-- Verify indexes
SHOW INDEX FROM order_items;
SHOW INDEX FROM products;
SHOW INDEX FROM orders;

You've now seen the full optimization loop: measure, improve, re-measure. Next up is the Secret Mission, where you'll push your SQL skills further with recursive CTEs.

Secret mission

Recursive CTE for Monthly Sales Trends

Your analytics database is powerful, but leadership wants a monthly trend report. Can you build a recursive CTE that generates a gap-free timeline of monthly revenue, running totals, and month-over-month growth percentages?

Clean Up Your Resources

Clean Up Your Resources

This project runs entirely locally, so there are no ongoing costs. Decide whether to keep your resources running, pause them to come back later, or delete them entirely.

Resources you used:

  • MySQL 8.0 server running locally via Homebrew services.
  • MySQL database ecommerce_analytics with four tables, sample data, and indexes.

Keep everything running

No action needed. Choose this if you're still actively building or experimenting with your queries.

  • MySQL will continue running in the background via Homebrew services. It restarts automatically on reboot.
  • Your ecommerce_analytics database and all its data persist on disk.
  • MySQL uses minimal memory and disk space when idle.

Pause - I'll come back to this later

Shut down the MySQL server to free up memory. Your data persists on disk and is ready whenever you restart.

  • Stop the MySQL server by running:
brew services stop mysql@8.0
  • When you're ready to come back, restart with:
brew services start mysql@8.0
  • Then reconnect with mysql -u root and run USE ecommerce_analytics; to pick up where you left off.

Delete - I don't want to use this again

Remove all project resources completely.

  • Connect to MySQL and drop the database by running:
mysql -u root -e "DROP DATABASE ecommerce_analytics;"
  • Stop the MySQL server by running:
brew services stop mysql@8.0
  • If you want to remove MySQL entirely from your machine, uninstall it by running:
brew uninstall mysql@8.0
  • This removes the MySQL binary and configuration files. Any remaining data directories will be cleaned up by Homebrew.

Nice Work!

Nice Work!

You just built a production-grade e-commerce analytics database from scratch using advanced MySQL features. From normalized schema design to recursive CTEs, you now have hands-on proof of intermediate SQL skills.

Here's what you accomplished:

  • Built a normalized 4-table e-commerce schema with proper foreign key relationships and wrote scalar, correlated, and derived-table subqueries to answer real business questions.
  • Used window functions including RANK(), DENSE_RANK(), ROW_NUMBER(), SUM() OVER, and LAG() for ranking, running totals, and period-over-period comparisons.
  • Wrote chained Common Table Expressions for multi-step analysis, added strategic composite indexes, and proved their impact using EXPLAIN.
  • Secret Mission: Built a recursive CTE generating a gap-free monthly trend report with running totals and month-over-month growth percentages.

Ready to quiz yourself?