Monday Coffee SQL Analysis

Project Overview

This project analyzes sales, customer behavior, product performance, and city-level business metrics for Monday Coffee using SQL.

The goal is to generate actionable insights that can help the company improve revenue, customer retention, and location strategy.

---

Dataset

The dataset contains four tables:

1. city – city-level data including population and rent
2. customers – customer information
3. products – product catalog
4. sales – transaction-level data

Dataset Size

• Cities: 14
• Customers: 497
• Products: 28
• Sales transactions: 10,388
• Sales period: Jan 2023 – Oct 2024

---

Business Questions Solved

1. Total revenue generated
2. Monthly revenue trends
3. Average order value
4. Top products by revenue
5. Most frequently sold products
6. Highest-rated products
7. Top customers by spending
8. Average purchases per customer
9. Repeat customer percentage
10. Revenue by city
11. Revenue per customer by city
12. Revenue per capita
13. Revenue-to-rent ratio
14. Top product per city
15. Month-over-month growth rate

---

Key Results

• Total revenue exceeded ₹60 lakh across 10,000+ transactions.
• Repeat customer rate reached 100%, indicating strong customer loyalty.
• Cold Brew Coffee Pack was the top-selling product across most cities.
• Pune and Jaipur showed the highest revenue efficiency per capita and per rent.
• Revenue fluctuated month-to-month, suggesting seasonal or campaign-driven demand.

---

Key Business Insights

• A small group of customers contributes a significant portion of total revenue.
• Core coffee products dominate both revenue and sales volume.
• High-rent metro cities generate strong revenue but lower efficiency.
• Mid-sized cities like Pune and Jaipur show higher profitability potential.

---

Business Recommendations

• Focus marketing and expansion efforts in high-efficiency cities like Pune and Jaipur.
• Promote the Cold Brew Coffee Pack as a flagship product.
• Introduce bundles and subscriptions to increase average order value.
• Investigate causes of revenue drops in certain months and replicate successful campaigns.

---

Key SQL Concepts Used

• Joins
• Aggregations
• GROUP BY and HAVING
• Subqueries
• Common Table Expressions (CTEs)
• Window functions

---

Tools Used

• PostgreSQL
• SQL

---

Project Structure

monday-coffee-sql-analysis │ ├── data │ ├── city.csv │ ├── customers.csv │ ├── products.csv │ └── sales.csv │ ├── schemas.sql ├── model_diagram.png ├── monday_coffee_business_analysis.sql └── README.md