Zepto E-Commerce SQL Data Analysis

Project Overview

This project focuses on analyzing e-commerce inventory data using SQL and PostgreSQL. The analysis explores product details, pricing, discounts, inventory availability, product categories, and estimated revenue to generate meaningful business insights.

The project demonstrates practical SQL skills through data cleaning, exploratory analysis, aggregation, filtering, conditional logic, and business-oriented queries.

Objectives

• Analyze e-commerce product and inventory data.
• Clean and validate the dataset before analysis.
• Explore products across different categories.
• Analyze product pricing and discount patterns.
• Examine inventory availability and stock levels.
• Identify products with significant discounts.
• Calculate estimated revenue based on selling price and available quantity.
• Generate useful business insights using SQL.

Dataset

The dataset contains product-level information from an e-commerce inventory system.

Key attributes include:

• Product name
• Category
• MRP
• Discount percentage
• Selling price
• Available quantity
• Out-of-stock status
• Product-related attributes

Technologies Used

• PostgreSQL
• SQL
• pgAdmin
• CSV

Analysis Performed

Data Exploration

The dataset was explored to understand its structure, product categories, pricing information, inventory levels, and availability.

Data Cleaning

Data quality checks were performed to identify and handle:

• Missing values
• Duplicate records
• Invalid values
• Pricing inconsistencies
• Products with unavailable inventory

Product Analysis

The analysis includes:

• Product distribution by category
• Product availability
• Inventory quantities
• Product pricing
• Discount patterns
• Products with high and low discounts

Pricing and Discount Analysis

SQL queries were used to analyze MRP, selling prices, discount percentages, and pricing patterns across different products and categories.

Inventory Analysis

Inventory data was analyzed to identify available and out-of-stock products and understand stock distribution.

Revenue Analysis

Estimated revenue was calculated using selling prices and available inventory quantities to understand the potential revenue contribution of products.

SQL Concepts Used

• SELECT
• WHERE
• DISTINCT
• ORDER BY
• GROUP BY
• HAVING
• Aggregate Functions
• CASE Statements
• String Functions
• Mathematical Operations
• NULL Handling
• Data Type Conversion
• Subqueries
• Conditional Filtering

Project Structure

zepto-sql-data-analysis/
│
├── Zepto_SQL_data_analysis.sql
├── zepto_v2.csv 
└── README.md

How to Run

1. Install PostgreSQL and pgAdmin.
2. Create a new PostgreSQL database.
3. Import the zepto_v2.csv dataset into the required table.
4. Open Zepto_SQL_data_analysis.sql in pgAdmin.
5. Execute the SQL statements sequentially.
6. Review the query results and business insights.

Key Skills Demonstrated

• SQL Data Analysis
• PostgreSQL
• Data Cleaning
• Exploratory Data Analysis
• Data Aggregation
• Business Analysis
• Inventory Analysis
• Revenue Analysis
• Data Quality Validation

Conclusion

This project demonstrates how SQL can be used to transform raw e-commerce inventory data into meaningful business insights. The analysis focuses on product performance, pricing, discounts, inventory availability, and estimated revenue using PostgreSQL.

Author

Gembali Uday Krishna

B.Tech - Electronics and Communication Engineering 2024 Graduate