"""SOURCE render logic for TI-MG-02 (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_MG_02(reg: dict) -> dict:
"""Multi-Genre Demand Combinations - DI by genre pair."""
chart_id = "TI-MG-02"
output_dir = THIS_DIR / chart_id / ROLE
conn = lib.connect_readonly()
t0 = time.time()
# Multi-label genre pairs from categorization_imdb_genres (categorization_genre under-covers multi-genre
# titles); worldwide-framed → geo_code='WORLD' only (old query double-counted US+WORLD); Demand Score basis.
# MEDIAN per pair (house-validated demand-per-title methodology: median-not-mean - a 300-title pair's
# leaderboard multiple must not ride one mega-hit). NB: gen is restricted to the demand universe FIRST -
# categorization_imdb_genres spans all of IMDb, and the self-join for pairs over the unrestricted table
# blows the statement timeout.
df = lib.run_query(conn, """
WITH latest AS (SELECT MAX(file_date) AS file_date FROM title_metrics_daily),
dem AS (
SELECT t.imdb_id, t.demand_score::numeric AS total_demand
FROM title_metrics_daily t JOIN latest l USING (file_date)
WHERE t.demand_score IS NOT NULL AND t.geo_code='WORLD'
),
gen AS (SELECT DISTINCT g.imdb_id, g.genre FROM categorization_imdb_genres g
JOIN dem USING (imdb_id)
WHERE g.genre IS NOT NULL AND g.genre NOT IN ('NA','N/A','Unknown','')),
pairs AS (
SELECT g1.imdb_id, g1.genre AS g_a, g2.genre AS g_b
FROM gen g1 JOIN gen g2 ON g1.imdb_id = g2.imdb_id AND g1.genre < g2.genre
)
SELECT p.g_a, p.g_b,
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY d.total_demand) AS med_demand,
COUNT(DISTINCT p.imdb_id) AS title_count,
(SELECT file_date FROM latest) AS freshness_date
FROM pairs p JOIN dem d USING (imdb_id)
GROUP BY p.g_a, p.g_b HAVING COUNT(DISTINCT p.imdb_id) >= 40;
""")
# baseline = the MEDIAN co-tagged title (same basis as the bars: a mean baseline under median bars
# would mix bases and silently inflate every multiple)
ov = lib.run_query(conn, """
WITH latest AS (SELECT MAX(file_date) AS file_date FROM title_metrics_daily),
dem AS (
SELECT t.imdb_id, t.demand_score::numeric AS total_demand
FROM title_metrics_daily t JOIN latest l USING (file_date)
WHERE t.demand_score IS NOT NULL AND t.geo_code='WORLD'
),
gen AS (SELECT DISTINCT g.imdb_id, g.genre FROM categorization_imdb_genres g
JOIN dem USING (imdb_id)
WHERE g.genre IS NOT NULL AND g.genre NOT IN ('NA','N/A','Unknown','')),
multi AS (SELECT imdb_id FROM gen GROUP BY imdb_id HAVING COUNT(*) >= 2)
SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY d.total_demand) AS med_all
FROM dem d JOIN multi USING (imdb_id);
""")
q_sec = time.time() - t0
conn.close()
if df.empty:
lib.needs_spec_review(output_dir, chart_id, reg["spec_file"], reg["pc_id"], "No genre pairs")
write_shim(output_dir, chart_id); return {"status": "needs_spec_review"}
freshness = str(df["freshness_date"].iloc[0])
df = df.drop(columns=["freshness_date"])
df["med_demand"] = df["med_demand"].astype(float); df["title_count"] = df["title_count"].astype(int)
# MEDIAN per title (house methodology): a pairing can't win on catalog size OR on one mega-hit
overall = float(ov["med_all"].iloc[0]) # the median co-tagged (2+ genre) title
n_qualifying = len(df) # pairs clearing the 40-title floor (declared on the surface, like TI-D-09)
df = df.sort_values("med_demand", ascending=False).head(18).sort_values("med_demand", ascending=True)
df["label"] = df["g_a"] + " + " + df["g_b"]
df["over"] = df["med_demand"] / overall
bar_texts = [f" {o:.1f}x typical · {c} titles" for o, c in zip(df["over"], df["title_count"])]
fig = go.Figure()
# navy = the leader (highest median per title) ONLY; the field stays blue (house contract)
fig.add_trace(go.Bar(x=df["med_demand"], y=df["label"], orientation="h",
marker=dict(color=lib.leader_colors(df["med_demand"])),
text=bar_texts,
textposition="outside", textfont=dict(size=10, color=lib.MUTED), cliponaxis=False, showlegend=False,
customdata=df[["over", "title_count"]],
hovertemplate="%{y}
median Demand Score %{x:,.1f} per title · %{customdata[0]:.1f}x the typical two-genre title"))
df_desc = df.sort_values("med_demand", ascending=False)
leader = df_desc.iloc[0]; runner = df_desc.iloc[1]
if lib.near_tie(df["med_demand"], fmt=lambda v: f"{v / overall:.1f}"):
title_lesson = (f"Per typical title, {leader['g_a']} + {leader['g_b']} and {runner['g_a']} + {runner['g_b']} are neck and neck - "
f"each pulls {leader['over']:.1f}x the typical two-genre title")
else:
title_lesson = (f"{leader['g_a']} + {leader['g_b']} pulls the most demand per typical title worldwide - {leader['over']:.1f}x the "
f"typical two-genre title; {runner['g_a']} + {runner['g_b']} is next")
what = ("Each bar = the median demand of a single title carrying that genre blend, so a pairing can't win on catalog size or "
f"on one mega-hit - worldwide viewing. Dashed line = the typical two-genre title; 'x' shows the multiple above it; top 18 of {n_qualifying} qualifying pairs shown.")
why = ("This is the quality of a blend, not its size. A high bar means the typical title in that combination over-performs per release - "
"the median resists one mega-hit, but still check the title count before treating a thin pair as a proven lane.")
fig.update_layout(**lib.layout_v2(title_lesson, what, width=1200, height=780),
xaxis=lib.standard_xaxis("Median Demand Score per title"), yaxis=lib.standard_yaxis(showgrid=False))
fig.update_xaxes(range=lib.pad_range(df["med_demand"], labels=bar_texts, font_px=10, fig_width=1200))
# the reference line crosses every bar: draw it ABOVE the fills and tie its label with an arrow
vline_above(fig, overall)
ref_label_arrow(fig, overall, f"typical two-genre title: {overall:,.1f}", y_paper=0.04, ax=170)
# one compact methods clause (the old two-line standalone block blew the footer budget)
lib.add_why_and_footer(fig, why, scope_footer(lib.assemble_footer("PC-MG-02",
["CL-006 viewer panel (distinct viewers), not census", "CL-010 genre taxonomy v2"], freshness,
["title_metrics_daily", "categorization_imdb_genres"],
"40+ co-tagged titles per pair; worldwide Demand Score; median per title")))
# baseline rides into the sidecar so every printed multiple reproduces from data.csv alone
lib.save_csv(df.assign(overall_median_two_genre=overall), output_dir); lib.export_chart(fig, output_dir, width=1200, height=780)
lib.write_lesson_design(output_dir, chart_id, f"# {chart_id}\n\n{title_lesson}\n\n{what}\n")
write_shim(output_dir, chart_id)
lib.write_manifest(output_dir, chart_id, "ok", reg["spec_file"], pc_id="PC-MG-02",
intended_lesson_from_spec="Genre-pair demand intensity (per-title normalized)",
rendered_lesson={"reader_title": title_lesson, "reader_subtitle": what,
"claim_strength": "strong", "rules_applied": "MEDIAN Demand Score per title (house median-not-mean) + over-index vs median co-tagged title; imdb multi-label pairs; WORLD"},
row_count=len(df), freshness_date=freshness,
query_seconds=q_sec, render_seconds=time.time() - t0 - q_sec)
return {"status": "ok", "rows": len(df)}