Top Revenue Users with Search Activity
Which 10 searching users generated the most ad revenue?
- Joins
- Subqueries
- Aggregation
- Numeric functions
- Sorting
Challenge brief
Understand the request
Ads + Search Integration needs a user-level view of search activity and ad revenue.
Return user_id, country, search_days, total_revenue in the declared deterministic order.
Return
- user_id
- country
- search_days (distinct search dates)
- total_revenue
Constraints
- Calculate search days and ad revenue independently per user
- Include users present in both activity sets
- Break revenue ties by user ID
Data you will use
Review the relevant tables before deciding how to join, filter, or aggregate them.
search_queries
query_idINTEGERuser_idINTEGERquery_textVARCHAR(200)search_dateDATE
ad_clicks
click_idINTEGERuser_idINTEGERad_idINTEGERclick_dateDATErevenueREAL
users
user_idINTEGERcountryVARCHAR(50)device_typeVARCHAR(20)signup_dateDATE
Hints, when you need them
Open one clue at a time so you still do the reasoning.
Hint 1
Search activity and ad revenue are separate one-to-many fact sources.
Hint 2
Reduce each source to one row per user before combining them.
Hint 3
Join those user-grain summaries, rank by true revenue, and take ten rows.
Verified SQL answer
Attempt the problem first, then compare structure and reasoning—not just syntax.
Reveal solution and explanation
WITH search_activity AS (SELECT user_id, COUNT(DISTINCT search_date) AS search_days FROM search_queries GROUP BY user_id), ad_revenue AS (SELECT user_id, ROUND(SUM(revenue), 2) AS total_revenue FROM ad_clicks GROUP BY user_id) SELECT u.user_id, u.country, s.search_days, a.total_revenue FROM users u INNER JOIN search_activity s ON u.user_id = s.user_id INNER JOIN ad_revenue a ON u.user_id = a.user_id ORDER BY a.total_revenue DESC, u.user_id LIMIT 10Why this works
Search days and ad revenue are reduced to one row per user before they are joined. This prevents the many-to-many fanout that would multiply each click by every search row for the same user.
Success check
10 users ranked by true user-level revenue without fact fanout
Expected result
Use this output to verify values, aliases, ordering, and row count.
| user_id | country | search_days | total_revenue |
|---|---|---|---|
| 2 | USA | 4 | 11.25 |
| 1 | USA | 9 | 10 |
| 4 | India | 4 | 10 |
| 5 | Canada | 2 | 8.4 |
| 7 | Germany | 1 | 7 |
| 10 | Canada | 1 | 6.4 |
| 16 | India | 1 | 5 |
| 18 | Germany | 1 | 4.6 |
| 9 | UK | 1 | 4.5 |
| 13 | Japan | 1 | 4.1 |
Learn the concepts behind this answer
Strengthen your understanding with these targeted learning topics:
Continue practicing
Explore related company challenges
Meta
Independent Meta-style social-product, engagement, content, community, messaging, and advertising SQL practice.
Microsoft
Independent Microsoft-style cloud, productivity, subscription, usage, support, and customer analytics SQL practice.
Netflix
Independent Netflix-style streaming, subscription, catalog, ratings, and engagement SQL practice.
Return to the complete interview preparation experience.