Rolling Average with Window Functions | Dataford Interview Questions

Your question is Rolling Average with Window Functions. Start with the requirements and the two tables on the right.

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

Problem

Impact Recruitment Group wants to monitor consultant placement activity over time. Write a PostgreSQL query that calculates a three-day rolling average of completed placements for each active consultant from January 1 through January 5, 2025.

Requirements

Include every active consultant for every date in the requested range, including dates with zero completed placements.
Count only placements whose placement_status is completed.
Use a window function partitioned by consultant and ordered by date. The rolling window must include the current date and the two preceding dates.
Return results ordered by consultant ID and activity date, with the rolling average rounded to two decimal places.

Schema

consultants

Column	Type	Description
consultant_idPK	INT	Unique consultant identifier
consultant_name	VARCHAR(100)	Consultant's full name
team_name	VARCHAR(100)	Recruitment team assignment
employment_status	VARCHAR(20)	Consultant employment status

placements

Column	Type	Description
placement_idPK	INT	Unique placement identifier
consultant_id	INT	Consultant responsible for the placement
placement_date	DATE	Date of the placement activity
placement_status	VARCHAR(20)	Placement status, such as completed or pending