Skip to content
Zeni Study
  • Pages
    • Learn Python In 7 Days
      • Day 1 : Variable + if/else
      • Day 2: Loops
      • Day 3 : Array + String
      • Day 4 — Functions
      • Day 5 — Dictionaries - Hashmap
      • Day 6 — Files & Errors
      • Day 7 — Mini Project & Programmer Thinking
    • Machine Coding:
      • Day 1 — Bank account + Wallet :
      • Day 2 : RateLimiter + seat booking
      • Day 3 : Library Management + Classroom Attendance System
      • Day 4 : Bank account and notification system.
      • Day 5 : Key-value and payment processor.
      • Day 6 : Discount engine + Seat booking( in depth)
      • Day 7: Parking lot System :
    • Machine coding extended:
      • Day 7.5 — Parking Lot (Constraint-Heavy Variant)
      • Day 8 : Order + Payment System
      • Day 8 — Extension Round (Mid-Interview Change)
      • Day 9 — Ticket Booking with Expiry (Simulated Time)
      • Day 9 — Extension Round (Seat Lock Ownership + Forced Unlock)
    • SQL + DB Thinking course
      • Day 1 — Tables, Rows & Thinking in Data
      • Day 2 — Relationships & Foreign Keys (Thinking in Connections)
      • Day 3 — Filtering, Aggregation & Answering Data Questions
      • Day 4 — JOINs (Combining Tables the Right Way)
      • Day 5 — Subqueries, EXISTS & NOT EXISTS (Thinking in Layers)
      • Day 6 — Indexes, Constraints & How Databases Think
      • Day 7 — Full SQL Interview Simulation (SDE-1 Ready)
      • icon picker
        Extended Day 1 — Core Business Systems (Deep Practice)
      • Extended Day 2 — Booking & Platform Systems (High-Depth Practice)
      • Extended Day 3 — Content & Learning Platforms (Capstone Practice)
    • LLD: Java
      • DAY 1 — Core Foundations (SDE-2 Level)
      • DAY 2 — State, Time & Domain Correctness
      • DAY 3 — Extensibility & Failure Handling (SDE-2 Core Signal)
      • DAY 4 — Concurrency, Scheduling & Consistency (SDE-2 Critical)
      • DAY 5 — Workflow Design & Config-Driven Systems (SDE-2+)
      • DAY 6 — Eventing & Usage-Based Systems (Advanced SDE-2)
      • DAY 7 — Interview Simulation (Hard, Production-Grade)
      • Hidden Edge Cases Interviewers Expect (Day-wise)
      • Detailed Testing file:
    • Assignment:
      • If else Practice Set
Zeni Study
/
/
Extended Day 1 — Core Business Systems (Deep Practice)
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
⁠

🧠 Step 1 — Thinking (Very Important)

Ask:
Can salary change? → Yes
Should we overwrite salary? → ❌ No
Do we need history? → Yes
Salary = time-based data
⁠

✍️ Step 2 — Identify Entities

Expected entities:
Employee
Department
SalaryHistory
⁠

🧱 Step 3 — Schema Design (Attempt First)

👉 Student Task Design schema with:
Start date
End date (nullable for current salary)
⁠

📝 Step 4 — Interview Queries

Write SQL for:
Current salary of each employee
Average salary per department
Employees earning more than dept average
Departments with no employees
⁠

🔍 HINTS

Current salary → end_date IS NULL
Dept average → GROUP BY department
“More than dept avg” → correlated subquery
Departments with no employees → LEFT JOIN
⁠

✅ Solution — Problem 2

✅ Schema

CREATE TABLE departments (
department_id INT PRIMARY KEY,
name VARCHAR(100)
);

CREATE TABLE employees (
emp_id INT PRIMARY KEY,
name VARCHAR(100),
department_id INT,
FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

CREATE TABLE salary_history (
salary_id INT PRIMARY KEY,
emp_id INT,
salary INT,
start_date DATE,
end_date DATE,
FOREIGN KEY (emp_id) REFERENCES employees(emp_id)
);
⁠

✅ Query Solutions

1️⃣ Current Salary of Each Employee
SELECT e.emp_id, e.name, s.salary
FROM employees e
JOIN salary_history s
ON e.emp_id = s.emp_id
WHERE s.end_date IS NULL;
⁠
2️⃣ Average Salary per Department
SELECT d.name, AVG(s.salary) AS avg_salary
FROM departments d
JOIN employees e ON d.department_id = e.department_id
JOIN salary_history s ON e.emp_id = s.emp_id
WHERE s.end_date IS NULL
GROUP BY d.name;
⁠
3️⃣ Employees Earning More Than Dept Average
SELECT e.name, s.salary
FROM employees e
JOIN salary_history s ON e.emp_id = s.emp_id
WHERE s.end_date IS NULL
AND s.salary > (
SELECT AVG(s2.salary)
FROM employees e2
JOIN salary_history s2 ON e2.emp_id = s2.emp_id
WHERE e2.department_id = e.department_id
AND s2.end_date IS NULL
);
⁠
4️⃣ Departments with No Employees
SELECT d.department_id, d.name
FROM departments d
LEFT JOIN employees e ON d.department_id = e.department_id
WHERE e.emp_id IS NULL;
⁠

🧠 Reflection Questions (Must Answer)

Why salary should not be in employee table?
Which table grows fastest?
Where would indexes help most?
Which query was hardest and why?
⁠

⭐ Extended Day 1 Golden Rule

If your schema is right, even complex SQL becomes manageable.
⁠


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.