In this case study, I have used different concepts of intermediate and Advanced SQL like CTE's, Window Functions, Nested Subqueries and Joins to gain relevant insights from the data.
Database:- Click here
| users | restaurants | orders | order_details | menu | food | |||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
|
|
|
|
|
|
primary_id for each table is in bold letters
Q1. Find customers who have never ordered
Ans. Anupama and Rishabh
Q2. Find average Price per dish
| f_name | avg_price |
|---|---|
| Chicken popcorn | 300 |
| Chicken Wings | 230 |
| Choco Lava Cake | 98.33 |
| Masala Dosa | 180 |
| Non Veg Pizza | 450 |
| Rava Idli | 120 |
| Rice Meal | 213.33 |
| Roti Meal | 140 |
| Schezwan Noodles | 220 |
| Veg Manchurian | 180 |
| Veg Pizza | 400 |
Q3. Top restaurant in terms of number of orders for a given month
Ans Dosa Plaza
Q4. Find restaurants with monthly sales greater than 700 for a July month
Ans dominos and kfc
Q5. Find restaurants with max repeated customers
| r_name | uniuqe_customers |
|---|---|
| KFC | 2 |
Q6. Month Over Month revenue growth of Swiggy
| Month_num | Monthly_revenue_growth |
|---|---|
| 5 | NULL |
| 6 | 32.78 |
| 7 | 50.46 |
The above values are in %ages with respect to previous month
Q7. Find the most loyal customers for all restaurant
| r_name | name |
|---|---|
| Dosa Plaza | Ankit |
| kfc | Neha |
| box8 | Nitish |
| kfc | Vartika |
There are more questions discussed in coding file but their ouptut varies with different inputs