Ad Fill Rate Day-Over-Day Drop | Dataford Interview Questions

Given a table of ad fill_rates, how would you return the records where fill_rate drops by more than 10% compared to the previous day?

Assume fill_rate is stored as a decimal between 0 and 1, and comparisons are made independently for each ad. A prior record must exist for the immediately preceding calendar day.

Output

One row per qualifying ad and date, with ad_id, rate_date, fill_rate, previous_day_fill_rate, and drop_percent.
Include only drops strictly greater than 10%, ordered by ad_id and rate_date ascending.

Schema

ad_fill_rates

Column	Type	Description
rate_idPK	INT	Unique fill rate record identifier
ad_id	VARCHAR(50)	Advertisement identifier
rate_date	DATE	Calendar date of the fill rate
fill_rate	NUMERIC(6,4)	Ad fill rate stored as a decimal between 0 and 1

Your question is Ad Fill Rate Day-Over-Day Drop. 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