Top Users Per Feature | Dataford Interview Questions

Write a SQL query to find the top 3 most active users per product feature over the last 30 days.

Use the users, product_features, and feature_events tables. Activity is measured by the number of feature events during the rolling 30-day period ending today.

Output

One row per qualifying user and product feature.
Include feature_id, feature_name, user_id, user_name, activity_count, and activity_rank.
Return no more than three users per feature, ordered by feature_id, then rank, with lower user_id breaking activity ties.

Schema

users

Column	Type	Description
user_idPK	INT	Unique user identifier
user_name	VARCHAR(100)	Displayed user name

product_features

Column	Type	Description
feature_idPK	INT	Unique product feature identifier
feature_name	VARCHAR(100)	Product feature name

feature_events

Column	Type	Description
event_idPK	INT	Unique feature event identifier
user_id	INT	User who generated the event
feature_id	INT	Feature associated with the event
occurred_at	TIMESTAMP	Timestamp when the event occurred

Your question is Top Users Per Feature. 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