Skip to content
Zeni Study
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;
⁠
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.