SQLhardWindow functions~22m

A streaming platform wants to identify episodes that are experiencing...

Problem

A streaming platform wants to identify episodes that are experiencing significant 'viewership momentum' - episodes where the daily watch count has increased for at least 3 consecutive days, and by at least 20% each day compared to the previous day. For each such episode, calculate the total momentum period length (consecutive days of growth), the cumulative viewership during the momentum period, and the peak daily growth rate achieved. Only include episodes that had their momentum period end within the last 30 days from 2024-03-15.

Examples

Example 1

Constraints

›Return episode_id, title, momentum_days, total_views, peak_growth_rate
›Only episodes with 3+ consecutive days of 20%+ growth
›Momentum period must end within 30 days of 2024-03-15
›Order by peak_growth_rate DESC, then episode_id ASC

Reference solution

Reference solution available after you attempt the question.

Ready to solve it?

Start a session on Mockbit #63. Write your SQL, run it against real data, and get graded on correctness, efficiency, query structure, and business understanding.