Top 10% Users Query | Dataford Interview Questions

Given a table of user interactions, write a query to identify the top 10% of users based on their active search queries over the last 30 days.

Count only interactions that are searches, are marked active, have a non-null query, and occurred within the rolling 30-day period. Include users with at least one qualifying query.

Output

One row for each selected user.
Columns: user_id, active_search_queries, and decile.
Return the highest-activity decile, ordered by active_search_queries descending and user_id ascending to break ties.

Schema

user_interactions

Column	Type	Description
interaction_idPK	INT	Unique interaction identifier
user_id	INT	Identifier of the user who generated the interaction
interaction_type	VARCHAR(50)	Type of interaction, such as search or message
is_active	BOOLEAN	Whether the interaction is active
query_text	TEXT	Search text entered by the user
occurred_at	TIMESTAMP	Timestamp when the interaction occurred

Your question is Top 10% Users Query. Start with the requirements and the one table 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