Analyze E-Commerce Sales Data with SQL

Build a relational database and write SQL queries to answer real business questions.

Introduction

30 Second Summary

Every online store generates data with every purchase. But raw rows of customer IDs and product numbers don't answer the questions a business actually cares about: who are our best customers, what's selling, and is revenue going up or down?

In this project, you will build a relational database from scratch, fill it with realistic e-commerce sales data, and write SQL queries that answer the exact business questions a manager would ask.

What You'll Build

You will type a query, hit execute, and instantly see a table ranking your top customers by total spending. No waiting, no extra tools. Just a question in SQL and an answer in a visual grid.

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

  • A three-table relational database you can open anytime to explore customers, products, and orders linked by relationships you designed.
  • Queries that filter, sort, and aggregate your data to answer specific questions like "who signed up most recently?" and "which category sells the most units?" with a single command.
  • A multi-table JOIN query that combines raw IDs into a readable report showing customer names, product details, and order totals side by side.
  • Secret Mission: Add a categories table and write a four-table JOIN that ranks category performance by revenue.

Are there any prerequisites?

None. This project includes full setup instructions for every tool you need. You do not need any prior SQL or programming experience.

Before We Start

Before we dive in, let's lock in what you're about to build and why it matters for your data analyst journey.

Install DB Browser for SQLite

To write SQL and see your results instantly, you need a tool that runs queries and displays the output in a visual table. This tight feedback loop (write a query, see the result) is how you will learn every SQL concept in this project.

DB Browser for SQLite is a free, open-source tool that gives you a query editor and a spreadsheet-like grid to view results. It requires zero server setup and stores everything in a single file on your computer.

In this step, get ready to:
  • Install DB Browser for SQLite on your computer.
  • Create an empty database file called ecommerce.db on your Desktop.
  • Confirm DB Browser is open and ready for SQL queries.
Download and install DB Browser for SQLite

DB Browser for SQLite is the only tool you need for this project. Let's check if you already have it installed.

  • Press the Windows key to open your search bar.
  • Type DB Browser for SQLite and press Enter.

✔️ I see DB Browser for SQLite open

  • Click Help in the top menu bar, then click About.
  • Confirm the version number shows 3.13.1 or higher.

If your version is 3.13.1 or higher, you're all set. Skip ahead to the next substep (Create your ecommerce database).

ⓧ I see an older version

You have DB Browser installed, but you need version 3.13.1 for this project. Download the latest version to replace it.

  • Open your browser and go to the DB Browser for SQLite downloads page:
  • https://sqlitebrowser.org/dl/
  • Under the Windows section, click Standard installer for 64-bit Windows.
  • Run the downloaded .msi file and follow the installer prompts, accepting all defaults.
  • Once installation finishes, open the app again using Windows search.

Check Help > About to confirm the version now shows 3.13.1.

ⓧ App not found

No problem. Let's install DB Browser for SQLite from scratch.

  • Open your browser and go to the DB Browser for SQLite downloads page:
  • https://sqlitebrowser.org/dl/
  • Under the Windows section, click Standard installer for 64-bit Windows.
  • Run the downloaded .msi file.
  • Accept all default settings in the installer and click through to finish.
  • Once installation completes, press the Windows key, type DB Browser for SQLite, and press Enter to launch it.

You should see the DB Browser for SQLite application window with a toolbar across the top and an empty workspace below.

Why DB Browser for SQLite?

DB Browser gives you immediate visual feedback on every query you run. You type SQL, hit execute, and see results in a grid instantly.

Unlike heavier databases (PostgreSQL, MySQL), SQLite requires no server, no accounts, and no configuration. Your entire database lives in a single .db file you can keep on your Desktop.

Create your ecommerce database

Now that DB Browser is open, you need an empty database file where all your tables and data will live.

  • In DB Browser, click New Database in the toolbar at the top of the window.
  • A save dialog appears. Navigate to your Desktop folder.
  • In the File name field, type ecommerce.db.
  • Click Save.

What happens when you click Save?

DB Browser creates an empty SQLite database file at the location you chose. A dialog may appear asking you to define a table. Click Cancel to dismiss it for now.

You will create your first table using SQL in the next step, which is more valuable practice than using the GUI form.

What do you expect to see in DB Browser now that you have created an empty database?

Look at the DB Browser window. You should see ecommerce.db in the title bar and an empty workspace with tabs like Database Structure, Browse Data, and Execute SQL. The tables list is empty because you have not created any yet.

Don't see ecommerce.db in the title bar?

  • Make sure you clicked Save (not Cancel) in the file dialog.
  • Check your Desktop for the ecommerce.db file. If it is not there, click New Database again and repeat the steps above.
  • If the title bar shows a different filename, close the database (File > Close Database) and create a new one with the correct name.

Still stuck? Help me troubleshoot DB Browser for SQLite not creating my database file.

Your tool is installed and your database file is ready. Next up, you will create your first table and insert real customer data so you can start writing queries.

Create Your First Table and See Data Appear

Your database file exists, but it is completely empty. Before you can analyze sales data, that data needs a home with a clear structure.

In this step, you will build your first table, fill it with customer records, and query it three different ways. Every SQL statement gives you visible results in seconds.

In this step, get ready to:
  • Create a customers table with columns for ID, name, email, city, and signup date.
  • Insert 5 customer rows with realistic data.
  • Query the table using SELECT, WHERE, and ORDER BY.
Create the customers table

A table defines the structure of your data. Each column has a name and a type, like a spreadsheet header that enforces what kind of value goes in each cell.

  • Switch to the Execute SQL tab at the top of DB Browser.
  • Clear any existing text in the editor, then type the following SQL to define your customers table:
-- Create customers table
CREATE TABLE customers (
    customer_id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT,
    city TEXT,
    signup_date TEXT
);
  • Press Ctrl+Return to execute the statement.

What does this code do?

  • CREATE TABLE customers tells SQLite to make a new table called customers.
  • Each line inside the parentheses defines a column with a name and a data type (INTEGER, TEXT).
  • INTEGER PRIMARY KEY makes customer_id a unique identifier for each row. No two customers can share the same ID.
  • TEXT NOT NULL on name means every customer must have a name. The database will reject any row without one.

You should see a message in the output pane below the editor confirming the query executed successfully.

Seeing an error instead?

  • Make sure every line ends exactly as shown, including the comma after each column definition except the last one.
  • Confirm the statement ends with a semicolon (;).
  • If you see table customers already exists, the table was already created from a previous attempt. You can continue to the next chunk.

Still stuck? Why is my CREATE TABLE statement failing?

Insert customer data

A table without rows is just an empty shell. Now you will populate it with 5 customers so you have real data to query.

  • Clear the editor (select all text and delete it), then type this INSERT statement to add 5 customer rows:
-- Insert customer data
INSERT INTO customers (customer_id, name, email, city, signup_date) VALUES
(1, 'Alice Johnson', 'alice@email.com', 'New York', '2024-01-15'),
(2, 'Bob Smith', 'bob@email.com', 'Chicago', '2024-02-20'),
(3, 'Carol White', 'carol@email.com', 'Los Angeles', '2024-03-10'),
(4, 'David Brown', 'david@email.com', 'Houston', '2024-01-28'),
(5, 'Eva Martinez', 'eva@email.com', 'New York', '2024-04-05');
  • Press Ctrl+Return to execute.

What does this code do?

INSERT INTO adds new rows to an existing table. The column list in parentheses tells SQLite which columns receive values, and each set of parentheses after VALUES is one complete row.

Notice that two customers (Alice and Eva) share the city New York. You will use this overlap to test filtering in a moment.

  • Click the Browse Data tab at the top of DB Browser.
  • Select customers from the Table dropdown.

You should see all 5 rows displayed in a spreadsheet-like grid with columns for customer_id, name, email, city, and signup_date.

Don't see any rows in Browse Data?

  • Make sure you selected customers from the Table dropdown at the top of the Browse Data tab.
  • Switch back to the Execute SQL tab and re-run the INSERT statement. If you see an error about a duplicate primary key, the data already exists.

Still stuck? Why doesn't my Browse Data tab show any customer rows?

  • Save your database by pressing Ctrl+S (this writes changes to the ecommerce.db file on disk).
Query your data with SELECT and WHERE

Now that your data is stored, you can ask questions about it. SELECT retrieves rows from a table, and WHERE filters them to only the rows that match a condition.

  • Switch back to the Execute SQL tab.
  • Clear the editor and run this query to retrieve every row and column:
SELECT * FROM customers;
  • Press Ctrl+Return to execute.

All 5 customer rows appear in the results pane below the editor. The * means "all columns."

  • Now try filtering. Clear the editor and run this query to find only customers in New York:
SELECT name, city FROM customers WHERE city = 'New York';
  • Press Ctrl+Return to execute.

Only 2 rows appear: Alice Johnson and Eva Martinez. The WHERE clause filtered out everyone whose city is not New York.

What is the difference between SELECT * and SELECT with column names?

SELECT * returns every column. SELECT name, city returns only the columns you list. In real analysis, you pick specific columns to keep results focused and readable.

Sort results with ORDER BY

Filtering shows you a subset of rows. Sorting rearranges rows so you can spot patterns, like who signed up most recently.

  • Clear the editor and run this query to sort customers by signup date, newest first:
SELECT name, city, signup_date FROM customers ORDER BY signup_date DESC;
  • Press Ctrl+Return to execute.

What does ORDER BY DESC do?

ORDER BY signup_date DESC sorts results by the signup_date column in descending order (newest dates first). Without DESC, SQL defaults to ascending order (oldest first).

Before you look at the results, predict which customer should appear at the top of the list. Who signed up most recently based on the dates you inserted?

Eva Martinez (2024-04-05) appears at the top, followed by Carol White, Bob Smith, David Brown, and Alice Johnson. The rows reorder by date without changing the actual data in the table.

Rows not sorted correctly?

  • Make sure you typed DESC after signup_date. Without it, the sort defaults to ascending (oldest first).
  • Confirm the query ends with a semicolon.

Still stuck? Why is my ORDER BY query not sorting correctly?

✔️ Awesome, I've got everything!

Great. Make sure you have saved your database with Ctrl+S so your table and data persist.

ⓧ I'd like to double check the full code

Here is all the SQL you executed in this step. Scroll through and confirm each statement matches what you ran.

-- Create customers table
CREATE TABLE customers (
    customer_id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT,
    city TEXT,
    signup_date TEXT
);
-- Insert customer data
INSERT INTO customers (customer_id, name, email, city, signup_date) VALUES
(1, 'Alice Johnson', 'alice@email.com', 'New York', '2024-01-15'),
(2, 'Bob Smith', 'bob@email.com', 'Chicago', '2024-02-20'),
(3, 'Carol White', 'carol@email.com', 'Los Angeles', '2024-03-10'),
(4, 'David Brown', 'david@email.com', 'Houston', '2024-01-28'),
(5, 'Eva Martinez', 'eva@email.com', 'New York', '2024-04-05');
SELECT * FROM customers;
SELECT name, city FROM customers WHERE city = 'New York';
SELECT name, city, signup_date FROM customers ORDER BY signup_date DESC;

You now have a table with real data and three SQL tools in your belt: SELECT to retrieve, WHERE to filter, and ORDER BY to sort. Next up, you will add products and orders tables so you can start connecting data across multiple tables.

Add Products and Orders to Build Relationships

Your customers table is alive with 5 rows you can filter and sort. That is great for one table, but real analysis needs more.

Your manager does not just want customer names. They want to know what products those customers bought, when they bought them, and how much they spent. To answer these questions, you need multiple tables that reference each other by ID.

In this step, get ready to:
  • Create a products table with 6 items across 3 categories.
  • Create an orders table with 10 orders linking customers to products.
  • Query the orders table and discover a limitation that motivates the next step.
Create the products table

Your e-commerce store needs a catalog. The products table stores every item the company sells, organized by category with a price.

  • In the Execute SQL tab, clear any previous SQL from the editor.
  • Create and populate the products table by pasting and running this SQL:
-- Create products table
CREATE TABLE products (
    product_id INTEGER PRIMARY KEY,
    product_name TEXT NOT NULL,
    category TEXT,
    price REAL
);

-- Insert product data
INSERT INTO products (product_id, product_name, category, price) VALUES
(1, 'Wireless Headphones', 'Electronics', 79.99),
(2, 'Cotton T-Shirt', 'Clothing', 24.99),
(3, 'Coffee Maker', 'Home', 49.99),
(4, 'Laptop Stand', 'Electronics', 34.99),
(5, 'Running Shoes', 'Clothing', 89.99),
(6, 'Desk Lamp', 'Home', 29.99);

What does this SQL do?

  • CREATE TABLE products defines a new table with four columns: an auto-assigned ID, a product name, a category label, and a decimal price.
  • INTEGER PRIMARY KEY makes product_id the unique identifier for each row.
  • REAL stores the price as a decimal number (like 79.99) rather than a whole number.
  • The INSERT statement adds 6 products spread across 3 categories: Electronics, Clothing, and Home.
  • Click the execute button (the play icon) or press Ctrl+Enter to run the SQL.
  • Confirm the products exist by running this query:
SELECT * FROM products;

You should see 6 rows in the results pane, each showing a product with its category and price.

  • Click the Browse Data tab and select products from the table dropdown to see your catalog in the visual grid.

Seeing an error about the table already existing?

If you see a message like "table products already exists", it means you ran the CREATE TABLE statement twice. Clear the editor, then run only the SELECT * FROM products; query to check if your data is already there.

If the table exists but has no data, run just the INSERT INTO products statement by itself.

Still stuck? Help me fix my products table error.

✔️ Awesome, I've got everything!

Great. Your products table is ready with 6 items across Electronics, Clothing, and Home.

ⓧ I'd like to double check the full code

Here is the full SQL you should have executed for the products table:

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

-- Insert product data
INSERT INTO products (product_id, product_name, category, price) VALUES
(1, 'Wireless Headphones', 'Electronics', 79.99),
(2, 'Cotton T-Shirt', 'Clothing', 24.99),
(3, 'Coffee Maker', 'Home', 49.99),
(4, 'Laptop Stand', 'Electronics', 34.99),
(5, 'Running Shoes', 'Clothing', 89.99),
(6, 'Desk Lamp', 'Home', 29.99);
Create the orders table

Now you need a table that records every purchase. Each order connects a customer to a product by storing their IDs, along with the quantity and date.

  • Switch back to the Execute SQL tab and clear the editor.
  • Create and populate the orders table by pasting and running this SQL:
-- Create orders table
CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    customer_id INTEGER,
    product_id INTEGER,
    quantity INTEGER,
    order_date TEXT
);

-- Insert order data
INSERT INTO orders (order_id, customer_id, product_id, quantity, order_date) VALUES
(1, 1, 1, 1, '2024-06-01'),
(2, 2, 3, 2, '2024-06-05'),
(3, 1, 4, 1, '2024-06-10'),
(4, 3, 2, 3, '2024-07-02'),
(5, 4, 5, 1, '2024-07-08'),
(6, 2, 1, 1, '2024-07-15'),
(7, 1, 6, 2, '2024-07-20'),
(8, 3, 3, 1, '2024-08-01'),
(9, 5, 4, 2, '2024-08-10'),
(10, 4, 2, 1, '2024-08-18');

What does this SQL do?

The orders table is where relationships happen. Instead of repeating a customer's full name or a product's full details in every row, it stores just the customer_id and product_id numbers.

  • customer_id links each order to a row in the customers table.
  • product_id links each order to a row in the products table.
  • The 10 orders span June through August 2024, with different customers buying different products in varying quantities.
  • Click the execute button or press Ctrl+Enter to run the SQL.
  • Confirm the orders were created by running:
SELECT * FROM orders;

You should see 10 rows in the results pane. Each row shows an order_id, a customer_id number, a product_id number, a quantity, and a date.

Seeing fewer than 10 rows?

Make sure you copied the entire INSERT statement including all 10 value groups. Check that the statement ends with a semicolon after the last row.

If you see "table orders already exists", run SELECT * FROM orders; first to check if the data is already there.

Need help? Help me fix my orders table.

✔️ Awesome, I've got everything!

Great. Your orders table is ready with 10 orders linking customers to products.

ⓧ I'd like to double check the full code

Here is the full SQL you should have executed for the orders table:

-- Create orders table
CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    customer_id INTEGER,
    product_id INTEGER,
    quantity INTEGER,
    order_date TEXT
);

-- Insert order data
INSERT INTO orders (order_id, customer_id, product_id, quantity, order_date) VALUES
(1, 1, 1, 1, '2024-06-01'),
(2, 2, 3, 2, '2024-06-05'),
(3, 1, 4, 1, '2024-06-10'),
(4, 3, 2, 3, '2024-07-02'),
(5, 4, 5, 1, '2024-07-08'),
(6, 2, 1, 1, '2024-07-15'),
(7, 1, 6, 2, '2024-07-20'),
(8, 3, 3, 1, '2024-08-01'),
(9, 5, 4, 2, '2024-08-10'),
(10, 4, 2, 1, '2024-08-18');
Query orders and discover a limitation

You have three tables now. Your manager asks: "Show me all orders from Alice Johnson." You know Alice's customer_id is 1, so you try filtering orders by that ID.

Before you run this query, think about what you expect to see. Will you get Alice's name in the results, or just her ID number?

  • Run this query to filter orders for customer 1:
SELECT * FROM orders WHERE customer_id = 1;

You see 3 rows. Each shows Alice's orders. But look at the columns closely.

The result says customer_id = 1 and product_id = 1, product_id = 4, product_id = 6. You cannot see "Alice Johnson" or "Wireless Headphones" anywhere. Just numbers.

Why can you only see numbers?

The orders table only stores ID numbers that point to the other tables. It does not duplicate the customer's name or the product's name. This is efficient for storage, but useless when your manager asks "who bought what?"

To see names alongside order details, you need a way to pull columns from multiple tables at once. That technique is called a JOIN, and it is exactly what you will learn next.

Your database now has three tables connected by IDs. You have customers, products, and orders that link them together. Next up, you will use JOINs to pull all that information into a single, readable result.

Combine Tables with JOIN to Answer Business Questions

Your database now holds customers, products, and orders. But in the last step you hit a wall: querying orders shows customer_id = 1, not "Alice Johnson."

Your manager wants reports with names and categories, not cryptic ID numbers. A JOIN connects multiple tables in a single query, pulling columns from each one to give you the complete picture.

In this step, get ready to:
  • Write a three-table INNER JOIN that replaces IDs with human-readable names.
  • Filter and sort joined data using WHERE and ORDER BY.
  • Compare INNER JOIN to LEFT JOIN and understand when each one matters.
Write a three-table INNER JOIN

An INNER JOIN matches rows from two tables based on a shared column. When two rows share the same ID value, the JOIN combines them into one result row with columns from both tables.

You can chain multiple JOINs in one query. Here you will join orders to customers (to get names) AND to products (to get product names) all at once.

  • Switch to the Execute SQL tab in DB Browser.
  • Clear any previous SQL in the editor, then type this query:
SELECT
    customers.name,
    products.product_name,
    orders.quantity,
    orders.order_date
FROM orders
INNER JOIN customers ON orders.customer_id = customers.customer_id
INNER JOIN products ON orders.product_id = products.product_id;

What does this query do?

This query starts from the orders table and reaches into two other tables to grab readable details.

  • INNER JOIN customers ON orders.customer_id = customers.customer_id links each order to the customer who placed it, so you can show their name.
  • INNER JOIN products ON orders.product_id = products.product_id links each order to the product purchased, so you can show the product name.
  • The SELECT line picks only the columns you care about: name, product name, quantity, and date.

Before you execute this, think about what you expect to see. Will the results show ID numbers or names?

  • Click the Execute button (or press Ctrl+Enter).

You should see 10 rows in the results pane, each showing a customer's actual name, the product they bought, how many they ordered, and the date. No more raw IDs.

Seeing an error about no such column?

  • Check that the column names in your ON clauses match exactly: orders.customer_id and customers.customer_id. A typo in either table or column name causes this error.
  • Make sure you completed Step 3 and all three tables exist. Click the Browse Data tab and check the table dropdown for customers, products, and orders.

Help me debug my INNER JOIN query

Filter and sort your joined results

A JOIN gives you the full combined dataset. You can still use WHERE and ORDER BY on joined queries, exactly the same way you used them on a single table.

  • Clear the SQL editor and type this query to find only Electronics orders, sorted by date:
SELECT
    customers.name,
    products.product_name,
    orders.quantity,
    orders.order_date
FROM orders
INNER JOIN customers ON orders.customer_id = customers.customer_id
INNER JOIN products ON orders.product_id = products.product_id
WHERE products.category = 'Electronics'
ORDER BY orders.order_date;

What changed from the previous query?

  • WHERE products.category = 'Electronics' filters across a joined table. You are referencing a column from the products table even though the query starts from orders.
  • ORDER BY orders.order_date sorts the filtered results chronologically.

This is the power of JOINs: once tables are connected, you can filter and sort on any column from any of them.

  • Click Execute to run the query.

You should see only the Electronics orders (Wireless Headphones and Laptop Stand), sorted from earliest to latest date. The result set is smaller than before because WHERE narrowed it down.

Getting zero rows back?

  • Check the spelling and capitalization of 'Electronics' in your WHERE clause. SQL string comparisons in SQLite are case-sensitive by default.
  • Make sure you are using single quotes around the value, not double quotes.

Help me fix my WHERE clause on a JOIN

Compare INNER JOIN to LEFT JOIN

An INNER JOIN only returns rows where both tables have a match. A LEFT JOIN returns ALL rows from the left table, even when there is no matching row in the right table. Unmatched rows show NULL for the right table's columns.

This distinction matters when your manager asks "which customers have never purchased?" Only a LEFT JOIN can answer that.

  • Clear the SQL editor and type this LEFT JOIN query:
SELECT
    customers.name,
    orders.order_id
FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id;

What does this query do?

  • FROM customers makes the customers table the "left" table. Every customer will appear in the results.
  • LEFT JOIN orders ON ... attaches order data where a match exists. If a customer has no orders, their row still appears with NULL in the order columns.

With INNER JOIN, customers with no orders would vanish from the results entirely. LEFT JOIN guarantees they stay visible.

Before you run this, predict: will you see more rows, fewer rows, or the same number of rows compared to an INNER JOIN of the same two tables?

  • Click Execute to run the query.

You should see a row for every customer in the results. In this dataset, all five customers have placed at least one order, so every row shows a valid order_id. Customers with multiple orders appear on multiple rows (once per order).

When does LEFT JOIN show NULL values?

If any customer had never placed an order, LEFT JOIN would still include them in the results with NULL in the order_id column. INNER JOIN would hide them completely.

In a real company database with thousands of customers, many will have zero orders. LEFT JOIN is how you find those inactive customers. That is the critical difference between the two JOIN types.

Results look identical to INNER JOIN?

That is expected with this dataset. All five customers have at least one order, so LEFT JOIN and INNER JOIN produce the same rows here. The difference surfaces when a left-table row has no match in the right table.

Help me understand LEFT JOIN vs INNER JOIN

✔️ Awesome, I've got everything!

Great work. You now have three JOIN queries you can reference anytime you need to combine data from multiple tables.

ⓧ I'd like to double check the full code

Here are all three queries from this step. Compare them against what you typed in the Execute SQL tab.

-- Three-table INNER JOIN
SELECT
    customers.name,
    products.product_name,
    orders.quantity,
    orders.order_date
FROM orders
INNER JOIN customers ON orders.customer_id = customers.customer_id
INNER JOIN products ON orders.product_id = products.product_id;

-- JOIN with filter and sort
SELECT
    customers.name,
    products.product_name,
    orders.quantity,
    orders.order_date
FROM orders
INNER JOIN customers ON orders.customer_id = customers.customer_id
INNER JOIN products ON orders.product_id = products.product_id
WHERE products.category = 'Electronics'
ORDER BY orders.order_date;

-- LEFT JOIN
SELECT
    customers.name,
    orders.order_id
FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id;

You can now combine data from any table in your database into a single result set. Next up, you will use GROUP BY and aggregate functions to collapse those rows into business insights: totals, averages, and counts that answer your manager's questions directly.

Answer Business Questions with GROUP BY and Aggregates

In Step 4, you combined data across tables using JOINs to see customer names and product details in one result. Your queries returned individual rows, one per order.

Your manager does not want individual rows. They want summaries: totals, averages, and counts. GROUP BY with aggregate functions like SUM, COUNT, and AVG collapses many rows into meaningful insights. This is the core of a data analyst's daily work.

In this step, get ready to:
  • Identify your top customers by total spending.
  • Find the best-selling product category by units sold.
  • Track monthly revenue trends over three months.
Find your top customers by total spending

Your manager asks: "Who are our best customers?" To answer this, you need to multiply each product's price by the order quantity, then sum those totals per customer.

  • In the Execute SQL tab, select all existing text and delete it.
  • Write and execute this query:
SELECT
    customers.name,
    SUM(products.price * orders.quantity) AS total_spent
FROM orders
INNER JOIN customers ON orders.customer_id = customers.customer_id
INNER JOIN products ON orders.product_id = products.product_id
GROUP BY customers.name
ORDER BY total_spent DESC;

What does this query do?

  • SUM(products.price * orders.quantity) multiplies the price by quantity for each order, then adds up all orders belonging to the same customer.
  • AS total_spent gives the calculated column a readable name. This is called an alias.
  • GROUP BY customers.name collapses all rows for the same customer into a single summary row.
  • ORDER BY total_spent DESC puts the biggest spender at the top.

You should see 5 rows in the results pane below. Each row shows one customer name and their total spending. Bob Smith appears at the top as the biggest spender.

Not seeing results?

  • Check that you have both INNER JOIN lines. A missing JOIN means the database cannot connect the tables.
  • Make sure GROUP BY comes after the JOIN lines and before ORDER BY. SQL requires this exact clause order.
  • help me fix my GROUP BY query for top customers
Identify the best-selling product category

Next question from your manager: "What product categories are performing best?" You need to count how many times each category was ordered and how many total units were sold.

  • Select all text in the SQL editor and delete it.
  • Write and execute this query:
SELECT
    products.category,
    COUNT(*) AS times_ordered,
    SUM(orders.quantity) AS units_sold
FROM orders
INNER JOIN products ON orders.product_id = products.product_id
GROUP BY products.category
ORDER BY units_sold DESC;

What does this query do?

  • COUNT(*) counts the number of order rows for each category. This tells you how many separate orders included that category.
  • SUM(orders.quantity) adds up the total units sold across all orders for that category.
  • GROUP BY products.category creates one summary row per category instead of one row per order.

You should see 3 rows, one per category (Clothing, Electronics, Home). Each row shows how many times that category was ordered and total units sold.

Seeing an error about the GROUP BY clause?

  • Every column in your SELECT that is not inside an aggregate function (like SUM or COUNT) must appear in the GROUP BY clause.
  • Confirm that products.category is in both your SELECT and your GROUP BY.
  • help me fix my category GROUP BY query
Track revenue trends month over month

The final question: "How is revenue trending month over month?" You need to group orders by their month and calculate the total revenue for each month.

Before you execute the next query, think about what you expect. Based on the 10 orders you inserted earlier (spread across June, July, and August), which month do you think had the highest revenue?

  • Select all text in the SQL editor and delete it.
  • Write and execute this query to find out:
SELECT
    substr(orders.order_date, 1, 7) AS month,
    SUM(products.price * orders.quantity) AS total_revenue
FROM orders
INNER JOIN products ON orders.product_id = products.product_id
GROUP BY month
ORDER BY month ASC;

What does this query do?

  • substr(orders.order_date, 1, 7) extracts the first 7 characters from the date string, giving you the year-month format (like 2024-06).
  • AS month names that extracted value so you can use it in GROUP BY and ORDER BY.
  • GROUP BY month collapses all orders from the same month into one summary row.
  • ORDER BY month ASC arranges the months in chronological order so you can read the trend from earliest to latest.

You should see 3 rows: 2024-06, 2024-07, and 2024-08. July has the highest revenue. Revenue went up from June to July, then dropped in August.

Why does this matter?

You just answered "How is revenue trending?" with a single query. In a real analyst role, you would run queries like this weekly or monthly to spot patterns and flag concerns. A revenue dip in August might trigger a conversation about marketing spend or seasonal demand.

Seeing only one row or getting an error?

  • Make sure substr has three arguments separated by commas: the column name, the starting position (1), and the length (7).
  • Confirm your GROUP BY uses the alias month, not the full substr(...) expression. SQLite allows grouping by alias.
  • help me fix my monthly revenue query

✔️ Awesome, I've got everything!

Great job. You have answered all three business questions with aggregate queries. Make sure your database is saved (click Write Changes in the toolbar if prompted).

ⓧ I'd like to double check the full code

Here are all three aggregate queries you wrote in this step. Scroll up and confirm each one matches what you have in your SQL editor.

Query 1: Top customers by total spending.

SELECT
    customers.name,
    SUM(products.price * orders.quantity) AS total_spent
FROM orders
INNER JOIN customers ON orders.customer_id = customers.customer_id
INNER JOIN products ON orders.product_id = products.product_id
GROUP BY customers.name
ORDER BY total_spent DESC;

Query 2: Best-selling product category.

SELECT
    products.category,
    COUNT(*) AS times_ordered,
    SUM(orders.quantity) AS units_sold
FROM orders
INNER JOIN products ON orders.product_id = products.product_id
GROUP BY products.category
ORDER BY units_sold DESC;

Query 3: Monthly revenue trend.

SELECT
    substr(orders.order_date, 1, 7) AS month,
    SUM(products.price * orders.quantity) AS total_revenue
FROM orders
INNER JOIN products ON orders.product_id = products.product_id
GROUP BY month
ORDER BY month ASC;

You just answered three real business questions using nothing but SQL. Your database is now a complete analytics tool with customers, products, orders, and the queries to make sense of them all. Ready for a challenge? The Secret Mission takes your database design to the next level.

Secret mission

Add a Categories Table and Build a Four-Table JOIN

Real databases rarely store category names as raw text repeated across rows. In this extension, you will normalize your database by extracting categories into their own table, then write a four-table JOIN that calculates performance metrics per category.

Clean Up Your Resources

Clean Up Your Resources

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

Resources you used:

  • ecommerce.db database file on your Desktop.
  • DB Browser for SQLite (installed application).

Keep everything running

No action needed. Choose this if you plan to keep practicing SQL, extending your database, or preparing for interviews.

  • Your ecommerce.db file stays on your Desktop, ready to open anytime.
  • DB Browser for SQLite remains installed for future projects.

Pause - I'll come back to this later

Close DB Browser for SQLite to free up memory. Your database file is saved on disk and will be there when you return.

  • Close DB Browser for SQLite by clicking the X in the top-right corner.
  • If prompted to save changes, click Save.
  • Your ecommerce.db file stays on your Desktop. To resume, open DB Browser and click Open Database, then select ecommerce.db from your Desktop.

Delete - I don't want to use this again

Remove all project resources and the application entirely.

  • Close DB Browser for SQLite if it is still open.
  • Delete the ecommerce.db file from your Desktop. Right-click it and select Delete.
  • Uninstall DB Browser for SQLite. Press the Windows key, type Add or remove programs, and press Enter.
  • Find DB Browser for SQLite in the list, click it, then click Uninstall.
  • Follow the uninstall wizard prompts to complete removal.

Nice Work!

Nice Work!

You just built a fully functional e-commerce database from scratch and used SQL to answer real business questions. This is the exact workflow data analysts use every day to turn raw data into actionable insights.

Here's what you accomplished:

  • Built a three-table relational database (customers, products, orders) from scratch and populated it with realistic e-commerce data.
  • Combined data across multiple tables using INNER JOIN and LEFT JOIN to produce human-readable results instead of raw IDs.
  • Answered real business questions (top customers, best-selling categories, monthly revenue trends) using GROUP BY with SUM, COUNT, and AVG.
  • Secret Mission: Normalized the database by extracting categories into their own table and wrote a complex four-table JOIN with grouped aggregate metrics.

Ready to quiz yourself?