Rank Users by Weekly Activity | Dataford Interview Questions

Write a SQL query using window functions to rank users by weekly activity within each cohort.

Use the users and user_activity tables. Include only users assigned to a cohort and activity records with a matching user.

Output

One row per user and activity week, with cohort_week, activity_week, user_id, weekly_activity, and activity_rank.
Aggregate each user's activity counts by week. Ties share the same rank.
Order by cohort_week, activity_week, activity_rank, and user_id.

users

Column	Type	Description
user_idPK	INTEGER	Unique user identifier
user_name	VARCHAR(100)	User display name
cohort_week	DATE	Monday date identifying the user's cohort week

user_activity

Column	Type	Description
activity_idPK	INTEGER	Unique activity record identifier
user_id	INTEGER	User associated with the activity record
activity_at	TIMESTAMP	Timestamp when activity occurred
activity_count	INTEGER	Number of activity units recorded

Your question is Rank Users by Weekly Activity. 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.