🛒 E-Commerce Sales Analysis using SQL

📌 Project Overview

This project analyses the Brazilian E-Commerce Public Dataset by Olist using SQL to explore customer behaviour, sales performance, product performance and revenue trends.

The project focuses on answering practical business questions using transactional data and demonstrates SQL skills in data aggregation, filtering, multi-table joins, date analysis and business insight generation.

---

🎯 Business Questions

The analysis aims to answer the following questions:

1. How many customers are in the dataset?
2. How many orders have been placed?
3. Which states have the highest number of orders?
4. Which cities generate the most orders?
5. What is the distribution of order statuses?
6. What is the total revenue generated?
7. Which product categories generate the highest revenue?
8. Which products generate the highest revenue?
9. What is the average order value?
10. Who are the highest-spending customers?
11. How has monthly revenue changed over time?
12. Which product categories sold the most items?

---

📊 Dataset

This project uses the Brazilian E-Commerce Public Dataset by Olist, which contains transactional information about customers, orders, products and order items.

Dataset Source:
Kaggle – Brazilian E-Commerce Public Dataset by Olist

Tables Used

• customers
• orders
• order_items
• products

---

🛠️ Tools

Database Environment: DB Browser for SQLite

---

💻 SQL Skills Demonstrated

Throughout the project, the following SQL concepts were applied:

• SELECT
• WHERE
• DISTINCT
• ORDER BY
• GROUP BY
• Aggregate functions (COUNT, SUM, AVG, MAX)
• INNER JOIN
• Multi-table joins
• Date functions
• Revenue calculations
• Business-oriented data analysis

---

🔍 Key Findings

Analysis	Result
Total Customers	99,441
Total Orders	99,441
Total Revenue	$13,591,643.70
Average Order Value	$137.75
State with Highest Orders	SP
City with Highest Orders	São Paulo
Highest Revenue Category	beleza_saude — $1,258,681.34
Most Sold Category	cama_mesa_banho — 11,115 items
Highest Spending Customer	0a0a92112bd4c708ca5fde585afaa872 — $13,440.00
Highest Revenue Month	November 2017 — $1,010,271.37

---

💡 Business Insights

The analysis highlights several patterns within the e-commerce dataset:

• São Paulo (SP) represents the largest concentration of orders, indicating a strong customer market in the state.
• beleza_saude (Health & Beauty) generated the highest product-category revenue.
• cama_mesa_banho (Bed, Bath & Table) recorded the highest number of items sold.
• November 2017 generated the highest monthly revenue, indicating a particularly strong sales period.
• Differences between high-volume and high-revenue product categories show the importance of evaluating both sales quantity and monetary performance.

These findings demonstrate how SQL can be used to transform transactional data into information that supports sales, product and customer analysis.

---

📁 Project Structure

SQL-Ecommerce-Analysis/
│
├── ecommerce_analysis.sql
└── README.md

---

🔗 SQL Analysis

The complete SQL queries used for this project are available here:

➡️ ecommerce_analysis.sql

---

👤 Author

Ismail Md Zani
Bachelor of Science (Mathematics)
Universiti Teknologi Malaysia (UTM)