Capstone Project

Congratulations on reaching the final lesson! In this capstone project, you'll design and query a complete e-commerce database from scratch. This combines everything you've learned: table design, relationships, joins, aggregation, subqueries, window functions, and more. Think of this as building a real analytics backend for an online store.

Designing the Schema

A solid e-commerce database needs to track customers, products, orders, and the items within each order. Let's build it step by step with proper normalization and constraints.

-- Create the complete e-commerce schema

-- Customers: who buys from us
CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT NOT NULL UNIQUE,
    city TEXT,
    country TEXT DEFAULT 'US',
    joined_date TEXT NOT NULL
);

-- Product categories
CREATE TABLE categories (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL UNIQUE,
    description TEXT
);

-- Products: what we sell
CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    category_id INTEGER NOT NULL,
    price REAL NOT NULL CHECK(price > 0),
    stock INTEGER NOT NULL DEFAULT 0 CHECK(stock >= 0),
    FOREIGN KEY (category_id) REFERENCES categories(id)
);

-- Orders: purchase transactions
CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    order_date TEXT NOT NULL,
    status TEXT NOT NULL DEFAULT 'pending' CHECK(status IN ('pending', 'shipped', 'delivered', 'cancelled')),
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);

-- Order items: the line items within each order
CREATE TABLE order_items (
    id INTEGER PRIMARY KEY,
    order_id INTEGER NOT NULL,
    product_id INTEGER NOT NULL,
    quantity INTEGER NOT NULL CHECK(quantity > 0),
    unit_price REAL NOT NULL CHECK(unit_price > 0),
    FOREIGN KEY (order_id) REFERENCES orders(id),
    FOREIGN KEY (product_id) REFERENCES products(id)
);

-- Verify the schema was created
SELECT name FROM sqlite_master WHERE type = 'table' ORDER BY name;

Notice the design choices: unit_price is stored in order_items (not looked up from products) because product prices change over time, but a customer's order should always reflect the price they paid.

Seeding the Database

Let's populate the schema with realistic sample data.

-- Recreate all tables
CREATE TABLE customers (
    id INTEGER PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL UNIQUE,
    city TEXT, country TEXT DEFAULT 'US', joined_date TEXT NOT NULL
);
CREATE TABLE categories (
    id INTEGER PRIMARY KEY, name TEXT NOT NULL UNIQUE, description TEXT
);
CREATE TABLE products (
    id INTEGER PRIMARY KEY, name TEXT NOT NULL, category_id INTEGER NOT NULL,
    price REAL NOT NULL, stock INTEGER NOT NULL DEFAULT 0
);
CREATE TABLE orders (
    id INTEGER PRIMARY KEY, customer_id INTEGER NOT NULL,
    order_date TEXT NOT NULL, status TEXT NOT NULL DEFAULT 'pending'
);
CREATE TABLE order_items (
    id INTEGER PRIMARY KEY, order_id INTEGER NOT NULL, product_id INTEGER NOT NULL,
    quantity INTEGER NOT NULL, unit_price REAL NOT NULL
);

-- Seed customers
INSERT INTO customers VALUES
    (1, 'Alice Johnson', 'alice@example.com', 'New York', 'US', '2024-03-15'),
    (2, 'Bob Smith', 'bob@example.com', 'London', 'UK', '2024-05-20'),
    (3, 'Carol Williams', 'carol@example.com', 'Paris', 'FR', '2024-06-10'),
    (4, 'David Brown', 'david@example.com', 'Tokyo', 'JP', '2024-08-01'),
    (5, 'Eva Martinez', 'eva@example.com', 'New York', 'US', '2024-09-12'),
    (6, 'Frank Lee', 'frank@example.com', 'Berlin', 'DE', '2024-11-05'),
    (7, 'Grace Kim', 'grace@example.com', 'Seoul', 'KR', '2025-01-02');

-- Seed categories
INSERT INTO categories VALUES
    (1, 'Electronics', 'Computers, phones, and accessories'),
    (2, 'Furniture', 'Desks, chairs, and home office'),
    (3, 'Books', 'Physical and digital books'),
    (4, 'Clothing', 'Apparel and accessories');

-- Seed products
INSERT INTO products VALUES
    (1, 'Laptop Pro', 1, 1299.99, 45),
    (2, 'Wireless Mouse', 1, 29.99, 200),
    (3, 'Mechanical Keyboard', 1, 89.99, 120),
    (4, 'USB-C Hub', 1, 49.99, 80),
    (5, 'Standing Desk', 2, 499.99, 25),
    (6, 'Ergonomic Chair', 2, 349.99, 30),
    (7, 'Desk Lamp', 2, 39.99, 100),
    (8, 'SQL Mastery', 3, 44.99, 500),
    (9, 'Web Dev Guide', 3, 39.99, 300),
    (10, 'Tech T-Shirt', 4, 24.99, 150);

-- Seed orders
INSERT INTO orders VALUES
    (1, 1, '2025-01-05', 'delivered'),
    (2, 2, '2025-01-08', 'delivered'),
    (3, 1, '2025-01-12', 'shipped'),
    (4, 3, '2025-01-15', 'delivered'),
    (5, 4, '2025-01-18', 'shipped'),
    (6, 5, '2025-01-20', 'delivered'),
    (7, 2, '2025-01-22', 'pending'),
    (8, 6, '2025-01-25', 'delivered'),
    (9, 1, '2025-01-28', 'pending'),
    (10, 7, '2025-02-01', 'shipped'),
    (11, 3, '2025-02-05', 'delivered'),
    (12, 5, '2025-02-08', 'pending');

-- Seed order items
INSERT INTO order_items VALUES
    (1, 1, 1, 1, 1299.99),
    (2, 1, 2, 2, 29.99),
    (3, 1, 3, 1, 89.99),
    (4, 2, 5, 1, 499.99),
    (5, 2, 7, 2, 39.99),
    (6, 3, 2, 3, 29.99),
    (7, 3, 4, 1, 49.99),
    (8, 4, 8, 2, 44.99),
    (9, 4, 9, 1, 39.99),
    (10, 5, 1, 1, 1299.99),
    (11, 5, 6, 1, 349.99),
    (12, 6, 3, 2, 89.99),
    (13, 6, 10, 3, 24.99),
    (14, 7, 8, 1, 44.99),
    (15, 7, 2, 1, 29.99),
    (16, 8, 5, 1, 499.99),
    (17, 8, 6, 1, 349.99),
    (18, 8, 7, 1, 39.99),
    (19, 9, 4, 2, 49.99),
    (20, 9, 10, 1, 24.99),
    (21, 10, 1, 1, 1299.99),
    (22, 10, 3, 1, 89.99),
    (23, 11, 8, 3, 44.99),
    (24, 11, 9, 2, 39.99),
    (25, 12, 2, 5, 29.99);

-- Quick data verification
SELECT 'customers' AS tbl, COUNT(*) AS rows FROM customers
UNION ALL SELECT 'categories', COUNT(*) FROM categories
UNION ALL SELECT 'products', COUNT(*) FROM products
UNION ALL SELECT 'orders', COUNT(*) FROM orders
UNION ALL SELECT 'order_items', COUNT(*) FROM order_items;

Revenue Analytics

Let's answer the questions a business would actually ask.

-- Full schema and data setup
CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT, email TEXT, city TEXT, country TEXT, joined_date TEXT);
CREATE TABLE categories (id INTEGER PRIMARY KEY, name TEXT, description TEXT);
CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT, category_id INTEGER, price REAL, stock INTEGER);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, order_date TEXT, status TEXT);
CREATE TABLE order_items (id INTEGER PRIMARY KEY, order_id INTEGER, product_id INTEGER, quantity INTEGER, unit_price REAL);

INSERT INTO customers VALUES (1,'Alice Johnson','alice@example.com','New York','US','2024-03-15'),(2,'Bob Smith','bob@example.com','London','UK','2024-05-20'),(3,'Carol Williams','carol@example.com','Paris','FR','2024-06-10'),(4,'David Brown','david@example.com','Tokyo','JP','2024-08-01'),(5,'Eva Martinez','eva@example.com','New York','US','2024-09-12'),(6,'Frank Lee','frank@example.com','Berlin','DE','2024-11-05'),(7,'Grace Kim','grace@example.com','Seoul','KR','2025-01-02');
INSERT INTO categories VALUES (1,'Electronics','Computers and accessories'),(2,'Furniture','Desks and chairs'),(3,'Books','Physical and digital'),(4,'Clothing','Apparel');
INSERT INTO products VALUES (1,'Laptop Pro',1,1299.99,45),(2,'Wireless Mouse',1,29.99,200),(3,'Mechanical Keyboard',1,89.99,120),(4,'USB-C Hub',1,49.99,80),(5,'Standing Desk',2,499.99,25),(6,'Ergonomic Chair',2,349.99,30),(7,'Desk Lamp',2,39.99,100),(8,'SQL Mastery',3,44.99,500),(9,'Web Dev Guide',3,39.99,300),(10,'Tech T-Shirt',4,24.99,150);
INSERT INTO orders VALUES (1,1,'2025-01-05','delivered'),(2,2,'2025-01-08','delivered'),(3,1,'2025-01-12','shipped'),(4,3,'2025-01-15','delivered'),(5,4,'2025-01-18','shipped'),(6,5,'2025-01-20','delivered'),(7,2,'2025-01-22','pending'),(8,6,'2025-01-25','delivered'),(9,1,'2025-01-28','pending'),(10,7,'2025-02-01','shipped'),(11,3,'2025-02-05','delivered'),(12,5,'2025-02-08','pending');
INSERT INTO order_items VALUES (1,1,1,1,1299.99),(2,1,2,2,29.99),(3,1,3,1,89.99),(4,2,5,1,499.99),(5,2,7,2,39.99),(6,3,2,3,29.99),(7,3,4,1,49.99),(8,4,8,2,44.99),(9,4,9,1,39.99),(10,5,1,1,1299.99),(11,5,6,1,349.99),(12,6,3,2,89.99),(13,6,10,3,24.99),(14,7,8,1,44.99),(15,7,2,1,29.99),(16,8,5,1,499.99),(17,8,6,1,349.99),(18,8,7,1,39.99),(19,9,4,2,49.99),(20,9,10,1,24.99),(21,10,1,1,1299.99),(22,10,3,1,89.99),(23,11,8,3,44.99),(24,11,9,2,39.99),(25,12,2,5,29.99);

-- Total revenue by category
SELECT
    cat.name AS category,
    COUNT(DISTINCT o.id) AS orders_with_category,
    SUM(oi.quantity) AS units_sold,
    ROUND(SUM(oi.quantity * oi.unit_price), 2) AS total_revenue,
    ROUND(AVG(oi.unit_price), 2) AS avg_unit_price
FROM order_items oi
JOIN products p ON oi.product_id = p.id
JOIN categories cat ON p.category_id = cat.id
JOIN orders o ON oi.order_id = o.id
GROUP BY cat.name
ORDER BY total_revenue DESC;

-- Monthly revenue trend
SELECT
    substr(o.order_date, 1, 7) AS month,
    COUNT(DISTINCT o.id) AS num_orders,
    ROUND(SUM(oi.quantity * oi.unit_price), 2) AS revenue
FROM orders o
JOIN order_items oi ON o.id = oi.order_id
GROUP BY substr(o.order_date, 1, 7)
ORDER BY month;

Customer Insights

-- Full schema setup
CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT, email TEXT, city TEXT, country TEXT, joined_date TEXT);
CREATE TABLE categories (id INTEGER PRIMARY KEY, name TEXT, description TEXT);
CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT, category_id INTEGER, price REAL, stock INTEGER);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, order_date TEXT, status TEXT);
CREATE TABLE order_items (id INTEGER PRIMARY KEY, order_id INTEGER, product_id INTEGER, quantity INTEGER, unit_price REAL);

INSERT INTO customers VALUES (1,'Alice Johnson','alice@example.com','New York','US','2024-03-15'),(2,'Bob Smith','bob@example.com','London','UK','2024-05-20'),(3,'Carol Williams','carol@example.com','Paris','FR','2024-06-10'),(4,'David Brown','david@example.com','Tokyo','JP','2024-08-01'),(5,'Eva Martinez','eva@example.com','New York','US','2024-09-12'),(6,'Frank Lee','frank@example.com','Berlin','DE','2024-11-05'),(7,'Grace Kim','grace@example.com','Seoul','KR','2025-01-02');
INSERT INTO categories VALUES (1,'Electronics','Computers and accessories'),(2,'Furniture','Desks and chairs'),(3,'Books','Physical and digital'),(4,'Clothing','Apparel');
INSERT INTO products VALUES (1,'Laptop Pro',1,1299.99,45),(2,'Wireless Mouse',1,29.99,200),(3,'Mechanical Keyboard',1,89.99,120),(4,'USB-C Hub',1,49.99,80),(5,'Standing Desk',2,499.99,25),(6,'Ergonomic Chair',2,349.99,30),(7,'Desk Lamp',2,39.99,100),(8,'SQL Mastery',3,44.99,500),(9,'Web Dev Guide',3,39.99,300),(10,'Tech T-Shirt',4,24.99,150);
INSERT INTO orders VALUES (1,1,'2025-01-05','delivered'),(2,2,'2025-01-08','delivered'),(3,1,'2025-01-12','shipped'),(4,3,'2025-01-15','delivered'),(5,4,'2025-01-18','shipped'),(6,5,'2025-01-20','delivered'),(7,2,'2025-01-22','pending'),(8,6,'2025-01-25','delivered'),(9,1,'2025-01-28','pending'),(10,7,'2025-02-01','shipped'),(11,3,'2025-02-05','delivered'),(12,5,'2025-02-08','pending');
INSERT INTO order_items VALUES (1,1,1,1,1299.99),(2,1,2,2,29.99),(3,1,3,1,89.99),(4,2,5,1,499.99),(5,2,7,2,39.99),(6,3,2,3,29.99),(7,3,4,1,49.99),(8,4,8,2,44.99),(9,4,9,1,39.99),(10,5,1,1,1299.99),(11,5,6,1,349.99),(12,6,3,2,89.99),(13,6,10,3,24.99),(14,7,8,1,44.99),(15,7,2,1,29.99),(16,8,5,1,499.99),(17,8,6,1,349.99),(18,8,7,1,39.99),(19,9,4,2,49.99),(20,9,10,1,24.99),(21,10,1,1,1299.99),(22,10,3,1,89.99),(23,11,8,3,44.99),(24,11,9,2,39.99),(25,12,2,5,29.99);

-- Customer lifetime value (CLV) with ranking
WITH customer_stats AS (
    SELECT
        c.id,
        c.name,
        c.country,
        c.joined_date,
        COUNT(DISTINCT o.id) AS total_orders,
        ROUND(SUM(oi.quantity * oi.unit_price), 2) AS lifetime_value
    FROM customers c
    LEFT JOIN orders o ON c.id = o.customer_id
    LEFT JOIN order_items oi ON o.id = oi.order_id
    GROUP BY c.id
)
SELECT
    name,
    country,
    total_orders,
    lifetime_value,
    RANK() OVER (ORDER BY lifetime_value DESC) AS value_rank,
    ROUND(lifetime_value * 100.0 / SUM(lifetime_value) OVER (), 1) AS pct_of_revenue
FROM customer_stats
ORDER BY lifetime_value DESC;

Product Performance with Window Functions

-- Full schema setup
CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT, email TEXT, city TEXT, country TEXT, joined_date TEXT);
CREATE TABLE categories (id INTEGER PRIMARY KEY, name TEXT, description TEXT);
CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT, category_id INTEGER, price REAL, stock INTEGER);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, order_date TEXT, status TEXT);
CREATE TABLE order_items (id INTEGER PRIMARY KEY, order_id INTEGER, product_id INTEGER, quantity INTEGER, unit_price REAL);

INSERT INTO customers VALUES (1,'Alice Johnson','alice@example.com','New York','US','2024-03-15'),(2,'Bob Smith','bob@example.com','London','UK','2024-05-20'),(3,'Carol Williams','carol@example.com','Paris','FR','2024-06-10'),(4,'David Brown','david@example.com','Tokyo','JP','2024-08-01'),(5,'Eva Martinez','eva@example.com','New York','US','2024-09-12'),(6,'Frank Lee','frank@example.com','Berlin','DE','2024-11-05'),(7,'Grace Kim','grace@example.com','Seoul','KR','2025-01-02');
INSERT INTO categories VALUES (1,'Electronics','Computers and accessories'),(2,'Furniture','Desks and chairs'),(3,'Books','Physical and digital'),(4,'Clothing','Apparel');
INSERT INTO products VALUES (1,'Laptop Pro',1,1299.99,45),(2,'Wireless Mouse',1,29.99,200),(3,'Mechanical Keyboard',1,89.99,120),(4,'USB-C Hub',1,49.99,80),(5,'Standing Desk',2,499.99,25),(6,'Ergonomic Chair',2,349.99,30),(7,'Desk Lamp',2,39.99,100),(8,'SQL Mastery',3,44.99,500),(9,'Web Dev Guide',3,39.99,300),(10,'Tech T-Shirt',4,24.99,150);
INSERT INTO orders VALUES (1,1,'2025-01-05','delivered'),(2,2,'2025-01-08','delivered'),(3,1,'2025-01-12','shipped'),(4,3,'2025-01-15','delivered'),(5,4,'2025-01-18','shipped'),(6,5,'2025-01-20','delivered'),(7,2,'2025-01-22','pending'),(8,6,'2025-01-25','delivered'),(9,1,'2025-01-28','pending'),(10,7,'2025-02-01','shipped'),(11,3,'2025-02-05','delivered'),(12,5,'2025-02-08','pending');
INSERT INTO order_items VALUES (1,1,1,1,1299.99),(2,1,2,2,29.99),(3,1,3,1,89.99),(4,2,5,1,499.99),(5,2,7,2,39.99),(6,3,2,3,29.99),(7,3,4,1,49.99),(8,4,8,2,44.99),(9,4,9,1,39.99),(10,5,1,1,1299.99),(11,5,6,1,349.99),(12,6,3,2,89.99),(13,6,10,3,24.99),(14,7,8,1,44.99),(15,7,2,1,29.99),(16,8,5,1,499.99),(17,8,6,1,349.99),(18,8,7,1,39.99),(19,9,4,2,49.99),(20,9,10,1,24.99),(21,10,1,1,1299.99),(22,10,3,1,89.99),(23,11,8,3,44.99),(24,11,9,2,39.99),(25,12,2,5,29.99);

-- Product performance ranked within each category
WITH product_stats AS (
    SELECT
        p.id,
        p.name AS product,
        cat.name AS category,
        p.price AS current_price,
        p.stock,
        COALESCE(SUM(oi.quantity), 0) AS units_sold,
        COALESCE(ROUND(SUM(oi.quantity * oi.unit_price), 2), 0) AS revenue
    FROM products p
    JOIN categories cat ON p.category_id = cat.id
    LEFT JOIN order_items oi ON p.id = oi.product_id
    GROUP BY p.id
)
SELECT
    product,
    category,
    units_sold,
    revenue,
    DENSE_RANK() OVER (PARTITION BY category ORDER BY revenue DESC) AS category_rank,
    ROUND(revenue * 100.0 / SUM(revenue) OVER (PARTITION BY category), 1) AS pct_of_category,
    stock,
    CASE
        WHEN units_sold > 0 AND stock > 0 THEN ROUND(CAST(stock AS REAL) / (units_sold / 2.0), 1)
        ELSE NULL
    END AS months_of_stock
FROM product_stats
ORDER BY category, revenue DESC;

Advanced Analytics: Order Patterns

-- Full schema setup
CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT, email TEXT, city TEXT, country TEXT, joined_date TEXT);
CREATE TABLE categories (id INTEGER PRIMARY KEY, name TEXT, description TEXT);
CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT, category_id INTEGER, price REAL, stock INTEGER);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, order_date TEXT, status TEXT);
CREATE TABLE order_items (id INTEGER PRIMARY KEY, order_id INTEGER, product_id INTEGER, quantity INTEGER, unit_price REAL);

INSERT INTO customers VALUES (1,'Alice Johnson','alice@example.com','New York','US','2024-03-15'),(2,'Bob Smith','bob@example.com','London','UK','2024-05-20'),(3,'Carol Williams','carol@example.com','Paris','FR','2024-06-10'),(4,'David Brown','david@example.com','Tokyo','JP','2024-08-01'),(5,'Eva Martinez','eva@example.com','New York','US','2024-09-12'),(6,'Frank Lee','frank@example.com','Berlin','DE','2024-11-05'),(7,'Grace Kim','grace@example.com','Seoul','KR','2025-01-02');
INSERT INTO categories VALUES (1,'Electronics','Computers and accessories'),(2,'Furniture','Desks and chairs'),(3,'Books','Physical and digital'),(4,'Clothing','Apparel');
INSERT INTO products VALUES (1,'Laptop Pro',1,1299.99,45),(2,'Wireless Mouse',1,29.99,200),(3,'Mechanical Keyboard',1,89.99,120),(4,'USB-C Hub',1,49.99,80),(5,'Standing Desk',2,499.99,25),(6,'Ergonomic Chair',2,349.99,30),(7,'Desk Lamp',2,39.99,100),(8,'SQL Mastery',3,44.99,500),(9,'Web Dev Guide',3,39.99,300),(10,'Tech T-Shirt',4,24.99,150);
INSERT INTO orders VALUES (1,1,'2025-01-05','delivered'),(2,2,'2025-01-08','delivered'),(3,1,'2025-01-12','shipped'),(4,3,'2025-01-15','delivered'),(5,4,'2025-01-18','shipped'),(6,5,'2025-01-20','delivered'),(7,2,'2025-01-22','pending'),(8,6,'2025-01-25','delivered'),(9,1,'2025-01-28','pending'),(10,7,'2025-02-01','shipped'),(11,3,'2025-02-05','delivered'),(12,5,'2025-02-08','pending');
INSERT INTO order_items VALUES (1,1,1,1,1299.99),(2,1,2,2,29.99),(3,1,3,1,89.99),(4,2,5,1,499.99),(5,2,7,2,39.99),(6,3,2,3,29.99),(7,3,4,1,49.99),(8,4,8,2,44.99),(9,4,9,1,39.99),(10,5,1,1,1299.99),(11,5,6,1,349.99),(12,6,3,2,89.99),(13,6,10,3,24.99),(14,7,8,1,44.99),(15,7,2,1,29.99),(16,8,5,1,499.99),(17,8,6,1,349.99),(18,8,7,1,39.99),(19,9,4,2,49.99),(20,9,10,1,24.99),(21,10,1,1,1299.99),(22,10,3,1,89.99),(23,11,8,3,44.99),(24,11,9,2,39.99),(25,12,2,5,29.99);

-- Repeat purchase analysis: days between each customer's orders
WITH customer_orders AS (
    SELECT
        c.name,
        o.id AS order_id,
        o.order_date,
        ROUND(SUM(oi.quantity * oi.unit_price), 2) AS order_total,
        ROW_NUMBER() OVER (PARTITION BY c.id ORDER BY o.order_date) AS order_num,
        LAG(o.order_date) OVER (PARTITION BY c.id ORDER BY o.order_date) AS prev_order_date
    FROM customers c
    JOIN orders o ON c.id = o.customer_id
    JOIN order_items oi ON o.id = oi.order_id
    GROUP BY c.name, o.id, o.order_date
)
SELECT
    name,
    order_num,
    order_date,
    order_total,
    prev_order_date,
    CASE
        WHEN prev_order_date IS NOT NULL
        THEN CAST(julianday(order_date) - julianday(prev_order_date) AS INTEGER)
        ELSE NULL
    END AS days_since_last_order
FROM customer_orders
ORDER BY name, order_num;

-- Geographic revenue analysis
SELECT
    c.country,
    COUNT(DISTINCT c.id) AS customers,
    COUNT(DISTINCT o.id) AS orders,
    ROUND(SUM(oi.quantity * oi.unit_price), 2) AS total_revenue,
    ROUND(SUM(oi.quantity * oi.unit_price) / COUNT(DISTINCT o.id), 2) AS avg_order_value
FROM customers c
JOIN orders o ON c.id = o.customer_id
JOIN order_items oi ON o.id = oi.order_id
GROUP BY c.country
ORDER BY total_revenue DESC;

Challenge Exercises

Now it's your turn. Try building these queries against the e-commerce schema above:

Best-selling product pairs: Find which products are most often purchased together in the same order
Customer cohort analysis: Group customers by their signup month and calculate the average number of orders per cohort
Revenue cumulative running total: Show daily revenue with a cumulative running total
Inventory alert: Find products where remaining stock will run out within 2 months based on current sales velocity
Top customer per country: Use window functions to find the highest-spending customer in each country

Key Takeaways

A well-designed schema separates entities into normalized tables with clear relationships
Store the price at order time (in order_items) to preserve historical accuracy
CTEs make complex analytics queries readable by breaking them into named steps
Window functions (RANK, LAG, running totals) power real business analytics
Combine JOINs, aggregation, subqueries, and window functions for comprehensive reports
Always verify your data with simple queries before building complex ones

Pro Tip: Real e-commerce databases also track shipping addresses, payment methods, discount codes, product reviews, and inventory movements. The schema you built here is a solid foundation. When extending it, follow the same normalization principles: each fact stored once, clear relationships, and appropriate constraints.

Congratulations!

You've completed the entire SQL curriculum. From your first SELECT to building a full analytics system, you now have the skills to design databases, write complex queries, and extract meaningful insights from data. The best way to solidify these skills is to keep building -- pick a project, design the schema, seed it with data, and start asking questions with SQL. Happy querying!

Next Steps

With the capstone complete, you are ready to expand your SQL vocabulary. The next lesson covers string functions — essential tools for cleaning, formatting, and extracting text data in your queries.

Ready to continue? Head to String Functions!

Date and Time Functions

Also learn

Also learn