Zomato Sales Data Analysis

PostgreSQL | SQL | Data Analytics | Windown Functions | CTE

Project Aim

This project analyzes Zomato’s restaurant, customer, rider, and order data using PostgreSQL. It involves data creation, cleaning, transformation, and insightful SQL queries to derive meaningful business metrics such as customer behavior, restaurant performance, and delivery efficiency.

---

Project Structure

The analysis consists of the following key steps:

1. Database Setup – Create all required tables.
2. Data Cleaning – Handle null values, enforce relationships, and remove invalid records.
3. Data Analysis Queries – Execute SQL queries to answer important business questions.

---

Database Schema & Relationship/Model View

Tables Created:

1. RESTAURANTS

   • Contains restaurant details like name, city, and opening hours.

2. RIDERS

   • Stores rider information and sign-up dates.

3. CUSTOMERS

   • Holds customer registration details.

4. ORDERS

   • Contains order details including customer, restaurant, status, and total amount.

5. DELIVERY

   • Tracks delivery information, rider assigned, and delivery status.

Relationships:

• ORDERS → CUSTOMERS → customer_id
• ORDERS → RESTAURANTS → restaurant_id
• DELIVERY → ORDERS → order_id
• DELIVERY → RIDERS → rider_id

---

Data Cleaning

• Checked and removed null or incomplete records from ORDERS, CUSTOMERS, and RESTAURANTS.
• Ensured referential integrity using foreign keys.
• Practiced inserting and deleting test data to validate constraints.

---

Key Analytical Queries

Customer & Order Analysis

• Top 5 most ordered dishes by a specific customer (e.g., Arjun Mehta).
• Average Order Value (AOV) for customers with more than 180 orders.
• High-value customers (spent > ₹100K).
• Customer churn analysis – who ordered in 2023 but not in 2024.
• Customer lifetime value (CLV) and segmentation into Gold/Silver tiers.

Restaurant Performance

• Orders without delivery (potential cancellations).
• Restaurant revenue ranking by city.
• Most popular dishes per city.
• Monthly restaurant growth ratio based on delivered orders.
• Monthly sales trends to observe revenue growth or decline.

Rider Insights

• Rider average delivery time.
• Monthly rider earnings (8% of order value).
• Rider ratings based on delivery speed (5⭐, 4⭐, 3⭐ logic).
• Rider efficiency – fastest and slowest average delivery times.

Seasonal & Time-Based Insights

• Order item popularity by season (Spring, Summer, Winter).
• Order frequency by day of the week – peak ordering day per restaurant.

---

How to Use

1. Create the database

   CREATE DATABASE zomato_sales;

2. Connect to the database

   \c zomato_sales;

3. Run the provided SQL script to create tables, clean data, and perform analysis.

4. Verify table creation

   \dt

5. Execute analysis queries section by section as per the business question.

---

Tools & Technologies

• PostgreSQL 14+
• SQL Window Functions (RANK, LAG, etc.)
• CTEs (Common Table Expressions)
• Aggregate Functions (SUM, AVG, COUNT)

---

Contact & Collaboration

This project is open for collaboration and contributions!
If you're passionate about data analytics, SQL optimization, or building data-driven insights, feel free to join in.

How You Can Contribute

• 🧱 Add new analytical SQL queries or visualizations
• 🧼 Improve data cleaning and integrity checks
• 📊 Build dashboards or reports based on query results
• 📘 Enhance documentation or README clarity

Collaboration Guidelines

1. Fork this repository
2. Create a new branch for your feature or improvement
3. Commit your changes with clear messages
4. Submit a pull request with a short description of your contribution

Contact

For questions, discussions, or collaboration opportunities, reach out via:

• 📧 Email: hajra.mshahid24@gmail.com

Let’s collaborate to make this Zomato Data Analysis project more powerful and insightful together!