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 units_sold FROM products p JOIN order_items oi ON p.product_id = oi.product_id JOIN orders o ON oi.order_id = o.order_id WHERE o.order_status = 'Delivered' GROUP BY p.product_name ORDER BY units_sold DESC LIMIT 10; 3. Average Order Value WITH order_values AS ( SELECT o.order_id, SUM(oi.quantity * oi.unit_price) AS order_value FROM orders o JOIN order_items oi ON o.order_id = oi.order_id WHERE o.order_status = 'Delivered' GROUP BY o.order_id ) SELECT ROUND(AVG(order_value), 2) AS average_order_value FROM order_values; 4. Repeat Customers SELECT customer_id, COUNT(order_id) AS total_orders FROM orders WHERE order_status = 'Delivered' GROUP BY customer_id HAVING COUNT(order_id) > 1; 5. Revenue by Store SELECT s.store_name, SUM(oi.quantity * oi.unit_price) AS revenue FROM stores s JOIN orders o ON s.store_id = o.store_id JOIN order_items oi ON o.order_id = oi.order_id WHERE o.order_status = 'Delivered' GROUP BY s.store_name ORDER BY revenue DESC; 6. Cancellation Rate SELECT ROUND(100.0 * SUM(CASE WHEN order_status = 'Cancelled' THEN 1 ELSE 0 END) / COUNT(*), 2) AS cancellation_rate FROM orders; 7. Peak Ordering Hours SELECT EXTRACT(HOUR FROM order_date) AS order_hour, COUNT(*) AS total_orders FROM orders WHERE order_status = 'Delivered' GROUP BY EXTRACT(HOUR FROM order_date) ORDER BY total_orders DESC; Claim your Free $5 Bonus Here: https://bit.ly/3wUxw09 Join our WhatsApp Channel 👇 https://whatsapp.com/channel/0029VbAi27y0lwghBe9mE42i WhatsApp Community Link 👇 https://chat.whatsapp.com/HPJDqRr6G1sKQIqfdJF3pL 1️⃣ GROUP FOR PROGRAMMERS🖥 📎 Channel Link: [ https://t.me/realgroupforprogrammer ] --- 2️⃣ Coding Community 📎 Channel Link: [ https://t.me/Coding_CommunityOfficial ] --- 3️⃣ Programming Bay 📎 Channel Link: [ https://t.me/programmingbay ] --- 4️⃣ Data Structures and Algorithms 📎 Channel Link: [ https://t.me/datastructuresandalgoofficial ] Share with your College Whatsapp Groups & Friends too