Top Revenue Users
Which 5 users have generated the most total ad revenue?
- Aggregation
- Numeric functions
- Sorting
- Top-N
Challenge brief
Understand the request
Ads Revenue wants to identify the top monetising users to understand what drives premium ad engagement.
Return user_id, revenue in the declared deterministic order.
Return
- user_id
- revenue (total)
Constraints
- Return the five users with the largest total ad revenue
- Break revenue ties by user ID
Data you will use
Review the relevant tables before deciding how to join, filter, or aggregate them.
ad_clicks
click_idINTEGERuser_idINTEGERad_idINTEGERclick_dateDATErevenueREAL
Hints, when you need them
Open one clue at a time so you still do the reasoning.
Hint 1
Build user-level revenue totals before selecting leaders.
Hint 2
Order totals from largest to smallest.
Hint 3
Use the user key as a stable tie-break before taking five rows.
Verified SQL answer
Attempt the problem first, then compare structure and reasoning—not just syntax.
Reveal solution and explanation
SELECT user_id, ROUND(SUM(revenue), 2) AS revenue FROM ad_clicks GROUP BY user_id ORDER BY revenue DESC, user_id LIMIT 5Why this works
SUM + GROUP BY + ORDER BY DESC + LIMIT 5 is the classic top-N pattern. Users 4 and 1 both have total revenue of $10 — the order between tied rows depends on the engine's internal sort. The expected output shows user 4 before user 1.
Success check
Returns the complete deterministic result for top revenue users
Expected result
Use this output to verify values, aliases, ordering, and row count.
| user_id | revenue |
|---|---|
| 2 | 11.25 |
| 1 | 10 |
| 4 | 10 |
| 5 | 8.4 |
| 7 | 7 |
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.