Revenue by User
What is the total ad revenue each user has generated through ad clicks?
- Aggregation
- Numeric functions
- Sorting
Challenge brief
Understand the request
Ads Revenue is building a user-level revenue attribution model and needs the total ad click revenue generated per user.
Return user_id, total_revenue in the declared deterministic order.
Return
- user_id
- total_revenue
Constraints
- Return users with at least one ad click
- Round revenue to two decimals
- Order by revenue descending, then 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
Revenue is recorded on ad-click facts.
Hint 2
Sum revenue independently for each user and round for presentation.
Hint 3
Order by the metric and use user ID for ties.
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 total_revenue FROM ad_clicks GROUP BY user_id ORDER BY total_revenue DESC, user_idWhy this works
20 users have at least one ad click. Users 21, 22, 23 have no ad_clicks rows and do not appear. User 2 earned $11.25 across 3 clicks. User 1 earned exactly $10 across 4 clicks (each worth $2.50).
Success check
Returns the complete deterministic result for revenue by user
Expected result
Use this output to verify values, aliases, ordering, and row count.
| user_id | total_revenue |
|---|---|
| 2 | 11.25 |
| 1 | 10 |
| 4 | 10 |
| 5 | 8.4 |
| 7 | 7 |
| 10 | 6.4 |
| 16 | 5 |
| 18 | 4.6 |
| 9 | 4.5 |
| 13 | 4.1 |
Previewing 10 of 21 expected rows. Run the query in the editor to inspect the full result.
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.