Swiggy-Style Delivery Schema

Problem Statement

Write SQL queries for a food delivery schema: restaurants(id, name, city, cuisine, rating) orders(id, customer_id, restaurant_id, total_amount, status, order_date) order_items(id, order_id, item_name, quantity, price) Write queries to: 1. Find top 3 restaurants by total revenue 2. Find the most ordered item across all restaurants 3. List all restaurants with their average order value

Sample Data

restaurants: (1,'Pizza Palace','Mumbai','Italian',4.5), (2,'Burger Barn','Delhi','American',4.2)
orders: (1,101,1,850,'delivered','2024-03-01'), (2,102,1,1200,'delivered','2024-03-02'), (3,103,2,600,'delivered','2024-03-01')
order_items: (1,1,'Margherita',2,400), (2,1,'Coke',1,50), (3,2,'Pepperoni',3,400), (4,3,'Burger',2,300)

Expected Output

Q1: Pizza Palace (2050), Burger Barn (600)
Q2: Pepperoni (3 units)
Q3: Pizza Palace avg=1025, Burger Barn avg=600