7-Day Rolling Active Learners | Dataford Interview Questions

Khan Academy wants to monitor learner engagement across grade levels. Write a PostgreSQL query that calculates the daily number of distinct active learners for each grade level and a seven-day rolling average of that count from January 1 through January 10, 2025.

Requirements

Generate one output row for every date and grade level, including dates with no activity.
Count each learner at most once per grade level per day.
Calculate the rolling average using the current day and the six preceding calendar days. Round the result to two decimal places.
Return rows ordered by grade_level and activity_date.

Schema

learners

Column	Type	Description
learner_idPK	INTEGER	Unique learner identifier
learner_name	VARCHAR(100)	Learner display name
grade_level	VARCHAR(20)	Learner's grade level

learner_activity

Column	Type	Description
activity_idPK	INTEGER	Unique activity event identifier
learner_id	INTEGER	Learner associated with the activity event
activity_date	DATE	Date of the activity
activity_type	VARCHAR(50)	Type of Khan Academy activity

Your question is 7-Day Rolling Active Learners. Start with the requirements and the two tables 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