"""SOURCE render logic for TI-RC-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_RC_01(reg: dict) -> dict:
"""The Demand 10 (weekly) - top-10 US shows by demand with WoW movement + streaks (M053)."""
chart_id = "TI-RC-01"
output_dir = THIS_DIR / chart_id / ROLE
conn = lib.connect_readonly()
t0 = time.time()
df = lib.run_query(conn, """
SELECT r.imdb_id, r.di_rank, b.primary_title, r.demand_score_7d::numeric AS score, r.reach_7d_avg::numeric AS viewers,
r.cred::numeric AS cred, r.rank_delta_7d, r.prev_week_rank, COALESCE(r.catalog_surge, false) AS catalog_surge,
r.weeks_at_rank1, r.weeks_in_top10, COALESCE(r.is_new_title, false) AS is_new, r.file_date AS freshness_date
FROM metrics_demand_rank_history r JOIN categorization_imdb_title_basics b USING (imdb_id)
WHERE r.file_date = (SELECT MAX(file_date) FROM metrics_demand_rank_history)
AND r.geo_code = 'US' AND r.title_type = 'shows'
ORDER BY r.di_rank LIMIT 10;
""")
# transparency lane (no silent exclusions): biggest RAW-panel shows the corroboration rule keeps off the bars
side = lib.run_query(conn, """
SELECT b.primary_title, t.reach, t.cred::numeric AS cred
FROM title_metrics_daily t JOIN categorization_imdb_title_basics b USING (imdb_id)
WHERE t.geo_code = 'US' AND b.title_type IN ('tvSeries','tvMiniSeries')
AND t.file_date = (SELECT MAX(file_date) FROM metrics_demand_rank_history)
ORDER BY t.reach DESC LIMIT 10;
""")
ph = lib.run_query(conn, "SELECT degraded FROM metrics_panel_health WHERE file_date = (SELECT MAX(file_date) FROM metrics_demand_rank_history);")
panel_degraded = bool(ph["degraded"].iloc[0]) if len(ph) else False
# Tenure derived from the rank history itself (weekly samples at the board's own cadence): the
# upstream weeks_in_top10 field contradicts prev_week_rank on re-entry rows (the Your Friends &
# Neighbors precedent: prev_week_rank=10 yet weeks_in_top10=1), so it never reaches the surface.
hist = lib.run_query(conn, """
WITH d AS (SELECT MAX(file_date) AS d0 FROM metrics_demand_rank_history)
SELECT DISTINCT r.imdb_id, ((SELECT d0 FROM d) - r.file_date) / 7 AS wks_back
FROM metrics_demand_rank_history r, d
WHERE r.geo_code = 'US' AND r.title_type = 'shows' AND r.di_rank <= 10
AND (d.d0 - r.file_date) % 7 = 0 AND r.file_date >= d.d0 - 730;
""")
q_sec = time.time() - t0
conn.close()
if df.empty:
lib.needs_spec_review(output_dir, chart_id, reg.get("spec_file", "specs/16_recurring.md"),
reg.get("pc_id", "PC-RC-01"), "No demand-rank history")
write_shim(output_dir, chart_id); return {"status": "needs_spec_review"}
df["score"] = df["score"].astype(float); df["viewers"] = df["viewers"].astype(float)
df["di_rank"] = df["di_rank"].astype(int)
df["is_new"] = df["is_new"].astype(bool)
# consecutive weekly on-board samples, counting back from this week (self-consistent with the
# displayed week-over-week moves, which also compare to 7 days earlier)
weeks_by_id = {}
for iid, g in hist.groupby("imdb_id"):
got = set(int(w) for w in g["wks_back"])
w = 0
while w in got:
w += 1
weeks_by_id[iid] = w
df["weeks_on_board"] = df["imdb_id"].map(weeks_by_id).fillna(1).astype(int).clip(lower=1)
# rendered debut flag: a genuinely NEW title in its FIRST week on the board (the upstream
# is_new_title flag alone stays true into week 2+ - the Spider-Noir precedent)
df["debut"] = df["is_new"] & (df["weeks_on_board"] <= 1)
df = df.rename(columns={"is_new": "is_new_title_upstream"})
freshness = str(df["freshness_date"].iloc[0])
def chip(delta, prev, debut):
if debut: return "NEW", lib.ACCENT # first week on the board only
if prev is None or pd.isna(prev): return "BACK", lib.GREEN # returned to the chart from off-chart
d = int(delta) if pd.notna(delta) else 0
if d > 0: return f"+{d}", lib.GREEN
if d < 0: return f"{d}", lib.RED
return "hold", lib.MUTED
deltas = [int(x) if pd.notna(x) else -10 ** 6 for x in df["rank_delta_7d"]]
max_delta = max(deltas)
mover_rank = int(df.iloc[deltas.index(max_delta)]["di_rank"]) if max_delta >= 5 else None
# A true debut (a NEW title in its first week on the board) inside the Top 10 is the purest breakout.
new_in_top = df[df["debut"] & (df["di_rank"] <= 10)].sort_values("di_rank")
debut_rank = int(new_in_top.iloc[0]["di_rank"]) if len(new_in_top) else None
# the slide story: last week's #1 no longer on top
faller = df[(df["prev_week_rank"] == 1) & (df["di_rank"] > 1)]
leader = df.iloc[0]
leader_took_top = pd.notna(leader["prev_week_rank"]) and int(leader["prev_week_rank"]) != 1
streak = int(leader["weeks_at_rank1"]) if pd.notna(leader["weeks_at_rank1"]) else 0
plot = df.sort_values("di_rank", ascending=False).reset_index(drop=True) # rank 10..1 -> #1 on top
plot["adj_factor"] = plot["score"] / plot["viewers"].clip(lower=1) # combined adjustment (corroboration x composition)
ticks, texts, colors = [], [], []
any_entry_wk1 = False # an established title in its first week on the board (no NEW chip)
for _, r in plot.iterrows():
rk = int(r["di_rank"]); title = str(r["primary_title"])
debut = bool(r["debut"])
ch, cc = chip(r["rank_delta_7d"], r["prev_week_rank"], debut)
is_lead = rk == 1; is_mover = mover_rank is not None and rk == mover_rank
ticks.append(f"{rk}. {title}" if (is_lead or is_mover or debut) else f"{rk}. {title}")
# catalog badge in purple: amber is reserved for the NEW-debut chip only
badge = f" CATALOG" if bool(r.get("catalog_surge", False)) else ""
wk = int(r["weeks_on_board"])
# ONE tenure grammar for every row: grey '· N wk(s)' = straight weeks in the Top 10, derived
# from the rank history itself (self-consistent with the printed move, which is also vs 7 days
# ago). Debuts skip it (the amber NEW chip already says 'first week'); nothing else is omitted.
if debut or wk <= 0:
tenure = ""
else:
tenure = f" · {wk} wk{'s' if wk != 1 else ''}"
if wk == 1:
any_entry_wk1 = True
texts.append(f"{r['score']:,.0f} {ch}{tenure}{badge}")
colors.append(lib.NAVY if is_lead else (lib.ACCENT if debut else (lib.GREEN if is_mover else lib.PALE_BLUE)))
fig = go.Figure()
fig.add_trace(go.Bar(
x=plot["score"].astype(float), y=ticks, orientation="h",
marker=dict(color=colors, line=dict(width=0)),
text=texts, textposition="outside", textfont=dict(size=11, color=lib.MUTED),
cliponaxis=False, showlegend=False,
customdata=plot[["viewers", "adj_factor"]].values,
hovertemplate="%{y}
Demand Score %{x:,.0f} (weighted) · %{customdata[0]:,.0f} avg daily panel viewers",
))
fig.update_xaxes(range=[0, float(plot["score"].max()) * 1.22])
if streak >= 2:
fig.add_annotation(x=float(leader["score"]), y=ticks[-1], xref="x", yref="y",
text=f"{streak} straight weeks at #1", showarrow=False,
xanchor="left", xshift=4, yshift=-14, font=dict(size=9.5, color=lib.MUTED))
if streak >= 2:
lead_part = f"{leader['primary_title']} runs its {_ordinal(streak)} straight week at #1"
else:
lead_part = f"{leader['primary_title']} is America's most-watched show this week"
if leader_took_top and len(faller):
f0 = faller.iloc[0]
wk_f = int(f0["weeks_on_board"])
wk_clause = f" in week {wk_f}" if (bool(f0["is_new_title_upstream"]) and wk_f >= 1) else ""
takeaway = (f"{leader['primary_title']} takes #1 on the US show chart, while {f0['primary_title']} slides "
f"from last week's top spot to #{int(f0['di_rank'])}{wk_clause}")
elif debut_rank is not None:
d0 = new_in_top.iloc[0]
takeaway = f"{lead_part}, while {d0['primary_title']} debuts straight into the Top {((debut_rank - 1) // 5 + 1) * 5}"
elif mover_rank is not None:
mv = df.iloc[deltas.index(max_delta)]; pv = mv["prev_week_rank"]
if pd.notna(pv):
takeaway = f"{lead_part}, while {mv['primary_title']} storms in from #{int(pv)} to #{int(mv['di_rank'])}"
else:
takeaway = f"{lead_part}, while {mv['primary_title']} returns to the top 10"
else:
takeaway = f"{lead_part}, as the chart holds steady week over week"
# Subtitle = TWO sentences max: conditional encodings (NEW chip / wk-1 entry, only when drawn
# this week) join the second sentence as semicolon clauses instead of extra sentences.
what = ("The 10 most-watched current shows in the US, limited series included, ranked by Demand "
"Score, our measure of weekly at-home audience. Row tags: the move since last week "
"(green climbed, red slipped, grey held), then straight weeks in the Top 10")
if debut_rank is not None:
what += "; an amber NEW chip = a new title in its first week on the board"
if any_entry_wk1:
what += "; a '1 wk' tag = an established title that just cracked the Top 10"
what += "."
why = ("This is the show chart to check every week. A multi-week run at #1 signals a real cultural lock-in, not a "
"one-week blip; a title leaping into the top 10 is the breakout to ride before everyone catches on.")
cautions = ["CL-006 US viewer panel, not census"]
# corroboration disclosure (no silent exclusions) folded INTO the one trust line as a COMPACT
# clause - no named-title editorial (that ran the trust line over budget)
pend = side[(side["cred"].isna()) | (side["cred"].astype(float) < 0.5)]
if len(pend):
cautions.append(f"{len(pend)} heavily-watched title{'s' if len(pend) != 1 else ''} await outside corroboration and sit off the board")
if panel_degraded:
cautions.append("panel volume atypical on this date; scores use a 7-day basis")
footer = lib.assemble_footer(pc_id=reg.get("pc_id", "PC-RC-01"),
caution_labels=cautions,
freshness_date=freshness, sources=["metrics_demand_rank_history"],
extra="shows incl. limited series · refreshed weekly · Demand Score decoded at METH-03")
fig.update_layout(**lib.layout_v2(takeaway, what, width=1180, height=720),
xaxis=lib.standard_xaxis("", showticklabels=False, showgrid=False),
yaxis=lib.standard_yaxis(showgrid=False))
lib.add_why_and_footer(fig, why, footer)
# sidecar hygiene: the upstream weeks_in_top10 column contradicts the table's own prev_week_rank
# on re-entry rows (verified again 2026-06-12), and the raw is_new_title flag stays true past a
# title's debut week - neither known-bad column ships; the derived weeks_on_board / debut
# columns (what the chart actually renders) carry the same intent correctly.
lib.save_csv(df.drop(columns=[c for c in ("weeks_in_top10", "is_new_title_upstream") if c in df.columns]), output_dir)
lib.export_chart(fig, output_dir, width=1180, height=720)
what_plain = re.sub(r"?b>", "", what)
lesson = {"reader_title": takeaway, "reader_subtitle": what_plain, "claim_strength": "strong",
"rules_applied": (f"M053 top-10; leader streak={streak}w; mover delta={max_delta if mover_rank else 0}; "
"tenure = consecutive weekly top-10 samples from the rank history (upstream weeks_in_top10 "
"and is_new_title excluded from the sidecar: inconsistent on re-entry/week-2 rows)")}
lib.write_lesson_design(output_dir, chart_id, f"# {chart_id}\n\n{takeaway}\n\n{what_plain}\n")
write_shim(output_dir, chart_id)
lib.write_manifest(output_dir, chart_id, "ok", reg.get("spec_file", "specs/16_recurring.md"),
pc_id=reg.get("pc_id", "PC-RC-01"),
intended_lesson_from_spec="Weekly Demand 10 with WoW movement + streaks (M053)",
rendered_lesson=lesson, row_count=len(df), freshness_date=freshness,
query_seconds=q_sec, render_seconds=time.time() - t0 - q_sec)
return {"status": "ok", "rows": len(df)}