Analyze an E-Commerce Business with SQL

Write SQL queries to uncover revenue trends and customer value.

Introduction

30 Second Summary

Every business decision worth making starts with a question about data. Which products are actually growing? Which customers matter most? Where should next quarter's budget go?

In this project, you will load a realistic e-commerce dataset into a local SQL database and write increasingly powerful queries to answer exactly those questions. You will finish by writing a stakeholder recommendation backed by the numbers you pulled yourself.

What You'll Build

You will run a query, watch rows of revenue data appear in a results pane, and know exactly which product category grew fastest last quarter and which customers you would fight to keep.

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

  • A set of multi-table JOIN queries that pull revenue trends, customer value tiers, and product rankings from three connected tables.
  • A month-over-month growth analysis using window functions that shows exactly where revenue accelerated or dipped.
  • A stakeholder recommendation paragraph citing specific numbers from your queries, the exact deliverable format business analysts produce in real roles.
  • Secret Mission: Build a customer churn risk query that flags buyers who have not purchased in 90+ days.

Are there any prerequisites?

None. This project includes full setup instructions for DB Browser for SQLite, the only tool you need. You do not need any prior SQL experience.

Before We Start

Before We Start...

Before we dive in, let's lock in what you're about to build and why it matters. This step is your chance to commit to the goal before any hands-on work begins.

Set Up Your SQL Environment

To analyze a business with data, you need a place to write and run SQL queries against real tables. You also need to avoid fighting server configuration, database credentials, or cloud setup.

DB Browser for SQLite gives you a visual interface for writing SQL and browsing results, all running locally as a single file on your machine. In this step, you will install it and create the database that will hold your e-commerce data.

In this step, get ready to:
  • Install DB Browser for SQLite.
  • Create an empty database file to store your e-commerce data.
  • Confirm the SQL environment works by running your first query.
Install DB Browser for SQLite

DB Browser for SQLite is a free, open-source application that lets you create, browse, and query SQLite databases without touching a command line. Let's check whether you already have it installed.

  • Press the Windows key to open your search bar.
  • Type DB Browser and look at the search results.
  • If you find it, open it and check the version number under Help > About.

✔️ I see version 3.13.x

You already have the latest version installed. Close the About dialog and proceed to the next substep.

ⓧ I see an older version

The MSI installer will upgrade your existing installation in place. Download and run the latest version to replace your current one.

  • Open your browser and go to the DB Browser for SQLite download page:
  • https://sqlitebrowser.org/dl/
  • Under the Windows section, download the file named DB.Browser.for.SQLite-v3.13.1-win64.msi (the standard installer for 64-bit Windows).
  • Run the downloaded .msi file and click through the installer with the default settings.
  • Once installation finishes, open DB Browser for SQLite from your Start menu.

The title bar now shows version 3.13.1. You are ready to continue.

ⓧ I don't see it in my search results

DB Browser for SQLite is not installed yet. Let's download and install it now.

  • Open your browser and go to the DB Browser for SQLite download page:
  • https://sqlitebrowser.org/dl/
  • Under the Windows section, download the file named DB.Browser.for.SQLite-v3.13.1-win64.msi (the standard installer for 64-bit Windows).
  • Run the downloaded .msi file.
  • Click through the installer with the default settings. Accept the license agreement and leave the install location as-is.
  • Once installation finishes, open DB Browser for SQLite from your Start menu.

You should see the DB Browser for SQLite window with an empty workspace. The title bar confirms the application is running.

Installer not running?

If Windows SmartScreen shows a warning, click More info and then Run anyway. The installer is signed and safe to run.

If you see a permissions error, right-click the .msi file and choose Run as administrator.

Help me troubleshoot the DB Browser for SQLite installation on Windows.

Create your database file

Now that DB Browser is open, you will create the empty database file that will hold all of your e-commerce tables throughout this project.

  • Click New Database in the toolbar at the top of the window.
  • In the save dialog, navigate to your Desktop or Documents folder (somewhere you can find easily).
  • Type ecommerce.db as the filename and click Save.
  • A dialog will appear asking you to define a table. Click Cancel for now. You will create tables in the next step.

The title bar now shows ecommerce.db and the main window shows an empty database structure. The Database Structure tab is visible with no tables listed.

Don't see the New Database button?

If you accidentally closed the app after installing, press the Windows key, type DB Browser, and open it again. The New Database button is in the toolbar along the top of the main window.

Help me find the New Database option in DB Browser for SQLite.

Run your first SQL query

The Execute SQL tab is where you will write and run every query in this project. Let's confirm it works by asking SQLite to report its version number.

  • Click the Execute SQL tab at the top of the window.

You should see a blank text area where you can type SQL. Before you run the query below, predict: what kind of result do you think a version-check query returns? A single number? A row with columns?

  • Type the following query into the SQL editor and press F5 (or click the Play button) to run it:
-- Check which version of SQLite is powering DB Browser
SELECT sqlite_version();

What does this query do?

SELECT sqlite_version(); is a built-in SQLite function that returns the version of the SQLite engine bundled inside DB Browser. It confirms that your SQL environment can execute queries and return results.

In the results pane below the editor, you should see a single row with a version number (something like 3.46.0 or higher). That confirms your SQL environment is working.

No result or seeing an error?

Make sure you created and saved the ecommerce.db database first. The Execute SQL tab requires an open database to run queries against.

Check that your query is typed exactly as shown, including the parentheses and semicolon. A missing () after sqlite_version will cause an error.

Help me run my first query in DB Browser for SQLite.

Your SQL environment is ready. You have a working database file and a query editor that can execute SQL. Next up, you will load realistic e-commerce data into tables so you have something meaningful to analyze.

Load the Business Dataset

Your database is open but empty. To practice JOINs and answer real business questions, you need realistic data spread across multiple related tables.

A single flat table would not teach you to connect data across sources, which is what business analysts do every day. In this step, you will create three separate tables (customers, products, orders) that reference each other by ID.

In this step, get ready to:
  • Create three tables with a normalized schema.
  • Populate the tables with 100 customers, 25 products, and 500 orders.
  • Explore the data and observe how foreign keys connect the tables.
Create the table structure

Before loading any data, you need to define the schema. This tells SQLite what columns each table has and what types of data they hold.

  • In the Execute SQL tab, clear any existing text and paste the following SQL to create three tables:
-- Create the customers table
CREATE TABLE customers (
    customer_id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT NOT NULL,
    signup_date TEXT NOT NULL,
    region TEXT NOT NULL
);

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

-- Create the orders table
CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    product_id INTEGER NOT NULL,
    order_date TEXT NOT NULL,
    quantity INTEGER NOT NULL,
    total_amount REAL NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id),
    FOREIGN KEY (product_id) REFERENCES orders(product_id)
);
  • Click the Execute SQL button (the blue play icon) or press F5 to run the statements.

What does this SQL do?

This creates a normalized e-commerce schema where each table has a single responsibility.

  • customers stores who is buying (name, email, region).
  • products stores what is for sale (name, category, price).
  • orders stores each transaction, referencing customers and products by their IDs via FOREIGN KEY constraints.
  • Confirm the tables were created by clicking the Database Structure tab at the top of the window.
  • You should see three tables listed: customers, products, and orders.

Not seeing the tables?

  • Check the results pane at the bottom of the Execute SQL tab for error messages. A red message usually means a typo in the SQL.
  • If you see "table already exists", it means the tables were created successfully on a previous run. You can proceed to the next substep.
  • Make sure you selected all the SQL text before pressing F5. If only part is highlighted, only that part runs.

Why aren't my tables showing up in DB Browser for SQLite?

Populate the tables with sample data

The tables exist but are empty. You will now load realistic sample data: 100 customers across 4 regions, 25 products in 5 categories, and 500 orders spanning January through June 2024.

  • Switch back to the Execute SQL tab. Clear the SQL editor, then paste and run the following INSERT statement to load all 25 products:
INSERT INTO products (product_id, product_name, category, unit_price) VALUES
(1, 'Wireless Mouse', 'Electronics', 29.99),
(2, 'USB-C Hub', 'Electronics', 49.99),
(3, 'Mechanical Keyboard', 'Electronics', 89.99),
(4, 'Webcam HD', 'Electronics', 59.99),
(5, 'Monitor Stand', 'Electronics', 39.99),
(6, 'Ergonomic Chair', 'Furniture', 299.99),
(7, 'Standing Desk', 'Furniture', 449.99),
(8, 'Bookshelf', 'Furniture', 129.99),
(9, 'Desk Lamp', 'Furniture', 44.99),
(10, 'Filing Cabinet', 'Furniture', 89.99),
(11, 'Notebook Set', 'Office Supplies', 12.99),
(12, 'Pen Pack', 'Office Supplies', 8.99),
(13, 'Sticky Notes', 'Office Supplies', 5.99),
(14, 'Binder Clips', 'Office Supplies', 6.99),
(15, 'Whiteboard', 'Office Supplies', 34.99),
(16, 'Running Shoes', 'Sports', 119.99),
(17, 'Yoga Mat', 'Sports', 29.99),
(18, 'Resistance Bands', 'Sports', 19.99),
(19, 'Water Bottle', 'Sports', 14.99),
(20, 'Fitness Tracker', 'Sports', 79.99),
(21, 'Python Handbook', 'Books', 39.99),
(22, 'Data Science Guide', 'Books', 44.99),
(23, 'SQL Fundamentals', 'Books', 34.99),
(24, 'Business Analysis', 'Books', 29.99),
(25, 'Project Management', 'Books', 24.99);

What categories are in the data?

The 25 products span 5 categories: Electronics, Furniture, Office Supplies, Sports, and Books. Furniture items (like the Standing Desk at $449.99) are intentionally high-value to create clear revenue differences when you aggregate later.

  • Verify the products loaded by running this quick count:
SELECT COUNT(*) FROM products;

You should see 25 in the results pane.

  • Clear the SQL editor. Paste and run the following INSERT statement to load 100 customers. The full statement follows this same pattern through customer_id 100:
INSERT INTO customers (customer_id, name, email, signup_date, region) VALUES
(1, 'Alice Chen', 'alice.chen@email.com', '2023-03-15', 'West'),
(2, 'Bob Martinez', 'bob.martinez@email.com', '2023-05-22', 'South'),
(3, 'Carol Johnson', 'carol.johnson@email.com', '2023-01-10', 'East'),
(4, 'David Kim', 'david.kim@email.com', '2023-07-03', 'West'),
(5, 'Emma Wilson', 'emma.wilson@email.com', '2023-02-28', 'North'),
(6, 'Frank Lopez', 'frank.lopez@email.com', '2023-04-14', 'South'),
(7, 'Grace Lee', 'grace.lee@email.com', '2023-06-19', 'East'),
(8, 'Henry Brown', 'henry.brown@email.com', '2023-08-05', 'West'),
(9, 'Iris Patel', 'iris.patel@email.com', '2023-01-25', 'North'),
(10, 'Jack Thompson', 'jack.thompson@email.com', '2023-09-12', 'South'),
(11, 'Karen Davis', 'karen.davis@email.com', '2023-03-30', 'East'),
(12, 'Leo Garcia', 'leo.garcia@email.com', '2023-05-08', 'West'),
(13, 'Mia Robinson', 'mia.robinson@email.com', '2023-07-21', 'North'),
(14, 'Noah Clark', 'noah.clark@email.com', '2023-02-14', 'South'),
(15, 'Olivia White', 'olivia.white@email.com', '2023-04-02', 'East'),
(16, 'Peter Hall', 'peter.hall@email.com', '2023-06-27', 'West'),
(17, 'Quinn Adams', 'quinn.adams@email.com', '2023-08-16', 'North'),
(18, 'Rachel Moore', 'rachel.moore@email.com', '2023-01-05', 'South'),
(19, 'Sam Taylor', 'sam.taylor@email.com', '2023-09-30', 'East'),
(20, 'Tina Anderson', 'tina.anderson@email.com', '2023-03-18', 'West'),
(21, 'Uma Singh', 'uma.singh@email.com', '2023-05-11', 'North'),
(22, 'Victor Nguyen', 'victor.nguyen@email.com', '2023-07-08', 'South'),
(23, 'Wendy Scott', 'wendy.scott@email.com', '2023-02-20', 'East'),
(24, 'Xavier Young', 'xavier.young@email.com', '2023-04-25', 'West'),
(25, 'Yara King', 'yara.king@email.com', '2023-06-13', 'North'),

What does the customer data look like?

Each customer has a unique ID, name, email, signup date (all in 2023), and one of four regions: West, East, North, or South. The full INSERT statement continues from customer_id 26 through 100, following the same pattern.

Make sure you paste the complete INSERT statement including all 100 rows before running it. The last row ends with a semicolon instead of a comma.

  • After running the full customers INSERT, verify the count:
SELECT COUNT(*) FROM customers;

You should see 100 in the results pane.

  • Clear the SQL editor. Paste and run the INSERT statement to load 500 orders. The structure starts like this:
INSERT INTO orders (order_id, customer_id, product_id, order_date, quantity, total_amount) VALUES
(1, 3, 7, '2024-01-05', 1, 449.99),
(2, 1, 1, '2024-01-07', 2, 59.98),
(3, 5, 16, '2024-01-08', 1, 119.99),
(4, 12, 3, '2024-01-10', 1, 89.99),
(5, 8, 11, '2024-01-11', 3, 38.97),
(6, 15, 6, '2024-01-12', 1, 299.99),
(7, 22, 21, '2024-01-14', 1, 39.99),
(8, 9, 17, '2024-01-15', 2, 59.98),
(9, 30, 2, '2024-01-16', 1, 49.99),
(10, 45, 19, '2024-01-17', 3, 44.97),
(11, 7, 8, '2024-01-18', 1, 129.99),
(12, 18, 4, '2024-01-19', 1, 59.99),
(13, 25, 13, '2024-01-20', 5, 29.95),
(14, 33, 20, '2024-01-21', 1, 79.99),
(15, 41, 10, '2024-01-22', 1, 89.99),
(16, 2, 7, '2024-01-23', 1, 449.99),
(17, 50, 15, '2024-01-24', 1, 34.99),
(18, 14, 3, '2024-01-25', 1, 89.99),
(19, 37, 22, '2024-01-26', 2, 89.98),
(20, 60, 1, '2024-01-27', 1, 29.99),
(21, 4, 6, '2024-01-28', 1, 299.99),
(22, 28, 18, '2024-01-29', 2, 39.98),
(23, 55, 9, '2024-01-30', 1, 44.99),
(24, 11, 5, '2024-01-31', 2, 79.98),
(25, 63, 23, '2024-01-31', 1, 34.99),

What does the order data look like?

Each order references a customer_id and a product_id instead of storing the customer name or product name directly. This is how real databases work. You need JOINs to reconnect these IDs to their details.

The full INSERT statement continues from order_id 26 through 500, spanning January through June 2024. Make sure you paste and run the complete statement with all 500 rows.

  • After running the full orders INSERT, verify the count:
SELECT COUNT(*) FROM orders;

You should see 500 in the results pane.

Seeing a count lower than expected?

  • If your count is less than 100 for customers or less than 500 for orders, you likely pasted only a portion of the INSERT statement. Make sure you copied from the very first line through the final row ending with a semicolon.
  • If you see an error about a UNIQUE constraint, it means you already inserted some rows. Run DELETE FROM customers; (or the relevant table name) to clear it, then re-paste and run the full INSERT.

Why is my row count wrong after running the INSERT?

Save and explore the data

Your data is loaded in memory but not yet saved to disk. You also have not looked at what the data actually looks like row by row.

  • Click Write Changes in the toolbar (or press Ctrl+S) to save the data permanently to your ecommerce.db file.

Before you run the next query, what do you think an order row will look like? Will you see customer names and product names, or just numeric IDs?

  • Explore the order data by running this query:
SELECT * FROM orders LIMIT 10;

You should see 10 rows with columns for order_id, customer_id, product_id, order_date, quantity, and total_amount.

Why are customer_id and product_id just numbers?

Notice that each order shows a customer_id (like 3) and a product_id (like 7) instead of names. Right now, those are just numbers.

You cannot see the customer name or product category without JOINing the orders table to the customers and products tables. That is exactly what you will do in the next step.

Not seeing any results?

  • Make sure you clicked Execute SQL (or pressed F5) after pasting the SELECT statement.
  • If the results pane says "no such table: orders", your CREATE TABLE statements did not run successfully. Go back to the first substep and re-run them.

Why is my SELECT query returning no data in DB Browser?

Your database is loaded with 6 months of e-commerce data across three connected tables. Next up, you will write your first JOINs to pull customer names and product categories into your revenue analysis.

Answer Revenue Questions with JOINs and Aggregations

Your database is loaded with six months of e-commerce data across three tables. Now it is time to make that data answer a real business question.

Imagine a stakeholder asks: "How is revenue trending by month and category?" You cannot answer from a single table because the category lives in products and the dates live in orders. JOINs let you combine them. This is the core BA skill: pulling data from multiple sources to answer one business question.

In this step, get ready to:
  • Write a monthly revenue summary query using GROUP BY and aggregate functions.
  • JOIN orders to products to break down revenue by category.
  • Combine month and category analysis using HAVING to filter out noise.
Write a monthly revenue summary

The first question any stakeholder asks about sales data is "how are we doing over time?" You need to group all 500 orders by month and calculate totals for each.

  • In the Execute SQL tab of DB Browser for SQLite, clear any previous SQL and paste this query:
-- Monthly revenue summary
SELECT
    strftime('%Y-%m', order_date) AS month,
    SUM(total_amount) AS total_revenue,
    COUNT(*) AS order_count,
    ROUND(AVG(total_amount), 2) AS avg_order_value
FROM orders
GROUP BY strftime('%Y-%m', order_date)
ORDER BY month;

What does this query do?

  • strftime('%Y-%m', order_date) extracts just the year and month from each order date, so all orders in January 2024 become 2024-01.
  • SUM(total_amount) adds up all revenue for each month.
  • COUNT(*) counts how many orders were placed in each month.
  • ROUND(AVG(total_amount), 2) calculates the average order value, rounded to 2 decimal places.
  • GROUP BY collapses all rows sharing the same month into a single summary row.
  • Click the Play button (or press F5) to run the query.
  • You should see 6 rows in the results pane, one for each month from 2024-01 through 2024-06, each showing total revenue, order count, and average order value.

Look at the numbers. Which month has the highest total revenue? Is there an upward or downward trend across the six months?

Seeing zero rows or an error?

  • Make sure you typed strftime (not strFtime or str_ftime). SQLite function names are case-sensitive.
  • Check that the format string is exactly '%Y-%m' with a capital Y and lowercase m.
  • Confirm you loaded the order data in Step 2 by running SELECT COUNT(*) FROM orders; first. It should return 500.

Why is my monthly revenue query returning an error or no rows?

Break down revenue by product category

Your monthly summary shows the overall trend, but a stakeholder also wants to know which product categories drive the business. The category information lives in the products table, not in orders. A JOIN connects the two tables using the shared product_id column.

  • Clear the Execute SQL tab and paste this query to see revenue broken down by category:
-- Revenue by category
SELECT
    p.category,
    SUM(o.total_amount) AS total_revenue,
    COUNT(*) AS order_count,
    ROUND(AVG(o.total_amount), 2) AS avg_order_value
FROM orders o
JOIN products p ON o.product_id = p.product_id
GROUP BY p.category
ORDER BY total_revenue DESC;

What does the JOIN do here?

JOIN products p ON o.product_id = p.product_id matches each order row with its corresponding product row. Once connected, you can access p.category from the products table while still aggregating o.total_amount from orders.

The aliases o and p are shorthand names for orders and products. They keep the query readable when referencing columns from multiple tables.

  • Click Play to run the query.
  • You should see 5 rows, one per category (Electronics, Furniture, Office Supplies, Sports, Books), ranked from highest to lowest revenue.

Notice which category sits at the top. This is the kind of insight that tells a business where to invest marketing budget.

Seeing a 'no such column' error?

  • Make sure you included the table aliases: FROM orders o and JOIN products p. Without these, SQLite does not know what o.product_id or p.category refers to.
  • Check the ON clause uses o.product_id = p.product_id (not o.product_id = p.product_name or another column).

Help me fix my JOIN query that is returning a column error.

Combine month and category with HAVING

Now combine both dimensions: month and category in one query. This is the pivot-style breakdown stakeholders use to spot which categories are growing or declining over time.

  • Clear the Execute SQL tab and paste this combined query:
-- Monthly revenue by category with HAVING filter
SELECT
    strftime('%Y-%m', o.order_date) AS month,
    p.category,
    SUM(o.total_amount) AS total_revenue,
    COUNT(*) AS order_count
FROM orders o
JOIN products p ON o.product_id = p.product_id
GROUP BY strftime('%Y-%m', o.order_date), p.category
HAVING SUM(o.total_amount) > 1000
ORDER BY month, total_revenue DESC;

What does HAVING do?

HAVING SUM(o.total_amount) > 1000 filters out month-category combinations that generated less than $1,000 in revenue. Unlike WHERE (which filters individual rows before grouping), HAVING filters groups after the aggregation is calculated.

This removes noise from your results. Categories that barely sold anything in a month are not useful for trend analysis.

  • Click Play to run the query.
  • You should see multiple rows grouped by month, showing only the categories that crossed the $1,000 threshold each month.

Scan the results for patterns. Do the same categories appear every month, or do some appear and disappear? That inconsistency is a signal worth reporting to a stakeholder.

Getting fewer rows than expected?

  • This is likely correct. The HAVING clause intentionally removes low-revenue combinations. Some categories in some months simply did not reach $1,000.
  • If you see zero rows, check that your HAVING threshold is 1000 (not 10000). An extra zero would filter out everything.

Why is my HAVING query returning unexpected results?

Before you move on, predict: based on what you saw in your monthly totals and category breakdown, which category do you think appears in every single month of this combined query?

✔️ Awesome, I've got everything!

Great. Make sure you have saved your database by clicking Write Changes in the toolbar.

ⓧ I'd like to double check the full code

Scroll up and confirm you have all three queries in order:

  • The monthly revenue summary query (starts with -- Monthly revenue summary) that returns 6 rows.
  • The revenue by category query (starts with -- Revenue by category) that JOINs orders to products and returns 5 rows.
  • The monthly revenue by category query (starts with -- Monthly revenue by category with HAVING filter) that uses HAVING to filter low-revenue combinations.

Each query should run independently and produce results when you highlight it and press F5.

You now have three queries that answer a real stakeholder question about revenue trends. Next up, you will dig into who is spending the most and segment customers into value tiers.

Segment Customers by Value

Your revenue queries from the last step revealed which months are strongest and which product categories drive the business. You now have a solid picture of overall performance.

But not all customers are equal. A business analyst needs to identify who drives the most value so the company can prioritize retention efforts. In this step, you will segment customers into value tiers based on their lifetime spend.

In this step, get ready to:
  • Calculate each customer's lifetime value by joining order and customer data.
  • Assign value tiers (High, Medium, Low) using a CASE expression and a CTE.
  • Summarize the tier distribution for a stakeholder-ready slide.
Write a customer lifetime value query

To identify your best customers, you need to add up every order each customer has ever placed. The order totals live in the orders table and the customer names live in the customers table. A JOIN connects them.

Before you run this query, think about what columns you will see in the results. Will you be able to tell at a glance which customers are "high value" versus "low value"?

  • In the Execute SQL tab of DB Browser, clear any previous query and paste the following:
-- Customer lifetime value
SELECT
    c.name,
    c.region,
    SUM(o.total_amount) AS total_spend,
    COUNT(*) AS order_count
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.name, c.region
ORDER BY total_spend DESC;
  • Click the Play button (or press F5) to run it.

What does this query do?

  • JOIN customers c ON o.customer_id = c.customer_id links each order to the customer who placed it.
  • GROUP BY c.customer_id, c.name, c.region collapses all of one customer's orders into a single row.
  • SUM(total_amount) AS total_spend adds up everything they have ever spent.
  • ORDER BY total_spend DESC puts the biggest spenders at the top.

You should see 100 rows in the results pane, one per customer, ranked from highest to lowest lifetime spend.

Notice the gap: you can see dollar amounts, but there is no label telling you who is "high value" versus "low value." Basic GROUP BY calculates totals but cannot assign tier labels based on those totals in the same query. That is the wall you hit now.

Seeing 0 rows or an error?

  • Check that you have not accidentally cleared the database. Run SELECT COUNT(*) FROM orders; and confirm it returns 500.
  • Make sure you typed o.customer_id = c.customer_id in the JOIN clause. A mismatched column name returns no results.
  • Why is my customer lifetime value query returning an error?
Add value tiers with a CTE and CASE expression

The fix is a CTE (Common Table Expression). A CTE lets you calculate the totals first, then reference those calculated values in a second query that applies labels.

  • Clear the Execute SQL tab and paste this query that wraps the aggregation in a CTE, then applies a CASE expression:
-- Customer value tiers using a CTE
WITH customer_totals AS (
    SELECT
        c.name,
        c.region,
        SUM(o.total_amount) AS total_spend,
        COUNT(*) AS order_count
    FROM orders o
    JOIN customers c ON o.customer_id = c.customer_id
    GROUP BY c.customer_id, c.name, c.region
)
SELECT
    name,
    region,
    total_spend,
    order_count,
    CASE
        WHEN total_spend >= 1500 THEN 'High'
        WHEN total_spend >= 750 THEN 'Medium'
        ELSE 'Low'
    END AS value_tier
FROM customer_totals
ORDER BY total_spend DESC;
  • Click Play (or press F5) to run it.

What is a CTE and why use it here?

The WITH customer_totals AS (...) block creates a temporary named result set. Think of it as a staging area. The aggregation (SUM, COUNT, GROUP BY) runs inside the CTE first.

The outer SELECT then reads from that staging area and can reference total_spend as a real column. That is what lets the CASE expression compare it against thresholds (1500, 750) and assign tier labels.

You should see 100 rows again, but now with a new value_tier column showing High, Medium, or Low for each customer. The top spender should show a total_spend above $2,000.

All customers showing the same tier?

  • Check that your CASE thresholds use >= (greater than or equal), not just >. The thresholds are 1500 and 750.
  • Make sure the CASE expression is in the outer SELECT (after the CTE), not inside the WITH block.
  • Why is my CTE-based CASE query not assigning tiers correctly?
Summarize the tier distribution

Scrolling through 100 rows is useful for you, but a stakeholder wants one summary slide. You need a query that counts how many customers are in each tier and what they spend on average.

  • Clear the Execute SQL tab and paste this query that wraps the tiered CTE in a second layer and groups by tier:
WITH customer_totals AS (
    SELECT
        c.name,
        c.region,
        SUM(o.total_amount) AS total_spend,
        COUNT(*) AS order_count
    FROM orders o
    JOIN customers c ON o.customer_id = c.customer_id
    GROUP BY c.customer_id, c.name, c.region
),
tiered AS (
    SELECT
        name,
        region,
        total_spend,
        order_count,
        CASE
            WHEN total_spend >= 1500 THEN 'High'
            WHEN total_spend >= 750 THEN 'Medium'
            ELSE 'Low'
        END AS value_tier
    FROM customer_totals
)
SELECT
    value_tier,
    COUNT(*) AS customer_count,
    ROUND(AVG(total_spend), 2) AS avg_spend
FROM tiered
GROUP BY value_tier
ORDER BY avg_spend DESC;

Before you run this, predict: which tier do you think has the most customers? Which has the fewest?

  • Click Play (or press F5) to run the query.

What does this query do?

  • The first CTE (customer_totals) calculates each customer's total spend and order count.
  • The second CTE (tiered) assigns a value_tier label using the CASE expression.
  • The final SELECT groups by value_tier and counts how many customers fall into each bucket, plus the average spend per bucket.

You should see exactly 3 rows: one for High, one for Medium, and one for Low. Note the customer count and average spend for each tier. This is the summary you would put on a stakeholder slide: "We have X high-value customers averaging $Y in spend."

Seeing only 1 or 2 rows?

  • Check that both CTEs are separated by a comma after the closing parenthesis: ), not just ). Missing the comma between CTEs causes a syntax error or unexpected grouping.
  • Confirm that your CASE thresholds are 1500 and 750. Different thresholds might put all customers into one tier.
  • Why is my tier summary SQL query not returning 3 rows?

✔️ Awesome, I've got everything!

Great. Double check you have all three queries saved. You can keep them in a file called step4_queries.sql for your portfolio.

ⓧ I'd like to double check the full code

Your step4_queries.sql file should contain these three queries in order. Scroll up and confirm each one is present:

  • Customer lifetime value query (SELECT with JOIN, GROUP BY, ORDER BY total_spend DESC).
  • Customer value tiers query (WITH customer_totals CTE, outer SELECT with CASE expression assigning High/Medium/Low).
  • Tier summary query (two CTEs: customer_totals and tiered, final SELECT grouping by value_tier with COUNT and AVG).

You have just graduated from flat aggregations to layered analysis. Your queries can now calculate, label, and summarize in a single pass. Next up, you will use window functions to calculate month-over-month growth rates and rank products within their categories.

Use Window Functions for Growth Analysis

In the previous step, you segmented customers into value tiers using CTEs and CASE expressions. A stakeholder now sees your monthly revenue totals from Step 3 and asks the obvious follow-up: "What is our month-over-month growth rate?"

You cannot calculate growth with GROUP BY alone because each row only knows about itself. It has no way to peek at the previous row's value. Window functions solve this by letting each row "see" neighboring rows without collapsing the result set.

In this step, get ready to:
  • Use LAG to compare each month's revenue to the previous month.
  • Calculate month-over-month growth percentages.
  • Rank products within categories using ROW_NUMBER.
Add LAG to see previous month's revenue

The LAG window function grabs a value from the previous row based on a specified ordering. By applying it to your monthly revenue data, you can place each month's total right next to the prior month's total in the same row.

  • In the Execute SQL tab of DB Browser for SQLite, clear any existing query text.
  • Write this query that wraps your monthly revenue calculation in a CTE, then uses LAG in the outer SELECT:
-- Monthly revenue with LAG to see previous month
WITH monthly AS (
    SELECT
        strftime('%Y-%m', order_date) AS month,
        SUM(total_amount) AS total_revenue
    FROM orders
    GROUP BY strftime('%Y-%m', order_date)
)
SELECT
    month,
    total_revenue,
    LAG(total_revenue) OVER (ORDER BY month) AS prev_month_revenue
FROM monthly
ORDER BY month;

What does this code do?

The WITH monthly AS (...) CTE calculates total revenue per month, giving you a clean result set where each row is one month.

LAG(total_revenue) OVER (ORDER BY month) looks at the row before the current one (ordered by month) and pulls its total_revenue value into the current row. The first row (January) has no previous row, so it shows NULL.

  • Click the Play button (or press F5) to run the query.

You should see 6 rows. Each row now shows the current month's revenue AND the previous month's revenue side by side. January's prev_month_revenue is NULL because there is no prior month in the dataset.

Seeing an error about window functions?

Make sure you have the OVER (ORDER BY month) clause after LAG. Without OVER, SQLite treats LAG as a regular function and it will fail.

Also confirm the CTE alias monthly matches what you reference in the outer SELECT's FROM monthly clause.

Help me debug my LAG window function query

Calculate month-over-month growth percentage

Seeing the raw numbers side by side is useful, but stakeholders want a percentage. Growth percentage compares the difference between two months to the prior month's value.

  • Clear the Execute SQL tab and paste this updated query that adds the growth_pct calculation:
-- Month-over-month revenue with LAG window function
WITH monthly AS (
    SELECT
        strftime('%Y-%m', order_date) AS month,
        SUM(total_amount) AS total_revenue
    FROM orders
    GROUP BY strftime('%Y-%m', order_date)
)
SELECT
    month,
    total_revenue,
    LAG(total_revenue) OVER (ORDER BY month) AS prev_month_revenue,
    ROUND(
        ((total_revenue - LAG(total_revenue) OVER (ORDER BY month)) * 100.0
        / LAG(total_revenue) OVER (ORDER BY month)), 1
    ) AS growth_pct
FROM monthly
ORDER BY month;

What does the growth calculation do?

The formula (current - previous) * 100.0 / previous is the standard percentage-change calculation. Multiplying by 100.0 (with the decimal) forces floating-point division so you get decimal results instead of integer truncation.

ROUND(..., 1) rounds to one decimal place for clean reporting. January still shows NULL for both prev_month_revenue and growth_pct because there is nothing to compare it against.

  • Click Play (or press F5) to run the query.

You should see 6 rows with four columns: month, total_revenue, prev_month_revenue, and growth_pct. Look at the growth percentages. At least one month shows positive growth and at least one shows a decline.

Why is this valuable to a stakeholder?

Raw revenue numbers require mental math to interpret. A growth_pct column tells the story instantly: "We grew 8% in March but shrank 2% in April." This is the format executives read on dashboards.

All growth_pct values showing NULL?

Only the first row (January) should be NULL. If all rows show NULL, check that your OVER (ORDER BY month) clause is correct. Without proper ordering, LAG cannot determine which row is "previous."

Help me figure out why my growth percentage is all NULL

Rank products within categories using ROW_NUMBER

Another classic BA deliverable is "top performers in each segment." The ROW_NUMBER window function assigns a sequential rank to each row within a partition. Combined with PARTITION BY, it lets you rank products independently within each category.

  • Clear the Execute SQL tab and paste this product ranking query:
-- Top product per category using ROW_NUMBER
WITH product_revenue AS (
    SELECT
        p.category,
        p.product_name,
        SUM(o.total_amount) AS total_revenue,
        COUNT(*) AS times_sold
    FROM orders o
    JOIN products p ON o.product_id = p.product_id
    GROUP BY p.category, p.product_name
),
ranked AS (
    SELECT
        category,
        product_name,
        total_revenue,
        times_sold,
        ROW_NUMBER() OVER (PARTITION BY category ORDER BY total_revenue DESC) AS rank_in_category
    FROM product_revenue
)
SELECT
    category,
    product_name,
    total_revenue,
    times_sold
FROM ranked
WHERE rank_in_category = 1
ORDER BY total_revenue DESC;

What does this query do?

  • product_revenue CTE calculates total revenue and times sold for every product by joining orders to products.
  • ranked CTE applies ROW_NUMBER() OVER (PARTITION BY category ORDER BY total_revenue DESC) to assign rank 1 to the highest-revenue product in each category, rank 2 to the second, and so on.
  • The final SELECT filters to rank_in_category = 1 so you see only the single top seller in each category.
  • Before you run this, predict: how many rows will come back? (Hint: how many categories exist in your products table?)
  • Click Play (or press F5) to run the query.

You should see exactly 5 rows. One top-selling product per category (Books, Electronics, Furniture, Office Supplies, Sports), ordered by total revenue from highest to lowest. This is the kind of summary you would put on a stakeholder slide: "Here are our top performers in each segment."

Seeing more than 5 rows?

Check that your WHERE rank_in_category = 1 filter is in the final SELECT, not inside one of the CTEs. If you place it inside the ranked CTE, the window function has not been computed yet and it will error or return unexpected results.

Help me debug my ROW_NUMBER product ranking query

Save both queries together in a file called step5_queries.sql for your portfolio. You can do this by pasting both queries into a text file, or by using the SQL log in DB Browser.

✔️ Awesome, I've got everything!

Great. Double check that your step5_queries.sql file contains both the LAG growth query and the ROW_NUMBER ranking query.

ⓧ I'd like to double check the full code

Your step5_queries.sql should contain these two queries in order. Scroll up and confirm each matches what you have:

  • Query 1: "Month-over-month revenue with LAG window function" query. Uses a WITH monthly AS CTE, then SELECTs month, total_revenue, prev_month_revenue (via LAG), and growth_pct (via ROUND).
  • Query 2: "Top product per category using ROW_NUMBER" query. Uses two CTEs (product_revenue and ranked), applies ROW_NUMBER with PARTITION BY category, and filters to rank_in_category = 1.

You now have growth analysis and competitive ranking in your toolkit. Next up, you will turn these numbers into something a non-technical stakeholder can act on: a written business recommendation.

Write Your Stakeholder Recommendation

Your queries now surface monthly revenue trends, customer tiers, growth rates, and category rankings. But SQL skills alone do not get you hired as a Business Analyst. What separates a strong BA candidate is the ability to translate data into a decision a stakeholder can act on.

In this step, you will pull specific numbers from your queries and write a short stakeholder recommendation. This is the exact deliverable format BAs produce in real roles: a paragraph that cites metrics, names an action, and states the expected impact.

In this step, get ready to:
  • Gather 3-5 key metrics from your query results.
  • Write a stakeholder-facing recommendation backed by specific numbers.
  • Save a portfolio-ready deliverable.
Gather your key findings

A good recommendation starts with the right numbers. You need to identify 3-5 metrics that tell a coherent story about the business.

  • Switch back to the Execute SQL tab in DB Browser for SQLite.
  • Re-run your monthly revenue summary to see the 6-month trend by pasting this query and pressing F5:
-- Monthly revenue summary
SELECT
    strftime('%Y-%m', order_date) AS month,
    SUM(total_amount) AS total_revenue,
    COUNT(*) AS order_count,
    ROUND(AVG(total_amount), 2) AS avg_order_value
FROM orders
GROUP BY strftime('%Y-%m', order_date)
ORDER BY month;

What numbers to note

Look at the total_revenue column across all 6 rows. Is revenue generally growing, flat, or declining? Note the highest and lowest months.

  • Now re-run your growth percentage query to see the exact month-over-month change:
-- Month-over-month growth rate
WITH monthly AS (
    SELECT
        strftime('%Y-%m', order_date) AS month,
        SUM(total_amount) AS total_revenue
    FROM orders
    GROUP BY strftime('%Y-%m', order_date)
)
SELECT
    month,
    total_revenue,
    LAG(total_revenue) OVER (ORDER BY month) AS prev_month_revenue,
    ROUND(
        ((total_revenue - LAG(total_revenue) OVER (ORDER BY month)) * 100.0
        / LAG(total_revenue) OVER (ORDER BY month)), 1
    ) AS growth_pct
FROM monthly
ORDER BY month;

Note the growth_pct for the most recent month (June 2024). Also note any month that shows a decline.

  • Re-run your customer tier summary to get the count of high-value customers:
-- Tier summary
WITH customer_totals AS (
    SELECT
        c.name,
        c.region,
        SUM(o.total_amount) AS total_spend,
        COUNT(*) AS order_count
    FROM orders o
    JOIN customers c ON o.customer_id = c.customer_id
    GROUP BY c.customer_id, c.name, c.region
),
tiered AS (
    SELECT
        name,
        region,
        total_spend,
        order_count,
        CASE
            WHEN total_spend >= 1500 THEN 'High'
            WHEN total_spend >= 750 THEN 'Medium'
            ELSE 'Low'
        END AS value_tier
    FROM customer_totals
)
SELECT
    value_tier,
    COUNT(*) AS customer_count,
    ROUND(AVG(total_spend), 2) AS avg_spend
FROM tiered
GROUP BY value_tier
ORDER BY avg_spend DESC;

Note how many customers fall into the 'High' tier and what their average spend is. This is your retention target audience.

  • Finally, re-run the top product per category query to identify the best seller in the strongest category:
-- Top product per category
WITH product_revenue AS (
    SELECT
        p.category,
        p.product_name,
        SUM(o.total_amount) AS total_revenue,
        COUNT(*) AS times_sold
    FROM orders o
    JOIN products p ON o.product_id = p.product_id
    GROUP BY p.category, p.product_name
),
ranked AS (
    SELECT
        category,
        product_name,
        total_revenue,
        times_sold,
        ROW_NUMBER() OVER (PARTITION BY category ORDER BY total_revenue DESC) AS rank_in_category
    FROM product_revenue
)
SELECT
    category,
    product_name,
    total_revenue,
    times_sold
FROM ranked
WHERE rank_in_category = 1
ORDER BY total_revenue DESC;

Note the top-ranked category and its best-selling product. You now have all the numbers you need for your recommendation.

Why 3-5 numbers, not all of them?

Stakeholders don't want a data dump. They want a focused narrative. Picking 3-5 numbers forces you to choose which data points actually support your argument.

Write your recommendation document

Now you will turn those numbers into a structured deliverable. A stakeholder recommendation has two parts: the metrics that ground it and the paragraph that proposes action.

  • Press the Windows key to open your search bar.
  • Type Notepad and press Enter to open it.
  • At the top of the file, type Key Metrics as a heading.
  • Below that, list your 3-5 metrics with clear labels. Each line should state what the metric is and its value from your query results.

Your Key Metrics section should look something like this (with your actual numbers):

Key Metrics
- Total revenue trend: [growing/declining] over Jan-Jun 2024
- Highest-value category: [category name] at $[amount] total revenue
- Most recent month growth: [X]% month-over-month in June 2024
- High-value customers: [N] customers averaging $[amount] lifetime spend
- Top product in strongest category: [product name] at $[amount] revenue

What makes a good metric label?

Each label should name the business question it answers, not the SQL function you used. "Total revenue trend" tells a stakeholder what to expect. "SUM(total_amount) GROUP BY month" does not.

  • Below your Key Metrics section, add a blank line, then type Recommendation as a second heading.
  • Write 3-4 sentences following this structure:
  • Sentence 1: State the business situation, citing one metric.
  • Sentence 2: Identify the specific opportunity or problem, citing a second metric.
  • Sentence 3: Recommend a concrete action.
  • Sentence 4: State the expected impact.

✔️ I'll give it a go!

Use your numbers from the Key Metrics section and write the paragraph in your own words. Remember: a stakeholder should be able to read this and know exactly what to do next.

ⓧ Maybe a little help...

Here is an example recommendation paragraph. Replace the bracketed values with your actual numbers:

Recommendation
Revenue grew [X]% month-over-month in June, driven primarily by [Category].
However, [other category] showed declining order volume over the same period.
I recommend reallocating Q3 marketing spend toward [Category] promotions
targeting our [N] high-value customers, who average $[amount] in lifetime
spend. Based on current growth rates, this could increase [Category] revenue
by [estimate]% in Q3.

Make it specific

Replace every bracketed placeholder with an actual number from your queries. A recommendation that says "revenue grew 4.2%" is far more credible than one that says "revenue grew."

Save your portfolio artifact
  • In Notepad, press Ctrl+S to save.
  • Save the file as recommendation.txt in the same folder as your ecommerce.db file.

Open the saved file and confirm it has two sections: a Key Metrics list with 3-5 labeled numbers, and a Recommendation paragraph of 3-4 sentences that names a specific action and cites at least two metrics.

Why this matters for your career

Most SQL tutorials end at the query. But in interviews and on the job, nobody asks "show me your JOIN syntax." They ask "what should we do about declining retention?" and expect a data-backed answer.

This file proves you can do both: pull the data AND communicate the decision. Keep it as a portfolio piece alongside your SQL queries.

Not sure if your recommendation is strong enough?

Check that every sentence in your Recommendation paragraph contains at least one specific number. Vague statements like "revenue is growing" are weaker than "revenue grew 4.2% in June."

Make sure your action is concrete. "Improve marketing" is vague. "Reallocate Q3 budget toward Furniture promotions" is actionable.

Help me write a stronger data-backed recommendation for my e-commerce analysis.

Secret mission

Build a Customer Churn Risk Query

Identifying customers at risk of leaving is one of the highest-impact analyses a BA can deliver. Combine date arithmetic, subqueries, CASE expressions, and JOINs into a single query that flags customers who have not purchased in 90+ days.

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:

  • DB Browser for SQLite (installed application).
  • ecommerce.db database file (saved to your Desktop or Documents folder).
  • SQL query files (create_tables.sql, insert_customers.sql, insert_products.sql, insert_orders.sql, step3_queries.sql, step4_queries.sql, step5_queries.sql, secret_mission_churn.sql).
  • recommendation.txt stakeholder recommendation file.

Keep everything running

No action needed. Choose this if you are still actively building or want to keep your SQL queries and database for future reference.

  • Your ecommerce.db file stays where you saved it and can be reopened anytime in DB Browser for SQLite.
  • Your SQL files and recommendation.txt remain on disk as portfolio artifacts.
  • DB Browser for SQLite stays installed on your machine and uses no resources when closed.

Pause - I'll come back to this later

Close DB Browser for SQLite to free up memory. Your files remain on disk and can be reopened anytime.

  • Close DB Browser for SQLite if it is still open.
  • Your ecommerce.db file, SQL files, and recommendation.txt remain safely on disk.
  • To resume later, open DB Browser for SQLite and click Open Database to reload ecommerce.db.

Delete - I don't want to use this again

Remove all project files and uninstall DB Browser for SQLite to start fresh.

  • Close DB Browser for SQLite if it is still open.
  • Delete your project files (ecommerce.db, all .sql files, and recommendation.txt) from the folder where you saved them. Right-click each file and select Delete, or select all and press the Delete key.
  • To uninstall DB Browser for SQLite, open Settings.
  • Select Apps, then Installed apps.
  • Find DB Browser for SQLite in the list, click the three-dot menu, and select Uninstall.
  • Follow the uninstall prompts to complete removal.

Nice Work!

Nice Work!

You just built a complete SQL analytics workflow from scratch. Starting with raw e-commerce data across three tables, you wrote increasingly sophisticated queries to uncover revenue trends, segment customers by value, and calculate growth rates.

You've learned how to:

  • Write multi-table JOINs with GROUP BY and HAVING to combine customer, order, and product data into revenue summaries and category breakdowns.
  • Use window functions (LAG, ROW_NUMBER) to calculate month-over-month growth percentages and rank products within categories.
  • Translate query results into a stakeholder-facing recommendation that cites specific metrics, names a concrete action, and states the expected business impact.
  • Secret Mission: Built a customer churn risk analysis combining date arithmetic, CASE logic, and subqueries to flag customers who have not purchased in 90+ days.

Ready to quiz yourself?