QuickCart Grocery Inventory SQL Analysis Project

Project Overview

This SQL portfolio project analyzes a fictional quick-commerce grocery company called QuickCart. The project is designed to feel similar to real retail and e-commerce analyst work: checking inventory health, cleaning product catalog data, studying pricing and discounts, and finding restocking opportunities.

The goal is to keep the project at an intermediate level: not too basic, but also not overloaded with advanced database engineering. It focuses on practical SQL that a recruiter or hiring manager can quickly understand.

Business Problem

QuickCart wants to improve its grocery catalog and inventory planning. The business team wants answers to questions like:

• Which categories have the most products?
• Which categories have the highest stockout rate?
• Which products are strong discount deals?
• Which expensive products are currently unavailable?
• Where is inventory value concentrated?
• Which low-stock products should be restocked first?
• Which short shelf-life categories need closer monitoring?

Dataset

The dataset is a modified quick-commerce grocery inventory dataset with 3,732 SKUs. It uses a fictional company name and includes product, pricing, inventory, supplier, shelf-life, and rating fields.

File: data/quickcart_inventory.csv

Data Dictionary

Column	Description
sku_id	Unique SKU identifier
category	Product category
product_name	Product name listed in the catalog
mrp_paise	Maximum retail price in paise
discount_percent	Discount percentage offered
selling_price_paise	Selling price in paise
available_quantity	Units currently available
weight_g	Product weight in grams
out_of_stock	Whether the SKU is currently out of stock
pack_quantity	Quantity or pack size listed for the SKU
supplier_city	City supplying the SKU
shelf_life_days	Approximate shelf life in days
item_rating	Customer rating for the item

Tools Used

• MySQL 8.0+
• MySQL Workbench
• SQL concepts: filtering, grouping, aggregation, CASE statements, CTEs, window functions, and basic data cleaning

Project Structure

QuickCart-Grocery-SQL-Analysis-Project/
├── data/
│   └── quickcart_inventory.csv
├── sql/
│   └── quickcart_inventory_analysis.sql
└── README.md

Workflow

1. Database Setup

The project creates a dedicated database called quickcart and loads the CSV into a raw table.

2. Data Exploration

The first part of the SQL script checks:

• Total rows loaded
• Sample records
• Missing values
• Product categories
• Stock availability split

3. Data Cleaning

The script creates a clean analysis table by:

• Trimming text fields
• Converting prices from paise to rupees
• Casting numeric fields into proper data types
• Converting stock flags into boolean values
• Removing invalid zero-price records
• Adding a primary key on sku_id

4. Business Analysis

The analysis answers 15 business questions across inventory, pricing, discounts, stockouts, supplier cities, shelf life, and customer ratings.

Key SQL Questions

1. How many SKUs and categories does QuickCart have?
2. Which categories have the most listed SKUs?
3. What is the in-stock vs out-of-stock split?
4. Which categories have the highest stockout rate?
5. What are the top 10 best discount deals currently in stock?
6. Which high-MRP products are out of stock?
7. What is the estimated inventory value by category?
8. Which supplier cities hold the highest inventory value?
9. Which products have multiple SKU variants?
10. Which products offer the best price per 100 grams?
11. How are products distributed by weight size?
12. Which low-stock products should be prioritized for restocking?
13. Which categories have short shelf-life risk?
14. What are the top discounted products inside each category?
15. Which categories combine high rating and strong discounts?

Sample Business Insights to Look For

• Categories with high stockout rates may need better replenishment planning.
• High-MRP out-of-stock products can represent missed revenue opportunities.
• Supplier cities with high inventory value may need stronger warehouse monitoring.
• Short shelf-life products need tighter stock control to reduce waste.
• Duplicate product names with different package sizes show how SKU variants affect pricing and catalog complexity.

How to Run

1. Open MySQL Workbench.
2. Open sql/quickcart_inventory_analysis.sql.
3. Run the database setup and inventory_raw table creation section.
4. Import data/quickcart_inventory.csv into the inventory_raw table using MySQL Workbench's Table Data Import Wizard.
5. Run the exploration, cleaning, and analysis queries.

After the import, this query should return 3732:

SELECT COUNT(*) AS total_rows
FROM inventory_raw;

If you see Error Code: 1049. Unknown database 'quickcart', run the setup lines at the top of the SQL file first:

DROP DATABASE IF EXISTS quickcart;
CREATE DATABASE quickcart;
USE quickcart;

Why This Project Works for a Portfolio

This project shows that you can move beyond simple SELECT queries and think like a data analyst. It includes data cleaning, exploratory analysis, business-focused questions, CTEs, window functions, and clear documentation while staying realistic for an intermediate SQL learner.