SQL Analytics with Classic Models

Write advanced SQL queries using JOINs, CTEs, and window functions.

Introduction

30 Second Summary

Every business decision starts with a question. "Which customers spend the most?" "What products are declining?" The people who can answer these questions by querying a database are in high demand across every industry.

In this project, you will write progressively complex SQL queries against a real business database, practicing the exact patterns that data analyst and engineer interviews test: multi-table JOINs, aggregations, CTEs, and window functions.

What You'll Build

A set of SQL analytics queries that answer business questions for Classic Models, a retailer of scale model cars, progressing from multi-table JOINs through advanced window functions.

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

Revenue analysis queries using JOINs across orders, customers, and products tables.
Customer segmentation queries using subqueries and CTEs to classify buyers into revenue tiers.
Sales trend analysis with window functions showing month-over-month growth, running totals, and ranked performance.
Secret Mission: Build a comprehensive sales rep leaderboard that ranks employees by revenue, shows their percentage contribution, and flags underperformers.

Are there any prerequisites?

You need MySQL 8.0 or later installed locally, along with MySQL Workbench. Basic familiarity with SELECT and WHERE statements is helpful but not required.

Set Up the classicmodels Database

Every SQL analytics project starts with data. In this project, you will query the classicmodels database, a sample dataset that simulates a scale model car retailer with customers, orders, products, and employees.

In this step, you will download the classicmodels database, load it into your local MySQL server, and verify all 8 tables are ready to query.

In this step, get ready to:

Download the classicmodels sample database file.
Load the database into your local MySQL server.
Verify all 8 tables are present and contain data.

Download the sample database

The classicmodels database is a free sample dataset hosted by mysqltutorial.org. It contains 8 interrelated tables covering customers, orders, products, employees, and offices.

Open this link in your browser: https://www.mysqltutorial.org/getting-started-with-mysql/mysql-sample-database/
Click Download MySQL Sample Database to get the ZIP file.
Unzip the downloaded file. The extracted file is named mysqlsampledatabase.sql.
Move the extracted file somewhere easy to find, like your Downloads folder.

What is classicmodels?

The classicmodels database simulates a retailer that sells scale model cars. Its 8 tables include customers, employees, offices, orders, orderdetails, payments, products, and productlines.

These tables have realistic foreign key relationships between them. Customers place orders, orders contain line items linked to products, employees act as sales reps for customers, and so on. This makes it perfect for practicing multi-table JOINs.

Load the database into MySQL

Now you will run the SQL script to create the database and populate it with sample data. MySQL Workbench makes this straightforward with its built-in script execution.

Switch to MySQL Workbench and connect to your local MySQL server by double-clicking your connection on the home screen.
A SQL query tab opens automatically after connecting.
Click File in the menu bar.
Click Open SQL Script.
Navigate to where you saved mysqlsampledatabase.sql and open it.
Click the lightning bolt icon in the toolbar to execute the entire script.

What does the lightning bolt do?

The lightning bolt icon executes all SQL statements in the current editor tab. In this case, it runs the full classicmodels script which creates the database, creates all 8 tables, and inserts the sample data in one go.

Prefer the command line?

You can also load the database from a terminal. Connect with mysql -u root -p and then run the source command pointing to your file's location.

mysql -u root -p

source [[SQLPATH="/path/to/mysqlsampledatabase.sql"]]

Script execution failed?

If you see an access denied error, make sure you are connecting with a user that has full privileges (like root). Check that your password is correct when connecting.

If Workbench seems to hang, give it a moment. The script inserts thousands of rows across multiple tables, so it may take 10-30 seconds to complete.

help me troubleshoot loading the classicmodels SQL script in MySQL Workbench

Verify the database loaded correctly

Before writing any analytics queries, confirm that the database and all its tables exist with data inside them.

Open a new query tab by pressing Cmd+T (macOS) or Ctrl+T (Windows).
Run the following verification queries by pasting them into the editor and clicking the lightning bolt icon:

-- Check that classicmodels appears in your databases
SHOW DATABASES;

-- Switch to the classicmodels database
USE classicmodels;

-- Confirm all 8 tables exist
SHOW TABLES;

-- Verify data is present (should return 326)
SELECT COUNT(*) FROM orders;

What should I see?

SHOW DATABASES; returns a list of all databases on your server. You should see classicmodels in that list.

SHOW TABLES; returns the 8 tables: customers, employees, offices, orderdetails, orders, payments, productlines, and products.

The SELECT COUNT(*) query should return exactly 326 rows in the orders table.

Confirm classicmodels appears in the database list.
Confirm SHOW TABLES; returns 8 tables.
Confirm the count query returns 326.

Don't see classicmodels in the list?

Go back to the tab where you executed the script and check the Output panel at the bottom of the screen. Look for any red error messages that may indicate what went wrong during execution.

If the script ran without errors but you still cannot see the database, try clicking the refresh icon next to SCHEMAS in the left sidebar panel.

help me figure out why classicmodels is not showing up after running the SQL script

As a final check, run a quick query to see actual customer data:

Run this query to preview real data from the customers table:

SELECT * FROM customers LIMIT 5;

You should see 5 rows of customer data including names, addresses, and credit limits.

Your database is loaded and ready to query. Next up, you will write multi-table JOIN queries to calculate revenue across orders, customers, and product lines.

Revenue Analysis with Multi-Table JOINs

Your database is loaded with 8 interconnected tables covering customers, orders, products, and employees. Now it's time to start asking business questions.

In this step, you'll write multi-table JOIN queries that calculate revenue by combining rows from different tables. These are the exact query patterns hiring managers look for in SQL interviews.

In this step, get ready to:

Calculate total revenue per order by joining two tables.
Find the top 10 customers by lifetime revenue across three tables.
Break down revenue by product line to identify top-performing categories.

Calculate total revenue per order

Each order in classicmodels is split across two tables. The orders table stores metadata like the order date, while orderdetails holds the individual line items with quantities and prices.

To calculate revenue, you need to JOIN these tables and multiply each line item's quantity by its price.

In MySQL Workbench, create a new query tab by clicking File in the menu bar, then New Query Tab.
Add the file header, database selector, and your first revenue query by pasting this code into the empty tab:

-- ============================================
-- SQL Analytics: Classic Models Database
-- ============================================

USE classicmodels;

-- ============================================
-- STEP 2: Revenue Analysis with Multi-Table JOINs
-- ============================================

-- 2.1 Total revenue per order
SELECT
    o.orderNumber,
    o.orderDate,
    SUM(od.quantityOrdered * od.priceEach) AS orderRevenue
FROM orders o
JOIN orderdetails od ON o.orderNumber = od.orderNumber
GROUP BY o.orderNumber, o.orderDate
ORDER BY orderRevenue DESC;

What does this query do?

JOIN orderdetails od ON o.orderNumber = od.orderNumber connects each order to its line items using the shared orderNumber column.
SUM(od.quantityOrdered * od.priceEach) multiplies quantity by price for each line item, then sums them into a single revenue figure per order.
GROUP BY o.orderNumber, o.orderDate collapses all line items for each order into one row.
The aliases o and od are short names for the tables, keeping the query readable.

Save the file by clicking File then Save Script As. Name it analytics.sql and save it to your Desktop.
Highlight just the SELECT query (from SELECT to the closing semicolon) and click the lightning bolt icon to execute it.
You should see a results grid with 326 rows, one per order, sorted by orderRevenue from highest to lowest.

Seeing zero rows or an error?

If you get an "Unknown database" error, make sure you highlighted and ran the USE classicmodels; line first.
If your results look wrong, check that you selected only the query text (not the comment lines above it) before clicking execute.
Help me debug my revenue per order query

Find top 10 customers by lifetime revenue

Revenue per order is useful, but business leaders want to know which customers spend the most. This requires joining a third table to connect orders back to the customer who placed them.

Add the following query below your existing code in analytics.sql:

-- 2.2 Top 10 customers by lifetime revenue
SELECT
    c.customerNumber,
    c.customerName,
    c.country,
    SUM(od.quantityOrdered * od.priceEach) AS totalRevenue
FROM customers c
JOIN orders o ON c.customerNumber = o.customerNumber
JOIN orderdetails od ON o.orderNumber = od.orderNumber
GROUP BY c.customerNumber, c.customerName, c.country
ORDER BY totalRevenue DESC
LIMIT 10;

What does this query do?

The three-table join chain goes customers → orders → orderdetails. Each JOIN connects using a shared key column.
GROUP BY c.customerNumber, c.customerName, c.country rolls up all of a customer's orders into one row. Every non-aggregated column in the SELECT must appear in GROUP BY.
LIMIT 10 caps the results at the top 10 after sorting by revenue descending.
Including c.country reveals geographic patterns in your highest-value customers.

Save analytics.sql.
Highlight the new query and click the lightning bolt icon to execute it.
You should see exactly 10 rows showing the highest-spending customers with their country and total revenue.

Getting more than 10 rows or a GROUP BY error?

If MySQL complains about GROUP BY, make sure every column in your SELECT that is not inside an aggregate function (SUM, COUNT, etc.) also appears in the GROUP BY clause.
If you see more than 10 rows, confirm you have LIMIT 10 at the very end of the query, after the ORDER BY line.
Help me debug my top 10 customers query

Break down revenue by product line

Knowing which product categories drive the most revenue helps a business decide where to invest. This query joins orderdetails to products to group sales data by product line.

Add the following query below your existing code in analytics.sql:

-- 2.3 Revenue breakdown by product line
SELECT
    p.productLine,
    SUM(od.quantityOrdered * od.priceEach) AS totalRevenue,
    SUM(od.quantityOrdered) AS totalQuantitySold,
    ROUND(AVG(od.priceEach), 2) AS avgPricePerUnit
FROM orderdetails od
JOIN products p ON od.productCode = p.productCode
GROUP BY p.productLine
ORDER BY totalRevenue DESC;

What does this query do?

SUM(od.quantityOrdered * od.priceEach) gives total revenue per product line.
SUM(od.quantityOrdered) counts how many units were sold across all orders in that category.
ROUND(AVG(od.priceEach), 2) computes the average selling price and rounds it to two decimal places for readability.
Starting the FROM clause at orderdetails and joining to products is efficient because every line item already has a productCode foreign key.

Save analytics.sql.
Highlight the new query and click the lightning bolt to run it.
You should see exactly 7 rows (one per product line), with Classic Cars at the top as the highest revenue category.

Not seeing 7 rows?

If you see fewer rows, check that the JOIN condition uses od.productCode = p.productCode. A wrong join column can silently drop rows.
If Classic Cars is not at the top, confirm your ORDER BY is totalRevenue DESC (descending, not ascending).
Help me debug my product line revenue query

✔️ Awesome, I've got everything!

Great. Double check you've saved analytics.sql with all three queries.

ⓧ I'd like to double check the full code

Here is the complete analytics.sql file at this point:

-- ============================================
-- SQL Analytics: Classic Models Database
-- ============================================

USE classicmodels;

-- ============================================
-- STEP 2: Revenue Analysis with Multi-Table JOINs
-- ============================================

-- 2.1 Total revenue per order
SELECT
    o.orderNumber,
    o.orderDate,
    SUM(od.quantityOrdered * od.priceEach) AS orderRevenue
FROM orders o
JOIN orderdetails od ON o.orderNumber = od.orderNumber
GROUP BY o.orderNumber, o.orderDate
ORDER BY orderRevenue DESC;

-- 2.2 Top 10 customers by lifetime revenue
SELECT
    c.customerNumber,
    c.customerName,
    c.country,
    SUM(od.quantityOrdered * od.priceEach) AS totalRevenue
FROM customers c
JOIN orders o ON c.customerNumber = o.customerNumber
JOIN orderdetails od ON o.orderNumber = od.orderNumber
GROUP BY c.customerNumber, c.customerName, c.country
ORDER BY totalRevenue DESC
LIMIT 10;

-- 2.3 Revenue breakdown by product line
SELECT
    p.productLine,
    SUM(od.quantityOrdered * od.priceEach) AS totalRevenue,
    SUM(od.quantityOrdered) AS totalQuantitySold,
    ROUND(AVG(od.priceEach), 2) AS avgPricePerUnit
FROM orderdetails od
JOIN products p ON od.productCode = p.productCode
GROUP BY p.productLine
ORDER BY totalRevenue DESC;

You now have three working revenue queries that combine data from multiple tables. Next up, you'll use subqueries and CTEs to segment customers into tiers and find patterns that simple JOINs can't reveal.

Customer Segmentation with Subqueries and CTEs

In Step 2, you built revenue queries that join multiple tables together. You now know which customers and product lines generate the most money.

Revenue totals tell you who spent the most, but they don't answer a harder question: which customers are missing entirely? In this step, you'll use subqueries and Common Table Expressions (CTEs) to segment customers into meaningful groups and surface gaps in the data.

In this step, get ready to:

Find customers who have never placed an order using a subquery.
Segment customers into revenue tiers using a CTE and CASE expression.
Find each customer's most recent order using a correlated subquery.

Find customers who have never placed an order

One of the most common SQL interview patterns is finding unmatched records. You have a customers table and an orders table, but not every customer has placed an order. A subquery with NOT IN lets you isolate those customers who exist in one table but have no match in the other.

Add the following section header and query to the bottom of analytics.sql:

-- ============================================
-- STEP 3: Customer Segmentation with Subqueries and CTEs
-- ============================================

-- 3.1 Customers who have never placed an order
SELECT
    c.customerName,
    c.city,
    c.country
FROM customers c
WHERE c.customerNumber NOT IN (
    SELECT DISTINCT o.customerNumber
    FROM orders o
);

What does this query do?

The inner subquery builds a list of every customerNumber that appears in the orders table. The outer query then returns only customers whose number is NOT in that list.

DISTINCT ensures the subquery doesn't return duplicate customer numbers from multiple orders. The result is every customer record that has zero order history.

Highlight just the query 3.1 block (from SELECT to the closing );) and click the lightning bolt icon to execute it.
You should see 24 rows returned in the result grid, each showing a customer name, city, and country.

Not seeing 24 rows?

Make sure you highlighted only the query 3.1 block before executing. If you run the entire file, Workbench executes all queries and you'll need to scroll through multiple result tabs at the bottom.

Check that your NOT IN clause references o.customerNumber (not o.orderNumber). Comparing the wrong column returns incorrect results.

Why is my NOT IN subquery returning the wrong number of customers?

Why is this pattern so common in interviews?

Finding unmatched records tests whether you understand the difference between a JOIN (which excludes non-matching rows) and a subquery approach (which explicitly filters them). In business contexts, these 24 customers represent leads who signed up but never converted to buyers.

Segment customers into revenue tiers using a CTE

A CTE (Common Table Expression) lets you define a temporary named result set that you can reference in the query that follows it. You'll use one here to first calculate each customer's total revenue, then classify them into tiers with a CASE expression.

Add this query below your 3.1 query in analytics.sql:

-- 3.2 Segment customers into revenue tiers
WITH customer_revenue AS (
    SELECT
        c.customerNumber,
        c.customerName,
        SUM(od.quantityOrdered * od.priceEach) AS totalRevenue
    FROM customers c
    JOIN orders o ON c.customerNumber = o.customerNumber
    JOIN orderdetails od ON o.orderNumber = od.orderNumber
    GROUP BY c.customerNumber, c.customerName
)
SELECT
    CASE
        WHEN totalRevenue > 100000 THEN 'Platinum'
        WHEN totalRevenue BETWEEN 50000 AND 100000 THEN 'Gold'
        ELSE 'Silver'
    END AS tier,
    COUNT(*) AS customerCount,
    ROUND(SUM(totalRevenue), 2) AS tierTotalRevenue
FROM customer_revenue
GROUP BY tier
ORDER BY tierTotalRevenue DESC;

What does this query do?

The WITH customer_revenue AS (...) block is the CTE. It calculates total lifetime revenue for each customer using the same three-table JOIN pattern from Step 2. Think of it as a temporary table that only exists for this query.

The outer SELECT uses a CASE expression to bucket customers into Platinum (above 100,000), Gold (50,000 to 100,000), or Silver (below 50,000). It then counts how many customers land in each tier and sums their combined revenue.

Highlight the entire query 3.2 block (from WITH through the final ;) and click the lightning bolt icon to execute.
You should see 3 rows in the result grid, one for each tier (Platinum, Gold, Silver), with customer counts and total revenue per tier.

Seeing an error about the WITH keyword?

CTEs require MySQL 8.0 or higher. Run SELECT VERSION(); to confirm your version. If you see a version below 8.0, you'll need to upgrade your MySQL installation.

Also make sure you highlighted the entire CTE block starting from the WITH keyword. If you start the selection partway through, MySQL won't recognize the CTE syntax.

Why is my CTE query failing in MySQL Workbench?

Why use a CTE instead of a subquery here?

You could write this as a nested subquery in the FROM clause, but CTEs are easier to read and debug. They give the intermediate result set a name (customer_revenue) that makes your intent clear. In interviews, CTEs signal that you write maintainable SQL.

Find each customer's most recent order using a correlated subquery

A correlated subquery runs once per row in the outer query. It's a powerful tool for comparing each row to an aggregate of its related rows. Here, you'll use it to find the most recent order date for each customer.

Add this query below your 3.2 query in analytics.sql:

-- 3.3 Each customer's most recent order
SELECT
    c.customerName,
    o.orderDate AS mostRecentOrderDate,
    o.status
FROM customers c
JOIN orders o ON c.customerNumber = o.customerNumber
WHERE o.orderDate = (
    SELECT MAX(o2.orderDate)
    FROM orders o2
    WHERE o2.customerNumber = c.customerNumber
)
ORDER BY o.orderDate DESC;

What does this query do?

The outer query joins customers to orders. The WHERE clause contains a subquery that finds the MAX order date for the same customer. The correlation happens through o2.customerNumber = c.customerNumber, which links the inner query back to the current row in the outer query.

The result is one row per customer showing their latest order date and whether that order shipped, was cancelled, or is still in progress.

Highlight query 3.3 and click the lightning bolt icon to execute.
You should see one row per customer with their most recent order date and the order status. The results are ordered with the newest dates at the top.

Seeing duplicate customers in the results?

If a customer placed two orders on the same date (their maximum date), both rows will appear. This is expected behavior with the correlated subquery approach. In the classicmodels data, most customers have a unique most-recent date, so duplicates are rare.

If you see far too many rows, check that your subquery references o2.customerNumber = c.customerNumber. Without this correlation, the subquery returns the global maximum date across all customers.

Why is my correlated subquery returning unexpected results?

Save analytics.sql with all three new queries.

✔️ Awesome, I've got everything!

Great. Double check you've saved your file before moving on.

ⓧ I'd like to double check the full code

Here is the complete analytics.sql file up to this point:

-- ============================================
-- SQL Analytics: Classic Models Database
-- ============================================

USE classicmodels;

-- ============================================
-- STEP 2: Revenue Analysis with Multi-Table JOINs
-- ============================================

-- 2.1 Total revenue per order
SELECT
    o.orderNumber,
    o.orderDate,
    SUM(od.quantityOrdered * od.priceEach) AS orderRevenue
FROM orders o
JOIN orderdetails od ON o.orderNumber = od.orderNumber
GROUP BY o.orderNumber, o.orderDate
ORDER BY orderRevenue DESC;

-- 2.2 Top 10 customers by lifetime revenue
SELECT
    c.customerNumber,
    c.customerName,
    c.country,
    SUM(od.quantityOrdered * od.priceEach) AS totalRevenue
FROM customers c
JOIN orders o ON c.customerNumber = o.customerNumber
JOIN orderdetails od ON o.orderNumber = od.orderNumber
GROUP BY c.customerNumber, c.customerName, c.country
ORDER BY totalRevenue DESC
LIMIT 10;

-- 2.3 Revenue breakdown by product line
SELECT
    p.productLine,
    SUM(od.quantityOrdered * od.priceEach) AS totalRevenue,
    SUM(od.quantityOrdered) AS totalQuantitySold,
    ROUND(AVG(od.priceEach), 2) AS avgPricePerUnit
FROM orderdetails od
JOIN products p ON od.productCode = p.productCode
GROUP BY p.productLine
ORDER BY totalRevenue DESC;

-- ============================================
-- STEP 3: Customer Segmentation with Subqueries and CTEs
-- ============================================

-- 3.1 Customers who have never placed an order
SELECT
    c.customerName,
    c.city,
    c.country
FROM customers c
WHERE c.customerNumber NOT IN (
    SELECT DISTINCT o.customerNumber
    FROM orders o
);

-- 3.2 Segment customers into revenue tiers
WITH customer_revenue AS (
    SELECT
        c.customerNumber,
        c.customerName,
        SUM(od.quantityOrdered * od.priceEach) AS totalRevenue
    FROM customers c
    JOIN orders o ON c.customerNumber = o.customerNumber
    JOIN orderdetails od ON o.orderNumber = od.orderNumber
    GROUP BY c.customerNumber, c.customerName
)
SELECT
    CASE
        WHEN totalRevenue > 100000 THEN 'Platinum'
        WHEN totalRevenue BETWEEN 50000 AND 100000 THEN 'Gold'
        ELSE 'Silver'
    END AS tier,
    COUNT(*) AS customerCount,
    ROUND(SUM(totalRevenue), 2) AS tierTotalRevenue
FROM customer_revenue
GROUP BY tier
ORDER BY tierTotalRevenue DESC;

-- 3.3 Each customer's most recent order
SELECT
    c.customerName,
    o.orderDate AS mostRecentOrderDate,
    o.status
FROM customers c
JOIN orders o ON c.customerNumber = o.customerNumber
WHERE o.orderDate = (
    SELECT MAX(o2.orderDate)
    FROM orders o2
    WHERE o2.customerNumber = c.customerNumber
)
ORDER BY o.orderDate DESC;

You now have three powerful patterns in your toolkit: subqueries for finding gaps, CTEs for readable multi-step logic, and correlated subqueries for row-by-row comparisons. Next up, you'll add window functions to analyze trends over time.

Sales Trends with Window Functions

In the previous step, you segmented customers into tiers and found unmatched records using CTEs and subqueries. Those techniques answer "who are our customers" questions. Now you need to answer a different category of question: "how are things changing over time?"

Window functions let you perform calculations across rows that are related to the current row, without collapsing them into a single group. They are the tool for trend analysis, rankings, and running totals. These are also some of the most commonly tested SQL concepts in data analyst interviews.

In this step, get ready to:

Calculate monthly revenue and month-over-month growth using LAG.
Rank products within each product line using RANK and PARTITION BY.
Compute a running total of payments using SUM as a window function.

Calculate monthly revenue with month-over-month growth

The LAG() window function returns a value from the previous row in the result set. By pairing it with a CTE that groups revenue by month, you can compare each month's revenue against the previous month and calculate percentage growth.

Add the following section header and query to the bottom of analytics.sql:

-- ============================================
-- STEP 4: Sales Trends with Window Functions
-- ============================================

-- 4.1 Monthly revenue with month-over-month growth
WITH monthly_revenue AS (
    SELECT
        YEAR(o.orderDate) AS orderYear,
        MONTH(o.orderDate) AS orderMonth,
        SUM(od.quantityOrdered * od.priceEach) AS monthRevenue
    FROM orders o
    JOIN orderdetails od ON o.orderNumber = od.orderNumber
    GROUP BY YEAR(o.orderDate), MONTH(o.orderDate)
)
SELECT
    orderYear,
    orderMonth,
    ROUND(monthRevenue, 2) AS monthRevenue,
    ROUND(LAG(monthRevenue) OVER (ORDER BY orderYear, orderMonth), 2) AS prevMonthRevenue,
    ROUND(
        (monthRevenue - LAG(monthRevenue) OVER (ORDER BY orderYear, orderMonth))
        / LAG(monthRevenue) OVER (ORDER BY orderYear, orderMonth) * 100,
        2
    ) AS pctGrowth
FROM monthly_revenue
ORDER BY orderYear, orderMonth;

What does this query do?

The CTE groups all order line items by year and month, summing their revenue into a single row per month.

In the outer query, LAG(monthRevenue) OVER (ORDER BY orderYear, orderMonth) reaches back one row to grab the previous month's revenue. The pctGrowth column calculates the percentage difference between the current and previous month.

The first row will always show NULL for prevMonthRevenue and pctGrowth because there is no previous month to compare against.

Save analytics.sql.
Highlight just the query 4.1 block (from WITH monthly_revenue to the closing semicolon) and click the lightning bolt icon to execute.
You should see a result grid with columns orderYear, orderMonth, monthRevenue, prevMonthRevenue, and pctGrowth. The first row's growth columns show NULL, and subsequent rows show positive or negative percentage changes.

Seeing an error about LAG?

Window functions require MySQL 8.0 or higher. Run SELECT VERSION(); to confirm your version. If you are on an older version, you will need to upgrade MySQL before continuing.

Also check that you highlighted the complete query from WITH through the closing semicolon. Partial selections cause syntax errors.

Help me debug my LAG window function query in MySQL Workbench.

Rank products by revenue within each product line

The RANK() function assigns a rank to each row within a partition. Combined with PARTITION BY, you can rank products independently within each product line, then filter to show only the top performers.

Add this query below query 4.1 in analytics.sql:

-- 4.2 Top 3 products by revenue within each product line
WITH product_revenue AS (
    SELECT
        p.productLine,
        p.productName,
        SUM(od.quantityOrdered * od.priceEach) AS totalRevenue
    FROM orderdetails od
    JOIN products p ON od.productCode = p.productCode
    GROUP BY p.productLine, p.productName
),
ranked_products AS (
    SELECT
        productLine,
        productName,
        totalRevenue,
        RANK() OVER (PARTITION BY productLine ORDER BY totalRevenue DESC) AS rankInLine
    FROM product_revenue
)
SELECT
    productLine,
    productName,
    ROUND(totalRevenue, 2) AS totalRevenue,
    rankInLine
FROM ranked_products
WHERE rankInLine <= 3
ORDER BY productLine, rankInLine;

What does this query do?

The first CTE (product_revenue) calculates total revenue for every product by joining orderdetails to products.

The second CTE (ranked_products) applies RANK() OVER (PARTITION BY productLine ORDER BY totalRevenue DESC). This restarts the ranking for each product line, so rank 1 in Classic Cars is independent of rank 1 in Motorcycles.

The final SELECT filters with WHERE rankInLine <= 3 to show only the top 3 products per category. You cannot filter by a window function in the same SELECT where it is defined. That is why the two-CTE approach is needed.

Save analytics.sql.
Highlight query 4.2 and click the lightning bolt icon to execute.
You should see up to 3 products per product line, each with a rankInLine value of 1, 2, or 3. The results are grouped alphabetically by productLine.

Why RANK instead of ROW_NUMBER?

RANK() gives tied values the same rank and leaves gaps (1, 2, 2, 4). ROW_NUMBER() forces a unique number on every row even when values are tied. For product rankings, ties are meaningful and should be preserved.

Getting fewer than 3 rows for some product lines?

That is expected. If a product line has fewer than 3 products with order history, you will see fewer rows for that category. The WHERE rankInLine <= 3 filter shows "up to 3" results, not exactly 3.

Help me understand my RANK window function results.

Compute a running total of payments

A running total shows the cumulative sum of a value as it grows over time. The SUM() OVER (ORDER BY ...) pattern turns a regular aggregate function into a window function that accumulates row by row.

Add this final query below query 4.2 in analytics.sql:

-- 4.3 Running total of payments received
SELECT
    paymentDate,
    customerNumber,
    amount,
    SUM(amount) OVER (ORDER BY paymentDate) AS runningTotal,
    DENSE_RANK() OVER (ORDER BY paymentDate) AS dateRank
FROM payments
ORDER BY paymentDate;

What does this query do?

SUM(amount) OVER (ORDER BY paymentDate) adds each payment's amount to the sum of all previous payments, producing a cumulative total. Every row shows how much total money has been received up to that date.

DENSE_RANK() OVER (ORDER BY paymentDate) assigns a rank to each unique payment date without gaps. Payments on the same date share the same rank, and the next date gets the next consecutive number. This demonstrates that you can combine an aggregate window function and a ranking window function in the same query.

Save analytics.sql.
Highlight query 4.3 and click the lightning bolt icon to execute.
You should see every payment row with a runningTotal column that increases monotonically (never decreases) and a dateRank column that increments for each new date.

Running total seems wrong or resets?

Check that your OVER clause uses ORDER BY paymentDate without a PARTITION BY. Adding PARTITION BY customerNumber would restart the running total for each customer, which is not what we want here.

Help me troubleshoot my running total window function query.

✔️ Awesome, I've got everything!

Great. Double check you have saved analytics.sql with all three Step 4 queries.

ⓧ I'd like to double check the full code

Here is the complete analytics.sql file including all queries from Steps 2, 3, and 4:

-- ============================================
-- SQL Analytics: Classic Models Database
-- ============================================

USE classicmodels;

-- ============================================
-- STEP 2: Revenue Analysis with Multi-Table JOINs
-- ============================================

-- 2.1 Total revenue per order
SELECT
    o.orderNumber,
    o.orderDate,
    SUM(od.quantityOrdered * od.priceEach) AS orderRevenue
FROM orders o
JOIN orderdetails od ON o.orderNumber = od.orderNumber
GROUP BY o.orderNumber, o.orderDate
ORDER BY orderRevenue DESC;

-- 2.2 Top 10 customers by lifetime revenue
SELECT
    c.customerNumber,
    c.customerName,
    c.country,
    SUM(od.quantityOrdered * od.priceEach) AS totalRevenue
FROM customers c
JOIN orders o ON c.customerNumber = o.customerNumber
JOIN orderdetails od ON o.orderNumber = od.orderNumber
GROUP BY c.customerNumber, c.customerName, c.country
ORDER BY totalRevenue DESC
LIMIT 10;

-- 2.3 Revenue breakdown by product line
SELECT
    p.productLine,
    SUM(od.quantityOrdered * od.priceEach) AS totalRevenue,
    SUM(od.quantityOrdered) AS totalQuantitySold,
    ROUND(AVG(od.priceEach), 2) AS avgPricePerUnit
FROM orderdetails od
JOIN products p ON od.productCode = p.productCode
GROUP BY p.productLine
ORDER BY totalRevenue DESC;

-- ============================================
-- STEP 3: Customer Segmentation with Subqueries and CTEs
-- ============================================

-- 3.1 Customers who have never placed an order
SELECT
    c.customerName,
    c.city,
    c.country
FROM customers c
WHERE c.customerNumber NOT IN (
    SELECT DISTINCT o.customerNumber
    FROM orders o
);

-- 3.2 Segment customers into revenue tiers
WITH customer_revenue AS (
    SELECT
        c.customerNumber,
        c.customerName,
        SUM(od.quantityOrdered * od.priceEach) AS totalRevenue
    FROM customers c
    JOIN orders o ON c.customerNumber = o.customerNumber
    JOIN orderdetails od ON o.orderNumber = od.orderNumber
    GROUP BY c.customerNumber, c.customerName
)
SELECT
    CASE
        WHEN totalRevenue > 100000 THEN 'Platinum'
        WHEN totalRevenue BETWEEN 50000 AND 100000 THEN 'Gold'
        ELSE 'Silver'
    END AS tier,
    COUNT(*) AS customerCount,
    ROUND(SUM(totalRevenue), 2) AS tierTotalRevenue
FROM customer_revenue
GROUP BY tier
ORDER BY tierTotalRevenue DESC;

-- 3.3 Each customer's most recent order
SELECT
    c.customerName,
    o.orderDate AS mostRecentOrderDate,
    o.status
FROM customers c
JOIN orders o ON c.customerNumber = o.customerNumber
WHERE o.orderDate = (
    SELECT MAX(o2.orderDate)
    FROM orders o2
    WHERE o2.customerNumber = c.customerNumber
)
ORDER BY o.orderDate DESC;

-- ============================================
-- STEP 4: Sales Trends with Window Functions
-- ============================================

-- 4.1 Monthly revenue with month-over-month growth
WITH monthly_revenue AS (
    SELECT
        YEAR(o.orderDate) AS orderYear,
        MONTH(o.orderDate) AS orderMonth,
        SUM(od.quantityOrdered * od.priceEach) AS monthRevenue
    FROM orders o
    JOIN orderdetails od ON o.orderNumber = od.orderNumber
    GROUP BY YEAR(o.orderDate), MONTH(o.orderDate)
)
SELECT
    orderYear,
    orderMonth,
    ROUND(monthRevenue, 2) AS monthRevenue,
    ROUND(LAG(monthRevenue) OVER (ORDER BY orderYear, orderMonth), 2) AS prevMonthRevenue,
    ROUND(
        (monthRevenue - LAG(monthRevenue) OVER (ORDER BY orderYear, orderMonth))
        / LAG(monthRevenue) OVER (ORDER BY orderYear, orderMonth) * 100,
        2
    ) AS pctGrowth
FROM monthly_revenue
ORDER BY orderYear, orderMonth;

-- 4.2 Top 3 products by revenue within each product line
WITH product_revenue AS (
    SELECT
        p.productLine,
        p.productName,
        SUM(od.quantityOrdered * od.priceEach) AS totalRevenue
    FROM orderdetails od
    JOIN products p ON od.productCode = p.productCode
    GROUP BY p.productLine, p.productName
),
ranked_products AS (
    SELECT
        productLine,
        productName,
        totalRevenue,
        RANK() OVER (PARTITION BY productLine ORDER BY totalRevenue DESC) AS rankInLine
    FROM product_revenue
)
SELECT
    productLine,
    productName,
    ROUND(totalRevenue, 2) AS totalRevenue,
    rankInLine
FROM ranked_products
WHERE rankInLine <= 3
ORDER BY productLine, rankInLine;

-- 4.3 Running total of payments received
SELECT
    paymentDate,
    customerNumber,
    amount,
    SUM(amount) OVER (ORDER BY paymentDate) AS runningTotal,
    DENSE_RANK() OVER (ORDER BY paymentDate) AS dateRank
FROM payments
ORDER BY paymentDate;

You now have trend analysis, rankings, and running totals in your SQL toolkit. These window function patterns show up constantly in technical interviews because they test whether you can think beyond simple aggregation. Next up, you will combine everything you have learned into a comprehensive sales rep leaderboard in the Secret Mission.

Secret mission

Build a Sales Rep Leaderboard

You've built queries that analyze revenue by customer, product line, and time period. But who on the sales team is actually driving those numbers? Build a comprehensive leaderboard that ranks every sales rep by revenue, shows their percentage contribution, and flags underperformers.

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:

The classicmodels database loaded into your local MySQL server.
The analytics.sql script file on your computer.
The sales_rep_leaderboard.sql script file on your computer.

Keep everything running

No action needed. Choose this if you're still actively building or plan to reference these queries soon.

Your classicmodels database stays loaded in MySQL and takes up minimal disk space.
Your SQL script files remain wherever you saved them.
MySQL and MySQL Workbench remain installed on your machine. These are general-purpose tools you can reuse for other projects.

Pause - I'll come back to this later

Shut down running processes to free up memory. Your files and database stay on disk for when you return.

Close MySQL Workbench.
If you want to free system resources, stop the MySQL server process. On macOS, open **System Settings**, select **MySQL**, and click **Stop MySQL Server**. On Windows, open **Services**, find **MySQL80**, right-click it, and select **Stop**.
Your classicmodels database and SQL files remain on disk and will be ready next time you start MySQL.

Delete - I don't want to use this again

Remove all project resources and start fresh. This deletes the sample database and your query files permanently.

Drop the sample database by opening a query tab in MySQL Workbench and running:

DROP DATABASE classicmodels;

Delete your SQL script files. On macOS or Linux, run:

rm analytics.sql sales_rep_leaderboard.sql

On Windows, run:

del analytics.sql, sales_rep_leaderboard.sql

If you also downloaded the mysqlsampledatabase.sql file from the tutorial site, delete that as well from your Downloads folder.

Nice Work!

Nice work! You just built a complete SQL analytics toolkit against a real relational database, progressing from foundational JOINs all the way to advanced window functions.

You've learned how to:

Write multi-table JOIN queries that calculate revenue across orders, customers, and product lines in the classicmodels database.
Use subqueries and CTEs to segment customers into revenue tiers and find unmatched records using NOT IN patterns.
Apply window functions like LAG, RANK, DENSE_RANK, and SUM() OVER() to analyze month-over-month trends, rank products, and compute running totals.
Secret Mission: Built a comprehensive sales rep leaderboard combining JOINs, CTEs, window functions, and percentage calculations into a single analytical query.

Ready to quiz yourself?