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)
      • Extended Day 1 — Core Business Systems (Deep Practice)
      • Extended Day 2 — Booking & Platform Systems (High-Depth Practice)
      • icon picker
        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 3 — Content & Learning Platforms (Capstone Practice)
Share
Explore

Extended Day 3 — Content & Learning Platforms (Capstone Practice)

Extended Day 3 — Content & Learning Platforms, the final capstone practice day for SQL + DB.
This day focuses on:
Many-to-many relationships
Analytics-style interview queries
Self-referencing tables
“Insight” questions, not just CRUD
If a student completes Extended Days 1–3, they are strongly SQL-ready for SDE-1.
⁠

🟢 Extended Day 3 — Content & Learning Platforms (Capstone Practice)

⏱ Time: 3–4 hours 🎯 Focus: Many-to-many + analytics + edge cases
⁠

🧠 Learning Objectives (Read First)

By the end of today, the student should be able to:
Design schemas with many-to-many relationships
Use junction tables correctly
Handle analytics queries interviewers love
Model self-referencing relationships
Explain trade-offs clearly
⚠️ Today is less about syntax and more about correct modeling.
⁠

🧩 Problem 1 — Online Learning Platform (Udemy / Coursera)

⁠

📘 Problem Statement

Design a database for an online learning platform where:
Users enroll in courses
Courses contain multiple lessons
Track lesson-level progress per user
Track course completion
Key constraints:
One user → many courses
One course → many users
Progress is tracked per lesson per user
⁠

🧠 Step 1 — Think Before SQL

Ask yourself:
Is user ↔ course one-to-many? → ❌ No
Is progress stored in course table? → ❌ No
Does enrollment need its own table? → ✅ Yes
⁠

✍️ Step 2 — Identify Entities (Must Do)

Expected entities:
User
Course
Lesson
Enrollment ← junction table
Progress ← critical
❌ Missing Enrollment or Progress = wrong schema.
⁠

🧱 Step 3 — Schema Design (Attempt First)

👉 Student Task Design tables with:
PKs, FKs
Minimal fields
Correct relationships
Pause here and attempt.
⁠

📝 Step 4 — Interview Queries (Attempt)

Write SQL for:
Users enrolled in most courses
Courses with highest completion rate
Users who enrolled but never started
Average completion percentage per course
⁠

🔍 HINTS (Read Only If Stuck)

Enrollment = many-to-many
Progress references user + lesson
Completion rate = completed lessons / total lessons
“Never started” = no progress rows
⁠

✅ Solution — Problem 1

✅ Schema

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

CREATE TABLE courses (
course_id INT PRIMARY KEY,
title VARCHAR(100)
);

CREATE TABLE lessons (
lesson_id INT PRIMARY KEY,
course_id INT,
title VARCHAR(100),
FOREIGN KEY (course_id) REFERENCES courses(course_id)
);

CREATE TABLE enrollments (
enrollment_id INT PRIMARY KEY,
user_id INT,
course_id INT,
enrolled_at DATE,
FOREIGN KEY (user_id) REFERENCES users(user_id),
FOREIGN KEY (course_id) REFERENCES courses(course_id)
);

CREATE TABLE progress (
progress_id INT PRIMARY KEY,
user_id INT,
lesson_id INT,
completed BOOLEAN,
FOREIGN KEY (user_id) REFERENCES users(user_id),
FOREIGN KEY (lesson_id) REFERENCES lessons(lesson_id)
);

⁠

✅ Query Solutions

1️⃣ Users Enrolled in Most Courses

SELECT user_id, COUNT(*) AS course_count
FROM enrollments
GROUP BY user_id
ORDER BY course_count DESC
LIMIT 1;

⁠

2️⃣ Courses with Highest Completion Rate

SELECT c.course_id, c.title,
AVG(CASE WHEN p.completed THEN 1 ELSE 0 END) AS completion_rate
FROM courses c
JOIN lessons l ON c.course_id = l.course_id
LEFT JOIN progress p ON l.lesson_id = p.lesson_id
GROUP BY c.course_id, c.title
ORDER BY completion_rate DESC;

⁠

3️⃣ Users Who Enrolled but Never Started

SELECT DISTINCT e.user_id
FROM enrollments e
LEFT JOIN progress p ON e.user_id = p.user_id
WHERE p.progress_id IS NULL;

⁠

4️⃣ Average Completion % per Course

SELECT c.course_id,
AVG(CASE WHEN p.completed THEN 1 ELSE 0 END) * 100 AS avg_completion
FROM courses c
JOIN lessons l ON c.course_id = l.course_id
LEFT JOIN progress p ON l.lesson_id = p.lesson_id
GROUP BY c.course_id;

⁠

🧠 Reflection (Must Answer)

Why is progress per lesson, not per course?
Why enrollment must be separate table?
Which table grows fastest?
Where would indexes help?
⁠

🧩 Problem 2 — Social Media Platform (Instagram Lite)

⁠

📘 Problem Statement

Design a database where:
Users create posts
Users can like posts
Users can comment on posts
Users can follow other users
Key constraints:
Likes are many-to-many
Followers are self-referencing
Users can like their own post
⁠

🧠 Step 1 — Thinking

Ask:
Is follow many-to-many? → Yes
Is follower table referencing same user table? → Yes
Can posts exist without likes/comments? → Yes
⁠

✍️ Step 2 — Identify Entities

Expected:
User
Post
Like ← many-to-many
Comment
Follow ← self-referencing
⁠

🧱 Step 3 — Schema Design (Attempt First)

👉 Student Task Design schema carefully.
⁠

📝 Step 4 — Interview Queries

Write SQL for:
Top 5 most liked posts
Users with no followers
Posts with no comments
Users who liked their own posts
⁠

🔍 HINTS

Likes = junction table
Followers = (follower_id, followee_id)
“No comments” → LEFT JOIN + NULL
Self-like = user_id = post.user_id
⁠

✅ Solution — Problem 2

✅ Schema

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

CREATE TABLE posts (
post_id INT PRIMARY KEY,
user_id INT,
content TEXT,
created_at TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(user_id)
);

CREATE TABLE likes (
like_id INT PRIMARY KEY,
user_id INT,
post_id INT,
FOREIGN KEY (user_id) REFERENCES users(user_id),
FOREIGN KEY (post_id) REFERENCES posts(post_id)
);

CREATE TABLE comments (
comment_id INT PRIMARY KEY,
post_id INT,
user_id INT,
text TEXT,
created_at TIMESTAMP,
FOREIGN KEY (post_id) REFERENCES posts(post_id),
FOREIGN KEY (user_id) REFERENCES users(user_id)
);

CREATE TABLE follows (
follower_id INT,
followee_id INT,
PRIMARY KEY (follower_id, followee_id),
FOREIGN KEY (follower_id) REFERENCES users(user_id),
FOREIGN KEY (followee_id) REFERENCES users(user_id)
);

⁠

✅ Query Solutions

1️⃣ Top 5 Most Liked Posts

SELECT p.post_id, COUNT(l.like_id) AS likes
FROM posts p
LEFT JOIN likes l ON p.post_id = l.post_id
GROUP BY p.post_id
ORDER BY likes DESC
LIMIT 5;

⁠

2️⃣ Users with No Followers

SELECT u.user_id, u.name
FROM users u
LEFT JOIN follows f ON u.user_id = f.followee_id
WHERE f.followee_id IS NULL;

⁠

3️⃣ Posts with No Comments

SELECT p.post_id
FROM posts p
LEFT JOIN comments c ON p.post_id = c.post_id
WHERE c.comment_id IS NULL;

⁠

4️⃣ Users Who Liked Their Own Posts

SELECT DISTINCT u.user_id, u.name
FROM users u
JOIN posts p ON u.user_id = p.user_id
JOIN likes l ON p.post_id = l.post_id
WHERE l.user_id = u.user_id;

⁠

🧠 Final Reflection (Must Answer)

Why followers table is self-referencing?
Why likes is separate table?
Which tables grow fastest?
What breaks if follows has no PK?
⁠

⭐ Extended Day 3 Golden Rule

If relationships are modeled correctly, analytics queries become obvious.
⁠

🎉 SQL + DB PRACTICE COMPLETE

After Extended Days 1–3, the student can:
Design schemas for any interview system
Identify many-to-many relationships correctly
Handle analytics SQL questions
Explain design trade-offs confidently
Pass most SDE-1 SQL & DB rounds
⁠

🧭 Where You Are Now (Big Picture)

You’ve now covered:
Python fundamentals
Machine coding (with extensions)
DSA (with LC mapping)
SQL + DB thinking
Advanced schema design practice
👉 This is full SDE-1 technical readiness.
⁠

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.