User Subscription Details
Return user_id, country, plan, and monthly_fee for every user, ordered by user_id.
- Joins
- Sorting
Challenge brief
Understand the request
Customer Success Customer success needs a unified view of each user's country and plan to prepare for a retention outreach campaign.
Return user_id, country, plan, monthly_fee in the declared deterministic order.
Return
- user_id
- country
- plan
- monthly_fee
Constraints
- Return every registered user, including users without a subscription
- Use NULL plan and fee when no subscription exists
- Order by user ID
Data you will use
Review the relevant tables before deciding how to join, filter, or aggregate them.
users
user_idINTEGERcountryVARCHAR(50)
subscriptions
user_idINTEGERplanVARCHAR(20)monthly_feeINTEGER
Hints, when you need them
Open one clue at a time so you still do the reasoning.
Hint 1
The user directory is the complete population; plan details are optional.
Hint 2
Preserve users while matching subscriptions on user ID.
Hint 3
Project both sources and order by the preserved identifier.
Verified SQL answer
Attempt the problem first, then compare structure and reasoning—not just syntax.
Reveal solution and explanation
SELECT u.user_id, u.country, s.plan, s.monthly_fee FROM users u LEFT JOIN subscriptions s ON u.user_id = s.user_id ORDER BY u.user_idWhy this works
The user directory is the preserved population. An optional subscription match keeps newly registered users visible even when no plan record exists.
Success check
Every registered user appears once; unsubscribed users retain NULL plan details
Expected result
Use this output to verify values, aliases, ordering, and row count.
| user_id | country | plan | monthly_fee |
|---|---|---|---|
| 1 | US | Premium | 20 |
| 2 | IN | Basic | 8 |
| 3 | UK | Standard | 15 |
| 4 | CA | Premium | 20 |
| 5 | US | Standard | 15 |
| 6 | IN | Basic | 8 |
| 7 | US | Premium | 20 |
| 8 | DE | Standard | 15 |
| 9 | BR | Basic | 8 |
| 10 | FR | Standard | 15 |
Previewing 10 of 11 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
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.