Revenue Contribution %
What percentage of total ad revenue does each user account for?
- Subqueries
- Aggregation
- Numeric functions
- NULL handling
- Sorting
Challenge brief
Understand the request
Ads Finance is preparing the revenue attribution report and needs each user's share of total platform ad revenue as a percentage.
Return user_id, revenue_pct in the declared deterministic order.
Return
- user_id
- revenue_pct (rounded to 2 decimals)
Constraints
- Calculate each user share from user revenue and the platform revenue total
- Return a defined result without division errors when total revenue is zero
- Order by percentage 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
Use each user revenue sum as the numerator and the full revenue sum as the denominator.
Hint 2
Protect the platform total before division and use decimal arithmetic.
Hint 3
Round the percentage and apply both ordering keys.
Verified SQL answer
Attempt the problem first, then compare structure and reasoning—not just syntax.
Reveal solution and explanation
SELECT user_id, ROUND(100.0 * SUM(revenue) / NULLIF((SELECT SUM(revenue) FROM ad_clicks), 0), 2) AS revenue_pct FROM ad_clicks GROUP BY user_id ORDER BY revenue_pct DESC, user_idWhy this works
The scalar subquery (SELECT SUM(revenue) FROM ad_clicks) runs once, returns the total ($98.7), and acts as a constant divisor for every row. 100.0 (not 100) forces floating-point division. All 20 percentages sum to 100%.
Success check
Returns the complete deterministic result for revenue contribution %
Expected result
Use this output to verify values, aliases, ordering, and row count.
| user_id | revenue_pct |
|---|---|
| 2 | 11.4 |
| 1 | 10.14 |
| 4 | 10.14 |
| 5 | 8.51 |
| 7 | 7.1 |
| 10 | 6.49 |
| 16 | 5.07 |
| 18 | 4.66 |
| 9 | 4.56 |
| 13 | 4.16 |
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.