Top 5% Users by Engagement | Dataford Interview Questions

The needs a quarterly engagement report identifying its most active users. For this exercise, treat the last quarter as 2025-04-01 through 2025-06-30, inclusive. Write a PostgreSQL query that calculates engagement scores and returns the top 5% of users.

Requirements

Include every user, including users with no qualifying activity.
Calculate each user's score from events during the quarter using these weights: login = 1, view = 2, like = 5, comment = 8, share = 10, and publish = 15. Treat unknown or NULL event types as zero points.
Rank users by score descending, breaking ties with user_id ascending, and return CEIL(5% of all users) rows.
Return the user ID, email, engagement score, and rank.

Schema

users

Column	Type	Description
user_idPK	INT	Unique user identifier
email	VARCHAR(255)	User email address

engagement_events

Column	Type	Description
event_idPK	INT	Unique engagement event identifier
user_id	INT	References users.user_id
event_type	VARCHAR(30)	Type of engagement event
occurred_at	DATE	Date when the event occurred

Your question is Top 5% Users by Engagement. 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