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