Interview Question Bank · Per-learner furthest point · inactivity cutoff · mode with RANK
11. Where learners drop out
HardEducationQuestion #227
Concept: Per-learner furthest point · inactivity cutoff · mode with RANKshow
Dropout analysis runs in two passes. First collapse each learner to one row: how far they got (the highest step reached, not the last thing they touched), whether they finished, and when they were last active. Then keep the learners who didn't finish and have gone quiet, count them by the step they stopped at, and rank those counts within each group to find the most common one, keeping ties.
Example (different data):
WITH per AS (
SELECT user_id, MAX(step_no) AS furthest, MAX(seen_at) AS last_seen,
bool_or(step_no = 5) AS finished
FROM onboarding_steps GROUP BY user_id
)
SELECT furthest, COUNT(*) AS stuck
FROM per
WHERE NOT finished AND last_seen < TIMESTAMP '2025-06-01'
GROUP BY furthest
ORDER BY stuck DESC;Your task
Brightpath Academy wants to fix the lesson where self-paced learners give up. In this 2025-04-30 snapshot, a learner who hasn't completed every lesson of a course and hasn't started any of its lessons since 2025-04-01 has dropped out, at the furthest lesson (highest lesson_no) they ever started.
For each self-paced course with dropouts, return course (title), lesson_no, lesson (lesson title) and dropouts (learners who dropped out at that lesson) for the lesson with the most dropouts, keeping ties. Sort by course, then lesson_no.
Starting Postgres in your browser…
Checking your access…