Rolling 7-Day Messages Per Account | Dataford Interview Questions

Write a query to find the rolling 7-day average of messages sent per Twilio customer account, accounting for days with zero activity.

Use the provided customer account and message data for January 1 through January 10, 2024. Return every account for every date in that period, including accounts and dates with no messages.

Output

One row per account and calendar date
Columns: account_id, activity_date, message_count, and rolling_7_day_average
The average should include available dates up to seven calendar days, with zero-message dates included
Order by account_id, then activity_date

Output details

Round rolling_7_day_average to exactly 2 decimal places.
Define the seven-day window as the current date plus the six preceding calendar dates.
Sort by account_id ascending, then activity_date ascending.

Schema

customer_accounts

Column	Type	Description
account_idPK	INT	Unique Twilio customer account identifier
account_name	VARCHAR(100)	Customer account name
created_at	DATE	Date the account was created

messages

Column	Type	Description
message_idPK	INT	Unique message identifier
account_id	INT	Customer account associated with the message
sent_at	TIMESTAMP	Timestamp when the message was sent
message_type	VARCHAR(30)	Message channel or type

Your question is Rolling 7-Day Messages Per Account. 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