"""SOURCE render logic for TI-F-06 (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_F_06(reg: dict) -> dict:
"""Visitors vs Residents - median longest unbroken stay in the US daily show top-100, by IMDb
rating band. ONE tenure definition throughout (longest consecutive run). 1,000-vote floor
disclosed; window-capped at the chart epoch so the residents' gap is conservative; the
franchise-IP exception is owned on the face (the shakiest point draws the loudest rebuttal,
so we print it first). Callout selection excludes Animation carriers per the de-skew rule."""
chart_id = "TI-F-06"
output_dir = THIS_DIR / chart_id / ROLE
conn = lib.connect_readonly()
t0 = time.time()
df = lib.run_query(conn, """
WITH t100 AS (
SELECT imdb_id, file_date,
file_date - (DENSE_RANK() OVER (PARTITION BY imdb_id ORDER BY file_date))::int AS grp
FROM metrics_demand_rank_history
WHERE geo_code = 'US' AND title_type = 'shows' AND di_rank <= 100
), runs AS (
SELECT imdb_id, grp, COUNT(*) AS run_len FROM t100 GROUP BY imdb_id, grp
), per_title AS (
SELECT imdb_id, MAX(run_len) AS longest_run, SUM(run_len) AS total_days
FROM runs GROUP BY imdb_id
)
SELECT p.imdb_id, b.primary_title, p.longest_run::int, p.total_days::int,
r.average_rating::numeric AS rating, r.num_votes,
EXISTS (SELECT 1 FROM categorization_imdb_genres g
WHERE g.imdb_id = p.imdb_id AND g.genre = 'Animation') AS is_animation
FROM per_title p
JOIN categorization_imdb_title_basics b USING (imdb_id)
JOIN categorization_imdb_ratings r USING (imdb_id)
WHERE r.num_votes >= 1000;
""")
win = lib.run_query(conn, """
SELECT MIN(file_date) AS d0, MAX(file_date) AS d1, COUNT(DISTINCT file_date) AS days
FROM metrics_demand_rank_history WHERE geo_code = 'US' AND title_type = 'shows';
""")
q_sec = time.time() - t0
conn.close()
if df.empty or win.empty:
lib.needs_spec_review(output_dir, chart_id, reg["spec_file"], reg["pc_id"], "No top-100 tenure data")
write_shim(output_dir, chart_id); return {"status": "needs_spec_review"}
df["rating"] = df["rating"].astype(float)
df["longest_run"] = df["longest_run"].astype(int); df["total_days"] = df["total_days"].astype(int)
epoch_days = int(win["days"].iloc[0]); d0, d1 = win["d0"].iloc[0], win["d1"].iloc[0]
freshness = str(d1)
bands = [("under 6.5", 0.0, 6.5), ("6.5 to 7", 6.5, 7.0), ("7 to 7.5", 7.0, 7.5),
("7.5 to 8", 7.5, 8.0), ("8 to 8.5", 8.0, 8.5), ("8.5 and up", 8.5, 11.0)]
rows = []
for lab, lo, hi in bands:
sub = df[(df["rating"] >= lo) & (df["rating"] < hi)]
# sidecar carries ONLY the plotted metric (median longest unbroken stay) per band, so the
# file is unambiguous about what the bars show; the alternate tenure measure (total days on
# chart) survives on the per-show exemplar rows below, where it is clearly labeled per title
rows.append({"rating_band": lab, "n_shows": len(sub),
"median_longest_stay_days": float(sub["longest_run"].median()) if len(sub) else float("nan")})
agg = pd.DataFrame(rows)
if agg["n_shows"].min() < 8:
lib.add_warning(f"thin band: min n={int(agg['n_shows'].min())}")
# Exemplars, all rule-derived (never hardcoded; tables rewrite nightly).
# Tourists: panned AND genuinely short-stay. The old "lowest-rated head(2)" rule could surface a
# low-rated title that nonetheless OUTLASTED the band median (e.g. a 35-day stay), which does not
# illustrate "visits the chart". So gate on a SHORT stay first: low-rated (<6.5), recognizable
# (50K+ votes), AND a longest run at or under the panned band's own median, then take the two
# lowest-rated of those (the recognizable flops that truly came and went).
tour_band_median = float(agg.loc[agg["rating_band"] == "under 6.5", "median_longest_stay_days"].iloc[0]) \
if (agg["rating_band"] == "under 6.5").any() and pd.notna(
agg.loc[agg["rating_band"] == "under 6.5", "median_longest_stay_days"].iloc[0]) else 14.0
tour_pool = df[(df["rating"] < 6.5) & (df["num_votes"] >= 50000)
& (df["longest_run"] <= tour_band_median)]
# graceful fallback: if the short-stay gate is too tight to yield two, fall back to shortest-stay
tour = (tour_pool.sort_values("rating").head(2) if len(tour_pool) >= 2
else df[(df["rating"] < 6.5) & (df["num_votes"] >= 50000)].sort_values("longest_run").head(2))
# Residents: longest unbroken runs in the 8.5+ band, Animation carriers excluded from callout copy.
resi = df[(df["rating"] >= 8.5) & (~df["is_animation"].astype(bool))].sort_values(
"longest_run", ascending=False).head(2)
# The franchise exception: lowest-rated title that still logged a 100+ day unbroken run.
exc = df[(df["rating"] < 6.5) & (df["longest_run"] >= 100)].sort_values("rating").head(1)
meds = agg["median_longest_stay_days"].tolist()
colors = lib.leader_colors(meds, leader=lib.NAVY, field=lib.PALE_BLUE)
ticks = [f"{r.rating_band}
n={r.n_shows}" for r in agg.itertuples()]
fig = go.Figure(go.Bar(
x=ticks, y=meds, marker=dict(color=colors, line=dict(width=0)),
text=[f"{m:g}" for m in meds], textposition="outside",
textfont=dict(size=12, color=lib.INK), cliponaxis=False, showlegend=False,
customdata=agg["n_shows"], hovertemplate="rated %{x}
median %{y:g} days
%{customdata} shows"))
fig.update_yaxes(range=[0, max(meds) * 1.16])
lo_med, hi_med = meds[0], meds[-1]
# Plain, self-explanatory title (founder 2026-06-19, refined again): a layman gets the point from
# the title ALONE, no special vocabulary. Dropped "panned"/"acclaimed" and the ambiguous verb
# "last" (read as final-days). Lead with the finding in plain words (well-reviewed stay long,
# poorly reviewed are gone fast), then ground it with the two LIVE band medians (rule-based, never
# hardcoded). The H1 still names the PLOTTED metric ('longest unbroken stay') so a bare 'days in
# the US top 100' is not mistaken for total days on chart, a different number the csv also carries.
takeaway = (f"Well-reviewed shows stay on the chart for months; poorly reviewed ones are gone within "
f"days: a median longest unbroken stay of {hi_med:g} days in the US top 100 for shows "
f"rated 8.5 and up, just {lo_med:g} for those rated under 6.5")
# second sentence ADDS information (the vote floor) instead of restating the headline's staircase
what = ("Each bar is the median longest unbroken stay in the top 100 of our daily US show chart, "
"grouped by the show's IMDb rating; n is how many shows each band covers. Only shows with "
"at least 1,000 IMDb ratings are counted, so a handful of votes cannot move a band.")
# correlational phrasing only: the staircase shows acclaim and tenure MOVE TOGETHER ('comes
# with'), it cannot prove acclaim CAUSES tenure ('buys'). Phrasing avoids "each half-point": the
# bottom band ("under 6.5") and the top band ("8.5 and up") are open-ended, not half-point steps,
# so a per-step half-point generalization would overstate the structure at the ends of the scale.
why = ("A rating does not just grade a show, it tracks its shelf life: climbing the rating scale comes with "
"visibly more time on the chart. Launch spikes can be bought; a months-long residency cannot, and "
"that durability is where quality actually pays.")
callout = []
if len(tour) >= 2:
t1, t2 = tour.iloc[0], tour.iloc[1]
callout.append(f"The tourists: {short_title(t1['primary_title'], 30)} (rated {t1['rating']:.1f}) "
f"lasted {int(t1['longest_run'])} days, {short_title(t2['primary_title'], 30)} "
f"({t2['rating']:.1f}) {int(t2['longest_run'])}")
if len(resi) >= 2:
r1, r2 = resi.iloc[0], resi.iloc[1]
r1_phrase = (f"every day of the window, all {int(r1['longest_run']):,}" if int(r1["longest_run"]) >= epoch_days
else f"{int(r1['longest_run']):,} straight")
callout.append(f"The residents: {short_title(r1['primary_title'], 30)} ({r1['rating']:.1f}) "
f"{r1_phrase} days, {short_title(r2['primary_title'], 30)} ({r2['rating']:.1f}) "
f"{int(r2['longest_run']):,} straight")
if len(exc):
e1 = exc.iloc[0]
callout.append(f"The exception: franchise pull can defy reviews, "
f"{short_title(e1['primary_title'], 30)} ({e1['rating']:.1f}) held on "
f"{int(e1['longest_run'])} days, the outlier the medians already price in")
fig.update_layout(**lib.layout_v2(takeaway, what, width=1180, height=780),
xaxis=lib.standard_xaxis("IMDb rating band", showgrid=False),
yaxis=lib.standard_yaxis("median longest unbroken stay, in days"))
if callout:
# faint chip + a small drop from the top so the dense three-line block clears the top
# gridline / 120 axis tick instead of crowding it; sits in the empty upper-left plot band
fig.add_annotation(text="
".join(callout), xref="paper", yref="paper", x=0.015, y=0.93,
xanchor="left", yanchor="top", showarrow=False, align="left",
font=dict(size=11, color=lib.INK),
bgcolor="rgba(255,255,255,0.92)", bordercolor=lib.CHIP_BORDER,
borderwidth=1, borderpad=6)
footer = lib.assemble_footer(
pc_id=reg.get("pc_id", "PC-F-06"),
caution_labels=["chart position reflects weekly Demand Score, our measure of at-home audience (methodology: METH-03)",
"IMDb ratings read as of today, not as aired"],
freshness_date=freshness,
# metric NAMED in the source label (founder terminology; matches the trust line's
# 'weekly Demand Score', never the library's near-bare 'weekly demand rankings' default)
sources=["PeerLogix weekly Demand Score rankings", "categorization_imdb_ratings"],
extra=f"US shows, top 100 daily, window {d0} to {d1} · shows with 1,000+ IMDb ratings")
lib.add_why_and_footer(fig, why, footer)
# data.csv carries the EXEMPLAR rows alongside the band medians: every tenure figure printed
# in the callout (tourists / residents / franchise exception) must be reconstructable from the
# shipped sidecar, not internal-only
exemplar_rows = []
for sub, rt in [(tour, "exemplar_tourist"), (resi, "exemplar_resident"), (exc, "exemplar_franchise_exception")]:
for _, r in sub.iterrows():
exemplar_rows.append({"row_type": rt, "show": str(r["primary_title"]),
"imdb_rating": float(r["rating"]),
"longest_unbroken_stay_days": int(r["longest_run"]),
"total_days_on_chart": int(r["total_days"])})
csv_df = pd.concat([agg.assign(row_type="rating_band"), pd.DataFrame(exemplar_rows)], ignore_index=True)
lib.save_csv(csv_df, output_dir)
lib.export_chart(fig, output_dir, width=1180, height=780)
what_plain = re.sub(r"?b>", "", what)
lesson = {"reader_title": takeaway, "reader_subtitle": what_plain, "claim_strength": "strong",
"rules_applied": ("six-band staircase; tenure = longest consecutive run in US shows top-100 over the "
f"full {epoch_days}-day window; 1,000-vote floor; exemplars rule-derived "
"(tourists = lowest-rated 50K+ votes; residents = longest non-animation runs in 8.5+; "
"exception = lowest-rated with 100+ day run) and shipped as data.csv exemplar 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["spec_file"], pc_id=reg.get("pc_id", "PC-F-06"),
intended_lesson_from_spec="Rating band predicts board tenure: six-band median staircase of longest top-100 stays",
rendered_lesson=lesson, row_count=len(agg), freshness_date=freshness,
query_seconds=q_sec, render_seconds=time.time() - t0 - q_sec,
extra={"epoch_days": epoch_days, "titles_in_pool": len(df),
"tourists": tour["primary_title"].tolist(),
"residents": resi["primary_title"].tolist(),
"franchise_exception": exc["primary_title"].tolist()})
return {"status": "ok", "rows": len(agg)}