Skip to content
Zeni Study
Share
Explore

Extended Day 1 — Core Business Systems (Deep Practice)

⁠

🟢 Extended Day 1 — Core Business Systems (Deep Practice)

⏱ Time: 3–4 hours 🎯 Focus: Schema design + interview-grade queries
⁠

🧠 Learning Objectives (Read First)

By the end of today, the student should be able to:
Convert a vague problem statement into entities
Decide table boundaries correctly
Avoid common schema design mistakes
Write SQL queries that interviewers ask most often
Explain why a table or relationship exists
⚠️ If you jump straight to SQL, you are doing it wrong. ​Design comes first.
⁠

🧩 Problem 1 — Food Delivery System (Zomato / Swiggy)

⁠

📘 Problem Statement

Design a database for a food delivery app where:
Users place orders
Restaurants list food items
An order can contain multiple food items
Each food item belongs to one restaurant
Track order status (PLACED, DELIVERED, CANCELLED)
⁠

🧠 Step 1 — Think Before Writing SQL

Ask These Questions

Can a user place multiple orders? → Yes
Can an order have multiple items? → Yes
Can a restaurant have many items? → Yes
Can an item belong to multiple restaurants? → No
⁠

✍️ Step 2 — Identify Entities (Must Do)

Expected entities (do NOT skip):
User
Restaurant
MenuItem
Order
OrderItem ← most important
❌ If OrderItem is missing → schema is incorrect.
⁠

🧱 Step 3 — Schema Design (Attempt First)

👉 Student Task Design tables with:
Primary keys
Foreign keys
Only important fields
DO NOT scroll yet.
⁠

📝 Step 4 — Interview Queries (Attempt)

Write SQL for:
Total revenue per restaurant
Top 3 selling food items (by quantity)
Users who ordered from more than 2 restaurants
Orders with no items
Average order value per user
⁠

🔍 HINTS (Read Only If Stuck)

Order ↔ MenuItem is many-to-many
Restaurant revenue comes via menu items → order items
“More than 2 restaurants” ≠ more than 2 orders
Orders with no items → LEFT JOIN + NULL check
Average order value = total amount / number of orders
⁠

✅ Solution — Problem 1 (Read Carefully)

✅ Schema

CREATE TABLE users (
user_id INT PRIMARY KEY,
name VARCHAR(100)
);

CREATE TABLE restaurants (
restaurant_id INT PRIMARY KEY,
name VARCHAR(100)
);

CREATE TABLE menu_items (
item_id INT PRIMARY KEY,
restaurant_id INT,
name VARCHAR(100),
price INT,
FOREIGN KEY (restaurant_id) REFERENCES restaurants(restaurant_id)
);

CREATE TABLE orders (
order_id INT PRIMARY KEY,
user_id INT,
status VARCHAR(20),
created_at DATE,
FOREIGN KEY (user_id) REFERENCES users(user_id)
);

CREATE TABLE order_items (
order_item_id INT PRIMARY KEY,
order_id INT,
item_id INT,
quantity INT,
FOREIGN KEY (order_id) REFERENCES orders(order_id),
FOREIGN KEY (item_id) REFERENCES menu_items(item_id)
);
⁠

✅ Query Solutions

1️⃣ Total Revenue per Restaurant
SELECT r.restaurant_id, r.name,
SUM(mi.price * oi.quantity) AS revenue
FROM restaurants r
JOIN menu_items mi ON r.restaurant_id = mi.restaurant_id
JOIN order_items oi ON mi.item_id = oi.item_id
GROUP BY r.restaurant_id, r.name;
⁠
2️⃣ Top 3 Selling Food Items
SELECT mi.item_id, mi.name, SUM(oi.quantity) AS total_sold
FROM menu_items mi
JOIN order_items oi ON mi.item_id = oi.item_id
GROUP BY mi.item_id, mi.name
ORDER BY total_sold DESC
LIMIT 3;
⁠
3️⃣ Users Ordering from > 2 Restaurants
SELECT o.user_id
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
JOIN menu_items mi ON oi.item_id = mi.item_id
GROUP BY o.user_id
HAVING COUNT(DISTINCT mi.restaurant_id) > 2;
⁠
4️⃣ Orders with No Items
SELECT o.order_id
FROM orders o
LEFT JOIN order_items oi ON o.order_id = oi.order_id
WHERE oi.order_item_id IS NULL;
⁠
5️⃣ Average Order Value per User
SELECT o.user_id,
AVG(order_total) AS avg_order_value
FROM (
SELECT o.order_id, o.user_id,
SUM(mi.price * oi.quantity) AS order_total
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
JOIN menu_items mi ON oi.item_id = mi.item_id
GROUP BY o.order_id, o.user_id
) t
GROUP BY user_id;
⁠

🧩 Problem 2 — Employee Management System

⁠

📘 Problem Statement

Design a database where:
Employees belong to departments
Employee salary changes over time
Salary history must be preserved
We should know current salary
Gallery
 
Want to print your doc?
This is not the way.
Try clicking the ··· in the right corner or using a keyboard shortcut (
CtrlP
) instead.