User Engagement Tiers
Return user_id, movies_watched, and engagement_tier ('Low', 'Medium', or 'High') for all watching users using NTILE(3) over movies watched. Order by movies_watched descending, then user_id ascending.
- Window functions
- Subqueries
- Aggregation
- CASE expressions
- Filtering
Challenge brief
Understand the request
Lifecycle Segmentation The growth team segments users into engagement tiers to assign different lifecycle marketing tracks.
Segment users into Low, Medium, and High engagement tiers based on movies watched.
Return
- user_id
- movies_watched
- engagement_tier
Constraints
- Count distinct watched movies for each identified viewer
- Split viewers into three equally sized tiers ordered by movie count and user ID
- Map the three buckets to Low, Medium, and High
- Order by movie count descending, then user ID
Data you will use
Review the relevant tables before deciding how to join, filter, or aggregate them.
watch_history
user_idINTEGERmovie_idINTEGER
Hints, when you need them
Open one clue at a time so you still do the reasoning.
Hint 1
Count distinct movies for each identified viewer.
Hint 2
Apply the three-bucket distribution with a deterministic viewer tie-break.
Hint 3
Translate bucket numbers to labels and apply the final order.
Verified SQL answer
Attempt the problem first, then compare structure and reasoning—not just syntax.
Reveal solution and explanation
WITH watch_counts AS (SELECT user_id, COUNT(DISTINCT movie_id) AS movies_watched FROM watch_history WHERE user_id IS NOT NULL GROUP BY user_id), percentiles AS (SELECT user_id, movies_watched, NTILE(3) OVER (ORDER BY movies_watched, user_id) AS bucket FROM watch_counts) SELECT user_id, movies_watched, CASE WHEN bucket = 1 THEN 'Low' WHEN bucket = 2 THEN 'Medium' ELSE 'High' END AS engagement_tier FROM percentiles ORDER BY movies_watched DESC, user_idWhy this works
The first CTE counts distinct movies per user. The second applies NTILE(3) to divide users into three equal-sized buckets ordered by activity. CASE maps bucket numbers to readable tier labels.
Success check
Returns the complete deterministic result for user engagement tiers
Expected result
Use this output to verify values, aliases, ordering, and row count.
| user_id | movies_watched | engagement_tier |
|---|---|---|
| 1 | 2 | High |
| 2 | 2 | High |
| 3 | 1 | Low |
| 4 | 1 | Low |
| 5 | 1 | Low |
| 6 | 1 | Medium |
| 7 | 1 | Medium |
| 8 | 1 | Medium |
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.