🛒 Retail SQL Analytics

Hey there! 👋 Welcome to my Retail SQL Analytics project.

If you've ever wondered how big retail stores track their top-spending customers, find their best-selling products, or figure out which city is driving the most revenue—this project shows exactly how it's done using purely SQL.

I built this project to simulate a realistic retail database and write the actual queries you'd need to extract meaningful business insights from raw transaction data.

---

🧭 What's inside?

I've kept the project simple and modular. You'll find three main files here:

• 🗂️ schema.sql – The structure. This sets up a clean, normalized relational database with tables for Customers, Products, and Sales.
• 📝 sample_data.sql – The mock data. This populates the database with a realistic dataset of retail transactions, multiple cities, and various product categories so you have something to actually query.
• 🔍 analysis_queries.sql – The brains of the operation. This contains all the analytical SQL queries needed to pull out business reports.

🗺️ Quick look at the Database Schema

erDiagram
    CUSTOMERS ||--o{ SALES : places
    PRODUCTS ||--o{ SALES : contains

    CUSTOMERS {
        int customer_id
        varchar name
        varchar location
    }
    PRODUCTS {
        int product_id
        varchar product_name
        varchar category
        float price
    }
    SALES {
        int transaction_id
        int customer_id
        int product_id
        date sale_date
        int quantity
    }

Loading

🚀 How to run it yourself

Want to play around with the data? It's super easy and works right out of the box with virtually any major SQL engine (MySQL, PostgreSQL, SQL Server, Oracle, etc.).

1. Spin up your DB: Connect to your preferred SQL environment.
2. Setup the tables: Run schema.sql to generate the structure.
3. Load up the data: Run sample_data.sql to fill the database with mock transactions.
4. Run the analytics: Fire the queries in analysis_queries.sql and watch the insights flow.

---

📊 What kind of insights does it generate?

The analytics script goes way beyond basic SELECT * statements. I wrote queries to answer real-world business questions like:

• 💰 Total Revenue: How much money did the store make overall?
• 👑 VIP Customers: Who are our top-spending customers that we should be treating well?
• 📦 Product Performance: What are the best-selling products flying off the shelves?
• 🏙️ City-wise Revenue: Which cities are generating the most cash?
• 📈 Daily Trends: How is our daily revenue fluctuating day-by-day?

Note: If you run analysis_queries.sql, you'll see a clean, formatted output aggregating all of this data instantly!

---

🛠️ The Tech & Skills

I built this specifically to showcase practical, hands-on database skills. Some of the core SQL concepts I used include:

• Database Design: Crafting a clean, normalized schema.
• Advanced SQL: Heavy use of JOINs, aggregations (GROUP BY, SUM, COUNT), subqueries, and filtering.
• Data Analysis: Translating raw datasets into human-readable business reports.
• Business Logic: Solving actual retail problems through data.

💡 Why I built this

I wanted to demonstrate the exact kind of practical, problem-solving SQL skills that top consulting and tech firms (like Accenture!) rely on every single day. It's a great reflection of the day-to-day work in Data Engineering, Analytics, or Business Consulting roles.

---

Thanks for checking out the project! Feel free to fork the repo, mess around with the queries, or reach out if you have any questions or feedback.