This project performs an end-to-end SQL business analysis on the Brazilian Olist E-Commerce dataset using PostgreSQL.
The objective of this project is to transform raw e-commerce data into meaningful business insights by:
- Designing a relational database structure
- Importing and validating data
- Performing exploratory data analysis
- Answering business questions using SQL
- Identifying sales, customer, product, and operational trends
An e-commerce company wants to understand its business performance across:
- Sales and revenue trends
- Customer behavior
- Product performance
- Delivery efficiency
- Customer satisfaction
The analysis helps identify opportunities for improving revenue, customer experience, and operational performance.
Dataset: Brazilian Olist E-Commerce Dataset
The dataset contains approximately 100,000 orders from a Brazilian marketplace platform.
It includes information about:
- Customers
- Orders
- Products
- Sellers
- Payments
- Reviews
- Geolocation
| Tool | Purpose |
|---|---|
| PostgreSQL | Database management and SQL analysis |
| pgAdmin 4 | Database administration |
| SQL | Data extraction and business analysis |
| GitHub | Project documentation and version control |
The database consists of 9 relational tables:
customers
|
|
orders
|
|
-------------------------------
| | |
order_items payments reviews
|
|
products
|
|
category_names_english
sellers
geolocation
order_items
Contains transaction-level information such as:
- Product
- Seller
- Price
- Freight value
- Order details
- Customers
- Products
- Sellers
- Category information
Created PostgreSQL tables with:
- Primary keys
- Foreign key relationships
- Appropriate data types
File:
01_database_setup.sql
Imported CSV datasets into PostgreSQL using pgAdmin Import/Export functionality.
File:
02_data_import.sql
Performed data quality checks:
- Row count verification
- Primary key uniqueness checks
- NULL value validation
- Duplicate record detection
- Business rule validation
- Data profiling
File:
03_data_validation.sql
Performed SQL analysis to answer business questions related to:
- Monthly revenue trends
- Highest revenue months
- Revenue by order status
- Average order value
- Top revenue-generating orders
- Customer distribution
- Repeat customer behavior
- High-value customers
- Best performing categories
- Product revenue contribution
- Product performance
- Average delivery time
- Late delivery percentage
- Delivery impact on customer satisfaction
- Average ratings
- Rating distribution
- Relationship between delivery performance and ratings
File:
04_business_analysis.sql
The project calculates:
- Total Orders
- Total Customers
- Total Revenue
- Average Order Value
- Average Delivery Time
- Monthly Revenue
- Customer Ratings
- Cancellation Rate
- Repeat Customer Percentage
The analysis helps answer questions such as:
- Which product categories generate the highest revenue?
- Which months perform best in sales?
- Who are the highest-value customers?
- How does delivery performance affect customer ratings?
- What percentage of customers make repeat purchases?
This project demonstrates:
- SELECT statements
- Filtering and aggregation
- GROUP BY and HAVING
- JOIN operations
- Subqueries
- CASE statements
- Window functions
- Date and timestamp analysis
- Data validation techniques
- Relational database concepts
Olist-Ecommerce-SQL-Analysis
│
├── 01_database_setup.sql
├── 02_data_import.sql
├── 03_data_validation.sql
├── 04_business_analysis.sql
└── README.md
This project demonstrates an end-to-end SQL analytics workflow starting from raw data preparation to business insight generation.
It highlights practical skills required for a Data Analyst role, including:
- Database management
- Data quality validation
- SQL querying
- Business problem solving
- Data-driven decision making