"""SOURCE render logic for TI-C-01 (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_01(reg: dict) -> dict:
"""Streaming Parent Demand Concentration (HHI)."""
chart_id = "TI-C-01"
output_dir = THIS_DIR / chart_id / ROLE
conn = lib.connect_readonly()
t0 = time.time()
parents = _parent_credit_normalized_di(conn, 1000)
# Sidecar stat (folded in from retired TI-C-13): of the 200 most in-demand streaming-eligible
# titles, how many each parent's services host. Per-(title,platform) streaming gate: a title
# credits a parent only where THAT platform carries it flatrate/free/ads (never rent-or-buy),
# resolved via the canonical platform-name map - mirrors the RC-family eligibility gate.
top200_counts = 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 (
SELECT t.imdb_id
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
),
eligible AS (
SELECT DISTINCT cpa.imdb_id, mpc.canonical AS platform
FROM categorization_platforms_available cpa
JOIN metrics_platform_canonical mpc ON mpc.raw_name = cpa.platforms_available
WHERE cpa.country_code = 'US' AND cpa.watch_type IN ('flatrate','free','ads')
),
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)
JOIN eligible e ON e.imdb_id = cxp.imdb_id AND e.platform = cxp.platform
WHERE cxp.parent_company IS NOT NULL AND btrim(cxp.parent_company) <> ''
)
SELECT pp.parent_company, COUNT(DISTINCT t.imdb_id) AS top200_count
FROM top200 t JOIN pp USING (imdb_id)
GROUP BY pp.parent_company;
""")
conn.close()
q_sec = time.time() - t0
if parents.empty:
lib.needs_spec_review(output_dir, chart_id, reg["spec_file"], reg["pc_id"], "No parent-mapping coverage")
write_shim(output_dir, chart_id)
return {"status": "needs_spec_review"}
freshness = str(parents["freshness_date"].iloc[0])
parents = parents.drop(columns=["freshness_date"])
parents["credit_normalized_demand"] = parents["credit_normalized_demand"].astype(float)
parents["share"] = parents["credit_normalized_demand"] / parents["credit_normalized_demand"].sum()
hhi = float((parents["share"] ** 2).sum() * 10000)
top15 = parents.head(15).copy()
other_share = parents.iloc[15:]["share"].sum() if len(parents) > 15 else 0.0
if other_share >= 0.01:
top15 = pd.concat([top15, pd.DataFrame([{
"parent_company": "Other (long tail)",
"credit_normalized_demand": parents.iloc[15:]["credit_normalized_demand"].sum(),
"title_count": int(parents.iloc[15:]["title_count"].sum()),
"share": float(other_share),
}])], ignore_index=True)
if hhi < 1500:
band = "unconcentrated"
elif hhi < 2500:
band = "moderate concentration"
else:
band = "high concentration"
plot_df = top15.sort_values("share", ascending=True)
leader = parents.iloc[0]
runner = parents.iloc[1] if len(parents) > 1 else None
bar_colors = [lib.NAVY if p == leader["parent_company"] else
(lib.MUTED if p == "Other (long tail)" else lib.BLUE) for p in plot_df["parent_company"]]
# 'WBD' decoded for the casual reader, on the axis and in the headline
disp = lambda p: {"WBD": "Warner Bros. Discovery (WBD)"}.get(str(p), str(p))
# The retired TI-C-13's host-count stays in the data.csv sidecar ONLY: printing a second
# quantity (a count) beside the %-share bars put two units on one surface (audit fix).
host_map = (dict(zip(top200_counts["parent_company"], top200_counts["top200_count"].astype(int)))
if not top200_counts.empty else {})
bar_texts = [f"{s:.1%}" for s in plot_df["share"]]
fig = go.Figure()
fig.add_trace(go.Bar(
x=plot_df["share"], y=[disp(p) for p in plot_df["parent_company"]], orientation="h",
marker=dict(color=bar_colors),
text=bar_texts,
textposition="outside", textfont=dict(size=11, color=lib.INK),
cliponaxis=False, showlegend=False,
hovertemplate="%{y}
%{x:.1%} of the top-1,000 titles' total demand",
))
fig.update_xaxes(range=lib.pad_range(plot_df["share"], labels=bar_texts, fig_width=1240))
# headline uses the spelled-out name without the parenthetical (no awkward "(WBD)'s" possessive)
disp_head = lambda p: {"WBD": "Warner Bros. Discovery"}.get(str(p), str(p))
if runner is not None and lib.near_tie([leader["share"], runner["share"]], fmt=lambda v: f"{v:.0%}"):
title_lesson = (f"{disp_head(leader['parent_company'])} and {disp_head(runner['parent_company'])} are neck and neck for the biggest share "
f"of today's demand for America's most-wanted titles, at about {leader['share']:.0%} each")
else:
title_lesson = (f"{disp_head(leader['parent_company'])}'s services host the biggest share of today's demand for "
f"America's most-wanted titles - {leader['share']:.0%}")
if runner is not None:
title_lesson += f", just ahead of {disp_head(runner['parent_company'])} ({runner['share']:.0%})"
dominance = "No single giant dominates" if hhi < 1500 else "Power is concentrating"
top2_share = float(parents["share"].iloc[:2].sum())
what = ("Of the 1,000 most in-demand titles, the share of today's total demand that lands on each parent company's streaming services. "
"A title on several services is split between them, so nobody is double-counted.")
why = ("This is who controls audience attention - about catalog pull, not profit or quality. A parent ranks high by hosting "
f"many titles people want, whether it made them or licensed them. {dominance}: the top two together hold "
f"~{top2_share:.0%}, and by the standard concentration test the market is {band}.")
footer = lib.assemble_footer(pc_id="PC-C-01",
caution_labels=["counts any title on the service, original or licensed"],
freshness_date=freshness,
sources=["title_metrics_daily", "categorization_title_x_platform",
"categorization_platforms_available"],
extra="Demand Score basis (US)")
fig.update_layout(**lib.layout_v2(title_lesson, what, width=1240, height=780),
xaxis=lib.standard_xaxis("", tickformat=".0%"),
yaxis=lib.standard_yaxis(showgrid=False),
)
fig.update_yaxes(tickfont=dict(size=12, color=lib.INK))
lib.add_why_and_footer(fig, why, footer)
lesson = {"reader_title": title_lesson, "reader_subtitle": what,
"claim_strength": "strong",
"rules_applied": f"HHI={hhi:.0f}; band={band}; sidecar = per-(title,platform) flatrate/free/ads-gated top-200 host count (ex TI-C-13)"}
if host_map:
parents["hosts_of_top200"] = parents["parent_company"].map(host_map).fillna(0).astype(int)
lib.save_csv(parents, output_dir)
lib.export_chart(fig, output_dir, width=1240, 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-C-01",
intended_lesson_from_spec="Parent concentration HHI in top-1000 universe",
rendered_lesson=lesson, row_count=len(parents), freshness_date=freshness,
query_seconds=q_sec, render_seconds=time.time() - t0 - q_sec)
return {"status": "ok", "rows": len(parents)}