This project is a complete end-to-end business analysis of a multi-state retail clothing chain using MySQL.
Instead of just running basic queries, this analysis focuses on answering real executive-level business questions:
- What actually drives revenue — location or category?
- Are longer operating hours improving profitability?
- Are large stores more efficient?
- Is the business dependent on the top 20% of stores?
- Where are we losing margins?
This project contains 70 structured business insights, covering revenue, customers, operations, profitability, and strategic expansion decisions.
The dataset contains:
- 📍 State & City information
- 🏪 Store details (type, size, manager, staff, security)
- 🧾 Order-level data
- 👤 Customer data
- 👕 Product & category data
- 💰 Price, quantity, and sales profit
- ⏰ Operating hours (feature engineered)
Key columns:
State, City, Category, Clothing Type,
Store Number, Store Type,
Selling Area Size (sq ft), Staff Count,
Order ID, Order Date, Month,
Customer ID, Product ID,
Price, Quantity, Sales Profit,
Opening_time, Closing_Time, Operating_hrs
One important enhancement performed:
Original column:
"10 AM - 9 PM"
Converted into:
Opening_timeClosing_TimeOperating_hrs
This enabled:
- Revenue per hour analysis
- Profit per hour efficiency ranking
- Long-hour vs short-hour performance comparison
- Operational productivity insights
- Revenue & profit by state
- Profit margins by category
- Most profitable clothing types
- Store-level profitability ranking
- Margin leakage detection
- High revenue but low margin stores
- Owned vs rented store comparison
- Revenue per sq ft
- Revenue per staff
- Profit per sq ft
- Parking & security impact
- Underperforming large stores
- Highly efficient small stores
- Best-selling categories
- Seasonal category trends
- Profit per unit analysis
- Category revenue share
- High margin but low sales opportunities
- Expansion candidates
- Top 10 high-value customers
- Repeat customer count
- Customer lifetime value
- Order size distribution
- Pareto analysis (Top 20% revenue contribution)
- Customer spending by state
- Monthly revenue & profit trends
- Month-over-month growth
- Best & worst performing months
- Seasonal spikes
- Revenue by store age
- Revenue per operating hour
- Profit per operating hour
- Revenue density per state
- Store size vs profitability relationship
- Staff impact on revenue
- What happens if bottom 10% stores are closed?
- Which 3 states deserve expansion?
- Is business location-driven or category-driven?
- Revenue concentration risk (Pareto analysis)
- Where are margins being lost?
⚡ Top 20% of stores contribute a disproportionate share of total revenue (Pareto effect observed). ⚡ Some large stores generate lower profit per sq ft than smaller stores. ⚡ Certain categories show strong margins but weak sales — untapped opportunity. ⚡ Longer operating hours do not always translate to higher profitability. ⚡ Revenue concentration suggests potential dependency risk.
(Replace with your actual findings after running queries.)
- Advanced SQL (MySQL 8)
- Window Functions (
LAG,NTILE) - CTEs
- Revenue modelling
- Profit margin analysis
- Operational efficiency metrics
- Business storytelling
- Executive-level thinking
- MySQL 8
- Feature Engineering (Pandas)
- GitHub for documentation
Most SQL projects:
“Find total sales by state.”
This project:
“Should we expand into a new state or optimise existing stores?”
It moves from descriptive analysis → strategic decision-making.
If you found this project insightful or want to discuss business analytics:
📩 Feel free to connect with me on LinkedIn.
Give it a ⭐ and let’s connect!