Revenue by Plan
Return plan and total_revenue (sum of monthly_fee) per plan, ordered by total_revenue descending.
- Aggregation
- Sorting
Challenge brief
Understand the request
Finance — Revenue Planning Finance is building the monthly revenue model and needs to know how much each plan tier contributes.
Calculate total monthly revenue generated by each subscription plan.
Return
- plan
- total_revenue
Constraints
- Return one row per plan
- Order by revenue descending, then plan
Data you will use
Review the relevant tables before deciding how to join, filter, or aggregate them.
subscriptions
planVARCHAR(20)monthly_feeINTEGER
Hints, when you need them
Open one clue at a time so you still do the reasoning.
Hint 1
Monthly fee is stored on each subscription.
Hint 2
Aggregate fee values at plan grain.
Hint 3
Order by revenue and then by plan.
Verified SQL answer
Attempt the problem first, then compare structure and reasoning—not just syntax.
Reveal solution and explanation
SELECT plan, SUM(monthly_fee) AS total_revenue FROM subscriptions GROUP BY plan ORDER BY total_revenue DESC, planWhy this works
SUM(monthly_fee) grouped by plan gives the total monthly contribution per tier. Premium and Standard both generate $60 total, so a secondary ORDER BY plan breaks the tie deterministically.
Success check
Returns the complete deterministic result for revenue by plan
Expected result
Use this output to verify values, aliases, ordering, and row count.
| plan | total_revenue |
|---|---|
| Premium | 60 |
| Standard | 60 |
| Basic | 24 |
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.