Top Active Users by Usage
For the top 10 users by total usage, show their country, number of distinct products subscribed to, and total usage minutes.
- Joins
- Subqueries
- Aggregation
- Sorting
- Top-N
Challenge brief
Understand the request
Enterprise Analytics is building an executive leaderboard of the most active Microsoft customers ranked by total product usage.
Join users, subscriptions, and usage_logs to build a per-user activity summary, ordered by usage descending.
Return
- user_id
- country
- products (distinct subscribed product count)
- total_usage
Constraints
- Count distinct subscribed products independently from usage totals
- Do not multiply usage when a user has multiple subscription records
- Return at most 10 users with deterministic tie handling
Data you will use
Review the relevant tables before deciding how to join, filter, or aggregate them.
subscriptions
subscription_idINTEGERuser_idINTEGERproduct_idINTEGERstart_dateDATEend_dateDATEpriceINTEGER
usage_logs
log_idINTEGERuser_idINTEGERproduct_idINTEGERusage_dateDATEusage_minutesINTEGER
users
user_idINTEGERcountryTEXTsignup_dateDATE
Hints, when you need them
Open one clue at a time so you still do the reasoning.
Hint 1
This requires three tables: users (for country), subscriptions (for product count), usage_logs (for usage minutes). JOIN all three on user_id. COUNT(DISTINCT product_id) from subscriptions, SUM(usage_minutes) from usage_logs.
Hint 2
FROM users u JOIN subscriptions s ON u.user_id = s.user_id JOIN usage_logs ul ON u.user_id = ul.user_id. GROUP BY u.user_id, u.country. COUNT(DISTINCT s.product_id), SUM(ul.usage_minutes). ORDER BY total_usage DESC LIMIT 10.
Hint 3
Build question 21 from the required result grain: choose the driving table, add only the joins and filters needed for that grain, then apply aggregation and deterministic ordering.
Verified SQL answer
Attempt the problem first, then compare structure and reasoning—not just syntax.
Reveal solution and explanation
WITH subscribed_products AS (SELECT user_id, COUNT(DISTINCT product_id) AS products FROM subscriptions GROUP BY user_id), usage_totals AS (SELECT user_id, SUM(usage_minutes) AS total_usage FROM usage_logs GROUP BY user_id) SELECT u.user_id, u.country, sp.products, ut.total_usage FROM users u INNER JOIN subscribed_products sp ON u.user_id = sp.user_id INNER JOIN usage_totals ut ON u.user_id = ut.user_id ORDER BY ut.total_usage DESC, u.user_id ASC LIMIT 10;Why this works
Aggregate subscription breadth and usage independently at user grain before joining them. This prevents multiple subscription records from multiplying usage minutes.
Success check
10 users — user 8 (US, 1 product, 580 min) leads; user 9 (DE, 1 product, 100 min) is 10th
Expected result
Use this output to verify values, aliases, ordering, and row count.
| user_id | country | products | total_usage |
|---|---|---|---|
| 8 | US | 1 | 580 |
| 12 | CA | 1 | 500 |
| 1 | US | 2 | 210 |
| 7 | IN | 1 | 210 |
| 3 | US | 1 | 200 |
| 5 | CA | 1 | 180 |
| 4 | UK | 1 | 150 |
| 11 | US | 1 | 130 |
| 6 | US | 1 | 110 |
| 9 | DE | 1 | 100 |
Learn the concepts behind this answer
Strengthen your understanding with these targeted learning topics:
Continue practicing
Explore related company challenges
Apple
Independent Apple-style product, retail, services, support, workforce, and device-usage SQL practice.
Google
Independent Google-style search, advertising, user-engagement, and video-product SQL practice.
Amazon
Independent Amazon-style e-commerce, warehouse, inventory, and customer analytics SQL practice.
Return to the complete interview preparation experience.