SQL Email Open Rate Drop Analysis | Dataford Interview Questions

Write a SQL query to find the email open rate by campaign and identify campaigns with the largest drop over time.

Use the provided campaign, email send, and email event data. Treat a send as opened when it has at least one matching event with event_type = 'open'. Report campaigns with at least one decline between consecutive months.

Output

One row per campaign and its largest month-over-month decline
Columns: campaign_id, campaign_name, from_month, to_month, prior_open_rate, current_open_rate, drop_percentage
Include only positive declines, ordered by drop_percentage descending, then campaign_id ascending

Schema

campaigns

Column	Type	Description
campaign_idPK	INT	Unique campaign identifier
campaign_name	VARCHAR(100)	Campaign name

email_sends

Column	Type	Description
send_idPK	INT	Unique email send identifier
campaign_id	INT	Campaign associated with the send
recipient_email	VARCHAR(255)	Recipient email address
sent_at	TIMESTAMP	Timestamp when the email was sent

email_events

Column	Type	Description
event_idPK	INT	Unique email event identifier
send_id	INT	Email send associated with the event
event_type	VARCHAR(30)	Event type, such as open or click
event_at	TIMESTAMP	Timestamp when the event occurred

Your question is SQL Email Open Rate Drop Analysis. Start with the requirements and the three 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