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
