Top Lessons by Completion Rate | Dataford Interview Questions

How would you use SQL to identify the top 10 lessons by completion rate in the last 30 days?

Use the lesson_progress table. Treat each row as one learner's lesson assignment, and calculate completion rate as completed assignments divided by all assignments in the period.

Output

One row per lesson, with lesson_name, completion_rate, and assignment_count.
Include only activity from the last 30 days and exclude records without a lesson name.
Sort by completion rate descending, then lesson name ascending, and return at most 10 rows.

Schema

lesson_progress

Column	Type	Description
progress_idPK	INT	Unique progress record identifier
learner_id	INT	Learner assigned to the lesson
lesson_name	VARCHAR(150)	Name of the lesson
activity_date	DATE	Date of the learner's lesson activity
completed	BOOLEAN	Whether the learner completed the lesson

Your question is Top Lessons by Completion Rate. Start with the requirements and the one table in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem