SQL Consecutive Anomalous Days | Dataford Interview Questions

Write a SQL query using SQL window functions to identify consecutive days of anomalous email sending behavior for a given user.

Use the daily email activity records for user_id = 101. Treat rows where is_anomalous is true as anomalous, and return only streaks containing at least two consecutive calendar days.

Output

One row per anomalous streak
Columns: user_id, streak_start, streak_end, and anomalous_days
Order by streak_start ascending

Schema

email_sending_daily

Column	Type	Description
record_idPK	INT	Unique daily activity record identifier
user_id	INT	Identifier of the email-sending user
activity_date	DATE	Calendar date of the user's email activity
sent_count	INT	Number of emails sent on the date
is_anomalous	BOOLEAN	Whether the daily sending behavior was classified as anomalous

Your question is SQL Consecutive Anomalous Days. 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