An end-to-end SQL analytics project on a quick-commerce dataset — modeled on the data a Data Analyst at Blinkit, Zepto, Flipkart, or Uber works with daily. One warehouse, five business-critical analyses, built entirely in MySQL 8.0.
Dark stores, delivery riders, an order-event stream, SKU economics, marketing spend, and a self-referencing referral tree — all interlocking so the same tables answer very different business questions. Each of the five modules solves a real operational or growth question end-to-end: business question → query → plain-English finding.
The project runs on a purpose-built synthetic dataset designed to exercise a wide range of analytical SQL patterns while remaining internally consistent and realistic.
Dataset at a glance
| Entity | Count |
|---|---|
| Customers | 6,000 |
| Orders | 26,504 |
| Order events | 128,815 |
| SKUs | 180 |
| Riders | 220 |
| Dark stores | 38 |
| Cities | 11 |
| Date range | Jan 2024 – Jun 2025 |
📄 See schema_diagram.md for the full data model and table relationships.
| File | Module | Core SQL techniques |
|---|---|---|
01_order_funnel_sla.sql |
Order Funnel & Delivery SLA | Self-joins, TIMESTAMPDIFF, CASE WHEN, window functions |
02_cohort_retention.sql |
Cohort Retention & Repeat Purchase | Window functions, cohort-month pivoting, TIMESTAMPDIFF |
03_sku_profitability_pareto.sql |
SKU Profitability & Pareto (ABC) | NTILE, HAVING, cumulative aggregates |
04_rider_utilisation.sql |
Rider Utilisation | LAG, PARTITION BY, idle-gap analysis, hour bucketing |
05_growth_cac_ltv_referrals.sql |
CAC, LTV & Referral Chains | Recursive CTE, multi-table joins, NULLIF safe division |
Every query follows the same self-documenting format:
-- Q: <business question>
SELECT ...
-- ANSWER: <finding, in plain English>- 92.1% of placed orders reach delivery (24,413 of 26,504); the largest funnel leak is Placed → Accepted (886 orders, 3.3%).
- Average delivery time is 11.1 minutes (range 4–41 min).
- 8.5% of delivered orders breach the 15-minute SLA (2,076 of 24,413).
- Worst-performing store is Nagpur DS-1 at a 23.3% breach rate — roughly 3× the average — pointing to a city-level operational issue.
- Delivery slows modestly at peak: dinner is slowest at 11.7 min vs 10.4 min off-peak.
- Overall cancellation rate is 7.89% (2,091 of 26,504).
- 85.2% of purchasing customers place more than one order (4,942 of 5,801 who ordered at least once).
- 18 monthly cohorts tracked (Jan 2024 – Jun 2025), growing from 29 → 478 new customers per month.
- Month-1 retention holds steady at ~44–52% across cohorts, with no decay as the business scales.
- Top SKU by contribution profit: Pampers Baby Cereal (₹4.42L).
- 15 SKUs are loss-making and flagged as delisting candidates (worst: Farm Apple 1kg, −₹2.17L).
- Classic 80/20 pattern confirmed: the top quintile of profitable SKUs (30 SKUs) drives ~68% of total profit.
- Most profitable category is Packaged Food (₹14.4L); weakest is Cold Drinks & Juices (₹4.0L).
- Busiest rider: Sunil Nair — 772 orders.
- When riders work back-to-back, the average gap between orders is ~28.7 minutes.
- Morning is the busiest shift at 62.5 orders per rider — over 5× the evening/night shift — suggesting staffing should be reallocated toward mornings.
- Most understaffed hours by orders-per-rider: 9 AM, 12 PM, and 8 AM.
- Cheapest acquisition channel: Social Ads (₹416 CAC); most expensive: Affiliate (₹727 CAC).
- Most efficient channel: Paid Search — 18.31× LTV:CAC ratio.
- Referral chains run 5 levels deep, with 970 of 6,000 customers (16.2%) acquired via referral — genuine multi-level word-of-mouth growth.
- Database: MySQL 8.0
- Techniques: self-joins, recursive CTEs, window functions (
NTILE,LAG,PARTITION BY),CASE WHENbucketing, idle-gap analysis, multi-table joins, and safe division withNULLIF.
-- 1. Load the schema and seed data
SOURCE mysql_quickcommerce.sql;
-- 2. Point the session at the database
USE quickcommerce;
-- 3. Run any module top to bottom
SOURCE 01_order_funnel_sla.sql;Shazia Noori — Data Analyst 📧 shaziawork28@gmail.com · 🔗 LinkedIn