"""SOURCE render logic for TI-C-05 (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_05(reg: dict) -> dict:
"""Platform Supply-Demand Gap Map - supply share vs demand share scatter."""
chart_id = "TI-C-05"
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),
ps AS (
SELECT cxp.platform, cxp.imdb_id
FROM categorization_title_x_platform cxp JOIN latest_p lp USING (snapshot_date)
WHERE cxp.platform IS NOT NULL
),
dem AS (
SELECT t.imdb_id, t.demand_score::numeric AS demand FROM title_metrics_daily t JOIN latest l USING (file_date)
WHERE t.demand_score IS NOT NULL AND t.geo_code='US'
),
counts AS (SELECT imdb_id, COUNT(DISTINCT platform) AS pc FROM ps GROUP BY imdb_id)
SELECT ps.platform,
COUNT(DISTINCT ps.imdb_id) AS catalog_size,
SUM(dem.demand / NULLIF(c.pc, 0)) AS credit_demand
FROM ps LEFT JOIN dem USING (imdb_id) LEFT JOIN counts c USING (imdb_id)
GROUP BY ps.platform
HAVING COUNT(DISTINCT ps.imdb_id) >= 100;
""")
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"], "Empty supply-demand")
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["credit_demand"] = df["credit_demand"].fillna(0).astype(float)
df["catalog_size"] = df["catalog_size"].astype(int)
df["supply_share"] = df["catalog_size"] / df["catalog_size"].sum()
df["demand_share"] = df["credit_demand"] / df["credit_demand"].sum() if df["credit_demand"].sum() > 0 else 0
df = df.nlargest(25, "catalog_size")
df["fit_ratio"] = df["demand_share"] / df["supply_share"].clip(lower=0.001)
def fit_label(r: float) -> str:
# ONE precision for every dot (uniform 1dp; the old near-1 two-decimal exception mixed
# precisions on one surface), and the value is rounded to 2dp FIRST so the printed number
# always agrees with the 2dp sidecar (0.75 in data.csv -> 0.8x, never a truncated 0.7x)
return f"{round(r, 2):.1f}×"
# label side: dots ABOVE the fair-share diagonal label on top, dots BELOW label underneath -
# so the dashed line can never strike through its own labels; then a dot-collision pass kicks
# a label sideways when another marker sits right where the label would land
df = df.reset_index(drop=True)
tp = []
for i, r in df.iterrows():
pos = "top center" if r["demand_share"] >= r["supply_share"] else "bottom center"
for j, o in df.iterrows():
if i == j:
continue
dx = abs(float(o["supply_share"]) - float(r["supply_share"]))
dy = float(o["demand_share"]) - float(r["demand_share"])
if dx < 0.013 and ((pos == "top center" and 0 < dy < 0.014) or
(pos == "bottom center" and -0.014 < dy < 0)):
# kick the label AWAY from the colliding neighbor
pos = "middle left" if o["supply_share"] >= r["supply_share"] else "middle right"
break
tp.append(pos)
fig = go.Figure()
fig.add_trace(go.Scatter(
x=df["supply_share"], y=df["demand_share"], mode="markers+text",
marker=dict(size=13, color=lib.BLUE, opacity=0.85, line=dict(width=0)), # one hue: position already tells the story
# per-dot ratio labels make the headline multiples verifiable on the chart face
text=[f"{p} · {fit_label(r)}" for p, r in zip(df["platform"], df["fit_ratio"])],
textposition=tp, textfont=dict(size=9, color=lib.INK),
customdata=df["fit_ratio"],
hovertemplate="%{text}
Catalog share %{x:.1%}
Demand share %{y:.1%}
Pulls %{customdata:.2f}× its fair share",
showlegend=False,
))
max_v = float(max(df["supply_share"].max(), df["demand_share"].max())) * 1.05
fig.add_shape(type="line", x0=0, y0=0, x1=max_v, y1=max_v, line=dict(color=lib.MUTED, dash="dash", width=1.4))
# label anchored ON the line, beside the data cloud (not floating in the empty corner)
fig.add_annotation(x=max_v * 0.62, y=max_v * 0.62, text="'fair share' line: demand share matches catalog share",
showarrow=False, xanchor="left", yanchor="top", xshift=6, yshift=-4,
font=dict(size=10, color=lib.MUTED), bgcolor="rgba(255,255,255,0.8)")
# the dramatic far-below outlier gets the one-line explanation it begs for
tubi = df[df["platform"].str.lower() == "tubi"]
if not tubi.empty:
tx, ty = float(tubi["supply_share"].iloc[0]), float(tubi["demand_share"].iloc[0])
fig.add_annotation(x=tx, y=ty, text=(f"Tubi: {tx:.0%} of all titles but {ty:.0%} of total demand -
"
"a huge free library viewers rarely seek out"),
showarrow=True, arrowhead=2, arrowcolor=lib.MUTED, ax=70, ay=28,
font=dict(size=9.5, color=lib.MUTED), align="left")
over = df.nlargest(2, "fit_ratio")
over_str = " and ".join(f"{p} ({r:.1f}× its fair share)" if i == 0 else f"{p} ({r:.1f}×)"
for i, (p, r) in enumerate(zip(over["platform"], over["fit_ratio"])))
max_row = df[df["platform"].str.lower().isin(["max", "hbo max"])]
max_note = ""
if not max_row.empty:
mr = float(max_row.iloc[0]["fit_ratio"])
if 0.9 <= mr <= 1.15:
max_note = f" Max sits on the line ({mr:.1f}×): a big catalog with proportionally big demand."
elif mr <= 1.35:
max_note = f" Max sits near the line ({mr:.1f}×): a big catalog with roughly proportional demand."
else:
max_note = f" Max is the exception - a big catalog that still over-delivers ({mr:.1f}× its fair share)."
title_lesson = f"Small, curated services punch above their weight: {over_str}"
what = ("Each dot = a service: right = catalog size (share of all titles), up = its share of total demand. "
"The diagonal = a service's 'fair share': above it = over-performing, below = a big catalog that underdelivers.")
why = ("This is the quality-vs-quantity test. Far above the line = winning on curation (a few titles people really want); "
f"far below = shelf-filler.{max_note}")
footer = lib.assemble_footer(pc_id="PC-C-05",
caution_labels=["a title can be on several services"],
freshness_date=freshness,
sources=["title_metrics_daily", "categorization_title_x_platform"],
extra=f"{len(df)} services with 100+ catalog titles")
fig.update_layout(**lib.layout_v2(title_lesson, what, width=1200, height=720),
xaxis=lib.standard_xaxis("Catalog size (share of all titles) →", tickformat=".0%"),
yaxis=lib.standard_yaxis("Audience demand (share of all services' total demand) →", tickformat=".0%"),
)
lib.add_why_and_footer(fig, why, footer)
lib.save_csv(df, output_dir); lib.export_chart(fig, output_dir, width=1200, 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-05",
intended_lesson_from_spec="Platform supply-demand fit (quality vs quantity)",
rendered_lesson={"reader_title": title_lesson, "reader_subtitle": what,
"claim_strength": "strong", "rules_applied": "supply vs demand share; single-hue dots"},
row_count=len(df), freshness_date=freshness,
query_seconds=q_sec, render_seconds=time.time() - t0 - q_sec)
return {"status": "ok", "rows": len(df)}