Media and Entertainment: Real-Time Content Recommendations

Streaming platforms (video, music, news) need real-time recommendation engines that adapt to user behavior in the current session — not yesterday's batch model. Streaming SQL computes engagement signals, content popularity, and user preferences in real time for immediate personalization.

Content Recommendation Views

-- Real-time content popularity
CREATE MATERIALIZED VIEW trending_content AS
SELECT content_id, content_type, title,
  COUNT(*) FILTER (WHERE ts > NOW()-INTERVAL '1 hour') as views_1h,
  AVG(watch_pct) FILTER (WHERE ts > NOW()-INTERVAL '1 hour') as avg_completion,
  COUNT(*) FILTER (WHERE event='share') as shares_1h
FROM content_events WHERE ts > NOW()-INTERVAL '1 hour'
GROUP BY content_id, content_type, title
ORDER BY views_1h DESC;

-- User session preferences
CREATE MATERIALIZED VIEW session_preferences AS
SELECT user_id, session_id,
  array_agg(DISTINCT genre) as genres_viewed,
  AVG(watch_pct) as avg_engagement,
  COUNT(DISTINCT content_id) as items_consumed
FROM content_events WHERE ts > NOW()-INTERVAL '30 minutes'
GROUP BY user_id, session_id;

Frequently Asked Questions

How does real-time recommendation differ from collaborative filtering?

Collaborative filtering uses historical patterns (users who watched X also watched Y). Real-time recommendation adds current session signals — what the user is watching RIGHT NOW — for immediate personalization.

Can streaming SQL replace a recommendation engine?

For trending content and session-aware signals, streaming SQL is sufficient. For deep learning-based recommendations (neural collaborative filtering), use streaming SQL to compute real-time features that feed into the ML model.