Loading
JAVA
SPRING
ANGULAR
SQL
HTML
CSS
JAVASCRIPT
Data Structure
Capstone Project
VideosDiscussion
SQL Capstone Project | E-commerce Analytics (Sales, Orders & Customers) - QuipoinBy the time someone reaches this chapter, they already know SQL syntax, joins, subqueries, window functions, and optimization techniques.
Now comes the most important step: applying everything together in a real-world scenario.

E-commerce platforms generate massive amounts of data every day:

  • Customers browse products
  • Orders are placed or cancelled
  • Payments are completed or failed
  • Sales fluctuate by time, region, and category

This capstone project simulates a real e-commerce analytics system, where SQL is used not just to fetch data, but to answer business questions and generate reports for decision-makers.

Think of this as:

  • A portfolio project
  • A practical interview case study
  • A bridge between theory and industry usage


Project Objective

The goal of this project is to analyze:

  • Sales performance
  • Customer behavior
  • Order trends
  • Revenue insights
using pure SQL queries, just like a Data Analyst or Backend Engineer would do in a real company.


Database Design Overview

In real systems, data is never stored in one table. We will work with a normalized schema, similar to production databases.


Core Tables Used


1. Customers Table

Stores user-related information.

customers (
    customer_id,
    name,
    email,
    city,
    signup_date
)


2. Products Table

Stores product catalog data.

products (
    product_id,
    product_name,
    category,
    price
)


3. Orders Table

Represents order-level information.

orders (
    order_id,
    customer_id,
    order_date,
    order_status
)


4. Order_Items Table

Stores product-level order details.

order_items (
    order_item_id,
    order_id,
    product_id,
    quantity,
    price
)

This separation mirrors real world transactional systems.


Analytics Part 1: Sales Analysis

Sales analytics helps businesses understand how much money is coming in and from where.


Total Revenue Generated

SELECT 
    SUM(quantity * price) AS total_revenue
FROM order_items;

This gives a high level business metric used in dashboards.


Revenue by Product Category

SELECT 
    p.category,
    SUM(oi.quantity * oi.price) AS category_revenue
FROM order_items oi
JOIN products p ON oi.product_id = p.product_id
GROUP BY p.category;

This helps identify:

  • Best-selling categories
  • Underperforming segments


Monthly Sales Trend

SELECT 
    DATE_FORMAT(o.order_date, '%Y-%m') AS month,
    SUM(oi.quantity * oi.price) AS revenue
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY month
ORDER BY month;

Time based analysis is critical for growth tracking.


Analytics Part 2: Order Analysis

Orders reflect customer intent, even if revenue is not generated every time.


Total Orders Vs Completed Orders

SELECT 
    order_status,
    COUNT(*) AS total_orders
FROM orders
GROUP BY order_status;

This highlights:

  • Cancellation rates
  • Fulfillment efficiency


Average Order Value (AOV)

SELECT 
    AVG(order_total) AS avg_order_value
FROM (
    SELECT 
        o.order_id,
        SUM(oi.quantity * oi.price) AS order_total
    FROM orders o
    JOIN order_items oi ON o.order_id = oi.order_id
    GROUP BY o.order_id
) t;

AOV is a key KPI in e-commerce businesses.


Analytics Part 3: Customer Analysis

Customer analytics tells who your buyers are and how loyal they are.


Total Customers Vs Active Customers

SELECT COUNT(*) AS total_customers
FROM customers;

SELECT COUNT(DISTINCT customer_id) AS active_customers
FROM orders;


Top Spending Customers

SELECT 
    c.name,
    SUM(oi.quantity * oi.price) AS total_spent
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY c.name
ORDER BY total_spent DESC
LIMIT 5;

This query is often used for:

  • Loyalty programs
  • VIP targeting


Repeat Customers

SELECT 
    customer_id,
    COUNT(order_id) AS order_count
FROM orders
GROUP BY customer_id
HAVING COUNT(order_id) > 1;

Reapeat customers indicate business sustainability.


Analytics Part 4: Advanced Reporting

This section combines window functions, subqueries, and business logic.


Rank Products by Sales

SELECT 
    product_name,
    total_sales,
    RANK() OVER (ORDER BY total_sales DESC) AS sales_rank
FROM (
    SELECT 
        p.product_name,
        SUM(oi.quantity * oi.price) AS total_sales
    FROM order_items oi
    JOIN products p ON oi.product_id = p.product_id
    GROUP BY p.product_name
) t;


Year over Year Sales Comparison

SELECT 
    year,
    revenue,
    revenue - LAG(revenue) OVER (ORDER BY year) AS growth
FROM (
    SELECT 
        YEAR(o.order_date) AS year,
        SUM(oi.quantity * oi.price) AS revenue
    FROM orders o
    JOIN order_items oi ON o.order_id = oi.order_id
    GROUP BY year
) t;

This is common in management and investor reports.


Questions Answered by This Project

By the end of this capstone, we can confidently answer:

  • Which products generate the most revenue?
  • Who are our most valuable customers?
  • Are sales growing month over month?
  • Which categories should we invest more in?
  • How healthy is customer retention?
These are real executive-level questions.


Interview Value of This Capstone

In interviews, this project demonstrates:

  • Schema understanding
  • Complex JOIN usage
  • Window functions
  • Business thinking
  • Reporting mindset

You can confidently say:
I have built and end-to-end SQL analytics project on an e-commerce databse.
That alone sets you apart.