Programming Bay

🚀 SQL Project Series Food & Grocery Delivery Analytics 🛒 Analyze customers, stores, products, orders, deliveries, and payments to understand sales performance, customer behavior, delivery efficiency, and operational costs. 🎯 Business Objectives ✅ Analyze order and revenue trends ✅ Identify top-selling products ✅ Measure customer retention ✅ Analyze store performance ✅ Track delivery efficiency ✅ Identify peak ordering periods ✅ Monitor cancellations and refunds ✅ Optimize product and store performance 📂 Database Setup CREATE DATABASE grocery_delivery_db; USE grocery_delivery_db; Tables Created: customers → customer_id, customer_name, city, signup_date stores → store_id, store_name, city, store_type products → product_id, product_name, category, price orders → order_id, customer_id, store_id, order_date, order_status, delivery_time_minutes, delivery_fee order_items → order_item_id, order_id, product_id, quantity, unit_price Sample data for 5 customers, 4 stores, 5 products, 5 orders already included. 🧠 SQL Concepts You'll Practice ✔ INNER JOIN, LEFT JOIN ✔ Aggregate Functions, GROUP BY, HAVING ✔ CASE WHEN, CTEs, Subqueries ✔ Window Functions ✔ Date & Time Functions ✔ Conditional Aggregation 📊 Business KPIs You Can Build 📈 Total Orders, Completed Orders, Cancelled Orders, Cancellation Rate 📈 Total Revenue, AOV, Average Basket Size, Items Sold 📈 Revenue by Category, Store, City 📈 Top-Selling / Low-Selling Products 📈 Customer Lifetime Value, Repeat Purchase Rate, Retention Rate 📈 Average Delivery Time, On-Time Delivery Rate 📈 Peak Ordering Hour, Peak Ordering Day, Monthly Revenue Growth 📈 Delivery Fee Revenue, Customer Acquisition Trend 📈 Executive Grocery Delivery Dashboard 💡 Example Queries 1. Total Revenue SELECT SUM(oi.quantity * oi.unit_price) AS total_revenue FROM orders o JOIN order_items oi ON o.order_id = oi.order_id WHERE o.order_status = 'Delivered'; 2. Top-Selling Products SELECT p.product_name, SUM(oi.quantity) AS