SQL Queries for Business Analysts

Build an e-commerce database and write 10+ business queries in MySQL.

Introduction

30 Second Summary

Every business decision starts with a question. "Which products are selling the most?" "Who are our best customers?" The answers live in databases, and the language you use to pull them out is SQL.

In this project, you will build a multi-table e-commerce database from scratch and write real business queries that answer stakeholder questions about revenue, customer behavior, and monthly trends.

What You'll Build

You will play the role of a Business Analyst at NovaMart, a mid-size e-commerce company, pulling insights from sales data to prepare for a quarterly review.

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

  • A 4-table relational database with realistic e-commerce data spanning 6 months of customers, products, orders, and order items.
  • A collection of reusable business queries that answer real stakeholder questions like revenue by category, top customers by lifetime value, and month-over-month growth.
  • Hands-on practice with the 5 core SQL patterns that appear in 90% of Business Analyst interviews: filtering, aggregations, joins, date analysis, and subqueries.
  • Secret Mission: Use window functions to rank products within each category and calculate running revenue totals.

Are there any prerequisites?

This project assumes you have MySQL installed on Windows and a basic understanding of what databases and tables are. Step 1 will walk you through verifying your setup and installing MySQL Workbench if needed.

Set Up Your Workspace

Before you can query any data, you need a database to query. In this project, you are stepping into the role of a Business Analyst at NovaMart, a mid-size e-commerce company.

Your manager needs insights from the company's sales database for an upcoming quarterly review. In this step, you will connect to your local MySQL server and create the NovaMart database from scratch.

In this step, get ready to:
  • Connect to your local MySQL instance and verify it is running.
  • Create the NovaMart database for your e-commerce data.
Connect to your local MySQL instance

MySQL Workbench is a graphical interface for managing your MySQL server. To run SQL queries, you first need to establish a connection between Workbench and the MySQL server running on your machine.

  • On the MySQL Workbench home screen, find your local connection tile under MySQL Connections. It is typically named Local instance MySQL80 or similar.
  • Double-click the connection tile to connect.
  • If prompted, enter the root password you set during MySQL installation, then click OK.

After connecting, Workbench opens an SQL editor tab automatically. This is where you will write and run all your queries.

  • Verify your connection is working by running this query in the SQL editor:
SELECT VERSION();
  • Click the lightning bolt icon (or press Ctrl+Shift+Enter) to execute the query.

What does this query do?

SELECT VERSION(); asks MySQL to report its version number. If you see a result like 8.0.x in the output grid below the editor, your connection is working.

You should see a single row in the results panel containing a version number such as 8.0.36 or higher.

✔️ I see a version number

Your MySQL server is running and Workbench is connected. You are ready to create the database.

ⓧ I see an error or no result

This usually means the MySQL service is not running. Try these fixes:

  • Press the Windows key, type Services, and press Enter.
  • Scroll down to find MySQL80 (or a similar MySQL entry).
  • Right-click it and select Start.
  • Return to MySQL Workbench and double-click your connection tile again.
  • Re-run SELECT VERSION(); to confirm.

If you are still stuck, check that the password you entered matches the root password you chose during MySQL installation.

Create the NovaMart database

Every SQL project starts by creating a dedicated database. This keeps your tables and data isolated from anything else on your MySQL server.

  • Create and select the NovaMart database by running this SQL in your query tab:
-- Create the e-commerce database
CREATE DATABASE IF NOT EXISTS novamart;

-- Switch to using the new database
USE novamart;
  • Click the lightning bolt icon (or press Ctrl+Shift+Enter) to execute both statements.

What does this code do?

  • CREATE DATABASE IF NOT EXISTS novamart; creates a new database called novamart. The IF NOT EXISTS clause prevents an error if you accidentally run it twice.
  • USE novamart; tells MySQL that all future queries in this session should target the novamart database. Creating a database does not automatically select it.
  • Confirm the database is active by running this query:
SELECT DATABASE();

You should see novamart returned in the results panel.

  • Save your SQL script by pressing Ctrl+S.
  • In the save dialog, name the file 01_create_database.sql and save it to your Desktop.

SELECT DATABASE() returning NULL?

  • Make sure you ran both CREATE DATABASE and USE novamart together. If you only ran the first line, the database exists but is not selected.
  • Highlight both lines in the editor and click the lightning bolt to execute them as a batch.
  • Check the bottom of Workbench for any error messages in the Output panel.

Help me fix my SELECT DATABASE() query returning NULL.

✔️ Awesome, I've got everything!

Great. Make sure you have saved your file as 01_create_database.sql.

ⓧ I'd like to double check the full code

Here is the complete file for 01_create_database.sql:

CREATE DATABASE IF NOT EXISTS novamart;
USE novamart;

Your workspace is ready. Next up, you will design the table structure that holds NovaMart's customers, products, and orders.

Build Your Database Schema

Your novamart database is created and selected. Right now it's an empty shell with no structure inside.

In this step, you'll create four tables that define NovaMart's e-commerce data model. Each table holds one type of information, and foreign key relationships connect them together.

In this step, get ready to:
  • Create a customers table with name, email, and signup information.
  • Create a products table with categories and pricing data.
  • Create orders and order_items tables linked by foreign keys.
Create the customers table

The customers table stores information about each buyer. Every row represents one customer with a unique ID that the database assigns automatically.

  • In MySQL Workbench, open a new query tab by clicking **File** in the menu bar, then selecting **New Query Tab**.
  • This new tab will hold all of your table creation SQL. You'll save it as a separate file at the end.

Why a new query tab?

Each query tab in MySQL Workbench acts like a separate file. Keeping your table creation SQL in its own tab makes it easy to re-run later if you need to rebuild the database from scratch.

  • Create the customers table by typing this SQL into your new query tab:
CREATE TABLE customers (
    customer_id INT NOT NULL AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    city VARCHAR(50),
    signup_date DATE,
    PRIMARY KEY (customer_id)
);

What does this code do?

  • customer_id INT NOT NULL AUTO_INCREMENT creates a numeric ID column that automatically counts up (1, 2, 3...) for each new customer.
  • VARCHAR(50) means variable-length text with a maximum of 50 characters.
  • NOT NULL means the column is required. The database will reject any insert that leaves it empty.
  • city and signup_date have no NOT NULL constraint, so they're optional fields.
  • PRIMARY KEY (customer_id) designates customer_id as the unique identifier for each row.
  • Select the entire CREATE TABLE customers statement by clicking at the start and dragging to the end.
  • Click the lightning bolt icon in the toolbar to execute it.
  • You should see a green checkmark or success message in the Output pane at the bottom of MySQL Workbench.

Seeing an error?

  • If you see Table 'customers' already exists, you may have run this statement before. Run DROP TABLE customers; first, then re-execute the CREATE TABLE statement.
  • If you see a syntax error, check that every line ends with a comma except the last column definition and the PRIMARY KEY line.
  • Make sure your novamart database is still selected. Run USE novamart; if you're unsure.

Having trouble with an error not listed above?

Create the products table

The products table tracks everything NovaMart sells. It includes a DECIMAL type for precise pricing and a stock count for inventory.

  • Add the following SQL below your customers table in the same query tab:
CREATE TABLE products (
    product_id INT NOT NULL AUTO_INCREMENT,
    product_name VARCHAR(100) NOT NULL,
    category VARCHAR(50) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    stock_quantity INT NOT NULL DEFAULT 0,
    PRIMARY KEY (product_id)
);

What does this code do?

  • DECIMAL(10,2) stores numbers with up to 10 total digits and 2 decimal places. This is the standard type for money in MySQL because it avoids floating-point rounding errors.
  • DEFAULT 0 means if you insert a product without specifying stock_quantity, the database automatically sets it to 0.
  • category is marked NOT NULL because every product must belong to a category for the business queries you'll write later.
  • Select just the CREATE TABLE products statement you added.
  • Click the lightning bolt icon to execute it.
  • You should see another success message in the Output pane.

Seeing a syntax error?

  • Double-check that DECIMAL(10,2) has parentheses, not brackets. The comma separates the total digits from the decimal places.
  • Make sure you selected only the products statement, not the customers statement above it. Re-executing a CREATE TABLE that already exists will produce an error.

Still stuck on an error?

Create the orders and order_items tables

The final two tables track purchases. The orders table records each transaction, while order_items stores the individual products within each order.

These tables use foreign keys to create relationships. A foreign key in one table points to the primary key of another table, enforcing that every order belongs to a real customer and every line item belongs to a real order.

  • Add the orders table below your existing SQL in the same query tab:
CREATE TABLE orders (
    order_id INT NOT NULL AUTO_INCREMENT,
    customer_id INT NOT NULL,
    order_date DATE NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'pending',
    PRIMARY KEY (order_id),
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

What does this code do?

  • customer_id INT NOT NULL stores which customer placed the order. Every order must have a customer.
  • DEFAULT 'pending' means new orders automatically start with a pending status unless you specify otherwise.
  • FOREIGN KEY (customer_id) REFERENCES customers(customer_id) tells MySQL to reject any order where the customer_id doesn't match an existing customer. This protects your data integrity.
  • Select just the CREATE TABLE orders statement and click the lightning bolt icon to execute it.
  • You should see a success message in the Output pane.
  • Now add the order_items table below the orders table:
CREATE TABLE order_items (
    item_id INT NOT NULL AUTO_INCREMENT,
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL,
    PRIMARY KEY (item_id),
    FOREIGN KEY (order_id) REFERENCES orders(order_id),
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

What does this code do?

  • This table links orders to products. Each row represents one product line in an order.
  • unit_price is stored here instead of just referencing the products table. This captures the price at the time of purchase, even if the product price changes later.
  • Two foreign keys connect this table to both orders and products. MySQL will reject any line item that references a non-existent order or product.
  • Select the CREATE TABLE order_items statement and click the lightning bolt icon to execute it.
  • Confirm all four tables exist by running this query:
SHOW TABLES;
  • You should see customers, order_items, orders, and products listed in the result grid.
  • Save this file by clicking **File**, then **Save Script As**. Name it 02_create_tables.sql.

Don't see all four tables?

  • If a table is missing, scroll up in your query tab and check that the CREATE TABLE statement for it executed without errors.
  • If you see a foreign key error on orders or order_items, the tables must be created in order. The customers table must exist before orders can reference it, and both orders and products must exist before order_items can reference them.
  • To start fresh, run DROP TABLE IF EXISTS order_items, orders, products, customers; and then re-execute all four CREATE TABLE statements in order from top to bottom.

Still having trouble getting all four tables created?

✔️ Awesome, I've got everything!

Great. Double check you've saved your file as 02_create_tables.sql.

ⓧ I'd like to double check the full code

Here's the complete 02_create_tables.sql file. Compare it against what you have in your query tab:

CREATE TABLE customers (
    customer_id INT NOT NULL AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    city VARCHAR(50),
    signup_date DATE,
    PRIMARY KEY (customer_id)
);

CREATE TABLE products (
    product_id INT NOT NULL AUTO_INCREMENT,
    product_name VARCHAR(100) NOT NULL,
    category VARCHAR(50) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    stock_quantity INT NOT NULL DEFAULT 0,
    PRIMARY KEY (product_id)
);

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

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

Your database schema is complete. Four tables with proper constraints and relationships are ready to hold data. Next up, you'll load realistic e-commerce data into these tables so you have something to query against.

Load Sample Business Data

Your database structure is in place. You have four empty tables connected by foreign keys, ready to hold NovaMart's business data.

A database schema without data is like a spreadsheet with only column headers. In this step, you will populate your tables with 6 months of realistic e-commerce transactions so you have meaningful data to query against.

In this step, get ready to:
  • Insert 15 customers across different cities.
  • Insert 12 products across 3 categories.
  • Insert 30 orders and 57 line items spanning January through June 2025.
Insert customer data

Customers are the foundation of the data model. Every order links back to a customer, so this table must be populated first.

  • Open a new query tab in MySQL Workbench by clicking the New Query Tab button (the + icon above the query editor).
  • Paste the following SQL to insert 15 customers with signup dates spanning January through June 2025:
-- Customers (15 customers across different cities)
INSERT INTO customers (first_name, last_name, email, city, signup_date) VALUES
('Sarah', 'Chen', 'sarah.chen@email.com', 'Toronto', '2025-01-05'),
('James', 'Wilson', 'james.w@email.com', 'Vancouver', '2025-01-12'),
('Priya', 'Sharma', 'priya.s@email.com', 'Toronto', '2025-01-20'),
('Michael', 'Brown', 'mbrown@email.com', 'Calgary', '2025-02-03'),
('Emma', 'Davis', 'emma.d@email.com', 'Montreal', '2025-02-14'),
('Liam', 'Garcia', 'liam.g@email.com', 'Vancouver', '2025-02-28'),
('Olivia', 'Martinez', 'olivia.m@email.com', 'Ottawa', '2025-03-10'),
('Noah', 'Anderson', 'noah.a@email.com', 'Toronto', '2025-03-15'),
('Ava', 'Thomas', 'ava.t@email.com', 'Edmonton', '2025-04-01'),
('Ethan', 'Jackson', 'ethan.j@email.com', 'Calgary', '2025-04-18'),
('Sophia', 'White', 'sophia.w@email.com', 'Montreal', '2025-05-02'),
('Lucas', 'Harris', 'lucas.h@email.com', 'Vancouver', '2025-05-15'),
('Mia', 'Clark', 'mia.c@email.com', 'Toronto', '2025-05-22'),
('Alexander', 'Lewis', 'alex.l@email.com', 'Ottawa', '2025-06-01'),
('Isabella', 'Walker', 'isabella.w@email.com', 'Edmonton', '2025-06-10');

What does this code do?

This single INSERT INTO ... VALUES statement adds 15 rows in one go. Each row in parentheses is one customer.

The cities are spread across Toronto, Vancouver, Calgary, Montreal, Ottawa, and Edmonton. The signup dates range from January to June 2025, giving you 6 months of customer acquisition data to analyse later.

  • Click the lightning bolt icon (or press Ctrl+Shift+Enter) to execute the statement.
  • You should see a green checkmark and "15 row(s) affected" in the output panel at the bottom.

Seeing a foreign key or duplicate entry error?

If you see Duplicate entry, you may have run the INSERT twice. Run DELETE FROM customers; first, then re-run the INSERT.

If you see a syntax error, check that every row ends with a comma except the last row, which ends with a semicolon.

Still stuck? Help me fix my INSERT INTO customers statement

Insert product data

Products are the items NovaMart sells. You will load 12 products across three categories: Electronics, Clothing, and Home.

  • Add this statement below your customer INSERT in the same query tab:
-- Products (12 products across 3 categories)
INSERT INTO products (product_name, category, price, stock_quantity) VALUES
('Wireless Headphones', 'Electronics', 79.99, 150),
('Bluetooth Speaker', 'Electronics', 49.99, 200),
('USB-C Hub', 'Electronics', 34.99, 300),
('Laptop Stand', 'Electronics', 89.99, 100),
('Cotton T-Shirt', 'Clothing', 24.99, 500),
('Denim Jacket', 'Clothing', 89.99, 120),
('Running Shoes', 'Clothing', 129.99, 80),
('Winter Scarf', 'Clothing', 19.99, 250),
('Ceramic Vase', 'Home', 44.99, 60),
('Scented Candle Set', 'Home', 29.99, 180),
('Throw Blanket', 'Home', 59.99, 90),
('Wall Art Print', 'Home', 39.99, 110);

What does this code do?

Each product has a name, category, price, and stock quantity. The prices range from 19.99 (Winter Scarf) to 129.99 (Running Shoes).

Having three distinct categories (Electronics, Clothing, Home) lets you write meaningful GROUP BY queries later to compare revenue across categories.

  • Select only the product INSERT statement, then click the lightning bolt icon to execute it.
  • You should see "12 row(s) affected" in the output panel.

Execute only selected text

If you highlight a portion of your SQL before clicking execute, MySQL Workbench runs only the highlighted text. This saves you from re-running earlier statements that would cause duplicate entry errors.

Insert orders and order items

Orders and order items are the transactional heart of your database. Orders record when a customer placed a purchase. Order items record exactly which products were in each order and at what price.

  • Add the orders INSERT below your product data:
-- Orders (30 orders spanning Jan-Jun 2025)
INSERT INTO orders (customer_id, order_date, status) VALUES
(1, '2025-01-10', 'completed'),
(2, '2025-01-15', 'completed'),
(3, '2025-01-22', 'completed'),
(1, '2025-01-28', 'completed'),
(4, '2025-02-05', 'completed'),
(5, '2025-02-10', 'completed'),
(2, '2025-02-14', 'cancelled'),
(6, '2025-02-20', 'completed'),
(3, '2025-02-25', 'completed'),
(7, '2025-03-02', 'completed'),
(1, '2025-03-08', 'completed'),
(8, '2025-03-12', 'completed'),
(4, '2025-03-18', 'completed'),
(9, '2025-03-25', 'pending'),
(5, '2025-04-01', 'completed'),
(10, '2025-04-05', 'completed'),
(2, '2025-04-10', 'completed'),
(6, '2025-04-15', 'cancelled'),
(11, '2025-04-20', 'completed'),
(3, '2025-04-28', 'completed'),
(12, '2025-05-03', 'completed'),
(1, '2025-05-10', 'completed'),
(7, '2025-05-15', 'completed'),
(13, '2025-05-20', 'pending'),
(8, '2025-05-25', 'completed'),
(14, '2025-06-02', 'completed'),
(5, '2025-06-08', 'completed'),
(9, '2025-06-12', 'completed'),
(10, '2025-06-18', 'pending'),
(15, '2025-06-22', 'completed');

What does this code do?

The customer_id values reference the customers you just inserted. Notice that some customers (like customer 1, Sarah Chen) appear multiple times because they placed multiple orders.

The status column uses three values: completed, pending, and cancelled. This mix lets you filter by order status in your queries.

  • Select the orders INSERT and execute it.
  • You should see "30 row(s) affected" in the output panel.
  • Now add the order items INSERT below the orders:
-- Order Items (50+ line items)
INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES
(1, 1, 1, 79.99),
(1, 3, 2, 34.99),
(2, 5, 3, 24.99),
(2, 10, 1, 29.99),
(3, 7, 1, 129.99),
(3, 9, 1, 44.99),
(4, 2, 1, 49.99),
(4, 11, 1, 59.99),
(5, 4, 1, 89.99),
(5, 6, 1, 89.99),
(6, 1, 2, 79.99),
(6, 8, 1, 19.99),
(7, 5, 2, 24.99),
(8, 12, 2, 39.99),
(8, 3, 1, 34.99),
(9, 7, 1, 129.99),
(9, 10, 2, 29.99),
(10, 4, 1, 89.99),
(10, 11, 1, 59.99),
(11, 2, 2, 49.99),
(11, 9, 1, 44.99),
(12, 6, 1, 89.99),
(12, 5, 2, 24.99),
(13, 1, 1, 79.99),
(13, 12, 1, 39.99),
(14, 3, 3, 34.99),
(15, 8, 2, 19.99),
(15, 10, 1, 29.99),
(16, 7, 1, 129.99),
(16, 11, 1, 59.99),
(17, 4, 1, 89.99),
(17, 2, 1, 49.99),
(18, 5, 1, 24.99),
(19, 6, 2, 89.99),
(19, 9, 1, 44.99),
(20, 1, 1, 79.99),
(20, 3, 1, 34.99),
(21, 12, 2, 39.99),
(21, 10, 1, 29.99),
(22, 7, 1, 129.99),
(22, 8, 1, 19.99),
(23, 4, 1, 89.99),
(23, 11, 2, 59.99),
(24, 2, 1, 49.99),
(24, 5, 1, 24.99),
(25, 1, 1, 79.99),
(25, 6, 1, 89.99),
(26, 9, 2, 44.99),
(26, 3, 1, 34.99),
(27, 12, 1, 39.99),
(27, 10, 2, 29.99),
(28, 7, 1, 129.99),
(28, 4, 1, 89.99),
(29, 11, 1, 59.99),
(29, 5, 2, 24.99),
(30, 1, 1, 79.99),
(30, 2, 1, 49.99);

What does this code do?

Each row links an order_id to a product_id with a quantity and price. Notice that many orders have multiple line items (e.g., order 1 contains both Wireless Headphones and two USB-C Hubs).

The unit_price is stored at time of purchase. This is important because product prices can change over time, but the historical order should reflect what the customer actually paid.

  • Select the order items INSERT and execute it.
  • You should see "57 row(s) affected" in the output panel.
  • Verify your orders loaded correctly by running this quick count:
SELECT COUNT(*) FROM orders;
  • You should see a result of 30 in the results grid.
  • Save the query tab as 03_insert_data.sql by pressing Ctrl+S.

Seeing a foreign key constraint error on orders or order_items?

Foreign key errors mean you are referencing an ID that does not exist in the parent table. Make sure you executed the customer INSERT before the orders INSERT, and both customers and products before order_items.

If you ran statements out of order, run DELETE FROM order_items; then DELETE FROM orders; to clear the child tables. Then re-run in the correct sequence: customers, products, orders, order_items.

Still stuck? Help me resolve my foreign key constraint error when inserting orders or order_items

✔️ Awesome, I've got everything!

Great. Double check you have saved your file as 03_insert_data.sql.

ⓧ I'd like to double check the full code

Here is the complete 03_insert_data.sql file. Compare it against yours:

-- Customers (15 customers across different cities)
INSERT INTO customers (first_name, last_name, email, city, signup_date) VALUES
('Sarah', 'Chen', 'sarah.chen@email.com', 'Toronto', '2025-01-05'),
('James', 'Wilson', 'james.w@email.com', 'Vancouver', '2025-01-12'),
('Priya', 'Sharma', 'priya.s@email.com', 'Toronto', '2025-01-20'),
('Michael', 'Brown', 'mbrown@email.com', 'Calgary', '2025-02-03'),
('Emma', 'Davis', 'emma.d@email.com', 'Montreal', '2025-02-14'),
('Liam', 'Garcia', 'liam.g@email.com', 'Vancouver', '2025-02-28'),
('Olivia', 'Martinez', 'olivia.m@email.com', 'Ottawa', '2025-03-10'),
('Noah', 'Anderson', 'noah.a@email.com', 'Toronto', '2025-03-15'),
('Ava', 'Thomas', 'ava.t@email.com', 'Edmonton', '2025-04-01'),
('Ethan', 'Jackson', 'ethan.j@email.com', 'Calgary', '2025-04-18'),
('Sophia', 'White', 'sophia.w@email.com', 'Montreal', '2025-05-02'),
('Lucas', 'Harris', 'lucas.h@email.com', 'Vancouver', '2025-05-15'),
('Mia', 'Clark', 'mia.c@email.com', 'Toronto', '2025-05-22'),
('Alexander', 'Lewis', 'alex.l@email.com', 'Ottawa', '2025-06-01'),
('Isabella', 'Walker', 'isabella.w@email.com', 'Edmonton', '2025-06-10');

-- Products (12 products across 3 categories)
INSERT INTO products (product_name, category, price, stock_quantity) VALUES
('Wireless Headphones', 'Electronics', 79.99, 150),
('Bluetooth Speaker', 'Electronics', 49.99, 200),
('USB-C Hub', 'Electronics', 34.99, 300),
('Laptop Stand', 'Electronics', 89.99, 100),
('Cotton T-Shirt', 'Clothing', 24.99, 500),
('Denim Jacket', 'Clothing', 89.99, 120),
('Running Shoes', 'Clothing', 129.99, 80),
('Winter Scarf', 'Clothing', 19.99, 250),
('Ceramic Vase', 'Home', 44.99, 60),
('Scented Candle Set', 'Home', 29.99, 180),
('Throw Blanket', 'Home', 59.99, 90),
('Wall Art Print', 'Home', 39.99, 110);

-- Orders (30 orders spanning Jan-Jun 2025)
INSERT INTO orders (customer_id, order_date, status) VALUES
(1, '2025-01-10', 'completed'),
(2, '2025-01-15', 'completed'),
(3, '2025-01-22', 'completed'),
(1, '2025-01-28', 'completed'),
(4, '2025-02-05', 'completed'),
(5, '2025-02-10', 'completed'),
(2, '2025-02-14', 'cancelled'),
(6, '2025-02-20', 'completed'),
(3, '2025-02-25', 'completed'),
(7, '2025-03-02', 'completed'),
(1, '2025-03-08', 'completed'),
(8, '2025-03-12', 'completed'),
(4, '2025-03-18', 'completed'),
(9, '2025-03-25', 'pending'),
(5, '2025-04-01', 'completed'),
(10, '2025-04-05', 'completed'),
(2, '2025-04-10', 'completed'),
(6, '2025-04-15', 'cancelled'),
(11, '2025-04-20', 'completed'),
(3, '2025-04-28', 'completed'),
(12, '2025-05-03', 'completed'),
(1, '2025-05-10', 'completed'),
(7, '2025-05-15', 'completed'),
(13, '2025-05-20', 'pending'),
(8, '2025-05-25', 'completed'),
(14, '2025-06-02', 'completed'),
(5, '2025-06-08', 'completed'),
(9, '2025-06-12', 'completed'),
(10, '2025-06-18', 'pending'),
(15, '2025-06-22', 'completed');

-- Order Items (50+ line items)
INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES
(1, 1, 1, 79.99),
(1, 3, 2, 34.99),
(2, 5, 3, 24.99),
(2, 10, 1, 29.99),
(3, 7, 1, 129.99),
(3, 9, 1, 44.99),
(4, 2, 1, 49.99),
(4, 11, 1, 59.99),
(5, 4, 1, 89.99),
(5, 6, 1, 89.99),
(6, 1, 2, 79.99),
(6, 8, 1, 19.99),
(7, 5, 2, 24.99),
(8, 12, 2, 39.99),
(8, 3, 1, 34.99),
(9, 7, 1, 129.99),
(9, 10, 2, 29.99),
(10, 4, 1, 89.99),
(10, 11, 1, 59.99),
(11, 2, 2, 49.99),
(11, 9, 1, 44.99),
(12, 6, 1, 89.99),
(12, 5, 2, 24.99),
(13, 1, 1, 79.99),
(13, 12, 1, 39.99),
(14, 3, 3, 34.99),
(15, 8, 2, 19.99),
(15, 10, 1, 29.99),
(16, 7, 1, 129.99),
(16, 11, 1, 59.99),
(17, 4, 1, 89.99),
(17, 2, 1, 49.99),
(18, 5, 1, 24.99),
(19, 6, 2, 89.99),
(19, 9, 1, 44.99),
(20, 1, 1, 79.99),
(20, 3, 1, 34.99),
(21, 12, 2, 39.99),
(21, 10, 1, 29.99),
(22, 7, 1, 129.99),
(22, 8, 1, 19.99),
(23, 4, 1, 89.99),
(23, 11, 2, 59.99),
(24, 2, 1, 49.99),
(24, 5, 1, 24.99),
(25, 1, 1, 79.99),
(25, 6, 1, 89.99),
(26, 9, 2, 44.99),
(26, 3, 1, 34.99),
(27, 12, 1, 39.99),
(27, 10, 2, 29.99),
(28, 7, 1, 129.99),
(28, 4, 1, 89.99),
(29, 11, 1, 59.99),
(29, 5, 2, 24.99),
(30, 1, 1, 79.99),
(30, 2, 1, 49.99);

Your database is alive with 6 months of NovaMart data. Next up, you will write queries that answer real business questions your manager would ask before a quarterly review.

Answer Business Questions with Queries

Your NovaMart database is loaded with 6 months of e-commerce data. Now comes the part that matters most for a Business Analyst: turning that raw data into answers your manager can act on.

In this step, you will write SQL queries that answer real business questions. You will start with basic filtering, then build up to aggregations and multi-table joins.

In this step, get ready to:
  • Filter and sort order data using WHERE, BETWEEN, and IN.
  • Aggregate revenue and order counts using GROUP BY and HAVING.
  • Combine data across multiple tables using INNER JOIN and LEFT JOIN.
Write basic filtering and sorting queries

Your manager's first request is simple: show all completed orders from Q1 2025. This requires filtering rows by two conditions at once.

  • In MySQL Workbench, open a new query tab by clicking the + icon above the existing query editor (or press Ctrl+T).
  • Write your first business query by pasting the following code into the new tab:
-- ============================================
-- BASIC FILTERING AND SORTING
-- ============================================

-- Q1: Show all completed orders from Q1 2025, sorted by date
SELECT order_id, customer_id, order_date, status
FROM orders
WHERE status = 'completed'
  AND order_date BETWEEN '2025-01-01' AND '2025-03-31'
ORDER BY order_date;

What does this query do?

  • WHERE status = 'completed' filters out cancelled and pending orders. The AND adds a second condition on the same row.
  • BETWEEN '2025-01-01' AND '2025-03-31' limits results to Q1 (January through March). BETWEEN is inclusive on both ends.
  • ORDER BY order_date sorts the results chronologically so your manager reads them in sequence.
  • Highlight the Q1 query (from SELECT through the closing ;), then click the lightning bolt icon in the toolbar to execute it.
  • You should see 12 rows in the Result Grid, all with status completed and dates between January and March 2025.

Seeing 0 rows or an error?

  • Make sure you have the novamart database selected. Run USE novamart; at the top of your tab if needed.
  • Check that you ran the INSERT statements from the previous step. Run SELECT COUNT(*) FROM orders; and confirm it returns 30.

Help me figure out why my SQL query returns no results.

Next, your manager wants a list of customers in Toronto and Vancouver for a regional marketing campaign.

  • Add the following query below Q1 in the same tab:
-- Q2: Find all customers in Toronto or Vancouver
SELECT first_name, last_name, email, city
FROM customers
WHERE city IN ('Toronto', 'Vancouver');

What does IN do?

IN ('Toronto', 'Vancouver') checks if a value matches any item in a list. It is a cleaner alternative to writing city = 'Toronto' OR city = 'Vancouver'.

  • Highlight Q2 and click the lightning bolt icon to execute.
  • You should see 7 rows showing customers from Toronto (4) and Vancouver (3).
Write aggregation queries with GROUP BY

Filtering shows individual rows, but your manager wants totals. Aggregation functions like SUM and COUNT collapse many rows into summary numbers.

GROUP BY tells SQL which column to group the summary by. Think of it as creating buckets: one bucket per category, one bucket per customer.

  • Add the following query below Q2 to answer "What is the total revenue by product category?":
-- ============================================
-- AGGREGATIONS WITH GROUP BY
-- ============================================

-- Q3: Total revenue by product category
SELECT p.category,
       SUM(oi.quantity * oi.unit_price) AS total_revenue
FROM order_items oi
INNER JOIN products p ON oi.product_id = p.product_id
INNER JOIN orders o ON oi.order_id = o.order_id
WHERE o.status = 'completed'
GROUP BY p.category
ORDER BY total_revenue DESC;

What does this query do?

  • SUM(oi.quantity * oi.unit_price) calculates revenue per line item (quantity times price), then sums all line items in each group.
  • INNER JOIN connects order_items to products (to get the category) and to orders (to filter by status). Only rows that match in both tables are included.
  • WHERE o.status = 'completed' excludes cancelled and pending orders from the revenue calculation. Counting unfinished orders would inflate the numbers.
  • GROUP BY p.category creates one summary row per category (Electronics, Clothing, Home).
  • Highlight Q3 and execute it.
  • You should see 3 rows, one per category, with positive revenue totals. The highest-revenue category appears first thanks to ORDER BY total_revenue DESC.

Getting an "Unknown column" error?

  • Check that your table aliases match. oi refers to order_items, p refers to products, and o refers to orders.
  • Make sure the ON clauses use the correct column names: oi.product_id = p.product_id and oi.order_id = o.order_id.

Help me fix my JOIN query error.

Your manager also wants to know which customers are repeat buyers. The HAVING clause filters groups after aggregation, unlike WHERE which filters individual rows before grouping.

  • Add the following query below Q3 to find customers with 3 or more orders:
-- Q4: Customers with 3 or more orders
SELECT c.first_name, c.last_name,
       COUNT(o.order_id) AS order_count
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.first_name, c.last_name
HAVING COUNT(o.order_id) >= 3
ORDER BY order_count DESC;

Why HAVING instead of WHERE?

WHERE filters individual rows before any grouping happens. HAVING filters after the groups are calculated. Since order_count only exists after the GROUP BY runs, you need HAVING to filter on it.

This is a common interview question for Business Analysts. If you remember one thing: WHERE is for raw rows, HAVING is for grouped results.

  • Highlight Q4 and execute it.
  • You should see several customers listed, each with an order_count of 3 or higher. Sarah Chen should appear near the top with the most orders.
Write multi-table JOIN queries

Real business questions rarely live in a single table. Your manager wants a full customer spending report that pulls names from customers, order info from orders, and line-item totals from order_items.

  • Add the following query below Q4 to show each customer's name, total orders, and total spending:
-- ============================================
-- MULTI-TABLE JOINS
-- ============================================

-- Q5: Each customer's name, total orders, and total spending
SELECT c.first_name, c.last_name,
       COUNT(DISTINCT o.order_id) AS total_orders,
       SUM(oi.quantity * oi.unit_price) AS total_spent
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
INNER JOIN order_items oi ON o.order_id = oi.order_id
WHERE o.status = 'completed'
GROUP BY c.customer_id, c.first_name, c.last_name
ORDER BY total_spent DESC;

Why COUNT(DISTINCT o.order_id)?

One order can have multiple line items in order_items. Without DISTINCT, the count would inflate because a single order gets counted once per line item. DISTINCT ensures each order is counted exactly once.

  • Highlight Q5 and execute it.
  • You should see a ranked list of customers by spending. Each row shows their name, how many completed orders they placed, and the total dollar amount.

Spending totals look too high?

  • If you removed DISTINCT from COUNT(DISTINCT o.order_id), the order count inflates. The spending total itself is correct because SUM already handles duplicates through multiplication.
  • If you forgot WHERE o.status = 'completed', cancelled orders are included in the totals.

Help me understand why my spending numbers are wrong.

Finally, your manager wants to know if any products have never sold. A LEFT JOIN keeps all rows from the left table even when there is no match on the right. Combined with WHERE IS NULL, it reveals orphan records.

  • Add the final query below Q5 to find products that have never been ordered:
-- Q6: Products that have never been ordered
SELECT p.product_name, p.category, p.price
FROM products p
LEFT JOIN order_items oi ON p.product_id = oi.product_id
WHERE oi.item_id IS NULL;

How does LEFT JOIN with IS NULL work?

LEFT JOIN returns every product, even those with no matching row in order_items. For unmatched products, all order_items columns come back as NULL.

WHERE oi.item_id IS NULL then keeps only those unmatched rows. This pattern is a classic interview question for finding "records with no relationship" (customers who never ordered, employees with no projects, etc.).

  • Highlight Q6 and execute it.
  • Your result grid should show 0 rows. This means every product in NovaMart's catalog has been ordered at least once. The query works correctly even when the answer is "none."
  • Save this file by pressing Ctrl+S. Name it 04_business_queries.sql.

Query returns rows you did not expect?

  • Make sure you used LEFT JOIN, not INNER JOIN. An INNER JOIN would return only products that HAVE been ordered.
  • Check that the WHERE clause references a column from the right-side table (oi.item_id IS NULL), not the left.

Help me understand LEFT JOIN with IS NULL.

✔️ Awesome, I've got everything!

Great. Double check you saved 04_business_queries.sql with all 6 queries.

ⓧ I'd like to double check the full code

-- ============================================
-- BASIC FILTERING AND SORTING
-- ============================================

-- Q1: Show all completed orders from Q1 2025, sorted by date
SELECT order_id, customer_id, order_date, status
FROM orders
WHERE status = 'completed'
  AND order_date BETWEEN '2025-01-01' AND '2025-03-31'
ORDER BY order_date;

-- Q2: Find all customers in Toronto or Vancouver
SELECT first_name, last_name, email, city
FROM customers
WHERE city IN ('Toronto', 'Vancouver');

-- ============================================
-- AGGREGATIONS WITH GROUP BY
-- ============================================

-- Q3: Total revenue by product category
SELECT p.category,
       SUM(oi.quantity * oi.unit_price) AS total_revenue
FROM order_items oi
INNER JOIN products p ON oi.product_id = p.product_id
INNER JOIN orders o ON oi.order_id = o.order_id
WHERE o.status = 'completed'
GROUP BY p.category
ORDER BY total_revenue DESC;

-- Q4: Customers with 3 or more orders
SELECT c.first_name, c.last_name,
       COUNT(o.order_id) AS order_count
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.first_name, c.last_name
HAVING COUNT(o.order_id) >= 3
ORDER BY order_count DESC;

-- ============================================
-- MULTI-TABLE JOINS
-- ============================================

-- Q5: Each customer's name, total orders, and total spending
SELECT c.first_name, c.last_name,
       COUNT(DISTINCT o.order_id) AS total_orders,
       SUM(oi.quantity * oi.unit_price) AS total_spent
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
INNER JOIN order_items oi ON o.order_id = oi.order_id
WHERE o.status = 'completed'
GROUP BY c.customer_id, c.first_name, c.last_name
ORDER BY total_spent DESC;

-- Q6: Products that have never been ordered
SELECT p.product_name, p.category, p.price
FROM products p
LEFT JOIN order_items oi ON p.product_id = oi.product_id
WHERE oi.item_id IS NULL;

You now have 6 reusable business queries covering filtering, aggregation, and joins. Next up, you will add date-based analysis and subqueries to answer even more complex questions about NovaMart's performance over time.

Time-Based Analysis and Subqueries

Your queries so far can filter, aggregate, and combine data across tables. Your manager is impressed, but now needs answers that involve time and multi-step logic. "What is our month-over-month revenue?" and "Who spends more than average?" are the kinds of questions that require a different approach.

In this step, you will use date functions like YEAR() and MONTH() to group data by time periods. You will also write subqueries, which are queries nested inside other queries, to answer questions that need two calculations in sequence.

In this step, get ready to:
  • Write date-based queries to track monthly revenue and customer signup trends.
  • Write subqueries to answer multi-step business questions like above-average spenders and category revenue share.
Write date-based business queries

One of the most common requests from stakeholders is trend data. "Is revenue going up or down?" requires grouping sales by month. MySQL's YEAR() and MONTH() functions extract those parts from a date column so you can group rows by time period.

  • In your 04_business_queries.sql file in MySQL Workbench, scroll to the bottom after Q6.
  • Add the time-based analysis section header and Q7 by pasting the following code below Q6:
-- ============================================
-- TIME-BASED ANALYSIS
-- ============================================

-- Q7: Monthly revenue for 2025
SELECT YEAR(o.order_date) AS order_year,
       MONTH(o.order_date) AS order_month,
       SUM(oi.quantity * oi.unit_price) AS monthly_revenue
FROM orders o
INNER JOIN order_items oi ON o.order_id = oi.order_id
WHERE o.status = 'completed'
GROUP BY YEAR(o.order_date), MONTH(o.order_date)
ORDER BY order_year, order_month;

What does this query do?

  • YEAR(o.order_date) and MONTH(o.order_date) extract the year and month number from each order's date.
  • SUM(oi.quantity * oi.unit_price) calculates total revenue for each month by multiplying quantity by price for every line item.
  • The WHERE o.status = 'completed' filter excludes cancelled and pending orders from the revenue calculation.
  • GROUP BY groups all order items into year-month buckets so the SUM produces one total per month.
  • Highlight just Q7 (the SELECT statement) and click the lightning bolt icon to execute it.
  • You should see 6 rows in the results grid, one for each month from January through June 2025, each with a revenue total.

Seeing 0 rows or an error?

  • Make sure you highlighted only the Q7 SELECT statement before clicking execute. If you highlight the comment lines without the query, nothing runs.
  • Check that your GROUP BY clause uses the same functions as your SELECT. Using column aliases in GROUP BY does not work in MySQL.

Help me debug my monthly revenue query.

Now for your manager's second time-based question: which month brought in the most new customers?

  • Add Q8 directly below Q7 by pasting this code:
-- Q8: Month with the most new customer signups
SELECT MONTH(signup_date) AS signup_month,
       COUNT(*) AS new_customers
FROM customers
GROUP BY MONTH(signup_date)
ORDER BY new_customers DESC
LIMIT 1;

What does this query do?

  • MONTH(signup_date) extracts the month number from each customer's signup date.
  • COUNT(*) counts how many customers signed up in each month.
  • ORDER BY new_customers DESC sorts from highest to lowest, and LIMIT 1 returns only the top result.
  • Highlight Q8 and execute it.
  • You should see a single row showing which month number had the most signups and how many new customers joined that month.

Getting more than one row?

Make sure you included LIMIT 1 at the end of the query. Without it, you will see all months listed instead of just the top one.

Write subqueries for complex business logic

Some business questions require two steps of calculation. "Who spends more than average?" first requires calculating what the average is, then comparing each customer against it. A subquery is a query nested inside another query that handles this multi-step logic in a single statement.

  • Add the subqueries section header and Q9 below Q8 by pasting this code:
-- ============================================
-- SUBQUERIES
-- ============================================

-- Q9: Customers whose spending exceeds the average
SELECT c.first_name, c.last_name, customer_totals.total_spent
FROM (
    SELECT o.customer_id,
           SUM(oi.quantity * oi.unit_price) AS total_spent
    FROM orders o
    INNER JOIN order_items oi ON o.order_id = oi.order_id
    WHERE o.status = 'completed'
    GROUP BY o.customer_id
) AS customer_totals
INNER JOIN customers c ON customer_totals.customer_id = c.customer_id
WHERE customer_totals.total_spent > (
    SELECT AVG(sub.total_spent)
    FROM (
        SELECT SUM(oi.quantity * oi.unit_price) AS total_spent
        FROM orders o
        INNER JOIN order_items oi ON o.order_id = oi.order_id
        WHERE o.status = 'completed'
        GROUP BY o.customer_id
    ) AS sub
)
ORDER BY customer_totals.total_spent DESC;

What does this query do?

This query uses two subqueries working together:

  • The first subquery (aliased as customer_totals) calculates total spending per customer from completed orders. It acts like a temporary table that the outer query selects from.
  • The second subquery (inside the WHERE clause) calculates the average of all per-customer totals. This gives you the "average customer spend" benchmark.
  • The outer query filters to only show customers whose total exceeds that average, then joins to the customers table to get their names.
  • Highlight Q9 (the entire SELECT statement including the nested subqueries) and execute it.
  • You should see a list of customer names and their total spending, where every row shows a total above the average. The results are sorted from highest spender to lowest.

Seeing a syntax error?

  • Every subquery must be wrapped in parentheses and given an alias with AS. Check that you have ) AS customer_totals and ) AS sub closing the inner queries.
  • Make sure you highlighted the entire Q9 statement from the first SELECT down to the closing semicolon. Partial highlights will cause incomplete query errors.

Help me fix my subquery syntax.

Your final query answers a question every executive asks in quarterly reviews: what percentage of total revenue does each product category contribute?

  • Add Q10 below Q9 by pasting this code:
-- Q10: Revenue percentage by category
SELECT p.category,
       SUM(oi.quantity * oi.unit_price) AS category_revenue,
       ROUND(
           SUM(oi.quantity * oi.unit_price) * 100.0 / (
               SELECT SUM(oi2.quantity * oi2.unit_price)
               FROM order_items oi2
               INNER JOIN orders o2 ON oi2.order_id = o2.order_id
               WHERE o2.status = 'completed'
           ), 1
       ) AS revenue_percentage
FROM order_items oi
INNER JOIN products p ON oi.product_id = p.product_id
INNER JOIN orders o ON oi.order_id = o.order_id
WHERE o.status = 'completed'
GROUP BY p.category
ORDER BY revenue_percentage DESC;

What does this query do?

  • The subquery inside ROUND() calculates the grand total revenue across all completed orders. This single number becomes the denominator for the percentage calculation.
  • The outer query calculates each category's revenue, then divides it by the grand total and multiplies by 100 to get a percentage.
  • ROUND(..., 1) rounds the result to one decimal place for clean reporting.
  • Highlight Q10 and execute it.
  • You should see 3 rows (one per category) with each category's revenue and its percentage of the total. Add the revenue_percentage values together. They should sum to approximately 100%.

Percentages not adding up to 100?

  • Because of rounding, the three values may sum to 99.9 or 100.1. This is normal and expected.
  • If the values are wildly off (e.g., each row shows over 100%), check that your subquery uses different aliases (oi2 and o2) so the inner query does not accidentally reference the outer query's tables.

Help me understand why my percentages are wrong.

✔️ Awesome, I've got everything!

Great. Double check you have saved 04_business_queries.sql with all 10 queries.

ⓧ I'd like to double check the full code

Here is the complete 04_business_queries.sql file with all 10 queries. Compare yours against this:

-- ============================================
-- BASIC FILTERING AND SORTING
-- ============================================

-- Q1: Show all completed orders from Q1 2025, sorted by date
SELECT order_id, customer_id, order_date, status
FROM orders
WHERE status = 'completed'
  AND order_date BETWEEN '2025-01-01' AND '2025-03-31'
ORDER BY order_date;

-- Q2: Find all customers in Toronto or Vancouver
SELECT first_name, last_name, email, city
FROM customers
WHERE city IN ('Toronto', 'Vancouver');

-- ============================================
-- AGGREGATIONS WITH GROUP BY
-- ============================================

-- Q3: Total revenue by product category
SELECT p.category,
       SUM(oi.quantity * oi.unit_price) AS total_revenue
FROM order_items oi
INNER JOIN products p ON oi.product_id = p.product_id
INNER JOIN orders o ON oi.order_id = o.order_id
WHERE o.status = 'completed'
GROUP BY p.category
ORDER BY total_revenue DESC;

-- Q4: Customers with 3 or more orders
SELECT c.first_name, c.last_name,
       COUNT(o.order_id) AS order_count
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.first_name, c.last_name
HAVING COUNT(o.order_id) >= 3
ORDER BY order_count DESC;

-- ============================================
-- MULTI-TABLE JOINS
-- ============================================

-- Q5: Each customer's name, total orders, and total spending
SELECT c.first_name, c.last_name,
       COUNT(DISTINCT o.order_id) AS total_orders,
       SUM(oi.quantity * oi.unit_price) AS total_spent
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
INNER JOIN order_items oi ON o.order_id = oi.order_id
WHERE o.status = 'completed'
GROUP BY c.customer_id, c.first_name, c.last_name
ORDER BY total_spent DESC;

-- Q6: Products that have never been ordered
SELECT p.product_name, p.category, p.price
FROM products p
LEFT JOIN order_items oi ON p.product_id = oi.product_id
WHERE oi.item_id IS NULL;

-- ============================================
-- TIME-BASED ANALYSIS
-- ============================================

-- Q7: Monthly revenue for 2025
SELECT YEAR(o.order_date) AS order_year,
       MONTH(o.order_date) AS order_month,
       SUM(oi.quantity * oi.unit_price) AS monthly_revenue
FROM orders o
INNER JOIN order_items oi ON o.order_id = oi.order_id
WHERE o.status = 'completed'
GROUP BY YEAR(o.order_date), MONTH(o.order_date)
ORDER BY order_year, order_month;

-- Q8: Month with the most new customer signups
SELECT MONTH(signup_date) AS signup_month,
       COUNT(*) AS new_customers
FROM customers
GROUP BY MONTH(signup_date)
ORDER BY new_customers DESC
LIMIT 1;

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

-- Q9: Customers whose spending exceeds the average
SELECT c.first_name, c.last_name, customer_totals.total_spent
FROM (
    SELECT o.customer_id,
           SUM(oi.quantity * oi.unit_price) AS total_spent
    FROM orders o
    INNER JOIN order_items oi ON o.order_id = oi.order_id
    WHERE o.status = 'completed'
    GROUP BY o.customer_id
) AS customer_totals
INNER JOIN customers c ON customer_totals.customer_id = c.customer_id
WHERE customer_totals.total_spent > (
    SELECT AVG(sub.total_spent)
    FROM (
        SELECT SUM(oi.quantity * oi.unit_price) AS total_spent
        FROM orders o
        INNER JOIN order_items oi ON o.order_id = oi.order_id
        WHERE o.status = 'completed'
        GROUP BY o.customer_id
    ) AS sub
)
ORDER BY customer_totals.total_spent DESC;

-- Q10: Revenue percentage by category
SELECT p.category,
       SUM(oi.quantity * oi.unit_price) AS category_revenue,
       ROUND(
           SUM(oi.quantity * oi.unit_price) * 100.0 / (
               SELECT SUM(oi2.quantity * oi2.unit_price)
               FROM order_items oi2
               INNER JOIN orders o2 ON oi2.order_id = o2.order_id
               WHERE o2.status = 'completed'
           ), 1
       ) AS revenue_percentage
FROM order_items oi
INNER JOIN products p ON oi.product_id = p.product_id
INNER JOIN orders o ON oi.order_id = o.order_id
WHERE o.status = 'completed'
GROUP BY p.category
ORDER BY revenue_percentage DESC;

Secret mission

Window Functions for Advanced Analytics

Your manager just asked: 'Can you show me which product is the top seller in each category, and how our revenue is trending month over month?' Standard GROUP BY won't cut it here. Time to unlock window functions.

Clean Up Your Resources

Clean Up Your Resources

This project runs entirely locally on your machine, so there are no ongoing costs. Decide whether to keep your database for future practice or remove it entirely.

Resources you used:

  • A local MySQL database called novamart with 4 tables (customers, products, orders, order_items).
  • SQL script files saved in MySQL Workbench (01_create_database.sql through 05_window_functions.sql).

Keep everything running

No action needed. Choose this if you want to keep practicing queries or adding more data to your database.

  • Your novamart database takes minimal disk space and costs nothing to keep.
  • You can reopen MySQL Workbench at any time to run new queries against your data.
  • Consider adding more rows to practice with larger datasets or writing new queries for different business questions.

Pause - I'll come back to this later

Stop the MySQL service to free up memory while keeping your database intact for later.

  • Close MySQL Workbench.
  • Open the Windows search bar by pressing the Windows key.
  • Type Services and press Enter.
  • Find MySQL80 in the list, right-click it, and select Stop.
  • When you want to resume, return to Services and right-click MySQL80 then select Start.

Delete - I don't want to use this again

Remove the database and all its data permanently. You can always recreate it by re-running your SQL scripts.

  • In MySQL Workbench, open a new query tab and run this command to drop the database:
DROP DATABASE novamart;
  • This removes the novamart database and all 4 tables along with their data.
  • Delete any saved SQL script files from your local file system if you no longer need them.
  • MySQL Workbench and MySQL Server can remain installed for future projects. They take minimal space and do not run background processes when Workbench is closed.

Nice Work!

Nice Work!

You just built a complete e-commerce database from scratch and wrote a portfolio of business queries that answer real stakeholder questions. These are the exact SQL skills that Business Analyst interviewers test for.

You've learned how to:

  • Build a 4-table relational database with primary keys, foreign keys, and proper data types to model real e-commerce relationships.
  • Write business queries covering the 5 core SQL patterns: filtering with WHERE, aggregations with GROUP BY/HAVING, multi-table JOINs, date analysis with YEAR() and MONTH(), and subqueries.
  • Translate stakeholder questions into actionable data insights, including revenue by category, top customers by lifetime value, and month-over-month trends.
  • Secret Mission: Applied window functions (RANK, SUM OVER, LAG) to rank products within categories and calculate running revenue totals with month-over-month growth.

Ready to quiz yourself?