"""SOURCE render logic for TI-AGE-03 (REFERENCE ONLY).
This is the exact function from our pipeline. It is DB-coupled — you cannot run it without our database.
The query result it produces is materialized for you in data.csv, and the rendered output is figure.json
(load that to display the chart). This file is provided so you can understand / re-theme the logic.
"""
def render_TI_AGE_03(reg: dict) -> dict:
"""Platform New-Slate Age-Skew (M052): among each service's NEW releases (2024+), the demand-weighted
audience age over-index. Which platforms' new content draws the youngest early-adopter crowd vs the most
mature. Conditioning on the new slate ~doubles the separation the full catalog washes out. English titles."""
import numpy as np
chart_id = "TI-AGE-03"
output_dir = THIS_DIR / chart_id / ROLE
conn = lib.connect_readonly()
t0 = time.time()
# Recognizability filter + demand weights repointed from the dead age-skew di column to the LATEST
# title_metrics_daily.demand_score. New-slate titles run smaller, so the floor is 20 (8 platforms
# clear the >=12-title gate today).
raw = lib.run_query(conn, """
WITH latest AS (SELECT MAX(window_end_date) AS d FROM metrics_persona_age_skew),
latest_t AS (SELECT MAX(file_date) AS fd FROM title_metrics_daily),
tp AS (SELECT DISTINCT imdb_id, parent_company FROM categorization_title_x_platform WHERE parent_company IS NOT NULL)
SELECT tp.parent_company AS plat, a.age_skew_oi, t.demand_score::numeric AS demand, a.window_end_date
FROM metrics_persona_age_skew a
JOIN tp ON tp.imdb_id = a.imdb_id
JOIN categorization_imdb_title_basics tb ON tb.imdb_id = a.imdb_id
JOIN categorization_title_metadata tm ON tm.imdb_id = a.imdb_id
JOIN title_metrics_daily t
ON t.imdb_id = a.imdb_id AND t.file_date = (SELECT fd FROM latest_t) AND t.geo_code = 'US'
WHERE a.window_end_date = (SELECT d FROM latest)
AND a.n_segments >= 8 AND tm.language_origin = 'English'
AND t.demand_score >= 20
AND tb.start_year >= 2024;
""")
q_sec = time.time() - t0
conn.close()
raw["age_skew_oi"] = pd.to_numeric(raw["age_skew_oi"], errors="coerce")
raw["demand"] = pd.to_numeric(raw["demand"], errors="coerce")
raw = raw.dropna(subset=["age_skew_oi", "demand"])
DISP = {"Netflix": "Netflix", "Comcast": "Peacock", "WBD": "Max", "Disney": "Disney+",
"Sony_Crunchyroll": "Crunchyroll", "Apple": "Apple TV+", "Paramount": "Paramount+",
"Amazon": "Prime Video", "Lionsgate_Starz": "Starz", "Fox_Tubi": "Tubi"}
rows = []
for plat, x in raw.groupby("plat"):
n = len(x)
if n < 12:
continue
wmean = float((x["age_skew_oi"] * x["demand"]).sum() / x["demand"].sum())
se = float(x["age_skew_oi"].std(ddof=1) / np.sqrt(n))
rows.append({"plat": DISP.get(plat, plat), "n": n, "wmean": wmean, "lo": wmean - 1.96 * se, "hi": wmean + 1.96 * se})
if len(rows) < 4:
lib.needs_spec_review(output_dir, chart_id, reg.get("spec_file", "specs/15_audience.md"),
"PC-AGE-03", "Too few platforms clear the >=12 new-slate-title gate")
write_shim(output_dir, chart_id); return {"status": "needs_spec_review"}
g = pd.DataFrame(rows).sort_values("wmean").reset_index(drop=True)
fresh = str(raw["window_end_date"].iloc[0])
# express the over-index as a PERCENT vs a typical title (1.17x -> +17%, 0.91x -> -9%): clearer than x
g["pct"] = (g["wmean"] - 1) * 100
g["lo_p"] = (g["lo"] - 1) * 100
g["hi_p"] = (g["hi"] - 1) * 100
# significance honesty: full-saturation color ONLY when the 95% CI clears zero; in-the-noise platforms fade
g["sig"] = (g["lo_p"] > 0) | (g["hi_p"] < 0)
colors = [lib.ACCENT if w >= 0 else lib.NAVY for w in g["pct"]]
opac = [1.0 if s else 0.4 for s in g["sig"]]
tpos = ["middle right" if w >= 0 else "middle left" for w in g["pct"]]
fig = go.Figure()
cx, cy = [], []
for lbl, w in zip(g["plat"], g["pct"]):
cx += [0, w, None]; cy += [lbl, lbl, None]
fig.add_trace(go.Scatter(x=cx, y=cy, mode="lines", line=dict(color="rgba(104,112,125,0.30)", width=2.0),
showlegend=False, hoverinfo="skip"))
wx, wy = [], []
for lbl, lo, hi in zip(g["plat"], g["lo_p"], g["hi_p"]):
wx += [lo, hi, None]; wy += [lbl, lbl, None]
fig.add_trace(go.Scatter(x=wx, y=wy, mode="lines", line=dict(color="rgba(32,36,42,0.22)", width=1.2),
showlegend=False, hoverinfo="skip"))
fig.add_trace(go.Scatter(
x=g["pct"], y=g["plat"], mode="markers+text",
marker=dict(size=14, color=colors, opacity=opac, line=dict(width=1, color="white")),
text=[f"{w:+.0f}%" for w in g["pct"]], textposition=tpos,
textfont=dict(size=10.5, color=lib.INK), cliponaxis=False, showlegend=False,
customdata=g[["n"]].values,
hovertemplate="%{y}
%{x:+.0f}% young viewers vs a typical title
%{customdata[0]} new-slate titles"))
fig.add_vline(x=0, line=dict(color=lib.INK, width=1.1))
fig.add_annotation(x=0, y=len(g) - 0.3, text="typical title (0%)", showarrow=False, yshift=8,
xanchor="center", yanchor="bottom", font=dict(size=10, color=lib.MUTED),
bgcolor="rgba(255,255,255,0.85)")
fig.add_annotation(x=g["hi_p"].max(), y=len(g) - 1.2, text="younger →", showarrow=False,
xanchor="right", yanchor="middle", font=dict(size=10.5, color=lib.ACCENT))
fig.add_annotation(x=g["lo_p"].min(), y=0.55, text="← more mature", showarrow=False,
xanchor="left", yanchor="middle", font=dict(size=10.5, color=lib.NAVY))
fig.add_annotation(xref="paper", yref="paper", x=0.99, y=0.02, xanchor="right", yanchor="bottom",
text="solid = clearly different from typical (95%); faded = within noise", showarrow=False,
font=dict(size=9.5, color=lib.MUTED))
fig.update_xaxes(range=[g["lo_p"].min() - 4, g["hi_p"].max() + 5], ticksuffix="%")
yng = g.iloc[-1]; old = g.iloc[0]
sig = g[g["sig"]]
if len(sig) == 0:
takeaway = ("On their new releases, no service's slate clearly skews young or old - every platform sits "
"within noise of the typical title")
elif len(sig) <= 2:
ex = sig.loc[sig["pct"].abs().idxmax()]
direction = "more" if ex["pct"] > 0 else "fewer"
takeaway = (f"On their new releases, {ex['plat']} stands out: {abs(ex['pct']):.0f}% {direction} young viewers than a "
f"typical title; most rivals are statistically in the middle of the pack")
else:
takeaway = (f"On their new releases, {yng['plat']} draws the youngest audience ({yng['pct']:+.0f}% young viewers vs typical) "
f"and {old['plat']} the most mature ({old['pct']:+.0f}%) - new content separates services the full catalog hides")
what = ("Each service's 2024+ releases, by how many more or fewer young viewers they draw than a typical "
"streaming title (0% = typical; right = younger, left = older). New content only - full catalogs all wash out "
"to ~neutral (demand-weighted; bars show the 95% range; English-language titles).")
why = ("A platform's library blurs its identity - its new slate shows who it's actually courting. Which services pull "
"the youngest, early-adopter crowd vs. lean mature is a direct read on where to launch a title for a target audience, "
"and which platforms are fighting over the under-35.")
footer = lib.assemble_footer(pc_id="PC-AGE-03", caution_labels=["CL-006 viewer panel (distinct viewers), not census", "M052 audience age-skew (v1)"],
freshness_date=fresh, sources=["metrics_persona_age_skew", "categorization_title_x_platform", "title_metrics_daily"],
extra="new-slate = 2024+ releases; % more/fewer young viewers vs a typical title (panel adjusted to population), demand-weighted; English titles, demand score ≥ 20; ≥12 titles/platform; 95% CI")
fig.update_layout(**lib.layout_v2(takeaway, what, width=1140, height=720),
xaxis=lib.standard_xaxis("New-slate audience age vs a typical title (% more / fewer young viewers)"),
yaxis=lib.standard_yaxis(showgrid=False))
fig.update_yaxes(automargin=True)
lib.add_why_and_footer(fig, why, footer)
lesson = {"reader_title": takeaway, "reader_subtitle": what, "claim_strength": "moderate",
"rules_applied": "M052 age-skew, platform new-slate (start_year>=2024), demand_score-weighted; English, Demand Score>=20; >=12 titles/platform; CI-gated significance coloring"}
lib.save_csv(g, output_dir); lib.export_chart(fig, output_dir, width=1140, height=720)
lib.write_lesson_design(output_dir, chart_id, f"# {chart_id}\n\n{takeaway}\n\n{what}\n")
write_shim(output_dir, chart_id)
lib.write_manifest(output_dir, chart_id, "ok", reg.get("spec_file", "specs/15_audience.md"), pc_id="PC-AGE-03",
intended_lesson_from_spec="Platform new-slate audience age-skew (M052)",
rendered_lesson=lesson, row_count=len(g), freshness_date=fresh,
query_seconds=q_sec, render_seconds=time.time() - t0 - q_sec)
return {"status": "ok", "rows": len(g)}
# ===========================================================================
# RECURRING SERIES (Trackers & Standings) - cadence-based franchise charts.
# Read the metrics agent's pre-computed M053-M055 tables (handoff 2026-06-04) so
# dashboard numbers always match marketing's published series values.
# Cadence: Demand 10 + Mover = weekly; Attention Standings = daily; One Show Away = monthly.
# ===========================================================================