Top Country by Revenue
Return country, total_revenue, and subscriber_count for the single country with the highest total subscription revenue.
- Window functions
- Joins
- Subqueries
- Aggregation
- Filtering
Challenge brief
Understand the request
Finance — Market Prioritisation Finance ranks markets by subscription revenue every quarter. This run identifies the current top market.
Rank countries by subscription revenue and use country code as the deterministic tie-break.
Return
- country
- total_revenue
- subscriber_count
Constraints
- Calculate subscription revenue and distinct subscribers per country
- Return exactly one highest-revenue country
- Break equal revenue by country code
Data you will use
Review the relevant tables before deciding how to join, filter, or aggregate them.
users
user_idINTEGERcountryVARCHAR(50)
subscriptions
user_idINTEGERmonthly_feeINTEGER
Hints, when you need them
Open one clue at a time so you still do the reasoning.
Hint 1
Aggregate subscription revenue and subscriber count at country grain.
Hint 2
Rank countries by revenue with country code as the tie-break.
Hint 3
Retain only the leading country.
Verified SQL answer
Attempt the problem first, then compare structure and reasoning—not just syntax.
Reveal solution and explanation
WITH country_revenue AS (SELECT u.country, SUM(s.monthly_fee) AS total_revenue, COUNT(DISTINCT s.user_id) AS subscriber_count FROM users u INNER JOIN subscriptions s ON u.user_id = s.user_id GROUP BY u.country), ranked AS (SELECT country, total_revenue, subscriber_count, ROW_NUMBER() OVER (ORDER BY total_revenue DESC, country) AS rn FROM country_revenue) SELECT country, total_revenue, subscriber_count FROM ranked WHERE rn = 1Why this works
The first CTE aggregates revenue and subscriber count per country. The second CTE ranks countries by revenue using ROW_NUMBER(). Filtering to rn = 1 returns only the top market safely even in case of ties.
Success check
Returns the complete deterministic result for top country by revenue
Expected result
Use this output to verify values, aliases, ordering, and row count.
| country | total_revenue | subscriber_count |
|---|---|---|
| US | 55 | 3 |
Learn the concepts behind this answer
Strengthen your understanding with these targeted learning topics:
Continue practicing
Explore related company challenges
Google
Independent Google-style search, advertising, user-engagement, and video-product SQL practice.
Meta
Independent Meta-style social-product, engagement, content, community, messaging, and advertising SQL practice.
Airbnb
Independent Airbnb-style marketplace, booking, listing, payment, review, and guest analytics SQL practice.
Return to the complete interview preparation experience.