SQL Window Functions for Trends | Dataford Interview Questions

How would you use SQL window functions to calculate rolling averages or identify trends in user behavior?

Use the provided user and activity data to calculate each active user's rolling average from the prior three activity days and classify the current activity relative to that average.

Output

One row per activity record for active users only.
Return user_id, user_name, activity_date, activity_count, rolling_avg, and trend_label.
Order by user_id, activity_date, and activity_id; label rows without sufficient history or with a null activity count as insufficient_history.

Schema

users

Column	Type	Description
user_idPK	INT	Unique user identifier
user_name	VARCHAR(100)	User display name
is_active	BOOLEAN	Whether the user is currently active

user_activity

Column	Type	Description
activity_idPK	INT	Unique activity record identifier
user_id	INT	Referenced user
activity_date	DATE	Date of the activity measurement
activity_count	INT	Number of recorded user activities

Your question is SQL Window Functions for Trends. 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