SQL PROJECT TAKEN FROM CHATGPT SEPTEMBER 2026

 

Below is one polished, blog-ready SQL project. I’ve removed the interview-focused material and kept the explanation practical and beginner-friendly.

SQL Data Analytics Project: E-Commerce Sales Analysis

Introduction

SQL is one of the most important tools for analyzing business data. In this project, we will build a simple E-Commerce Sales Analysis project using SQL.

The goal is to analyze customer orders and answer important business questions such as:

How much revenue did the business generate?

Which products are selling the most?

Who are the top customers?

What are the monthly sales trends?

What is the average order value?

Which categories generate the most revenue?

How can SQL queries be optimized for large datasets?

This project is suitable for beginners who want to learn SQL through a practical data analytics example.

1. Project Objective

Imagine that we work with an online shopping company.

The company stores information about:

Customers

Products

Orders

Order details

Product categories

Management wants to understand sales performance and customer behavior.

We will use SQL to transform this raw data into useful business information.

2. Database Structure

We will create four tables:

Customers

    |

    | customer_id

    ↓

Orders

    |

    | order_id

    ↓

Order_Items

    |

    | product_id

    ↓

Products

The tables are:

customers

orders

products

order_items

3. Create the Customers Table

CREATE TABLE customers (

    customer_id INT PRIMARY KEY,

    customer_name VARCHAR(100),

    city VARCHAR(50),

    signup_date DATE

);

The table contains basic customer information.

Example:

customer_id customer_name city signup_date

1 Rahul Sharma Delhi 2025-01-10

2 Priya Singh Mumbai 2025-02-15

3 Amit Kumar Bangalore 2025-03-20

4. Create the Products Table

CREATE TABLE products (

    product_id INT PRIMARY KEY,

    product_name VARCHAR(100),

    category VARCHAR(50),

    price DECIMAL(10,2)

);

Example data:

product_id product_name category price

101 Laptop Electronics 60000

102 Mouse Electronics 800

103 Keyboard Electronics 1500

104 Shoes Fashion 2500

105 T-Shirt Fashion 1200

5. Create the Orders Table

CREATE TABLE orders (

    order_id INT PRIMARY KEY,

    customer_id INT,

    order_date DATE,

    status VARCHAR(30),

    FOREIGN KEY (customer_id)

    REFERENCES customers(customer_id)

);

Example:

order_id customer_id order_date status

1001 1 2026-01-05 Completed

1002 2 2026-01-10 Completed

1003 1 2026-02-15 Completed

1004 3 2026-02-20 Cancelled

6. Create the Order Items Table

An order can contain multiple products, so we create a separate table.

CREATE TABLE order_items (

    order_item_id INT PRIMARY KEY,

    order_id INT,

    product_id INT,

    quantity INT,

    FOREIGN KEY (order_id)

    REFERENCES orders(order_id),

    FOREIGN KEY (product_id)

    REFERENCES products(product_id)

);

Example:

order_item_id order_id product_id quantity

1 1001 101 1

2 1001 102 2

3 1002 104 1

4 1003 103 2

7. Project Question 1: How Many Customers Do We Have?

SELECT COUNT(*) AS total_customers

FROM customers;

COUNT() counts the number of records.

This query gives us the total number of registered customers.

8. Project Question 2: How Many Orders Were Placed?

SELECT COUNT(*) AS total_orders

FROM orders;

This gives the total number of orders in the database.

If cancelled orders should not be included:

SELECT COUNT(*) AS completed_orders

FROM orders

WHERE status = 'Completed';

This is more useful when calculating actual business performance.

9. Project Question 3: Calculate Total Revenue

Revenue can be calculated using:

Quantity × Product Price

SQL:

SELECT

    SUM(oi.quantity * p.price) AS total_revenue

FROM order_items oi

JOIN products p

    ON oi.product_id = p.product_id

JOIN orders o

    ON oi.order_id = o.order_id

WHERE o.status = 'Completed';

Here we combine three tables:

order_items

     ↓

products

     ↓

orders

The query calculates revenue only from completed orders.

10. Project Question 4: Find the Top 5 Products

SELECT

    p.product_name,

    SUM(oi.quantity) AS total_quantity_sold

FROM order_items oi

JOIN products p

    ON oi.product_id = p.product_id

JOIN orders o

    ON oi.order_id = o.order_id

WHERE o.status = 'Completed'

GROUP BY p.product_name

ORDER BY total_quantity_sold DESC

LIMIT 5;

The query:

Joins products and orders.

Removes cancelled orders.

Calculates total quantity sold.

Groups products.

Sorts them from highest to lowest.

Returns the top five.

11. Project Question 5: Find Revenue by Product

SELECT

    p.product_name,

    SUM(oi.quantity * p.price) AS revenue

FROM order_items oi

JOIN products p

    ON oi.product_id = p.product_id

JOIN orders o

    ON oi.order_id = o.order_id

WHERE o.status = 'Completed'

GROUP BY p.product_name

ORDER BY revenue DESC;

This helps the business understand which products generate the most money.

A product with high sales quantity is not always the product with the highest revenue.

For example:

Mouse

100 units × ₹800 = ₹80,000

Laptop

2 units × ₹60,000 = ₹120,000

The laptop sells fewer units but generates more revenue.

12. Project Question 6: Find the Top 5 Customers

SELECT

    c.customer_id,

    c.customer_name,

    SUM(oi.quantity * p.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

JOIN products p

    ON oi.product_id = p.product_id

WHERE o.status = 'Completed'

GROUP BY

    c.customer_id,

    c.customer_name

ORDER BY total_spent DESC

LIMIT 5;

This identifies the customers who contribute the most revenue.

The company can use this information to understand its most valuable customers.

13. Project Question 7: Calculate Average Order Value

Average Order Value, or AOV, tells us the average amount spent per order.

SELECT

    AVG(order_total) AS average_order_value

FROM (

    SELECT

        o.order_id,

        SUM(oi.quantity * p.price) AS order_total

    FROM orders o

    JOIN order_items oi

        ON o.order_id = oi.order_id

    JOIN products p

        ON oi.product_id = p.product_id

    WHERE o.status = 'Completed'

    GROUP BY o.order_id

) AS order_totals;

The calculation works in two steps.

First:

Calculate the value of every order

Then:

Calculate the average of those order values

14. Project Question 8: Monthly Revenue

Monthly revenue is one of the most useful metrics in sales analytics.

For PostgreSQL:

SELECT

    DATE_TRUNC('month', o.order_date) AS month,

    SUM(oi.quantity * p.price) AS revenue

FROM orders o

JOIN order_items oi

    ON o.order_id = oi.order_id

JOIN products p

    ON oi.product_id = p.product_id

WHERE o.status = 'Completed'

GROUP BY DATE_TRUNC('month', o.order_date)

ORDER BY month;

The result might look like:

Month Revenue

January ₹150,000

February ₹185,000

March ₹220,000

This allows the company to identify growth or declining sales.

15. Project Question 9: Revenue by Category

SELECT

    p.category,

    SUM(oi.quantity * p.price) AS revenue

FROM order_items oi

JOIN products p

    ON oi.product_id = p.product_id

JOIN orders o

    ON oi.order_id = o.order_id

WHERE o.status = 'Completed'

GROUP BY p.category

ORDER BY revenue DESC;

Example result:

Category Revenue

Electronics ₹500,000

Fashion ₹300,000

Home ₹180,000

This helps management understand which product categories are performing best.

16. Project Question 10: Find Customers Who Never Ordered

This is an important example of a LEFT JOIN.

SELECT

    c.customer_id,

    c.customer_name

FROM customers c

LEFT JOIN orders o

    ON c.customer_id = o.customer_id

WHERE o.order_id IS NULL;

Why use LEFT JOIN?

Because we want to keep all customers, including customers who don't have an order.

The result identifies customers who registered but never purchased anything.

17. Project Question 11: Find Cancelled Orders

SELECT

    order_id,

    customer_id,

    order_date

FROM orders

WHERE status = 'Cancelled';

This can help the business investigate why orders are being cancelled.

18. Project Question 12: Find the Highest-Value Order

SELECT

    o.order_id,

    SUM(oi.quantity * p.price) AS order_value

FROM orders o

JOIN order_items oi

    ON o.order_id = oi.order_id

JOIN products p

    ON oi.product_id = p.product_id

WHERE o.status = 'Completed'

GROUP BY o.order_id

ORDER BY order_value DESC

LIMIT 1;

This identifies the largest completed order.

19. Using CASE WHEN for Sales Classification

We can classify customers based on their spending.

SELECT

    c.customer_id,

    c.customer_name,

    SUM(oi.quantity * p.price) AS total_spent,

    CASE

        WHEN SUM(oi.quantity * p.price) >= 100000

            THEN 'High Value'

        WHEN SUM(oi.quantity * p.price) >= 50000

            THEN 'Medium Value'

        ELSE 'Low Value'

    END AS customer_segment

FROM customers c

JOIN orders o

    ON c.customer_id = o.customer_id

JOIN order_items oi

    ON o.order_id = oi.order_id

JOIN products p

    ON oi.product_id = p.product_id

WHERE o.status = 'Completed'

GROUP BY

    c.customer_id,

    c.customer_name;

Now every customer receives a segment:

High Value

Medium Value

Low Value

This is useful for customer segmentation.

20. Finding the Top Product in Each Category

Window functions can solve more advanced analytical problems.

WITH product_sales AS (

    SELECT

        p.category,

        p.product_name,

        SUM(oi.quantity * p.price) AS revenue

    FROM products p

    JOIN order_items oi

        ON p.product_id = oi.product_id

    JOIN orders o

        ON oi.order_id = o.order_id

    WHERE o.status = 'Completed'

    GROUP BY

        p.category,

        p.product_name

),

ranked_products AS (

    SELECT

        *,

        RANK() OVER (

            PARTITION BY category

            ORDER BY revenue DESC

        ) AS product_rank

    FROM product_sales

)

SELECT

    category,

    product_name,

    revenue

FROM ranked_products

WHERE product_rank = 1;

This gives the highest-revenue product in every category.

The important SQL concepts here are:

CTE

GROUP BY

RANK()

PARTITION BY

ORDER BY

21. Common SQL Errors in This Project

Error 1: Forgetting GROUP BY

Incorrect:

SELECT

    customer_id,

    SUM(total_amount)

FROM orders;

If we select customer_id along with an aggregate, we generally need to group by the customer.

Correct:

SELECT

    customer_id,

    SUM(total_amount) AS revenue

FROM orders

GROUP BY customer_id;

Error 2: Using WHERE with an Aggregate

Incorrect:

SELECT

    customer_id,

    SUM(total_amount)

FROM orders

GROUP BY customer_id

WHERE SUM(total_amount) > 50000;

The WHERE clause filters individual rows before aggregation.

Use HAVING for filtering aggregated groups:

SELECT

    customer_id,

    SUM(total_amount) AS revenue

FROM orders

GROUP BY customer_id

HAVING SUM(total_amount) > 50000;

Error 3: Using INNER JOIN When LEFT JOIN Is Required

Suppose we want customers who never ordered.

An INNER JOIN removes customers without matching orders.

Use:

LEFT JOIN

instead.

SELECT

    c.customer_id,

    c.customer_name

FROM customers c

LEFT JOIN orders o

    ON c.customer_id = o.customer_id

WHERE o.order_id IS NULL;

Error 4: Counting Duplicate Rows

When multiple order items exist for one order, careless joins can create duplicate rows.

Instead of blindly using:

COUNT(*)

you may need:

COUNT(DISTINCT o.order_id)

For example:

SELECT

    COUNT(DISTINCT o.order_id) AS total_orders

FROM orders o

JOIN order_items oi

    ON o.order_id = oi.order_id;

This counts orders rather than order-item rows.

22. SQL Optimization Methods

When the database contains thousands of rows, most queries will work quickly.

But when the database grows to millions or billions of rows, query optimization becomes important.

Optimization 1: Avoid SELECT *

Instead of:

SELECT *

FROM orders;

select only the columns you need:

SELECT

    order_id,

    customer_id,

    order_date,

    status

FROM orders;

This reduces unnecessary data processing and transfer.

Optimization 2: Create Indexes

If customers are frequently joined with orders using customer_id, an index can help.

CREATE INDEX idx_orders_customer_id

ON orders(customer_id);

For product joins:

CREATE INDEX idx_order_items_product_id

ON order_items(product_id);

For date-based filtering:

CREATE INDEX idx_orders_order_date

ON orders(order_date);

Indexes can significantly improve suitable lookup and join operations, but they also consume storage and can make inserts and updates more expensive.

23. Optimization 3: Use Date Ranges

Instead of:

WHERE EXTRACT(YEAR FROM order_date) = 2026

a range condition is often preferable:

WHERE order_date >= '2026-01-01'

  AND order_date < '2027-01-01'

This can make it easier for the database to use an index on order_date.

24. Optimization 4: Use EXPLAIN

Before optimizing a query, we should understand how the database executes it.

For example:

EXPLAIN

SELECT

    customer_id,

    COUNT(*)

FROM orders

GROUP BY customer_id;

Depending on the database, the execution plan can show operations such as:

Index Scan

Sequential Scan

Hash Join

Sort

Aggregate

The execution plan helps identify expensive operations.

25. Optimization 5: Avoid Unnecessary Joins

If information from a table is not required, don't join that table.

For example, if we only need the order date:

SELECT

    order_id,

    order_date

FROM orders;

There is no reason to join customers, products, and order_items.

Every unnecessary operation can increase query complexity and processing cost.

26. Optimization 6: Use CTEs for Complex Analysis

A Common Table Expression can make a complex analytical query easier to understand.

Example:

WITH monthly_sales AS (

    SELECT

        DATE_TRUNC('month', order_date) AS month,

        SUM(total_amount) AS revenue

    FROM orders

    WHERE status = 'Completed'

    GROUP BY DATE_TRUNC('month', order_date)

)

SELECT *

FROM monthly_sales

ORDER BY month;

CTEs improve readability and can make multi-step analytical logic easier to maintain.

Whether a CTE improves performance depends on the database engine and the specific query, so performance should be checked with an execution plan.

27. Final Project Workflow

The complete project follows this process:

Raw E-Commerce Data

        ↓

Create Database Tables

        ↓

Clean and Validate Data

        ↓

Join Related Tables

        ↓

Calculate Revenue

        ↓

Analyze Customers

        ↓

Analyze Products

        ↓

Analyze Categories

        ↓

Analyze Monthly Sales

        ↓

Identify Business Trends

        ↓

Optimize SQL Queries

        ↓

Create Reports / Dashboard

28. SQL Concepts Covered

By completing this project, you practice:

SELECT

WHERE

ORDER BY

GROUP BY

HAVING

COUNT

SUM

AVG

MIN

MAX

CASE WHEN

INNER JOIN

LEFT JOIN

DISTINCT

Subqueries

CTEs

RANK()

PARTITION BY

Date Functions

Indexes

EXPLAIN

Query Optimization

29. Business Insights From the Project

After completing the analysis, the company can answer questions such as:

What is our total revenue?

Which products generate the most revenue?

Which products sell the most units?

Who are our highest-value customers?

Which categories perform best?

What is our average order value?

How are sales changing month by month?

How many customers have never purchased?

How many orders are cancelled?

Which product is the top performer in each category?

These insights can then be presented in a dashboard using tools such as Power BI, Tableau, or another visualization platform.

Conclusion

The E-Commerce Sales Analysis project is an excellent practical SQL project because it covers both fundamental and advanced SQL concepts.

You begin with simple operations such as:

SELECT

WHERE

COUNT

SUM

and gradually move toward:

JOIN

CASE WHEN

CTE

Window Functions

RANK

Indexes

EXPLAIN

Query Optimization

The most important lesson is that SQL is not only about writing queries. A good data analytics project combines data understanding, correct calculations, business questions, error handling, and efficient query design.

By completing this project with a realistic dataset, you can build a strong foundation in SQL and apply the same techniques to many other areas such as finance, marketing, healthcare, customer analytics, and business intelligence.