Rolling Three-Month Error Average | Dataford Interview Questions

Precisely Data Integrity Suite monitoring needs a monthly view of ingestion errors for every client, including clients with no errors in a month. Write a PostgreSQL query for January through April 2025.

Requirements

Aggregate ingestion error events by client and calendar month.
Return one row for every client in every reporting month, using zero when no error events occurred.
Calculate a rolling three-month average of each client’s monthly error count. For the first two months, average only the months available in the reporting period.
Return results ordered by client_id and month_start.

Schema

clients

Column	Type	Description
client_idPK	INTEGER	Unique client identifier
client_name	VARCHAR(100)	Client name
active_from	DATE	Date the client began using the platform

ingestion_errors

Column	Type	Description
error_idPK	BIGINT	Unique ingestion error identifier
client_id	INTEGER	Client associated with the error
occurred_at	TIMESTAMP	Timestamp when the error occurred
error_code	VARCHAR(30)	Ingestion error classification

Your question is Rolling Three-Month Error Average. 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