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.
SELECT
streamer_name,
month,
follower_count,
follower_count - LAG(follower_count) OVER (
PARTITION BY streamer_name
ORDER BY month
) AS growth
FROM (
SELECT
streamer_name,
STRFTIME('%Y-%m', stat_date) AS month,
MAX(follower_count) AS follower_count
FROM streamer_stats
GROUP BY streamer_name, month
) monthly_snapshots
ORDER BY streamer_name, monthThe subquery collapses daily snapshots into monthly maximums per streamer using STRFTIME and MAX. The outer query applies LAG() with PARTITION BY streamer_name so each streamer has an independent lag sequence — without PARTITION BY, the lag would bleed across streamers when their months interleave. Growth is the simple difference from the previous month's follower count. MAX is used because follower counts are cumulative and the end-of-month peak best represents that month. The first month per streamer returns NULL growth, which is correct.
WITH monthly AS (
SELECT
streamer_name,
STRFTIME('%Y-%m', stat_date) AS month,
MAX(follower_count) AS follower_count
FROM streamer_stats
GROUP BY streamer_name, month
),
with_growth AS (
SELECT
streamer_name,
month,
follower_count,
LAG(follower_count) OVER (
PARTITION BY streamer_name
ORDER BY month
) AS prev_month_count
FROM monthly
)
SELECT
streamer_name,
month,
follower_count,
follower_count - prev_month_count AS growth
FROM with_growth
ORDER BY streamer_name, monthThe CTE chain separates monthly aggregation from window computation, making each step independently readable and testable. prev_month_count is stored as a named column, making the final growth arithmetic transparent and easy to extend (e.g., adding percentage growth). This version is preferred for production pipelines and code reviews where maintainability is a priority. It also makes debugging easier — SELECT * FROM with_growth reveals both the current and previous follower counts side by side, helping validate correctness before computing the delta.
This is a Hard-difficulty query with two major cost centers: the GROUP BY aggregation with STRFTIME formatting, and the LAG() window function with PARTITION BY. An index on (streamer_name, stat_date) covering follower_count will accelerate the aggregation step. STRFTIME prevents direct index use on stat_date, so for very large tables, storing a pre-computed year_month column and indexing it is a common optimization. The window function requires a sort by (streamer_name, month) — composite index on these columns helps. At Twitch scale (thousands of streamers × years of daily stats = tens of millions of rows), incremental processing via streaming or batch pipelines (e.g., Spark Structured Streaming) is standard — the SQL query would run against a pre-aggregated monthly summary table rather than raw daily stats. MAX(follower_count) per month is correct for cumulative follower counts but would be wrong for event-based metrics like views.
The most critical mistake is omitting PARTITION BY streamer_name in the LAG() window — without it, LAG spans across streamers alphabetically, producing completely wrong growth values when months interleave between streamers. Using SUM instead of MAX for the monthly aggregation is incorrect for follower counts (which are cumulative snapshots, not additive events). Candidates sometimes use MIN or AVG, neither of which represents end-of-month followers accurately. Another pitfall is formatting month as STRFTIME('%Y-%m-%d', ...) instead of '%Y-%m', which prevents correct month-level grouping. Growth for the first month will always be NULL — forgetting to handle or explain this is a common oversight. Ordering only by month without streamer_name produces an unsorted, confusing result.
Follower counts are point-in-time snapshots — each daily row records the total followers a streamer has at that moment, not new followers gained that day. Summing snapshots would produce an inflated, meaningless number. MAX captures the end-of-month peak, which best represents the streamer's follower base for that month. If the data instead recorded daily new followers gained, SUM would be correct — always clarify the metric semantics before aggregating.
Replace the growth calculation with ROUND((follower_count - prev_month_count) * 100.0 / NULLIF(prev_month_count, 0), 2) AS pct_growth. NULLIF prevents division-by-zero when a streamer had 0 followers the previous month. The 100.0 literal forces floating-point division. This percentage metric is more useful for comparing growth across streamers of different sizes — a 10K gain means very different things for a 20K vs. 2M follower streamer.
Add platform to the PARTITION BY clause: PARTITION BY streamer_name, platform. Also include platform in the GROUP BY of the aggregation CTE and in the final SELECT. This ensures LAG computes growth independently per streamer-platform combination, preventing cross-platform lag bleed. It is also a good idea to add platform to the ORDER BY for deterministic output.