Fastest Mastery Segment SQL

HardSQL · PostgreSQL

Problem

Khan Academy wants to identify learners who reach skill mastery most efficiently. Write a PostgreSQL query that finds the fastest 5% of learners based on the elapsed time between their first practice attempt and their first recorded mastery.

Requirements

Calculate each learner's elapsed time to mastery for every skill with a qualifying mastery event.
Summarize each learner using their average hours to mastery and count of mastered skills.
Exclude learners without a qualifying mastery event. Treat mastery as an event where mastery_level = 'mastered'.
Rank eligible learners by average mastery time in ascending order and return the fastest 5%. Round average hours to two decimal places.
Return learner_id, learner_name, mastered_skills, avg_hours_to_mastery, rank_position, and eligible_learner_count.

Schema

learners

Column	Type	Description
learner_idPK	INT	Unique Khan Academy learner identifier
learner_name	VARCHAR(100)	Display name of the learner

practice_attempts

Column	Type	Description
attempt_idPK	INT	Unique practice attempt identifier
learner_id	INT	Learner who made the attempt
skill_name	VARCHAR(100)	Khan Academy skill practiced
attempted_at	TIMESTAMP	Timestamp when the practice attempt occurred

skill_mastery_events

Column	Type	Description
mastery_event_idPK	INT	Unique mastery event identifier
learner_id	INT	Learner associated with the mastery event
skill_name	VARCHAR(100)	Khan Academy skill whose mastery status changed
mastery_level	VARCHAR(30)	Recorded status, such as in_progress or mastered
mastered_at	TIMESTAMP	Timestamp when the status was recorded

Tableslearnerspractice_attemptsskill_mastery_events