Detect Anomalous Viewer Drop-offs | Dataford Interview Questions

Your question is Detect Anomalous Viewer Drop-offs. 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

Twitch analytics needs to identify channels whose daily active viewers have dropped sharply compared with their recent historical performance. Write a PostgreSQL query that compares each channel's latest viewer count with the average from earlier dates having the same weekday.

Requirements

Identify the latest metric date in daily_channel_metrics.
Calculate each channel's historical baseline using matching weekdays from the preceding 28 days, excluding the latest date.
Return channels with at least two historical observations and a current viewer count at least 25% below a positive baseline.
Include the channel name, current viewers, baseline viewers, and percentage drop, ordered by the largest drop first.

Schema

twitch_channels

Column	Type	Description
channel_idPK	INTEGER	Unique Twitch channel identifier
channel_name	VARCHAR(100)	Twitch channel name

daily_channel_metrics

Column	Type	Description
channel_idPK	INTEGER	Twitch channel identifier
metric_datePK	DATE	Date of the daily metric
daily_active_viewers	INTEGER	Distinct viewers active on the channel that day