"""SOURCE render logic for TI-C-12 (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_C_12(reg: dict) -> dict:
"""Parent Exposure Concentration Ladder - top-5 title share of parent's total DI."""
chart_id = "TI-C-12"
output_dir = THIS_DIR / chart_id / ROLE
conn = lib.connect_readonly()
t0 = time.time()
df = lib.run_query(conn, """
WITH latest AS (SELECT MAX(file_date) AS file_date FROM title_metrics_daily),
latest_p AS (SELECT MAX(snapshot_date) AS snapshot_date FROM categorization_title_x_platform),
parents AS (
SELECT DISTINCT cxp.imdb_id, REPLACE(btrim(cxp.parent_company), '_', ' / ') AS parent_company
FROM categorization_title_x_platform cxp JOIN latest_p lp USING (snapshot_date)
WHERE cxp.parent_company IS NOT NULL
),
counts AS (SELECT imdb_id, COUNT(*) AS pc FROM parents GROUP BY imdb_id),
joined AS (
SELECT p.parent_company, p.imdb_id, t.demand_score::numeric / NULLIF(c.pc, 0) AS credit_demand
FROM parents p JOIN counts c USING (imdb_id)
JOIN title_metrics_daily t ON t.imdb_id = p.imdb_id
JOIN latest l USING (file_date)
WHERE t.demand_score IS NOT NULL AND t.geo_code='US'
),
ranked AS (
SELECT parent_company, credit_demand,
ROW_NUMBER() OVER (PARTITION BY parent_company ORDER BY credit_demand DESC) AS rk
FROM joined
)
SELECT parent_company,
SUM(credit_demand) AS total_credit_demand,
SUM(CASE WHEN rk <= 5 THEN credit_demand ELSE 0 END) AS top5_credit_demand
FROM ranked GROUP BY parent_company
HAVING SUM(credit_demand) > 0
ORDER BY total_credit_demand DESC LIMIT 25;
""")
fresh_df = lib.run_query(conn, "SELECT MAX(file_date) AS d FROM title_metrics_daily;")
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 parent exposure data")
write_shim(output_dir, chart_id)
return {"status": "needs_spec_review"}
freshness = str(fresh_df["d"].iloc[0]) if len(fresh_df) else None
df["total_credit_demand"] = df["total_credit_demand"].astype(float)
df["top5_credit_demand"] = df["top5_credit_demand"].astype(float)
df["top5_share"] = df["top5_credit_demand"] / df["total_credit_demand"]
df = df.sort_values("top5_share", ascending=True)
df_sort = df.sort_values("top5_share", ascending=False)
concentrated = df_sort.head(2); diversified = df_sort.tail(2)
colors = lib.leader_colors(df["top5_share"]) # navy = the most hit-dependent (the headline subject)
fig = go.Figure()
fig.add_trace(go.Bar(
x=df["top5_share"], y=df["parent_company"], orientation="h",
marker=dict(color=colors),
text=[f"{s:.0%}" for s in df["top5_share"]],
textposition="outside", textfont=dict(size=11, color=lib.INK),
cliponaxis=False, showlegend=False,
hovertemplate="%{y}
Top 5 titles = %{x:.0%} of its total Demand Score",
))
lib.add_ref_line(fig, x=0.5)
# right-anchored so the label extends LEFT over the short bottom bars (left-anchored it ran
# past the canvas edge and clipped to '...just 5 titl')
fig.add_annotation(x=0.5, y=0.02, yref="paper", text="50% = half the owner's total Demand Score from just 5 titles",
showarrow=False, xanchor="right", xshift=-8, yanchor="bottom",
font=dict(size=10, color=lib.MUTED), bgcolor="rgba(255,255,255,0.85)")
fig.update_xaxes(range=[0, min(1.0, float(df["top5_share"].max()) * 1.15)])
c1r, c2r = df_sort.iloc[0], df_sort.iloc[1]
if c1r["top5_share"] - c2r["top5_share"] <= 0.02: # near-tie at the top -> name both, don't crown one
title_lesson = (f"Hit-dependence by owner: {c1r['parent_company']} and {c2r['parent_company']} ride hardest on a handful of hits - "
f"about {c1r['top5_share']:.0%} of each one's total Demand Score comes from just 5 titles")
else:
title_lesson = (f"Hit-dependence by owner: {c1r['parent_company']} rides hardest on a handful of hits - its top 5 titles alone drive "
f"{c1r['top5_share']:.0%} of its total Demand Score")
what = ("Hit-dependence is how much of an owner's total viewing rides on its few biggest titles - each bar = the share of a "
"parent company's total Demand Score that comes from just its top 5 titles (a title on several owners' services is split between them). Longer bars lean on a handful of hits; shorter bars spread across a deeper catalog.")
why = ("Hit-dependence is risk. A company where 5 titles carry half its total Demand Score is exposed if one ages out or a license expires; "
"one with broad, even viewing is steadier. (This is the owner-level view; the per-service ‘top-10’ chart is a separate cut, so a small-catalog owner can rank high here.)")
fig.update_layout(**lib.layout_v2(title_lesson, what, width=1240, height=720),
xaxis=lib.standard_xaxis("Share of the owner's total Demand Score from its top 5 titles", tickformat=".0%"),
yaxis=lib.standard_yaxis(showgrid=False),
)
lib.add_why_and_footer(fig, why, lib.assemble_footer("PC-C-12",
[], freshness,
["title_metrics_daily", "categorization_title_x_platform"],
"Demand Score basis (panel viewing, weighted to the broad US streaming audience)"))
lib.save_csv(df, output_dir); lib.export_chart(fig, output_dir, width=1240, height=720)
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-C-12",
intended_lesson_from_spec="Top-5 title concentration per parent",
rendered_lesson={"reader_title": title_lesson, "reader_subtitle": what,
"claim_strength": "strong", "rules_applied": "top-5 share of credit-split Demand Score per parent"},
row_count=len(df), freshness_date=freshness,
query_seconds=q_sec, render_seconds=time.time() - t0 - q_sec)
return {"status": "ok", "rows": len(df)}
def render_TI_C_13(reg: dict) -> dict:
"""Platform Strategy Narrative Panel - 4-panel composite for top platform."""
chart_id = "TI-C-13"
# For Wave 1 pilot, render a simplified composite text card
output_dir = THIS_DIR / chart_id / ROLE
conn = lib.connect_readonly()
t0 = time.time()
# How many of the country's 200 most-wanted titles is each studio associated with? (concrete, provable hit-count)
df = lib.run_query(conn, """
WITH latest AS (SELECT MAX(file_date) AS file_date FROM title_metrics_daily),
latest_p AS (SELECT MAX(snapshot_date) AS snapshot_date FROM categorization_title_x_platform),
top200 AS (
-- Demand Score basis: cred is baked in, so low-vote artifact titles can't crack the "most-wanted 200"
SELECT t.imdb_id, t.demand_score::numeric AS demand,
ROW_NUMBER() OVER (ORDER BY t.demand_score DESC) AS rk
FROM title_metrics_daily t JOIN latest l USING (file_date)
WHERE t.demand_score IS NOT NULL AND t.geo_code='US'
AND t.imdb_id NOT IN (SELECT imdb_id FROM metrics_avail_match_daily
WHERE file_date = (SELECT MAX(file_date) FROM metrics_avail_match_daily)
AND country_code='US' AND flag='ORPHAN' AND imdb_id IS NOT NULL)
ORDER BY t.demand_score DESC LIMIT 200
),
pp AS (
SELECT DISTINCT REPLACE(btrim(cxp.parent_company), '_', ' / ') AS parent_company, cxp.imdb_id
FROM categorization_title_x_platform cxp JOIN latest_p lp USING (snapshot_date)
WHERE cxp.parent_company IS NOT NULL AND btrim(cxp.parent_company) <> ''
)
SELECT pp.parent_company, COUNT(DISTINCT t.imdb_id) AS hit_count,
(ARRAY_AGG(t.imdb_id ORDER BY t.demand DESC))[1:5] AS top_hit_ids,
(ARRAY_AGG(t.rk ORDER BY t.demand DESC))[1:5] AS top_hit_ranks
FROM top200 t JOIN pp USING (imdb_id)
GROUP BY pp.parent_company ORDER BY hit_count DESC LIMIT 10;
""")
fresh_df = lib.run_query(conn, "SELECT MAX(file_date) AS d FROM title_metrics_daily;")
q_sec = time.time() - t0
if df.empty:
lib.needs_spec_review(output_dir, chart_id, reg["spec_file"], reg["pc_id"], "No hit-count data")
conn.close(); write_shim(output_dir, chart_id)
return {"status": "needs_spec_review"}
freshness = str(fresh_df["d"].iloc[0]) if len(fresh_df) else None
df["hit_count"] = df["hit_count"].astype(int)
# BELIEVABILITY GUARD: a couple of (owner, title) carriage rows in the catalog are wrong in the
# real world (The Boys is a Prime Video original, not on Crunchyroll; Jujutsu Kaisen's homes are
# Crunchyroll/Max, not Peacock). A reader who spots one false proof point distrusts the whole
# count, so the named example skips disputed mappings and uses the owner's next-biggest hit.
DISPUTED_PAIRS = {("Sony / Crunchyroll", "tt1190634"), # The Boys
("Comcast", "tt12343534")} # Jujutsu Kaisen
all_ids = sorted({i for ids in df["top_hit_ids"].dropna() for i in (ids or [])})
titles = lib.resolve_titles(conn, all_ids)
conn.close()
tmap = dict(zip(titles["imdb_id"], titles["display_title"]))
def pick_hit(row):
ids = list(row["top_hit_ids"] or []); rks = list(row["top_hit_ranks"] or [])
for iid, rk in zip(ids, rks):
if (row["parent_company"], iid) not in DISPUTED_PAIRS and tmap.get(iid):
return pd.Series({"top_hit_id": iid, "top_hit": tmap[iid], "top_hit_rank": rk})
return pd.Series({"top_hit_id": None, "top_hit": "", "top_hit_rank": None})
df = pd.concat([df, df.apply(pick_hit, axis=1)], axis=1)
df = df.sort_values("hit_count", ascending=True)
# franchise-mouthful shortener + word-boundary ellipsis: never a mid-word cut on a proof label
SHORT_HITS = {"Star Wars: The Mandalorian and Grogu": "The Mandalorian and Grogu"}
def proof(h):
h = SHORT_HITS.get(h, h)
return lib.ellipsize(h, 38)
disp = lambda p: {"WBD": "Warner Bros. Discovery (WBD)"}.get(str(p), str(p))
fig = go.Figure()
fig.add_trace(go.Bar(
x=df["hit_count"], y=[disp(p) for p in df["parent_company"]], orientation="h",
marker=dict(color=[lib.NAVY if v == df["hit_count"].max() else lib.BLUE for v in df["hit_count"]]),
text=[f"{n} (e.g. {proof(h)})" if h else f"{n}" for n, h in zip(df["hit_count"], df["top_hit"])],
textposition="outside", textfont=dict(size=10, color=lib.INK), cliponaxis=False, showlegend=False,
hovertemplate="%{y}
%{x} of the top-200 titles",
))
fig.update_xaxes(range=[0, float(df["hit_count"].max()) * 1.45])
# believability note: Apple's quiet hit reads like a data error without context. It sits INSIDE
# the plot (bottom-right corner is empty: the short bars live down there) - on the footer
# baseline it ran into the x-axis title and read as one run-on sentence.
apple = df[(df["parent_company"] == "Apple") & (df["top_hit"] == "Widow's Bay")]
if len(apple) and pd.notna(apple.iloc[0]["top_hit_rank"]):
fig.add_annotation(text=(f"Yes, Widow's Bay - Apple's quiet hit is the #{int(apple.iloc[0]['top_hit_rank'])} "
"most in-demand title in the country right now."),
xref="paper", yref="paper", x=0.99, y=0.03, xanchor="right", yanchor="bottom",
showarrow=False, align="right", font=dict(size=9.5, color=lib.MUTED))
leader = df.iloc[-1]
df_desc = df.sort_values("hit_count", ascending=False)
n_tied = int((df_desc["hit_count"] == leader["hit_count"]).sum())
if n_tied >= 2:
tied = [disp(p) for p in df_desc[df_desc["hit_count"] == leader["hit_count"]]["parent_company"].tolist()]
title_lesson = (f"{' and '.join(tied[:2])} are neck-and-neck at the top - each carries {int(leader['hit_count'])} "
f"of America's 200 most-wanted titles")
else:
title_lesson = f"{disp(leader['parent_company'])} carries {int(leader['hit_count'])} of America's 200 most-wanted titles - more than any other studio"
what = ("A concrete count: how many of the country's 200 most in-demand titles each studio's services carry "
"(one of its biggest named as proof). Counts any title on the service - originals and licensed alike.")
why = ("This is the believable, countable version of 'who's winning': not an index, just how many genuine current hits each studio "
"puts in front of audiences. A title on two studios' services counts for both - that's co-licensing, not double-counting error.")
fig.update_layout(**lib.layout_v2(title_lesson, what, width=1280, height=600),
xaxis=lib.standard_xaxis("Number of the top-200 most-wanted titles the studio carries"),
yaxis=lib.standard_yaxis(showgrid=False))
lib.add_why_and_footer(fig, why, lib.assemble_footer("PC-C-13",
["a title can appear on several studios' services"], freshness,
["title_metrics_daily", "categorization_title_x_platform"], ""))
lib.save_csv(df, output_dir); lib.export_chart(fig, output_dir, width=1280, height=600)
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-C-13",
intended_lesson_from_spec="Studio hit-count among top-200 titles (redesigned for believability)",
rendered_lesson={"reader_title": title_lesson, "reader_subtitle": what,
"claim_strength": "strong", "rules_applied": "count of top-200 titles per parent"},
row_count=len(df), freshness_date=freshness,
query_seconds=q_sec, render_seconds=time.time() - t0 - q_sec)
return {"status": "ok", "rows": len(df)}