"""SOURCE render logic for TI-D-11 (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_D_11(reg: dict) -> dict:
"""Geo Breadth vs Intensity Scatter - n_effective vs total DI for top titles."""
chart_id = "TI-D-11"
output_dir = THIS_DIR / chart_id / ROLE
conn = lib.connect_readonly()
t0 = time.time()
# Worldwide-framed chart → geo_code='WORLD' only (the old SUM over all geo rows double-counted US + WORLD).
# Demand Score basis for the audience-size axis and the top-500 selection.
df = lib.run_query(conn, """
WITH latest AS (SELECT MAX(file_date) AS file_date FROM title_metrics_daily),
latest_g AS (SELECT MAX(file_date) AS file_date FROM metrics_geographic_breadth_daily),
total_demand AS (
SELECT imdb_id, demand_score::numeric AS total_demand FROM title_metrics_daily JOIN latest USING (file_date)
WHERE demand_score IS NOT NULL AND geo_code='WORLD'
)
SELECT t.imdb_id, t.total_demand, g.n_effective::numeric AS n_effective,
(SELECT file_date FROM latest) AS freshness_date
FROM total_demand t JOIN metrics_geographic_breadth_daily g
ON g.imdb_id = t.imdb_id AND g.file_date = (SELECT file_date FROM latest_g)
WHERE g.n_effective IS NOT NULL
ORDER BY t.total_demand DESC LIMIT 500;
""")
q_sec = time.time() - t0
if df.empty:
lib.needs_spec_review(output_dir, chart_id, reg["spec_file"], reg["pc_id"], "No breadth-intensity data")
conn.close(); 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 = lib.drop_demand_orphans(df, conn) # hide in-theaters / not-yet-streaming demand-orphans
titles = lib.resolve_titles(conn, df["imdb_id"].tolist())
conn.close()
df = df.merge(titles[["imdb_id", "display_title"]], on="imdb_id", how="left")
df["display_title"] = df["display_title"].fillna(df["imdb_id"])
df["total_demand"] = df["total_demand"].astype(float); df["n_effective"] = df["n_effective"].astype(float)
# Correlation
corr = df[["n_effective", "total_demand"]].corr().iloc[0, 1]
# Top-5 outliers by demand
top5 = df.nlargest(5, "total_demand")
fig = go.Figure()
fig.add_trace(go.Scatter(x=df["n_effective"], y=df["total_demand"], mode="markers",
marker=dict(size=7, color=lib.PALE_BLUE, opacity=0.6),
text=df["display_title"], name="Top 500",
hovertemplate="%{text}
effective country reach %{x:.1f}
Demand Score %{y:,.0f}",
showlegend=False))
# Position-aware label sides: text always extends INTO the plot (a left-edge dot labels rightward,
# never off-canvas), and two labels stacked at the same x/y split top/bottom to stay unambiguous.
top5 = top5.sort_values("n_effective").reset_index(drop=True)
x_span = float(df["n_effective"].max() - df["n_effective"].min()) or 1.0
x_mid = float(df["n_effective"].min()) + x_span / 2
positions = []
for i, r in top5.iterrows():
side = "right" if float(r["n_effective"]) < x_mid else "left"
vert = "top"
if i > 0:
prev = top5.iloc[i - 1]
if (abs(float(r["n_effective"]) - float(prev["n_effective"])) < 0.06 * x_span
and abs(np.log10(float(r["total_demand"])) - np.log10(float(prev["total_demand"]))) < 0.18
and positions[-1].startswith("top")):
vert = "bottom"
positions.append(f"{vert} {side}")
fig.add_trace(go.Scatter(x=top5["n_effective"], y=top5["total_demand"], mode="markers+text",
marker=dict(size=12, color=lib.NAVY, line=dict(width=1, color="white")),
text=[lib.ellipsize(t, 25) for t in top5["display_title"]],
textposition=positions,
textfont=dict(size=9, color=lib.INK),
showlegend=False))
# headline scoped to the chart's own universe: the sample is selected BY demand, so the claim
# cannot generalize to all titles (range restriction) - say whose law it is
title_lesson = (f"Among the {len(df)} biggest titles, going global and being big are different things - "
"country reach barely predicts total demand")
what = (f"Each dot = one of the {len(df)} most-watched titles: right = its effective country reach (how many markets it has real "
"traction in); up = its total demand (log scale). Dark dots = the 5 biggest titles by demand.")
why = ("It debunks 'go wide to win.' Some giant titles are near-domestic; some globe-spanners are mid-sized. Reach and scale are separate "
"levers - a title can be a global traveler OR a domestic blockbuster, and you market each kind differently.")
fig.update_layout(**lib.layout_v2(title_lesson, what, width=1200, height=720),
xaxis=lib.standard_xaxis("Effective country reach (markets with real traction) →"),
yaxis=lib.standard_yaxis("", type="log"),
)
lib.set_log_axis(fig, "y", "Worldwide Demand Score ↑")
lib.add_why_and_footer(fig, why, scope_footer(lib.assemble_footer("PC-D-11",
["CL-006 viewer panel (distinct viewers), not census"], freshness,
["title_metrics_daily", "metrics_geographic_breadth_daily"],
f"top {len(df)} titles by worldwide Demand Score; reach is capped by where a title is available; "
f"reach-demand correlation within this sample r={corr:.2f}")))
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-D-11",
intended_lesson_from_spec="Breadth vs intensity",
rendered_lesson={"reader_title": title_lesson, "reader_subtitle": what,
"claim_strength": "strong", "rules_applied": "top 500 by worldwide Demand Score"},
row_count=len(df), freshness_date=freshness,
query_seconds=q_sec, render_seconds=time.time() - t0 - q_sec)
return {"status": "ok", "rows": len(df)}