Watch Time by Country
Return user_id, country, and total_minutes for each user who has watched content, ordered by total_minutes descending.
- Joins
- Aggregation
- Sorting
Challenge brief
Understand the request
International Analytics The international team wants to understand viewing intensity by geography, starting with a per-user breakdown that includes country.
Show total watch minutes per user alongside their country.
Return
- user_id
- country
- total_minutes
Constraints
- Return identified users with watch history
- Sum watch minutes per user
- Order by total minutes descending, then user ID
Data you will use
Review the relevant tables before deciding how to join, filter, or aggregate them.
users
user_idINTEGERcountryVARCHAR(50)
watch_history
user_idINTEGERwatch_minutesINTEGER
Hints, when you need them
Open one clue at a time so you still do the reasoning.
Hint 1
Country comes from users and minutes from watch history.
Hint 2
Connect identified viewers to watch facts before aggregation.
Hint 3
Sum at user grain and stabilize equal totals with user ID.
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, SUM(w.watch_minutes) AS total_minutes FROM users u INNER JOIN watch_history w ON u.user_id = w.user_id GROUP BY u.user_id, u.country ORDER BY total_minutes DESC, u.user_idWhy this works
country lives in users; watch_minutes lives in watch_history. JOIN on user_id combines them. GROUP BY user_id, country and SUM gives the per-user total. Users 9 and 10 are excluded because they have no watch history (INNER JOIN).
Success check
Returns the complete deterministic result for watch time by country
Expected result
Use this output to verify values, aliases, ordering, and row count.
| user_id | country | total_minutes |
|---|---|---|
| 1 | US | 800 |
| 2 | IN | 650 |
| 3 | UK | 600 |
| 6 | IN | 550 |
| 8 | DE | 480 |
| 5 | US | 400 |
| 4 | CA | 350 |
| 7 | US | 200 |
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.