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
      • icon picker
        Day 7 — Full SQL Interview Simulation (SDE-1 Ready)
      • 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
/
/
Day 7 — Full SQL Interview Simulation (SDE-1 Ready)
Share
Explore

Day 7 — Full SQL Interview Simulation (SDE-1 Ready)

⁠

🟢 Day 7 — Full SQL Interview Simulation (SDE-1 Ready)

🎯 Day 7 Outcome

By the end of Day 7, the student should be able to:
Design a clean schema from a vague problem
Write JOIN + GROUP BY + subquery queries
Explain why their SQL works
Handle follow-up questions
Stay calm in a real interview
If someone clears Day 7 honestly, they are SQL-ready for SDE-1 interviews.
⁠

⏱ Interview Rules (Set the Context)

Tell the student:
⏰ Time limit: 90 minutes
📝 First design schema (on paper / editor)
❌ No Googling
✅ Explain logic in English before SQL
❌ No over-optimization
✅ Correctness > cleverness
⁠

🧩 Interview Problem — E-Commerce Database

Problem Statement (As Interviewer Would Say)

“We have a simple e-commerce system. Users can place orders. Each order can have multiple items. Each item refers to a product.”
⁠

1️⃣ Step 1 — Identify Entities (Thinking Step)

Student should identify:
User
Product
Order
OrderItem (important!)
If OrderItem is missed → design is wrong
⁠

2️⃣ Step 2 — Design Schema (Expected)

Users

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

Products

CREATE TABLE products (
product_id INT PRIMARY KEY,
name VARCHAR(100),
price INT
);
⁠

Orders

CREATE TABLE orders (
order_id INT PRIMARY KEY,
user_id INT,
order_date DATE,
FOREIGN KEY (user_id) REFERENCES users(user_id)
);
⁠

Order Items

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

3️⃣ Interview Queries (Core Part)

Query 1 — Total Revenue

“What is the total revenue?”
SELECT SUM(p.price * oi.quantity) AS total_revenue
FROM order_items oi
JOIN products p
ON oi.product_id = p.product_id;
⁠

Query 2 — Revenue Per User

“How much did each user spend?”
SELECT u.user_id, u.name, SUM(p.price * oi.quantity) AS total_spent
FROM users u
JOIN orders o ON u.user_id = o.user_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
GROUP BY u.user_id, u.name;
⁠

Query 3 — Users With No Orders

“Which users never placed any order?”
SELECT u.user_id, u.name
FROM users u
LEFT JOIN orders o
ON u.user_id = o.user_id
WHERE o.order_id IS NULL;
⁠

Query 4 — Top 3 Selling Products

“Which products sold the most?”
SELECT p.product_id, p.name, SUM(oi.quantity) AS total_sold
FROM order_items oi
JOIN products p
ON oi.product_id = p.product_id
GROUP BY p.product_id, p.name
ORDER BY total_sold DESC
LIMIT 3;
⁠

Query 5 — Orders With No Items (Edge Case)

“Find orders which have 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;
⁠

4️⃣ Follow-Up Interview Questions (Verbal)

Student must answer verbally:

Q1. Why do we need order_items table?

Expected Answer:
One order can have multiple products
Quantity varies per product
Avoid duplication
⁠

Q2. Why not store product price in order_items?

Expected Answer:
Price may change
Historical price handling depends on requirement
Current design keeps it simple
⁠

Q3. Which columns would you index?

Expected Answer:
Primary keys
Foreign keys:
orders.user_id
order_items.order_id
order_items.product_id
⁠

Q4. What breaks if foreign keys are removed?

Expected Answer:
Invalid references
Data inconsistency
Orphan rows
⁠

5️⃣ Extension Question (Very Common)

“Now add a requirement: Find users who spent more than average user spend.”

Expected SQL

SELECT user_id, name
FROM (
SELECT u.user_id, u.name, SUM(p.price * oi.quantity) AS total_spent
FROM users u
JOIN orders o ON u.user_id = o.user_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
GROUP BY u.user_id, u.name
) t
WHERE total_spent > (
SELECT AVG(total_spent)
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.