"""SOURCE render logic for TI-A-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_A_05(reg: dict) -> dict:
"""Spike Magnitude Outlier Ladder - distribution of spike ratios with outliers labeled."""
chart_id = "TI-A-05"
output_dir = THIS_DIR / chart_id / ROLE
conn = lib.connect_readonly()
t0 = time.time()
df = lib.run_query(conn, """
SELECT imdb_id, spike_peak_date, ratio::numeric AS ratio
FROM spike_pulse_events
WHERE spike_peak_date >= CURRENT_DATE - INTERVAL '180 days'
AND ratio IS NOT NULL
AND baseline_value >= 20 -- real spikes only (exclude divide-by-near-zero ratio noise)
AND COALESCE((SELECT r.num_votes FROM categorization_imdb_ratings r
WHERE r.imdb_id = spike_pulse_events.imdb_id), 0)
>= CASE WHEN (SELECT bt.title_type FROM categorization_imdb_title_basics bt
WHERE bt.imdb_id = spike_pulse_events.imdb_id) = 'movie'
THEN 3000 ELSE 20000 END; -- type-aware corroboration gate (cred>=0.5: films K=3K, shows K=20K)
""")
q_sec = time.time() - t0
if df.empty:
lib.needs_spec_review(output_dir, chart_id, reg["spec_file"], reg["pc_id"], "No spike events in 180 days")
conn.close(); write_shim(output_dir, chart_id)
return {"status": "needs_spec_review"}
df = lib.drop_demand_orphans(df, conn) # hide in-theaters / not-yet-streaming demand-orphans
df["ratio"] = df["ratio"].astype(float)
p50, p90, p95, p99 = df["ratio"].quantile([0.50, 0.90, 0.95, 0.99])
p50, p90, p95, p99 = float(p50), float(p90), float(p95), float(p99)
outliers = df.nlargest(15, "ratio").copy()
titles = lib.resolve_titles(conn, outliers["imdb_id"].tolist())
conn.close()
outliers = outliers.merge(titles[["imdb_id", "display_title"]], on="imdb_id", how="left")
outliers["display_title"] = outliers["display_title"].fillna(outliers["imdb_id"])
# Manual log-bin histogram (Plotly's Histogram doesn't combine well with log-x)
log_min = np.log10(max(df["ratio"].min(), 1.0))
log_max = np.log10(df["ratio"].max())
bin_edges = np.logspace(log_min, log_max, 40)
counts, edges = np.histogram(df["ratio"], bins=bin_edges)
bin_centers = (edges[:-1] + edges[1:]) / 2
bin_widths = edges[1:] - edges[:-1]
n_tail = int((df["ratio"] >= p99).sum())
pct_under_3 = (df["ratio"] < 3.0).mean()
fig = go.Figure()
fig.add_trace(go.Bar(
x=bin_centers, y=counts, width=bin_widths,
marker=dict(color=lib.PALE_BLUE, line=dict(width=0)),
showlegend=False,
hovertemplate="Spikes around %{x:.1f}x: %{y} events",
))
min_ratio = float(df["ratio"].min())
def ref_x_label(v: float) -> str:
# the label must carry the line's TRUE position: '2x' on a line at 2.2 reads as misplaced
# against the 2x tick. Integers (and big values, where a 1% offset is invisible on the log
# scale) print whole; everything else keeps a decimal.
return f"{v:.0f}x" if (abs(v - round(v)) < 0.05 or v >= 10) else f"{v:.1f}x"
takeaway = f"Most breakouts are small - a 'typical' spike is just {ref_x_label(p50)}, and only the rarest 1% top {ref_x_label(p99)}"
what = (f"How common each spike size is across {len(df):,} spikes in the last 180 days. "
"Spike size compares a title's peak day to its own normal level - a 3x spike tripled it "
f"(the detector flags sustained surges starting around {min_ratio:.1f}x, so sizes below 2x appear too).")
why = (f"This is the yardstick for judging any single breakout. A 3-5x spike is ordinary "
f"(half are smaller); a {ref_x_label(p99)}+ spike is genuinely rare. It tells you whether a title's surge is routine or a real event worth acting on.")
footer = lib.assemble_footer(pc_id="PC-A-03",
caution_labels=[],
freshness_date=str(df["spike_peak_date"].max()),
sources=["spike_pulse_events"],
extra="180 days · real spikes only (baseline 20+, corroborated titles) · log scale on x-axis")
fig.update_layout(**lib.layout_v2(takeaway, what, width=1160, height=640),
yaxis=lib.standard_yaxis("Number of spikes"),
bargap=0.02)
lib.set_log_axis(fig, "x", "Spike size · how many times above normal (log scale)",
tickvals=[1, 2, 3, 5, 10, 20, 30, 50, 80],
ticktext=["1x", "2x", "3x", "5x", "10x", "20x", "30x", "50x", "80x"])
# Percentile markers with plain-language meaning: DATA-coordinate dashed lines drawn at the
# actual 2x/5x/22x values (shape-based ref lines on this log axis land far-left of their
# labels - raw-vs-log10 quirk - verified by pixel-mapping against the axis ticks).
y_top = float(counts.max()) * 1.02
for v, label in [(p50, f"Typical (median)
{ref_x_label(p50)}"),
(p90, f"Top 10% start
{ref_x_label(p90)}"),
(p99, f"Rarest 1%
{ref_x_label(p99)}+")]:
data_refline(fig, x=v, lo=0, hi=y_top)
fig.add_annotation(x=np.log10(v), y=1.0, yref="paper", xref="x",
text=label, showarrow=False, yanchor="bottom",
font=dict(size=10, color=lib.MUTED), align="center")
# Name the single biggest outlier - it answers the question the bare tail raises
top_out = outliers.sort_values("ratio", ascending=False).iloc[0]
max_ratio = float(top_out["ratio"])
fig.add_annotation(x=np.log10(max_ratio), y=0, xref="x", yref="y",
text=f"{lib.ellipsize(top_out['display_title'], 28)} · {max_ratio:.0f}x",
showarrow=True, arrowhead=2, arrowcolor=lib.MUTED, ax=-30, ay=-40,
font=dict(size=10, color=lib.INK))
lib.add_why_and_footer(fig, why, footer)
lesson = {"reader_title": takeaway, "reader_subtitle": what,
"claim_strength": "strong", "rules_applied": f"180-day; median={p50:.0f}x, P99={p99:.0f}x"}
hist_csv = pd.DataFrame({"bin_low": edges[:-1], "bin_high": edges[1:], "n_spikes": counts})
lib.save_csv(hist_csv, output_dir)
outliers.to_csv(output_dir / "outliers.csv", index=False)
lib.export_chart(fig, output_dir, width=1160, height=640)
lib.write_lesson_design(output_dir, chart_id, f"# {chart_id}\n\n{takeaway}\n\n{what}\n")
write_shim(output_dir, chart_id)
lib.write_manifest(output_dir, chart_id, "ok", reg["spec_file"], pc_id="PC-A-03",
intended_lesson_from_spec="Spike ratio distribution + outliers",
rendered_lesson=lesson, row_count=len(df),
freshness_date=str(df["spike_peak_date"].max()),
query_seconds=q_sec, render_seconds=time.time() - t0 - q_sec)
return {"status": "ok", "rows": len(df)}