Window frames are the precision tools of SQL analytics - they control exactly which rows participate in each calculation. Netflix uses frames for viewing trend analysis: "3-day moving average of watch time" (ROWS 2 PRECEDING), "month-to-date totals" (RANGE UNBOUNDED PRECEDING). Financial firms like Goldman Sachs use frames for risk calculations: "5-day volatility windows" for trading algorithms, "quarter-to-date performance" for reporting. Amazon uses frames for inventory management: "7-day moving average demand" for restocking decisions, "seasonal comparison windows" for pricing. Without proper frame specifications, FIRST_VALUE and LAST_VALUE give wrong results, moving averages include wrong rows, and running totals break. Frames are the difference between correct and incorrect analytics.

Netflix Viewing Analytics - Trend Analysis with Moving Averages

Netflix uses sophisticated frame-based analytics to understand viewing patterns, content performance, and user engagement trends for content recommendation and acquisition decisions.

-- Netflix-style viewing analytics with multiple frame specifications

SELECT

viewing_date,

content_id,

content_genre,

daily_watch_hours,

unique_viewers,

-- Short-term trend (3-day moving average for responsiveness)

ROUND(AVG(daily_watch_hours) OVER (

PARTITION BY content_id

ORDER BY viewing_date

ROWS BETWEEN 2 PRECEDING AND CURRENT ROW

), 2) as trend_3day,

-- Medium-term trend (7-day moving average for stability)

ROUND(AVG(daily_watch_hours) OVER (

PARTITION BY content_id

ORDER BY viewing_date

ROWS BETWEEN 6 PRECEDING AND CURRENT ROW

), 2) as trend_7day,

-- Long-term baseline (30-day moving average)

ROUND(AVG(daily_watch_hours) OVER (

PARTITION BY content_id

ORDER BY viewing_date

ROWS BETWEEN 29 PRECEDING AND CURRENT ROW

), 2) as baseline_30day,

-- Volatility measurement (7-day rolling standard deviation)

ROUND(STDDEV(daily_watch_hours) OVER (

PARTITION BY content_id

ORDER BY viewing_date

ROWS BETWEEN 6 PRECEDING AND CURRENT ROW

), 2) as volatility_7day,

-- Peak performance comparison

daily_watch_hours / NULLIF(MAX(daily_watch_hours) OVER (

PARTITION BY content_id

ORDER BY viewing_date

ROWS BETWEEN 90 PRECEDING AND CURRENT ROW

), 0) * 100 as pct_of_90day_peak,