Backend practice series: 1. Requirements and data design · 2. APIs and overbooking · 3. Interview review · 4. Recursion and sorting · 5. SQL exercises · 6. SQL on the E-Learning database
This post solves the 9 SQL questions from the second practice session on the real schema of the E-Learning pet project (an online-course platform: teachers, courses, chapters, lessons, learners who enrol, rate and progress lesson by lesson), then reviews that schema like a pull request. An earlier version used a made-up schema because the slide only shows the diagram as an image; the real one changes the answers a lot: teachers and admins are separate tables, ratings sit on the enrolment table, and progress uses START/PROCESSING/DONE. Read the review of joins, GROUP BY/HAVING and evaluation order in part 5 first, and write each query yourself before opening the solution.
At a glance:
flowchart LR
U[user] --> UC["user_course: rating"]
C[course] --> UC
UC --> UCL["user_course_lesson: progress"]
Q[Writing queries] --> F["Fan-out: COUNT DISTINCT / CTE"]
Q --> LJ["Filter LEFT JOIN in ON"]
Q --> P["Ratios: NULLIF, 100.0"]
Q --> RK["Keep ties: DENSE_RANK"]
1. The pet project’s E-Learning schema
Key idea: learners, teachers and admins are three separate tables; the enrolment user_course holds the rating, and per-lesson progress lives in user_course_lesson. A lesson belongs to a course through its chapter (lesson → chapter → course); lesson has no course_id.
I transcribed the physical diagram from the slide image, switched table names to snake_case and kept every column. The step-by-step ERD design for this system is in Databases: ERD, joins, GROUP BY — Exercise 1, and the ORM/JPA side of the same schema is in the ORM and JPA series. This post sticks to SQL.
-- Learners (the User table on the slide). Teachers and admins are SEPARATE tables, not roles.
user(id PK, username UNIQUE, password, status, nickname, created_date, updated_date)
full_name(id PK, first_name, mid_name, last_name, status, created_date, updated_date,
user_id FK -> user) -- 1-1 with user
address(id PK, country, city, district, street, address_detail)
user_address(user_id FK, address_id FK, PK(user_id, address_id)) -- N-M
admin(id PK, username UNIQUE, password, status, created_date, updated_date)
teacher(id PK, username UNIQUE, password, status, name, created_date, updated_date)
course(id PK, name, description, status, teacher_id FK -> teacher, created_date, updated_date) -- teacher 1-N course
chapter(id PK, name, description, status, `order`, course_id FK, created_date, updated_date)
lesson(id PK, name, description, status, type, url, `order`, chapter_id FK, created_date, updated_date)
-- Enrolment: also where a learner's rating and review of the course are stored
user_course(user_id FK, course_id FK, rating, review, status, created_date, updated_date,
PK(user_id, course_id))
-- Per-lesson progress of one enrolment
user_course_lesson(user_id, course_id, lesson_id FK, status, created_date, updated_date,
PK(user_id, course_id, lesson_id), FK(user_id, course_id) -> user_course)
Value conventions (from the notes on the diagram):
| Column | Values |
|---|---|
user_course.status, user_course_lesson.status |
START, PROCESSING, DONE (progress) |
status on the “config” tables: user, admin, teacher, course, lesson |
ACTIVE, INACTIVE |
lesson.type |
VIDEO, PDF, DOCX, SLIDE |
From the questions’ wording to columns:
- “Completed lesson” →
user_course_lesson.status = 'DONE'. - “Course rating” →
user_course.rating. - “Locked account” →
user.status = 'INACTIVE'. - “Course currently taught” →
course.status = 'ACTIVE'.
Exercises
Exercise 1: nine queries
I ran all nine queries on MySQL 8.4 against sample data (a locked learner, an INACTIVE course, hidden chapters and lessons, an empty course) and checked the results by hand.
Q1. Top 5 highest-rated courses.
The crux: the rating sits on user_course and can be NULL; you need a minimum number of ratings so a course with a single 5-star rating doesn’t top the list.
Solution to Q1
SELECT c.id, c.name,
ROUND(AVG(uc.rating), 2) AS avg_rating,
COUNT(uc.rating) AS ratings
FROM course c
JOIN user_course uc ON uc.course_id = c.id
WHERE c.status = 'ACTIVE'
AND uc.rating IS NOT NULL
GROUP BY c.id, c.name
HAVING COUNT(uc.rating) >= 10 -- stop a course with a single 5-star rating from topping the list
ORDER BY avg_rating DESC, ratings DESC
LIMIT 5;
A learner can enrol without rating, so the rating is nullable. AVG and COUNT(uc.rating) already skip NULLs; WHERE uc.rating IS NOT NULL just makes the intent explicit and shrinks the rows to group.
Q2. Top 5 learners enrolled in the most courses.
The crux: every row in user is a learner, so no role filter; the name is in a 1-1 table, hence LEFT JOIN; for ties, use DENSE_RANK.
Solution to Q2
SELECT u.id, u.username,
CONCAT_WS(' ', f.last_name, f.mid_name, f.first_name) AS full_name,
COUNT(*) AS courses
FROM user u
JOIN user_course uc ON uc.user_id = u.id
LEFT JOIN full_name f ON f.user_id = u.id
GROUP BY u.id, u.username, f.last_name, f.mid_name, f.first_name
ORDER BY courses DESC, u.id
LIMIT 5;
- Teachers live in their own
teachertable, so there is no role filter as in a merged schema. LEFT JOIN full_namekeeps users without a name record.CONCAT_WSskips NULL parts, so there is no stray double space when there is no middle name.u.idinORDER BYkeeps the output stable on ties.
LIMIT 5 still cuts through a tie between 5th and 6th place. To keep everyone who ties, use a window function (a function computed over a “window” of rows without collapsing them):
SELECT id, username, courses
FROM (
SELECT u.id, u.username, COUNT(*) AS courses,
DENSE_RANK() OVER (ORDER BY COUNT(*) DESC) AS rnk
FROM user u
JOIN user_course uc ON uc.user_id = u.id
GROUP BY u.id, u.username
) t
WHERE rnk <= 5;
Q3. Top 5 courses with the most chapters and lessons.
The crux: count chapters with COUNT(DISTINCT ch.id) because of fan-out; filter chapter/lesson status in ON so an empty course still shows up with zeros.
Solution to Q3
SELECT c.id, c.name,
COUNT(DISTINCT ch.id) AS chapters,
COUNT(l.id) AS lessons
FROM course c
LEFT JOIN chapter ch ON ch.course_id = c.id AND ch.status = 'ACTIVE'
LEFT JOIN lesson l ON l.chapter_id = ch.id AND l.status = 'ACTIVE'
WHERE c.status = 'ACTIVE'
GROUP BY c.id, c.name
ORDER BY lessons DESC, chapters DESC
LIMIT 5;
Putting the chapter and lesson status = 'ACTIVE' filters in WHERE would drop the NULL rows the LEFT JOIN produces, turning it into an INNER JOIN and losing courses with no chapters yet.
Q4. The 5 lessons a learner completed most recently.
The crux: the schema has no completed_at, so all you have is the updated_date of DONE rows, and that isn’t quite the completion time.
Solution to Q4
SELECT l.id, l.name AS lesson, c.name AS course, ucl.updated_date AS completed_at
FROM user_course_lesson ucl
JOIN lesson l ON l.id = ucl.lesson_id
JOIN course c ON c.id = ucl.course_id
WHERE ucl.user_id = :userId
AND ucl.status = 'DONE'
ORDER BY ucl.updated_date DESC
LIMIT 5;
-- Suggested index: (user_id, status, updated_date), so the whole history isn't sorted
Q5. For one course, how many lessons each learner has completed in each chapter (learner name, course name, chapter name, lessons done in that chapter).
The crux: start from the enrolment, go through every chapter of the course, and only then LEFT JOIN down to progress, with ucl.status = 'DONE' inside ON.
Solution to Q5
SELECT u.username,
CONCAT_WS(' ', f.last_name, f.mid_name, f.first_name) AS full_name,
c.name AS course,
ch.name AS chapter,
COUNT(ucl.lesson_id) AS done_lessons
FROM user_course uc
JOIN user u ON u.id = uc.user_id
LEFT JOIN full_name f ON f.user_id = u.id
JOIN course c ON c.id = uc.course_id
JOIN chapter ch ON ch.course_id = c.id AND ch.status = 'ACTIVE'
LEFT JOIN lesson l ON l.chapter_id = ch.id AND l.status = 'ACTIVE'
LEFT JOIN user_course_lesson ucl
ON ucl.user_id = uc.user_id
AND ucl.course_id = uc.course_id
AND ucl.lesson_id = l.id
AND ucl.status = 'DONE'
WHERE uc.course_id = :courseId
GROUP BY u.id, u.username, f.last_name, f.mid_name, f.first_name,
c.name, ch.id, ch.name, ch.`order`
ORDER BY u.username, ch.`order`;
Because every chapter is joined first, a learner who hasn’t touched a chapter still gets a row with 0. With ucl.status = 'DONE' in WHERE, chapters with no finished lesson would vanish from the result.
Q6. Courses that have learners with locked accounts, and who those learners are.
The crux: the wording is vague and overlaps with Q8, so I read this one as the per-course version (one row per course, with the count and list of locked learners); Q8 is the detailed one-row-per-pair version.
Solution to Q6
SELECT c.id AS course_id, c.name AS course,
COUNT(*) AS locked_students,
GROUP_CONCAT(u.username ORDER BY u.username SEPARATOR ', ') AS usernames
FROM user_course uc
JOIN user u ON u.id = uc.user_id AND u.status = 'INACTIVE'
JOIN course c ON c.id = uc.course_id
GROUP BY c.id, c.name;
If the intent was “list the locked courses and the locked learners separately” (both tables have a status), those are two independent queries, which you can stitch together with UNION ALL:
SELECT 'COURSE' AS kind, id, name AS label FROM course WHERE status = 'INACTIVE'
UNION ALL
SELECT 'USER', id, username FROM user WHERE status = 'INACTIVE';
On a real team, ask whoever wrote the requirement and write down the interpretation you agreed on.
Q7. Top 5 teachers currently teaching the most courses.
The crux: “currently teaching” = ACTIVE courses of an ACTIVE teacher; that is a business assumption, so state it in the code review or a comment.
Solution to Q7
SELECT t.id, t.name, COUNT(*) AS active_courses
FROM teacher t
JOIN course c ON c.teacher_id = t.id AND c.status = 'ACTIVE'
WHERE t.status = 'ACTIVE'
GROUP BY t.id, t.name
ORDER BY active_courses DESC, t.id
LIMIT 5;
Retired (INACTIVE) courses don’t count. I also filter t.status = 'ACTIVE', because a locked teacher is no longer “teaching” even if their courses haven’t been switched off yet.
Q8. Locked learners who are enrolled in courses: username and course name.
The crux: two status columns with different meanings sit side by side (user.status is the account, ACTIVE/INACTIVE; user_course.status is progress, START/PROCESSING/DONE), so always qualify with the table alias.
Solution to Q8
SELECT u.username, c.name AS course, uc.status AS enrollment_status
FROM user u
JOIN user_course uc ON uc.user_id = u.id
JOIN course c ON c.id = uc.course_id
WHERE u.status = 'INACTIVE'
ORDER BY u.username, c.name;
Q9. Each learner’s completion percentage in each course.
The crux: compute the lesson total and the DONE count in two separate CTEs (no fan-out), both counting only ACTIVE lessons, then LEFT JOIN them onto the enrolment.
Solution to Q9
WITH course_size AS (
SELECT ch.course_id, COUNT(*) AS total_lessons
FROM chapter ch
JOIN lesson l ON l.chapter_id = ch.id
WHERE ch.status = 'ACTIVE' AND l.status = 'ACTIVE'
GROUP BY ch.course_id
),
done AS (
SELECT ucl.user_id, ucl.course_id, COUNT(*) AS done_lessons
FROM user_course_lesson ucl
JOIN lesson l ON l.id = ucl.lesson_id AND l.status = 'ACTIVE'
JOIN chapter ch ON ch.id = l.chapter_id AND ch.status = 'ACTIVE'
AND ch.course_id = ucl.course_id -- the lesson must really belong to this course
WHERE ucl.status = 'DONE'
GROUP BY ucl.user_id, ucl.course_id
)
SELECT u.username, c.name AS course,
COALESCE(d.done_lessons, 0) AS done_lessons,
COALESCE(cs.total_lessons, 0) AS total_lessons,
ROUND(100.0 * COALESCE(d.done_lessons, 0) / NULLIF(cs.total_lessons, 0), 2) AS percent_done
FROM user_course uc
JOIN user u ON u.id = uc.user_id
JOIN course c ON c.id = uc.course_id
LEFT JOIN course_size cs ON cs.course_id = uc.course_id
LEFT JOIN done d ON d.user_id = uc.user_id AND d.course_id = uc.course_id
ORDER BY c.name, percent_done DESC;
- The lesson total goes through
chapter → lesson, becauselessonhas nocourse_id. - If the denominator drops hidden lessons but the numerator still counts them, the percentage can exceed 100%.
NULLIF(..., 0)guards against dividing by zero for a course with no lessons;100.0forces decimal division.- The
ch.course_id = ucl.course_idcheck in thedoneCTE blocks the trap described below.
Exercise 2 (my own): review the schema like a pull request
Task (my own): read the E-Learning schema from section 1 as if it were a design PR, list the problems and propose something better.
The schema is fine for practising joins, but as a real PR I would comment:
- Three duplicated account tables.
user,teacherandadminall carryusername,passwordandstatus. As a result, someone who both learns and teaches needs two accounts,usernameis only unique within each table, and login has to look in three places. full_nameas a separate 1-1 table is over-normalisation: every screen that shows a name pays an extra join for nothing (names aren’t shared between users). Fold the name columns into the account table.- Status as free-form
varchar(50): nothing stops'DONEE'or'active'. UseENUM, aCHECK(enforced by MySQL since 8.0.16) or a lookup table with a foreign key. ratinghas no domain constraint (CHECK (rating BETWEEN 1 AND 5)),passwordshould bepassword_hash, and there is nocompleted_atfor the single most important event in a learning system.orderis a keyword, and there should be aUNIQUE (chapter_id, position)so two lessons can’t share a slot.course.name varchar(50)is too short for real course titles.
Solution: the proposed schema (MySQL 8.4 DDL)
Trimmed; I ran it on MySQL 8.4, and both the CHECK constraints and the foreign keys reject bad data.
CREATE TABLE account (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(255) NOT NULL UNIQUE,
password_hash VARCHAR(255) NOT NULL,
role ENUM('STUDENT','TEACHER','ADMIN') NOT NULL,
status ENUM('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
first_name VARCHAR(100), middle_name VARCHAR(100), last_name VARCHAR(100),
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
CREATE TABLE lesson (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
chapter_id BIGINT NOT NULL,
name VARCHAR(255) NOT NULL,
type ENUM('VIDEO','PDF','DOCX','SLIDE') NOT NULL,
url VARCHAR(1024) NOT NULL,
position INT NOT NULL,
status ENUM('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
UNIQUE (chapter_id, position),
FOREIGN KEY (chapter_id) REFERENCES chapter(id)
);
CREATE TABLE enrollment ( -- user_course
student_id BIGINT NOT NULL,
course_id BIGINT NOT NULL,
status ENUM('START','PROCESSING','DONE') NOT NULL DEFAULT 'START',
rating TINYINT NULL CHECK (rating BETWEEN 1 AND 5),
review VARCHAR(2000) NULL,
enrolled_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (student_id, course_id),
FOREIGN KEY (student_id) REFERENCES account(id),
FOREIGN KEY (course_id) REFERENCES course(id)
);
CREATE TABLE lesson_progress ( -- user_course_lesson
student_id BIGINT NOT NULL,
course_id BIGINT NOT NULL,
lesson_id BIGINT NOT NULL,
status ENUM('START','PROCESSING','DONE') NOT NULL DEFAULT 'START',
completed_at TIMESTAMP NULL,
PRIMARY KEY (student_id, course_id, lesson_id),
FOREIGN KEY (student_id, course_id) REFERENCES enrollment(student_id, course_id),
FOREIGN KEY (lesson_id) REFERENCES lesson(id),
INDEX idx_recent_done (student_id, status, completed_at),
CHECK ((status = 'DONE') = (completed_at IS NOT NULL)) -- DONE must have a completed_at
);
Remember
- Can you explain why the rating lives on
user_course, and why “completed most recently” needscompleted_atrather thanupdated_date? - Can you write a chapter-and-lesson count that doesn’t fan out, and say why
DISTINCTcan’t save you withSUM? - Do you know when a condition goes in
ONversusWHEREwith a LEFT JOIN, and can you write a completion percentage that never exceeds 100% or divides by zero? - Can you name the risk of the redundant
course_idin the progress table and three ways to block it? - Can you review a schema: account tables, how statuses are stored, a column named
order, inline foreign keys in MySQL?
Interview questions
Junior
In the E-Learning schema, why does the course rating live on user_course rather than on course?
Because a rating is one learner’s opinion of one course, an attribute of the N–M relationship, so it belongs on the join table (the enrolment).
The course table can only hold an aggregate (average score, number of ratings) as denormalised data for fast reads. That number has to be recomputed from user_course whenever a new rating arrives.
Why is naming a column order asking for trouble?
ORDER is a reserved SQL keyword, so unquoted it is a syntax error and you have to quote it everywhere.
CREATE TABLE, the column list of an INSERT and ORDER BY order all break without quotes (`order` in MySQL, "order" in PostgreSQL), including in ORM-generated SQL. Name it position or sort_order and you never have to remember.
Mid
How would you compute each learner’s completion percentage in a course, and which traps do you avoid?
Compute the course’s lesson total (through chapter → lesson) and each enrolment’s DONE count in two separate CTEs, then LEFT JOIN both onto the enrolment table.
The traps:
- Joining everything and then counting: fan-out.
status = 'DONE'inWHEREafter a LEFT JOIN: learners who haven’t started disappear.- Integer division: returns 0.
- A course with no lessons: divide by zero (use
NULLIF). - Numerator and denominator filtering hidden lessons differently: the ratio goes past 100%.
Would you store statuses (ACTIVE/INACTIVE, START/PROCESSING/DONE) as varchar, ENUM, CHECK or a lookup table?
Not free-form varchar, because it lets bad values in; choose between ENUM, varchar + CHECK and a lookup table depending on how often the list changes.
- MySQL
ENUMis compact and rejects bad values, but adding a value means anALTER TABLE, and it sorts by declaration order rather than alphabetically. varcharplusCHECK (status IN (...))is readable and portable (MySQL enforces it since 8.0.16).- A lookup table with a foreign key suits lists that change often or need extra attributes (display name, ordering).
- On the Java side, map it with
@Enumerated(EnumType.STRING), neverORDINAL.
You count chapters and lessons per course in one query and COUNT(ch.id) comes out wrong. Why?
Joining course → chapter → lesson produces one row per lesson, so each chapter repeats once per lesson and COUNT(ch.id) actually counts lessons.
Use COUNT(DISTINCT ch.id) for chapters and COUNT(l.id) for lessons, or count each level in its own subquery. That is the fan-out trap; for SUM there is no DISTINCT to rescue you, so aggregating before joining is the safe habit.
Senior
The progress table user_course_lesson stores both course_id and lesson_id. What is the risk, and how do you handle it?
course_id is redundant (derivable via lesson → chapter → course) and nothing forces the two paths to agree, so the data can go silently wrong.
An application bug can record progress on a course-A lesson against a course-B enrolment, and every report that trusts course_id is wrong. Fixes: validate in the service before writing; join back to cross-check when computing; or put course_id on lesson with a UNIQUE (id, course_id) so a composite foreign key (lesson_id, course_id) is enforced by the database.
The reason to keep course_id here at all is the foreign key to the enrolment (user_id, course_id), which guarantees progress is only recorded for enrolled learners.
Would you split user, teacher and admin into three tables as the slide’s E-Learning schema does?
Usually not: I’d use one account table with a role, plus role-specific profile tables when needed.
Three tables duplicate the login columns, one person can’t both learn and teach with one account, usernames can collide across tables, and the login flow looks in several places. One account table with a role (or an account_role table) puts authentication and authorisation in one place; teacher_profile holds teacher-specific data. The cost: a foreign key pointing straight at account can’t check “the instructor has the TEACHER role”, so either point it at teacher_profile or check it in the service.
A “my courses” page shows progress for every course. Do you compute it with SQL on every request or store it?
For a read-heavy platform I’d store it, and keep the computing query as the source of truth for reconciliation.
- Computing: always correct, but every page view recounts, and the cost grows with lessons and learners.
- Storing:
done_lessonsandtotal_lessonson the enrolment, updated in the same transaction when a lesson flips to DONE; O(1) reads. - The cost of storing: when a course adds or hides lessons the denominator changes for every learner, and you need a periodic reconciliation job to fix drift.
Useful phrases for interviews
- “The rating is an attribute of the relationship, so it belongs on the enrolment table, not on the course.”
- “I aggregate each level in its own CTE first and join afterwards, so the counts don’t fan out.”
- “That condition has to go in the ON clause, otherwise the LEFT JOIN quietly becomes an inner join.”
- “
updated_datetells you when the row last changed, not when the lesson was completed, so I’d add acompleted_atcolumn.” - “The redundant
course_idcan drift from the lesson’s real course; a composite foreign key would let the database enforce it.” - “I’d rather have one account table with roles than three tables that duplicate the login columns.”
- “
LIMIT 5cuts through ties; if ties matter, I useDENSE_RANKand filter on the rank.” - “The requirement is ambiguous, so let me state my interpretation before I write the query.”
- “In MySQL I always declare foreign keys at table level, because inline
REFERENCESis silently ignored.”
Notes based on the Fundamentals of Backend course (Ronin Engineer), rewritten in my own words.