Día 3 (tarde) -- Modelo analítico de contenido

1-- Content Performance Score: evalúa cada título por engagement real
2
3WITH content_stats AS (
4    SELECT
5        c.content_id,
6        c.title,
7        c.content_type,
8        c.genre,
9        c.duration_seconds,
10        c.duration_seconds / 60 AS duration_minutes,
11        c.rating_imdb,
12        c.license_cost_eur,
13        COUNT(p.playback_id) AS total_plays,
14        COUNT(DISTINCT p.user_id) AS unique_viewers,
15        AVG(p.watch_seconds) AS avg_watch_seconds,
16        AVG(p.watch_seconds * 1.0 / NULLIF(c.duration_seconds, 0)) AS avg_completion,
17        SUM(CASE WHEN p.completed THEN 1 ELSE 0 END) * 100.0 / COUNT(*) AS completion_pct,
18        SUM(p.watch_seconds) / 3600.0 AS total_watch_hours
19    FROM content c
20    LEFT JOIN playbacks p ON c.content_id = p.content_id
21    WHERE p.started_at >= CURRENT_DATE - INTERVAL '90 days'
22    GROUP BY c.content_id, c.title, c.content_type, c.genre,
23             c.duration_seconds, c.rating_imdb, c.license_cost_eur
24),
25with_scores AS (
26    SELECT
27        *,
28        -- Reach score: qué porcentaje del MAU lo vio (normalizado)
29        unique_viewers * 1.0 / (SELECT COUNT(DISTINCT user_id) FROM playbacks
30            WHERE started_at >= CURRENT_DATE - INTERVAL '90 days') AS reach_ratio,
31        -- ROI: horas de watch time por euro de licencia
32        CASE WHEN license_cost_eur > 0
33            THEN total_watch_hours / license_cost_eur
34            ELSE 0 END AS hours_per_eur
35    FROM content_stats
36    WHERE total_plays >= 10  -- Mínimo 10 reproducciones para ser evaluable
37)
38SELECT
39    content_id,
40    title,
41    content_type,
42    genre,
43    duration_minutes,
44    unique_viewers,
45    completion_pct,
46    total_watch_hours,
47    hours_per_eur,
48    -- Content Performance Score (0-100)
49    ROUND(
50        (LEAST(reach_ratio / 0.05, 1.0) * 30 +    -- Alcance (30%)
51         LEAST(completion_pct / 80.0, 1.0) * 40 +  -- Finalización (40%)
52         LEAST(hours_per_eur / 2.0, 1.0) * 30)     -- ROI (30%)
53    , 1) AS performance_score
54FROM with_scores
55ORDER BY performance_score DESC
56LIMIT 50;

Content Performance Score: alcance (30%) + finalización (40%) + ROI por euro (30%).

1-- Rendimiento por género: la respuesta a "¿comedia o drama?"
2SELECT
3    c.genre,
4    COUNT(DISTINCT c.content_id) AS titles_available,
5    COUNT(DISTINCT p.user_id) AS unique_viewers,
6    COUNT(p.playback_id) AS total_plays,
7    ROUND(AVG(p.watch_seconds) / 60.0, 1) AS avg_watch_minutes,
8    ROUND(SUM(CASE WHEN p.completed THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 1) AS completion_pct,
9    ROUND(SUM(p.watch_seconds) / 3600.0, 0) AS total_watch_hours,
10    ROUND(SUM(p.watch_seconds) / 3600.0 / SUM(c.license_cost_eur) * 1000, 2) AS hours_per_1000eur,
11    -- Retención: % de usuarios del género que vuelven en 7 días
12    ROUND(
13        COUNT(DISTINCT CASE
14            WHEN EXISTS (
15                SELECT 1 FROM playbacks p2
16                WHERE p2.user_id = p.user_id
17                  AND p2.content_id != p.content_id
18                  AND DATE(p2.started_at) BETWEEN DATE(p.started_at) + 1
19                                              AND DATE(p.started_at) + 7
20            ) THEN p.user_id END
21        ) * 100.0 / NULLIF(COUNT(DISTINCT p.user_id), 0),
22    1) AS pct_return_7d
23FROM content c
24JOIN playbacks p ON c.content_id = p.content_id
25WHERE p.started_at >= CURRENT_DATE - INTERVAL '90 days'
26  AND p.watch_seconds > 30
27GROUP BY c.genre
28HAVING COUNT(p.playback_id) >= 100
29ORDER BY hours_per_1000eur DESC;

Análisis por género con ROI y retención. La respuesta que Sofía necesita para invertir 200K EUR.

1-- Rendimiento por franja de duración
2SELECT
3    CASE
4        WHEN c.duration_seconds < 1200 THEN '< 20 min (corto)'
5        WHEN c.duration_seconds < 2700 THEN '20-45 min (episodio)'
6        WHEN c.duration_seconds < 5400 THEN '45-90 min (medio)'
7        WHEN c.duration_seconds < 7200 THEN '90-120 min (película)'
8        ELSE '> 120 min (largo)'
9    END AS duration_bucket,
10    COUNT(DISTINCT c.content_id) AS titles,
11    COUNT(DISTINCT p.user_id) AS unique_viewers,
12    ROUND(AVG(p.watch_seconds * 1.0 / c.duration_seconds) * 100, 1) AS avg_completion_pct,
13    ROUND(AVG(p.watch_seconds) / 60.0, 1) AS avg_watch_min,
14    ROUND(SUM(p.watch_seconds) / 3600.0, 0) AS total_hours,
15    -- Engagement: ¿está duración genera más sesiones por usuario?
16    ROUND(COUNT(p.playback_id) * 1.0 / COUNT(DISTINCT p.user_id), 1) AS plays_per_viewer
17FROM content c
18JOIN playbacks p ON c.content_id = p.content_id
19WHERE p.started_at >= CURRENT_DATE - INTERVAL '90 days'
20  AND p.watch_seconds > 30
21GROUP BY duration_bucket
22ORDER BY avg_completion_pct DESC;

Análisis por duración: los episodios de 20-45 min tienen el completion rate más alto.

1-- Tabla gold: content_performance (se actualiza cada noche)
2CREATE TABLE gold_content_performance AS
3SELECT
4    c.content_id,
5    c.title,
6    c.content_type,
7    c.genre,
8    c.duration_seconds / 60 AS duration_minutes,
9    c.release_year,
10    c.rating_imdb,
11    c.license_cost_eur,
12    c.is_premium,
13    -- Métricas de performance (últimos 90 días)
14    COUNT(p.playback_id) AS total_plays_90d,
15    COUNT(DISTINCT p.user_id) AS unique_viewers_90d,
16    SUM(p.watch_seconds) / 3600.0 AS total_hours_90d,
17    AVG(p.watch_seconds) / 60.0 AS avg_watch_minutes,
18    SUM(CASE WHEN p.completed THEN 1 ELSE 0 END) * 100.0 / COUNT(*) AS completion_pct,
19    -- ROI
20    CASE WHEN c.license_cost_eur > 0
21        THEN SUM(p.watch_seconds) / 3600.0 / c.license_cost_eur * 1000
22        ELSE 0 END AS hours_per_1000eur,
23    -- Timestamp de actualización
24    CURRENT_TIMESTAMP AS updated_at
25FROM content c
26LEFT JOIN playbacks p ON c.content_id = p.content_id
27    AND p.started_at >= CURRENT_DATE - INTERVAL '90 days'
28    AND p.watch_seconds > 30
29GROUP BY c.content_id, c.title, c.content_type, c.genre,
30         c.duration_seconds, c.release_year, c.rating_imdb,
31         c.license_cost_eur, c.is_premium;

Tabla gold de content performance: se actualiza cada noche, Sofía la consulta cuando quiera.