Analyze E-Commerce Sales with SQL

Write SQL queries to uncover revenue trends and customer insights.

Introduction

30 Second Summary

Every time you buy something online, that purchase generates data. Someone at that company uses SQL to turn those transactions into answers: which products are selling, which customers keep coming back, and where revenue is trending.

In this project, you will write SQL queries that analyze e-commerce sales data to answer real business questions like "Which product category generates the most revenue?" and "Who are our highest-value customers?"

What You'll Build

A complete SQL-based sales analysis of an e-commerce store, built from scratch in a free browser-based SQL environment.

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

  • A 4-table e-commerce database loaded with customers, products, orders, and order items.
  • 10+ SQL queries that answer real business questions about revenue, trends, and customer behavior.
  • A business summary report combining JOINs, aggregations, and subqueries to rank products and compare countries.
  • Secret Mission: Build a customer segmentation model that classifies customers as VIP, Regular, or Occasional based on their total spending.

Are there any prerequisites?

No installations or accounts required. This project uses SQLite Online, a free browser-based tool. All you need is a web browser.

Set Up Your SQL Environment and Database

Before you can analyze sales trends or find top customers, you need data to work with. Every database starts with a structure (tables and columns) that defines what kind of data it holds.

In this step, you'll build a complete e-commerce database from scratch. You'll create four related tables and populate them with realistic order data for 15 customers and 10 products.

In this step, get ready to:
  • Create 4 related tables to define your database structure.
  • Insert sample e-commerce data including customers, products, orders, and order items.
  • Verify that all data loaded correctly using SELECT queries.
Create your database tables

A database needs a schema before it can hold any data. Your schema defines what tables exist, what columns they have, and how tables relate to each other.

  • In SQLite Online, clear any existing text in the editor panel.
  • Paste the following SQL into the editor to create all four tables:
-- Create the customers table
CREATE TABLE customers (
    customer_id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT NOT NULL,
    country TEXT NOT NULL,
    signup_date TEXT NOT NULL
);

-- Create the products table
CREATE TABLE products (
    product_id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    category TEXT NOT NULL,
    price REAL NOT NULL
);

-- Create the orders table
CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    order_date TEXT NOT NULL,
    status TEXT NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

-- Create the order_items table
CREATE TABLE order_items (
    item_id INTEGER PRIMARY KEY,
    order_id INTEGER NOT NULL,
    product_id INTEGER NOT NULL,
    quantity INTEGER NOT NULL,
    FOREIGN KEY (order_id) REFERENCES orders(order_id),
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

What does this code do?

This SQL creates four tables that model a real e-commerce store:

  • customers stores who is buying (name, email, country, signup date).
  • products stores what is for sale (name, category, price).
  • orders records each purchase event (which customer, when, status).
  • order_items records which products were in each order and how many.

The FOREIGN KEY lines tell the database that orders must reference a valid customer, and order_items must reference a valid order and product. This keeps your data consistent.

  • Click Run at the top of the page.
  • Check the left sidebar. You should see the four table names (customers, products, orders, order_items) appear in the database panel.

Tables not appearing in the sidebar?

  • Make sure you pasted all four CREATE TABLE statements, not just one.
  • Check for red error highlighting in the editor. A missing comma or parenthesis will cause the statement to fail.
  • If you see "table already exists," the tables were already created from a previous run. You can continue to the next substep.

Why aren't my tables showing up in SQLite Online?

✔️ Awesome, I've got everything!

Great. Your database schema is ready to hold data.

ⓧ I'd like to double check the full code

-- Create the customers table
CREATE TABLE customers (
    customer_id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT NOT NULL,
    country TEXT NOT NULL,
    signup_date TEXT NOT NULL
);

-- Create the products table
CREATE TABLE products (
    product_id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    category TEXT NOT NULL,
    price REAL NOT NULL
);

-- Create the orders table
CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    order_date TEXT NOT NULL,
    status TEXT NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

-- Create the order_items table
CREATE TABLE order_items (
    item_id INTEGER PRIMARY KEY,
    order_id INTEGER NOT NULL,
    product_id INTEGER NOT NULL,
    quantity INTEGER NOT NULL,
    FOREIGN KEY (order_id) REFERENCES orders(order_id),
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);
Insert sample e-commerce data

Empty tables are not useful for analysis. You need realistic records to query against. The data below represents a small online store with 15 customers across 7 countries, 10 products in 4 categories, and 35 orders placed throughout 2023.

  • Clear the editor in SQLite Online (select all text and delete it).
  • Paste the following SQL to insert all the sample data:
-- Insert customers
INSERT INTO customers (customer_id, name, email, country, signup_date) VALUES
(1, 'Alice Johnson', 'alice@email.com', 'USA', '2023-01-15'),
(2, 'Bob Smith', 'bob@email.com', 'USA', '2023-02-20'),
(3, 'Carlos Rivera', 'carlos@email.com', 'Mexico', '2023-03-10'),
(4, 'Diana Chen', 'diana@email.com', 'Canada', '2023-01-25'),
(5, 'Erik Larsson', 'erik@email.com', 'Sweden', '2023-04-05'),
(6, 'Fatima Hassan', 'fatima@email.com', 'UK', '2023-02-14'),
(7, 'George Park', 'george@email.com', 'USA', '2023-05-01'),
(8, 'Hannah Mueller', 'hannah@email.com', 'Germany', '2023-03-22'),
(9, 'Ivan Petrov', 'ivan@email.com', 'UK', '2023-06-10'),
(10, 'Julia Santos', 'julia@email.com', 'Brazil', '2023-04-18'),
(11, 'Kevin Okafor', 'kevin@email.com', 'Nigeria', '2023-07-02'),
(12, 'Laura Martinez', 'laura@email.com', 'Mexico', '2023-05-15'),
(13, 'Michael Brown', 'michael@email.com', 'USA', '2023-08-20'),
(14, 'Nadia Volkov', 'nadia@email.com', 'Canada', '2023-06-30'),
(15, 'Oscar Nguyen', 'oscar@email.com', 'USA', '2023-09-01');

-- Insert products
INSERT INTO products (product_id, name, category, price) VALUES
(1, 'Wireless Mouse', 'Electronics', 29.99),
(2, 'Mechanical Keyboard', 'Electronics', 89.99),
(3, 'USB-C Hub', 'Electronics', 49.99),
(4, 'Laptop Stand', 'Accessories', 39.99),
(5, 'Webcam HD', 'Electronics', 69.99),
(6, 'Desk Organizer', 'Accessories', 24.99),
(7, 'Monitor Light Bar', 'Accessories', 54.99),
(8, 'Noise Cancelling Headphones', 'Audio', 149.99),
(9, 'Bluetooth Speaker', 'Audio', 79.99),
(10, 'Ergonomic Chair Cushion', 'Furniture', 44.99);

-- Insert orders
INSERT INTO orders (order_id, customer_id, order_date, status) VALUES
(1, 1, '2023-03-01', 'completed'),
(2, 1, '2023-04-15', 'completed'),
(3, 2, '2023-03-20', 'completed'),
(4, 3, '2023-04-01', 'completed'),
(5, 4, '2023-03-25', 'completed'),
(6, 5, '2023-05-10', 'completed'),
(7, 1, '2023-06-01', 'completed'),
(8, 6, '2023-04-20', 'completed'),
(9, 7, '2023-06-15', 'completed'),
(10, 2, '2023-05-30', 'completed'),
(11, 8, '2023-05-05', 'completed'),
(12, 3, '2023-06-20', 'completed'),
(13, 9, '2023-07-01', 'completed'),
(14, 4, '2023-07-10', 'completed'),
(15, 10, '2023-06-05', 'completed'),
(16, 1, '2023-08-01', 'completed'),
(17, 11, '2023-08-15', 'completed'),
(18, 5, '2023-07-20', 'completed'),
(19, 12, '2023-08-05', 'completed'),
(20, 2, '2023-09-01', 'completed'),
(21, 6, '2023-08-25', 'completed'),
(22, 13, '2023-09-10', 'completed'),
(23, 14, '2023-09-15', 'completed'),
(24, 3, '2023-09-20', 'completed'),
(25, 7, '2023-10-01', 'completed'),
(26, 15, '2023-10-05', 'completed'),
(27, 4, '2023-10-15', 'completed'),
(28, 1, '2023-11-01', 'completed'),
(29, 8, '2023-10-20', 'cancelled'),
(30, 5, '2023-11-10', 'completed'),
(31, 2, '2023-11-15', 'pending'),
(32, 9, '2023-11-20', 'completed'),
(33, 11, '2023-11-25', 'completed'),
(34, 6, '2023-12-01', 'completed'),
(35, 10, '2023-12-05', 'completed');

-- Insert order items
INSERT INTO order_items (item_id, order_id, product_id, quantity) VALUES
(1, 1, 1, 1),
(2, 1, 2, 1),
(3, 2, 5, 1),
(4, 2, 4, 2),
(5, 3, 2, 1),
(6, 3, 3, 1),
(7, 4, 8, 1),
(8, 4, 1, 2),
(9, 5, 7, 1),
(10, 5, 6, 1),
(11, 6, 9, 1),
(12, 6, 10, 1),
(13, 7, 8, 1),
(14, 7, 2, 1),
(15, 8, 5, 1),
(16, 8, 3, 1),
(17, 9, 1, 2),
(18, 9, 4, 1),
(19, 10, 8, 1),
(20, 10, 7, 1),
(21, 11, 2, 1),
(22, 11, 6, 2),
(23, 12, 3, 1),
(24, 12, 9, 1),
(25, 13, 10, 2),
(26, 13, 1, 1),
(27, 14, 8, 1),
(28, 14, 5, 1),
(29, 15, 2, 1),
(30, 15, 4, 1),
(31, 16, 3, 2),
(32, 16, 7, 1),
(33, 17, 9, 1),
(34, 17, 6, 1),
(35, 18, 8, 1),
(36, 18, 1, 1),
(37, 19, 5, 1),
(38, 19, 10, 1),
(39, 20, 2, 1),
(40, 20, 3, 1),
(41, 21, 7, 2),
(42, 21, 4, 1),
(43, 22, 1, 1),
(44, 22, 9, 1),
(45, 23, 8, 1),
(46, 23, 6, 1),
(47, 24, 2, 1),
(48, 24, 10, 1),
(49, 25, 5, 1),
(50, 25, 3, 1),
(51, 26, 8, 1),
(52, 26, 4, 1),
(53, 27, 1, 2),
(54, 27, 7, 1),
(55, 28, 2, 1),
(56, 28, 8, 1),
(57, 29, 3, 1),
(58, 29, 9, 1),
(59, 30, 5, 1),
(60, 30, 6, 1),
(61, 31, 8, 1),
(62, 31, 2, 1),
(63, 32, 7, 1),
(64, 32, 10, 1),
(65, 33, 2, 1),
(66, 33, 3, 1),
(67, 34, 8, 1),
(68, 34, 1, 1),
(69, 35, 5, 1),
(70, 35, 9, 1);

What does this data represent?

You just loaded a complete e-commerce dataset:

  • 15 customers from 7 different countries (USA, Mexico, Canada, Sweden, UK, Germany, Brazil, Nigeria).
  • 10 products across 4 categories (Electronics, Accessories, Audio, Furniture).
  • 35 orders placed between March and December 2023, with most completed but a few cancelled or pending.
  • 70 order items linking specific products to specific orders with quantities.

The order_items table is the bridge between orders and products. One order can contain multiple products, and one product can appear in multiple orders. This is how real e-commerce databases work.

  • Click Run.
  • The editor should complete without any red error highlighting. If you see the output panel with no error messages, your data is loaded.

Seeing an error about a missing table?

  • This means the CREATE TABLE statements from the previous substep did not run successfully. Go back, paste the CREATE TABLE code, and click Run before trying the INSERT statements again.
  • If you see a "UNIQUE constraint failed" error, the data was already inserted from a previous run. You can move on to the verification step.

Help me fix my INSERT INTO errors in SQLite Online.

✔️ Awesome, I've got everything!

Great. Your database now has all the data you need for analysis.

ⓧ I'd like to double check the full code

-- Insert customers
INSERT INTO customers (customer_id, name, email, country, signup_date) VALUES
(1, 'Alice Johnson', 'alice@email.com', 'USA', '2023-01-15'),
(2, 'Bob Smith', 'bob@email.com', 'USA', '2023-02-20'),
(3, 'Carlos Rivera', 'carlos@email.com', 'Mexico', '2023-03-10'),
(4, 'Diana Chen', 'diana@email.com', 'Canada', '2023-01-25'),
(5, 'Erik Larsson', 'erik@email.com', 'Sweden', '2023-04-05'),
(6, 'Fatima Hassan', 'fatima@email.com', 'UK', '2023-02-14'),
(7, 'George Park', 'george@email.com', 'USA', '2023-05-01'),
(8, 'Hannah Mueller', 'hannah@email.com', 'Germany', '2023-03-22'),
(9, 'Ivan Petrov', 'ivan@email.com', 'UK', '2023-06-10'),
(10, 'Julia Santos', 'julia@email.com', 'Brazil', '2023-04-18'),
(11, 'Kevin Okafor', 'kevin@email.com', 'Nigeria', '2023-07-02'),
(12, 'Laura Martinez', 'laura@email.com', 'Mexico', '2023-05-15'),
(13, 'Michael Brown', 'michael@email.com', 'USA', '2023-08-20'),
(14, 'Nadia Volkov', 'nadia@email.com', 'Canada', '2023-06-30'),
(15, 'Oscar Nguyen', 'oscar@email.com', 'USA', '2023-09-01');

-- Insert products
INSERT INTO products (product_id, name, category, price) VALUES
(1, 'Wireless Mouse', 'Electronics', 29.99),
(2, 'Mechanical Keyboard', 'Electronics', 89.99),
(3, 'USB-C Hub', 'Electronics', 49.99),
(4, 'Laptop Stand', 'Accessories', 39.99),
(5, 'Webcam HD', 'Electronics', 69.99),
(6, 'Desk Organizer', 'Accessories', 24.99),
(7, 'Monitor Light Bar', 'Accessories', 54.99),
(8, 'Noise Cancelling Headphones', 'Audio', 149.99),
(9, 'Bluetooth Speaker', 'Audio', 79.99),
(10, 'Ergonomic Chair Cushion', 'Furniture', 44.99);

-- Insert orders
INSERT INTO orders (order_id, customer_id, order_date, status) VALUES
(1, 1, '2023-03-01', 'completed'),
(2, 1, '2023-04-15', 'completed'),
(3, 2, '2023-03-20', 'completed'),
(4, 3, '2023-04-01', 'completed'),
(5, 4, '2023-03-25', 'completed'),
(6, 5, '2023-05-10', 'completed'),
(7, 1, '2023-06-01', 'completed'),
(8, 6, '2023-04-20', 'completed'),
(9, 7, '2023-06-15', 'completed'),
(10, 2, '2023-05-30', 'completed'),
(11, 8, '2023-05-05', 'completed'),
(12, 3, '2023-06-20', 'completed'),
(13, 9, '2023-07-01', 'completed'),
(14, 4, '2023-07-10', 'completed'),
(15, 10, '2023-06-05', 'completed'),
(16, 1, '2023-08-01', 'completed'),
(17, 11, '2023-08-15', 'completed'),
(18, 5, '2023-07-20', 'completed'),
(19, 12, '2023-08-05', 'completed'),
(20, 2, '2023-09-01', 'completed'),
(21, 6, '2023-08-25', 'completed'),
(22, 13, '2023-09-10', 'completed'),
(23, 14, '2023-09-15', 'completed'),
(24, 3, '2023-09-20', 'completed'),
(25, 7, '2023-10-01', 'completed'),
(26, 15, '2023-10-05', 'completed'),
(27, 4, '2023-10-15', 'completed'),
(28, 1, '2023-11-01', 'completed'),
(29, 8, '2023-10-20', 'cancelled'),
(30, 5, '2023-11-10', 'completed'),
(31, 2, '2023-11-15', 'pending'),
(32, 9, '2023-11-20', 'completed'),
(33, 11, '2023-11-25', 'completed'),
(34, 6, '2023-12-01', 'completed'),
(35, 10, '2023-12-05', 'completed');

-- Insert order items
INSERT INTO order_items (item_id, order_id, product_id, quantity) VALUES
(1, 1, 1, 1),
(2, 1, 2, 1),
(3, 2, 5, 1),
(4, 2, 4, 2),
(5, 3, 2, 1),
(6, 3, 3, 1),
(7, 4, 8, 1),
(8, 4, 1, 2),
(9, 5, 7, 1),
(10, 5, 6, 1),
(11, 6, 9, 1),
(12, 6, 10, 1),
(13, 7, 8, 1),
(14, 7, 2, 1),
(15, 8, 5, 1),
(16, 8, 3, 1),
(17, 9, 1, 2),
(18, 9, 4, 1),
(19, 10, 8, 1),
(20, 10, 7, 1),
(21, 11, 2, 1),
(22, 11, 6, 2),
(23, 12, 3, 1),
(24, 12, 9, 1),
(25, 13, 10, 2),
(26, 13, 1, 1),
(27, 14, 8, 1),
(28, 14, 5, 1),
(29, 15, 2, 1),
(30, 15, 4, 1),
(31, 16, 3, 2),
(32, 16, 7, 1),
(33, 17, 9, 1),
(34, 17, 6, 1),
(35, 18, 8, 1),
(36, 18, 1, 1),
(37, 19, 5, 1),
(38, 19, 10, 1),
(39, 20, 2, 1),
(40, 20, 3, 1),
(41, 21, 7, 2),
(42, 21, 4, 1),
(43, 22, 1, 1),
(44, 22, 9, 1),
(45, 23, 8, 1),
(46, 23, 6, 1),
(47, 24, 2, 1),
(48, 24, 10, 1),
(49, 25, 5, 1),
(50, 25, 3, 1),
(51, 26, 8, 1),
(52, 26, 4, 1),
(53, 27, 1, 2),
(54, 27, 7, 1),
(55, 28, 2, 1),
(56, 28, 8, 1),
(57, 29, 3, 1),
(58, 29, 9, 1),
(59, 30, 5, 1),
(60, 30, 6, 1),
(61, 31, 8, 1),
(62, 31, 2, 1),
(63, 32, 7, 1),
(64, 32, 10, 1),
(65, 33, 2, 1),
(66, 33, 3, 1),
(67, 34, 8, 1),
(68, 34, 1, 1),
(69, 35, 5, 1),
(70, 35, 9, 1);
Verify your data loaded correctly

Running a quick SELECT on each table confirms everything loaded as expected. This is a habit real analysts follow after any data load.

  • Clear the editor again.
  • Run the following query to check your customers table:
-- Verify each table has data
SELECT * FROM customers;
  • Click Run. You should see a table of 15 rows with columns for customer_id, name, email, country, and signup_date.
  • Now try the other three tables one at a time by replacing the query with each of these:
SELECT * FROM products;
SELECT * FROM orders;
SELECT * FROM order_items;

What should I see?

  • customers should return 15 rows.
  • products should return 10 rows.
  • orders should return 35 rows.
  • order_items should return 70 rows.

If any table returns 0 rows, the INSERT statements for that table did not run successfully. Go back to the previous substep and re-run the INSERT code.

Seeing "no such table" error?

  • This means the CREATE TABLE step was skipped or failed. Return to the first substep and run the CREATE TABLE statements before running any INSERT or SELECT.
  • If you refreshed the browser page, your database was reset. SQLite Online stores data in memory by default, so a page refresh clears everything. Start again from the CREATE TABLE statements.

Help me figure out why my SELECT query is failing.

Keep this browser tab open

SQLite Online stores your database in memory. If you close or refresh the tab, your tables and data will be lost. Keep this tab open for the rest of the project.

Your database is built and loaded with realistic e-commerce data. Next up, you'll start querying this data to answer real business questions.

Explore Your Data with Basic Queries

Your database is loaded with customers, products, orders, and order items. Now it's time to start asking questions of that data.

In this step, you'll write your first SQL queries to filter, sort, and count records. These are the building blocks every data analyst uses to explore a new dataset before diving into deeper analysis.

In this step, get ready to:
  • Filter orders and products using WHERE clauses.
  • Sort and limit results with ORDER BY and LIMIT.
  • Count records and group them using aggregate functions.
Filter data with SELECT and WHERE

A database with thousands of rows is useless if you can't zoom in on what matters. The WHERE clause lets you filter rows based on a condition, so you only see the data that matches your criteria.

  • Clear any existing text in the SQLite Online SQL editor.
  • Paste the following query and click Run to see only completed orders:
-- Filter orders by status
SELECT order_id, customer_id, order_date, status
FROM orders
WHERE status = 'completed';

What does this query do?

SELECT picks which columns to show in the results. FROM names the table to pull data from.

WHERE status = 'completed' filters the rows so only orders with a status of completed appear. Everything else is excluded from the results.

  • You should see 33 rows in the results table below the editor. Each row has a status of completed.

Seeing zero rows or an error?

Make sure the value completed is wrapped in single quotes. SQL uses single quotes for text values. Double quotes or no quotes will produce an error.

Also confirm that you ran your CREATE TABLE and INSERT statements from Step 1 successfully. If the tables are empty, re-run the code from the previous step first.

Help me fix my WHERE query in SQLite Online.

  • Now clear the editor, paste this next query, and click Run to filter products by category:
-- Filter products by category
SELECT name, category, price
FROM products
WHERE category = 'Electronics';

What changed in this query?

This time you're querying the products table instead of orders. The WHERE clause filters on category instead of status.

The pattern is always the same: WHERE column = 'value'. You can filter any table by any column this way.

  • You should see 4 rows: Wireless Mouse, Mechanical Keyboard, USB-C Hub, and Webcam HD. All have the category Electronics.
Sort and limit results with ORDER BY and LIMIT

Filtering shows you a subset of rows. Sorting lets you rank those rows so the most important ones appear first. Combined with LIMIT, you get a quick "top N" list.

  • Clear the editor, paste the following query, and click Run to find the 5 most expensive products:
-- Find the 5 most expensive products
SELECT name, category, price
FROM products
ORDER BY price DESC
LIMIT 5;

What does ORDER BY DESC do?

ORDER BY price DESC sorts the results from highest price to lowest. DESC means descending (big to small). If you used ASC instead, you'd get the cheapest items first.

LIMIT 5 cuts off the results after the first 5 rows. Without it, you'd see all 10 products.

  • You should see 5 rows with Noise Cancelling Headphones ($149.99) at the top and USB-C Hub ($49.99) at the bottom.

Results not sorted correctly?

Make sure you typed DESC after price. Without it, SQL defaults to ascending order (cheapest first).

Help me understand ORDER BY and LIMIT in SQL.

  • Clear the editor, paste this query, and click Run to find the 10 most recent orders:
-- Find the 10 most recent orders
SELECT order_id, customer_id, order_date, status
FROM orders
ORDER BY order_date DESC
LIMIT 10;

Sorting dates in SQL

SQLite stores dates as text in YYYY-MM-DD format. Because this format is alphabetically sortable, ORDER BY order_date DESC correctly puts the newest dates first.

  • You should see 10 rows starting with orders from December 2023 at the top. Notice the mix of statuses: most are completed, but you'll spot cancelled and pending orders too.
Get quick totals with COUNT and GROUP BY

Filtering and sorting show individual rows. But what if you need a single number, like "how many orders do we have?" That's where aggregate functions come in.

  • Clear the editor, paste this query, and click Run to count the total number of orders:
-- Count total number of orders
SELECT COUNT(*) AS total_orders
FROM orders;

What is COUNT(*)?

COUNT(*) counts every row in the table, regardless of what's in each column. The AS total_orders part gives the result column a readable name instead of a generic label.

This is called an aggregate function because it takes many rows and reduces them to a single value.

  • You should see one row with a single value: 35. That's the total number of orders across all customers.
  • Now clear the editor, paste this query, and click Run to count customers broken down by country:
-- Count customers per country
SELECT country, COUNT(*) AS customer_count
FROM customers
GROUP BY country
ORDER BY customer_count DESC;

What does GROUP BY do?

GROUP BY country splits all customer rows into groups based on their country. Then COUNT(*) counts how many customers are in each group.

Think of it like sorting a deck of cards by suit, then counting how many cards are in each pile. The result is one row per group with the count for that group.

  • You should see a list of countries with USA at the top (5 customers), followed by Mexico, Canada, and UK (2 each), then Sweden, Germany, Brazil, and Nigeria (1 each).

Getting a GROUP BY error?

If you see an error about columns not being in the GROUP BY clause, make sure the only columns in your SELECT are either listed in GROUP BY or wrapped in an aggregate function like COUNT().

Help me understand GROUP BY in SQL.

✔️ Awesome, I've got everything!

Great. Make sure you've run all six queries and can see results for each one.

ⓧ I'd like to double check the full code

Here is the complete set of basic queries from this step:

-- Filter orders by status
SELECT order_id, customer_id, order_date, status
FROM orders
WHERE status = 'completed';

-- Filter products by category
SELECT name, category, price
FROM products
WHERE category = 'Electronics';

-- Find the 5 most expensive products
SELECT name, category, price
FROM products
ORDER BY price DESC
LIMIT 5;

-- Find the 10 most recent orders
SELECT order_id, customer_id, order_date, status
FROM orders
ORDER BY order_date DESC
LIMIT 10;

-- Count total number of orders
SELECT COUNT(*) AS total_orders
FROM orders;

-- Count customers per country
SELECT country, COUNT(*) AS customer_count
FROM customers
GROUP BY country
ORDER BY customer_count DESC;

You've now explored your data from multiple angles using filters, sorting, and aggregation. Next up, you'll combine data from multiple tables using JOINs to answer real business questions like "how much revenue did each product category generate?"

Analyze Sales Performance with JOINs

Your basic queries from the last step showed you individual tables in isolation. But real business questions rarely live inside a single table.

To answer "how much revenue did we make?" you need to connect order records to the items in each order, and then look up each item's price from the products table. This is where JOINs come in. They let you combine rows from multiple tables based on a shared column.

In this step, get ready to:
  • Write a JOIN query that combines three tables to calculate total revenue.
  • Break down revenue by product category using GROUP BY.
  • Calculate monthly revenue trends to see how sales change over time.
Calculate total revenue with a three-table JOIN

Revenue lives across three tables. The orders table tells you which orders were completed. The order_items table tells you the quantity of each product in an order. The products table tells you each product's price.

  • Clear the editor in SQLite Online, then paste this query:
-- Calculate total revenue (joining orders, order_items, and products)
SELECT SUM(oi.quantity * p.price) AS total_revenue
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 o.status = 'completed';

What does this query do?

This query chains two JOIN clauses to connect three tables. The aliases o, oi, and p are shortcuts so you don't have to type full table names every time.

  • JOIN order_items oi ON o.order_id = oi.order_id links each order to the items purchased in it.
  • JOIN products p ON oi.product_id = p.product_id links each item to its product record (where the price lives).
  • SUM(oi.quantity * p.price) multiplies each item's quantity by its unit price, then adds everything up.
  • WHERE o.status = 'completed' excludes cancelled and pending orders from the revenue total.
  • Click **Run** to execute the query.
  • You should see a single row with a total_revenue value in the results panel below the editor.

Seeing an error or no results?

  • Make sure your database tables still have data. If you refreshed the page, SQLite Online resets. Re-run your CREATE TABLE and INSERT statements from earlier steps first.
  • Check that you have only this one query in the editor. If old queries are still there, they may conflict.

Why is my JOIN query returning an error?

What is a JOIN?

A JOIN combines rows from two or more tables based on a related column between them. Think of it like looking up a friend's phone number: you know their name (the shared column), so you can "join" your contact list to find the number that belongs to that name.

Without JOINs, you would only ever see one table at a time. JOINs are what make relational databases powerful for answering multi-dimensional questions.

Break down revenue by product category

Knowing the total revenue is useful, but a manager would immediately ask: which product categories are driving that revenue? GROUP BY lets you split results into groups and run aggregate functions on each group separately.

  • Clear the editor and paste this query to see revenue broken down by category:
-- Revenue by product category
SELECT p.category,
       SUM(oi.quantity * p.price) AS category_revenue,
       COUNT(DISTINCT o.order_id) AS total_orders
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 o.status = 'completed'
GROUP BY p.category
ORDER BY category_revenue DESC;

What does this query do?

  • GROUP BY p.category splits the results into one row per category (Electronics, Audio, Accessories, Furniture).
  • SUM(oi.quantity * p.price) AS category_revenue calculates the total revenue within each category group.
  • COUNT(DISTINCT o.order_id) AS total_orders counts how many unique orders included items from that category. DISTINCT prevents counting the same order twice when it has multiple items from one category.
  • ORDER BY category_revenue DESC sorts the results so the highest-earning category appears first.
  • Click **Run** to execute the query.
  • You should see 4 rows in the results panel, one for each product category, sorted from highest to lowest revenue.

Seeing fewer than 4 rows?

  • Make sure your products table has all 10 products inserted. Run SELECT DISTINCT category FROM products; to verify you have Electronics, Audio, Accessories, and Furniture.
  • Check that your order_items data was fully inserted. Run SELECT COUNT(*) FROM order_items; and confirm you get 70.

Help me debug my GROUP BY category query.

Calculate monthly revenue trends

Knowing total revenue by category is a snapshot. Business stakeholders also want to see trends over time. Is revenue growing month over month, or declining?

SQLite stores dates as text, so you need the substr() function to extract just the year and month portion (like 2023-03) from each order date.

  • Clear the editor and paste this query to see monthly revenue trends:
-- Monthly revenue trends
SELECT substr(o.order_date, 1, 7) AS month,
       SUM(oi.quantity * p.price) AS monthly_revenue,
       COUNT(DISTINCT o.order_id) AS orders_count
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 o.status = 'completed'
GROUP BY substr(o.order_date, 1, 7)
ORDER BY month;

What does this query do?

  • substr(o.order_date, 1, 7) extracts the first 7 characters from the date string. A date like 2023-03-01 becomes 2023-03.
  • GROUP BY substr(o.order_date, 1, 7) groups all orders from the same month together.
  • ORDER BY month sorts results chronologically so you can read the trend from earliest to latest.
  • Click **Run** to execute the query.
  • You should see 10 rows, one for each month from 2023-03 through 2023-12, each showing that month's total revenue and order count.

Getting a syntax error on substr?

  • Make sure you typed substr (not substring or SUBSTR() with a capital S is fine). The function name is case-insensitive in SQLite.
  • Check that the GROUP BY expression matches the SELECT expression exactly: both must say substr(o.order_date, 1, 7).

Help me fix my monthly revenue trend query.

Why substr instead of a DATE function?

SQLite stores dates as plain text strings in YYYY-MM-DD format. It doesn't have a built-in DATE type like PostgreSQL or MySQL. The substr() function is the standard SQLite approach for extracting parts of a date string.

✔️ Awesome, I've got everything!

Great. Make sure you have all three queries saved or noted down. You will build on this same JOIN pattern in the next steps.

ⓧ I'd like to double check the full code

Here is the complete set of sales analysis queries from this step:

-- Calculate total revenue (joining orders, order_items, and products)
SELECT SUM(oi.quantity * p.price) AS total_revenue
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 o.status = 'completed';

-- Revenue by product category
SELECT p.category,
       SUM(oi.quantity * p.price) AS category_revenue,
       COUNT(DISTINCT o.order_id) AS total_orders
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 o.status = 'completed'
GROUP BY p.category
ORDER BY category_revenue DESC;

-- Monthly revenue trends
SELECT substr(o.order_date, 1, 7) AS month,
       SUM(oi.quantity * p.price) AS monthly_revenue,
       COUNT(DISTINCT o.order_id) AS orders_count
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 o.status = 'completed'
GROUP BY substr(o.order_date, 1, 7)
ORDER BY month;

You now know how to combine multiple tables with JOINs and summarize data with GROUP BY. Next up, you will use these same techniques to discover which customers are spending the most and how their purchasing patterns differ.

Discover Customer Insights

You now know which product categories drive the most revenue and how sales trend over time. The next question a business will ask is: who are our most valuable customers?

In this step, you will calculate each customer's total lifetime spending, identify the top spenders, and measure how much each customer spends per order on average. These are the exact queries data analysts use to prioritize marketing spend and retention efforts.

In this step, get ready to:
  • Calculate Customer Lifetime Value by joining all 4 tables.
  • Find the top 5 customers by total spending.
  • Calculate the average order value per customer.
Calculate Customer Lifetime Value

Customer Lifetime Value (CLV) measures the total amount each customer has spent at your store across all their orders. To calculate it, you need to connect customer names to their orders, then to the individual items in each order, and finally to the product prices.

  • Clear the editor in SQLite Online and paste this query:
-- Customer Lifetime Value (total spending per customer)
SELECT c.name,
       c.country,
       SUM(oi.quantity * p.price) AS total_spent,
       COUNT(DISTINCT o.order_id) AS total_orders
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
WHERE o.status = 'completed'
GROUP BY c.customer_id, c.name, c.country
ORDER BY total_spent DESC;

What does this query do?

  • The query joins all 4 tables: customers (aliased as c), orders (o), order_items (oi), and products (p). This connects each customer to the actual prices they paid.
  • SUM(oi.quantity * p.price) AS total_spent multiplies quantity by price for every item a customer bought, then adds them all up.
  • COUNT(DISTINCT o.order_id) AS total_orders counts how many separate orders each customer placed.
  • GROUP BY c.customer_id, c.name, c.country groups all the rows belonging to each customer so the SUM and COUNT operate per person.
  • Click Run to execute the query.
  • You should see a results table listing all 15 customers ranked by total spending. Alice Johnson should appear at the top with 5 orders and approximately $904.88 in total spending.

Seeing fewer than 15 customers?

The WHERE o.status = 'completed' filter excludes cancelled and pending orders. If you see 0 rows, make sure you ran the INSERT statements from Step 1 successfully. If you see an error about a missing column, double-check the table aliases match (c for customers, o for orders, etc.).

Help me debug my Customer Lifetime Value query

Find the top 5 customers by total spending

Now that you can see every customer's spending, the next business question is: who are the top 5 highest-value customers? This is a common request from marketing teams who want to target their best customers with loyalty programs.

  • Clear the editor and paste this query:
-- Top 5 customers by total spending
SELECT c.name,
       c.country,
       SUM(oi.quantity * p.price) AS total_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
JOIN products p ON oi.product_id = p.product_id
WHERE o.status = 'completed'
GROUP BY c.customer_id, c.name, c.country
ORDER BY total_spent DESC
LIMIT 5;

What changed from the previous query?

This query is almost identical to the CLV query above. The only difference is LIMIT 5 at the end, which cuts the result to only the top 5 rows. Since the results are already sorted by total_spent DESC, those 5 rows are your highest spenders.

The total_orders column was also removed since this query focuses on spending amount only.

  • Click Run.
  • You should see exactly 5 rows. The results include each customer's name, country, and total amount spent.

Seeing more than 5 rows?

Make sure LIMIT 5 appears on the very last line of your query, after the ORDER BY clause. If you accidentally left the previous query in the editor, clear it completely before pasting the new one.

Help me fix my top 5 customers query

Calculate average order value per customer

Knowing total spending is useful, but it does not tell you whether a customer places many small orders or a few large ones. Average order value reveals this purchasing pattern.

  • Clear the editor and paste this query:
-- Average order value and orders per customer
SELECT c.name,
       COUNT(DISTINCT o.order_id) AS num_orders,
       ROUND(SUM(oi.quantity * p.price) / COUNT(DISTINCT o.order_id), 2) AS avg_order_value
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
WHERE o.status = 'completed'
GROUP BY c.customer_id, c.name
ORDER BY num_orders DESC;

What does this query do?

  • ROUND(SUM(oi.quantity * p.price) / COUNT(DISTINCT o.order_id), 2) divides total spending by the number of orders, giving the average spent per order. ROUND(..., 2) rounds the result to 2 decimal places so it reads like a clean dollar amount.
  • The results are sorted by num_orders DESC so frequent buyers appear first. This lets you compare whether frequent buyers also tend to have high average order values.
  • Click Run.
  • You should see all customers listed with their order count and average order value. Alice Johnson should appear at the top with the most orders.

Seeing NULL or 0 for avg_order_value?

Check that your JOIN conditions connect the tables correctly. The chain should go customers → orders → order_items → products. If any link is broken, the multiplication oi.quantity * p.price will not have the data it needs.

Help me debug my average order value query

Why is Customer Lifetime Value important?

CLV is one of the most-requested metrics in e-commerce analytics. It tells businesses how much revenue each customer generates over time. Companies use this to decide how much to spend on acquiring new customers versus retaining existing ones.

When a company knows its top spenders, it can offer them loyalty perks, early access to sales, or personalized recommendations. The queries you just wrote are the foundation for that kind of analysis.

✔️ Awesome, I've got everything!

Great. Make sure you have run all three customer insight queries successfully before moving on.

ⓧ I'd like to double check the full code

Here is the complete set of customer insight queries. Compare your results against this:

-- Customer Lifetime Value (total spending per customer)
SELECT c.name,
       c.country,
       SUM(oi.quantity * p.price) AS total_spent,
       COUNT(DISTINCT o.order_id) AS total_orders
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
WHERE o.status = 'completed'
GROUP BY c.customer_id, c.name, c.country
ORDER BY total_spent DESC;

-- Top 5 customers by total spending
SELECT c.name,
       c.country,
       SUM(oi.quantity * p.price) AS total_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
JOIN products p ON oi.product_id = p.product_id
WHERE o.status = 'completed'
GROUP BY c.customer_id, c.name, c.country
ORDER BY total_spent DESC
LIMIT 5;

-- Average order value and orders per customer
SELECT c.name,
       COUNT(DISTINCT o.order_id) AS num_orders,
       ROUND(SUM(oi.quantity * p.price) / COUNT(DISTINCT o.order_id), 2) AS avg_order_value
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
WHERE o.status = 'completed'
GROUP BY c.customer_id, c.name
ORDER BY num_orders DESC;

You now know exactly who your best customers are and how they shop. Next up, you will build a full business summary report that ranks products, compares country performance, and calculates each category's share of total revenue.

Build a Business Summary Report

In the previous step, you uncovered who your highest-value customers are and how much they spend on average. Now it's time to pull everything together into a business summary report.

A summary report answers the big-picture questions that executives and stakeholders care about: Which products drive the most revenue? Which countries are our strongest markets? How does each category contribute to the bottom line? In this step, you will write three queries that combine all the SQL techniques you have learned so far.

In this step, get ready to:
  • Rank every product by total revenue.
  • Build a country performance summary with customers, orders, and revenue.
  • Calculate each category's percentage of total revenue using a subquery.
Rank products by total revenue

A product ranking shows which items generate the most money for the business. This query joins all four tables and groups results by individual product, calculating both the revenue and the number of units sold.

  • Clear the editor in SQLite Online and paste the following query:
-- Product ranking by total revenue
SELECT p.name AS product_name,
       p.category,
       SUM(oi.quantity * p.price) AS product_revenue,
       SUM(oi.quantity) AS units_sold
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 o.status = 'completed'
GROUP BY p.product_id, p.name, p.category
ORDER BY product_revenue DESC;

What does this query do?

  • SUM(oi.quantity * p.price) AS product_revenue calculates the total revenue each product has generated across all completed orders.
  • SUM(oi.quantity) AS units_sold counts the total number of units sold for each product.
  • GROUP BY p.product_id, p.name, p.category groups by product_id to ensure each product gets its own row, even if two products share the same name.
  • ORDER BY product_revenue DESC sorts from highest revenue to lowest, so your top performer appears first.
  • Click Run to execute the query.
  • You should see a table listing all 10 products ranked by revenue, with Noise Cancelling Headphones at the top.

Not seeing 10 rows?

Make sure your WHERE clause filters for o.status = 'completed' and not oi.status. The status column lives on the orders table, not on order_items.

Help me debug my product ranking query

Create a country performance summary

Businesses often need to compare performance across geographic markets. This query answers the question: which countries have the most customers, orders, and revenue?

  • Clear the editor and paste the following query:
-- Country performance summary
SELECT c.country,
       COUNT(DISTINCT c.customer_id) AS total_customers,
       COUNT(DISTINCT o.order_id) AS total_orders,
       ROUND(SUM(oi.quantity * p.price), 2) AS total_revenue
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
WHERE o.status = 'completed'
GROUP BY c.country
ORDER BY total_revenue DESC;

What does this query do?

  • COUNT(DISTINCT c.customer_id) counts unique customers per country. Without DISTINCT, a customer with 5 orders would be counted 5 times.
  • COUNT(DISTINCT o.order_id) counts unique orders. One order can have multiple items, so DISTINCT prevents double-counting.
  • ROUND(SUM(...), 2) rounds the revenue to 2 decimal places for cleaner output.
  • Click Run to execute the query.
  • You should see a table with one row per country, showing total customers, total orders, and total revenue. USA should appear at the top.

Why use DISTINCT here?

When you JOIN multiple tables, the result set expands. A single order with 3 items becomes 3 rows after joining with order_items. Without DISTINCT, COUNT would report 3 orders instead of 1.

This is one of the most common mistakes in SQL analytics. Whenever you aggregate after a JOIN, ask yourself: could this row be duplicated?

Seeing inflated order counts?

If your total_orders column shows numbers much higher than expected, you likely wrote COUNT(o.order_id) instead of COUNT(DISTINCT o.order_id). Add the DISTINCT keyword and re-run.

Help me fix my country performance query

Calculate category percentage of total revenue

Knowing raw revenue by category is useful, but stakeholders often want to see percentages. A subquery lets you calculate the total revenue once, then use that number inside your main query to compute each category's share.

  • Clear the editor and paste the following query:
-- Category percentage of total revenue
SELECT p.category,
       ROUND(SUM(oi.quantity * p.price), 2) AS category_revenue,
       ROUND(SUM(oi.quantity * p.price) * 100.0 / (
           SELECT SUM(oi2.quantity * p2.price)
           FROM orders o2
           JOIN order_items oi2 ON o2.order_id = oi2.order_id
           JOIN products p2 ON oi2.product_id = p2.product_id
           WHERE o2.status = 'completed'
       ), 1) AS percentage_of_total
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 o.status = 'completed'
GROUP BY p.category
ORDER BY category_revenue DESC;

What does this query do?

  • The subquery inside the parentheses calculates the overall total revenue from all completed orders. It uses different aliases (o2, oi2, p2) so it does not conflict with the outer query's table references.
  • SUM(...) * 100.0 / (subquery) divides each category's revenue by the grand total, then multiplies by 100 to get a percentage.
  • ROUND(..., 1) rounds the percentage to one decimal place for a clean report.

What is a subquery?

A subquery is a query nested inside another query. It runs first, produces a single value (in this case, total revenue), and that value gets used in the outer calculation.

Think of it like calculating a class average before grading on a curve. You need the overall number first, then you compare each individual against it.

  • Click Run to execute the query.
  • You should see 4 rows (one per category) with the category_revenue and percentage_of_total columns. The percentages should add up to 100.

Seeing a syntax error near the subquery?

The most common mistake is mismatched parentheses. The subquery must be wrapped in ( ) and placed after the division operator /. Count your opening and closing parentheses to make sure they match.

Also check that you used 100.0 (with the decimal) and not 100. Without the decimal, SQLite performs integer division and you will see 0 for most percentages.

Help me fix my category percentage query

✔️ Awesome, I've got everything!

Great. You have completed all three business summary queries. Your analysis now covers products, geography, and category share.

ⓧ I'd like to double check the full code

Here is the complete 06_business_report.sql file with all three queries:

-- Product ranking by total revenue
SELECT p.name AS product_name,
       p.category,
       SUM(oi.quantity * p.price) AS product_revenue,
       SUM(oi.quantity) AS units_sold
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 o.status = 'completed'
GROUP BY p.product_id, p.name, p.category
ORDER BY product_revenue DESC;

-- Country performance summary
SELECT c.country,
       COUNT(DISTINCT c.customer_id) AS total_customers,
       COUNT(DISTINCT o.order_id) AS total_orders,
       ROUND(SUM(oi.quantity * p.price), 2) AS total_revenue
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
WHERE o.status = 'completed'
GROUP BY c.country
ORDER BY total_revenue DESC;

-- Category percentage of total revenue
SELECT p.category,
       ROUND(SUM(oi.quantity * p.price), 2) AS category_revenue,
       ROUND(SUM(oi.quantity * p.price) * 100.0 / (
           SELECT SUM(oi2.quantity * p2.price)
           FROM orders o2
           JOIN order_items oi2 ON o2.order_id = oi2.order_id
           JOIN products p2 ON oi2.product_id = p2.product_id
           WHERE o2.status = 'completed'
       ), 1) AS percentage_of_total
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 o.status = 'completed'
GROUP BY p.category
ORDER BY category_revenue DESC;

Secret mission

Customer Segmentation with CASE WHEN

You've already identified your top customers. Now take it further by building a customer segmentation model that classifies every customer into VIP, Regular, or Occasional tiers based on their total spending. This introduces two intermediate SQL techniques that frequently appear in data analyst interviews: Common Table Expressions (CTEs) and CASE WHEN conditional logic.

Clean Up Your Resources

Clean Up Your Resources

This project runs entirely in your browser using SQLite Online. There are no ongoing costs, no cloud resources, and no local installations to manage.

Decide whether to keep your browser tab open, export your work for later, or close everything out.

Resources you used:

  • SQLite Online browser tab (contains your 4-table database and all query results).

Keep everything running

No action needed. Choose this if you're still actively running queries or want to experiment further.

  • Keep your SQLite Online browser tab open.
  • Your database and query history remain available as long as the tab stays open.

Pause - I'll come back to this later

Export your database so you can reload it in a future session. SQLite Online does not persist data between sessions, so closing the tab without exporting means starting over.

  • In SQLite Online, click the **Export** button in the top toolbar to download your database as a file.
  • Save your SQL queries by copying them into a text file on your computer for reference.
  • Close the browser tab when you're done exporting.

Delete - I don't want to use this again

Remove all project resources completely. Since everything lives in the browser, this is straightforward.

  • Close the SQLite Online browser tab. This destroys the in-memory database and all query history immediately.

Nice Work!

Nice Work!

You just built a complete SQL-based sales analysis of an e-commerce store. Starting from raw data, you wrote queries that answer the exact business questions real data analysts face every day.

You've learned how to:

  • Built a 4-table relational database from scratch using CREATE TABLE and INSERT INTO, then explored it with SELECT, WHERE, ORDER BY, and LIMIT.
  • Combined multiple tables using JOINs and calculated revenue by category, monthly trends, and customer lifetime value using GROUP BY and aggregate functions.
  • Built a business summary report with product rankings, country performance, and category revenue percentages using subqueries.
  • Secret Mission: Segmented customers into VIP, Regular, and Occasional tiers using CASE WHEN and CTEs.

Ready to quiz yourself?