Calculate each streamer's month-over-month follower growth. Show streamer_name, month, follower_count, and growth (difference from previous month). Use window functions to partition by streamer.
This growth analytics problem is essential for Twitch's creator economy. Understanding which streamers are growing helps the platform allocate resources, recommend content, and identify trending creators. This question tests your ability to use window functions with PARTITION BY, work with date functions to extract months, and calculate period-over-period changes—advanced skills for product analytics roles.
Advanced concepts: LAG() window function with PARTITION BY for per-streamer comparisons, STRFTIME() for extracting year-month from dates, ORDER BY within window function to define sequence, and handling NULL for first month (no previous period). Understanding how PARTITION BY creates separate windows for each streamer is crucial.
Twitch's analytics teams use similar queries to: identify fast-growing streamers for partnership programs, calculate creator retention metrics, detect viral content patterns, generate monthly growth reports for creators, power recommendation algorithms with growth signals, and predict which streamers will reach partnership thresholds.
When tackling this Twitch problem, the key is to understand the grain of the result. Are you returning one row per user, or one row per category? Always start by identifying your unique join keys and consider if filtered aggregations (CASE WHEN) are more efficient than multiple subqueries.
Be careful with NULL values in your JOIN conditions or aggregate functions. In interview scenarios, datasets often include edge cases like zero-count categories or duplicate entries that can throw off a simple COUNT(*) if not handled with DISTINCT.
Share your approach, optimized queries, or ask questions. Learning from others is the fastest way to master SQL.