Rolling 30-Day Behavior Patterns | Dataford Interview Questions

Write a SQL query to identify recurring user behavior patterns over a rolling 30-day window for Kake. Treat a behavior as recurring when a user performs it on at least three distinct dates within the 30-day period ending on an activity date. Exclude incomplete activity records and users without a matching account.

Output

One row per qualifying user, behavior, and window end date
Columns: username, behavior_type, window_end_date, active_days_30d, and event_count_30d
Include only windows with at least three active days, ordered by username, behavior_type, and window_end_date ascending

Schema

users

Column	Type	Description
user_idPK	INT	Unique Kake user identifier
username	VARCHAR(100)	Display name for the user

user_activity

Column	Type	Description
activity_idPK	INT	Unique activity record identifier
user_id	INT	User associated with the activity
behavior_type	VARCHAR(50)	Observed user behavior
occurred_at	TIMESTAMP	Timestamp when the behavior occurred

Your question is Rolling 30-Day Behavior Patterns. 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