"""SOURCE render logic for TI-C-14 (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_14(reg: dict) -> dict: """The Age of Each Streamer's Shelf - per-service Demand Score share by title-vintage band, curated 8 SVOD. De-skewed lane (founder rule 2026-06-12): weights = title_metrics_daily.demand_score over the FULL US universe - NOT the top-500 boards, which truncate exactly the old-catalog tail this chart is about. Per-(title,platform) streaming gate via metrics_platform_canonical raw_name join (flatrate/free/ads only, so rent-or-buy storefront raw_names stay out - the Plex-rentals-class leak).""" chart_id = "TI-C-14" output_dir = THIS_DIR / chart_id / ROLE conn = lib.connect_readonly() t0 = time.time() # Anchor = latest day with a CLEAN panel-health row (a degraded day never anchors a single-day read). df = lib.run_query(conn, r""" WITH anchor AS ( SELECT MAX(file_date) AS fd FROM metrics_panel_health WHERE degraded = false ), elig AS ( -- per-(title,platform) availability gate: subscription/free/ads only, canonical names SELECT DISTINCT cpa.imdb_id, mpc.canonical AS platform FROM categorization_platforms_available cpa JOIN metrics_platform_canonical mpc ON mpc.raw_name = cpa.platforms_available WHERE cpa.country_code = 'US' AND cpa.watch_type IN ('flatrate','free','ads') AND mpc.canonical IN ('Netflix','Prime Video','Hulu','Max','Disney+','Paramount+','Peacock','Apple TV+') ), dem AS ( SELECT t.imdb_id, t.demand_score::numeric AS ds FROM title_metrics_daily t, anchor a WHERE t.file_date = a.fd AND t.geo_code = 'US' AND t.demand_score IS NOT NULL ), meta_year AS ( -- vintage fallback; categorization_metadata has duplicate rows, so aggregate first SELECT imdb_id, MAX(NULLIF(substring(release_year from '\d{4}'), '')::int) AS release_year FROM categorization_metadata WHERE release_year IS NOT NULL GROUP BY imdb_id ), vint AS ( SELECT e.imdb_id, e.platform, d.ds, COALESCE(NULLIF(tb.start_year, 0), my.release_year) AS vintage, tb.title_type, tb.end_year FROM elig e JOIN dem d USING (imdb_id) LEFT JOIN categorization_imdb_title_basics tb USING (imdb_id) LEFT JOIN meta_year my USING (imdb_id) ) SELECT platform, CASE WHEN vintage IS NULL THEN 'unknown' WHEN vintage >= EXTRACT(YEAR FROM (SELECT fd FROM anchor)) - 1 THEN 'b0' WHEN vintage >= EXTRACT(YEAR FROM (SELECT fd FROM anchor)) - 5 THEN 'b1' WHEN vintage >= EXTRACT(YEAR FROM (SELECT fd FROM anchor)) - 10 THEN 'b2' ELSE 'b3' END AS band, SUM(ds) AS band_score, -- end_year 0/NULL = ongoing (recurrent-rule convention): new seasons of old shows SUM(ds) FILTER (WHERE title_type IN ('tvSeries','tvMiniSeries') AND (end_year IS NULL OR end_year = 0)) AS still_airing_score, COUNT(*) AS titles, (SELECT fd FROM anchor) AS file_date FROM vint GROUP BY 1, 2; """) q_sec = time.time() - t0 conn.close() if df.empty: lib.needs_spec_review(output_dir, chart_id, reg["spec_file"], reg["pc_id"], "No shelf-age rows") write_shim(output_dir, chart_id); return {"status": "needs_spec_review"} df["band_score"] = df["band_score"].astype(float) df["still_airing_score"] = pd.to_numeric(df["still_airing_score"], errors="coerce").fillna(0.0) freshness = str(df["file_date"].iloc[0]) yr = int(freshness[:4]) unk_pct = 100.0 * df.loc[df["band"] == "unknown", "band_score"].sum() / max(df["band_score"].sum(), 1e-9) if unk_pct >= 0.1: lib.add_warning(f"unknown-vintage titles hold {unk_pct:.2f}% of weight (excluded from bands)") df = df[df["band"] != "unknown"].copy() tot = df.groupby("platform")["band_score"].sum() n_tot = df.groupby("platform")["titles"].sum() df["share"] = df.apply(lambda r: 100.0 * r["band_score"] / tot[r["platform"]], axis=1) # Oldest band on the LEFT: every shelf's museum block shares the x=0 baseline, so the story # variable (10y+ share) is comparable bar to bar at a glance. yy = lambda y: f"{y % 100:02d}" # Era bands = the sanctioned monochrome SEQUENTIAL ORDINAL ramp (lib contract note): darkest = # oldest, lightest = newest. The same band carries the same fill on every service, so the darkest # block never reads as a per-row leader; navy appears ONLY as the oldest ordinal pole here. BANDS = [("b3", f"{yr - 11} or earlier", lib.NAVY, "white"), ("b2", f"{yr - 10}-{yy(yr - 6)}", lib.BLUE, "white"), ("b1", f"{yr - 5}-{yy(yr - 2)}", lib.PALE_BLUE, lib.INK), ("b0", f"{yr - 1}-{yy(yr)}", lib.SEQ_PALE, lib.INK)] pv = df.pivot_table(index="platform", columns="band", values="share", fill_value=0.0) for k, _, _, _ in BANDS: if k not in pv.columns: pv[k] = 0.0 order = pv.sort_values("b3", ascending=False).index.tolist() # museum first b3rows = df[df["band"] == "b3"].set_index("platform").reindex(order) sa_within = (100.0 * b3rows["still_airing_score"] / b3rows["band_score"].replace(0, pd.NA)).fillna(0.0) ylab = {p: f"{p} · {int(n_tot[p]):,} titles" for p in order} ylabels = [ylab[p] for p in order] # Mandated caveat carried ON the face: the platform whose 10y+ block leans most on still-airing # series (counted by original premiere) gets the clause printed inside its navy block. cav_p = None cand = [(p, float(sa_within[p])) for p in order if float(sa_within[p]) >= 25.0 and float(pv.loc[p, "b3"]) >= 30.0] if cand: cav_p = max(cand, key=lambda t: t[1] * float(pv.loc[t[0], "b3"]))[0] fig = go.Figure() for key, label, col, txtcol in BANDS: vals = [float(pv.loc[p, key]) for p in order] texts = [] for p, v in zip(order, vals): # EVERY slice carries ONLY its band share (one number), so a slice never crowds two figures # into one segment. The still-airing caveat that used to ride INSIDE the museum block (a dense # '51% · 45% ...' run-on that read as one figure at feed scale) is moved OFF the bar to a clean # margin note at that platform's row (C-14 fix). Minimum-slice suppression unchanged: a band # under 4% suppresses its in-slice label so a narrow slice cannot collide with its neighbour # (e.g. Apple TV+'s 0.03% museum block, handled by the structural note instead). texts.append(f"{v:.0f}%" if v >= 4 else "") fig.add_trace(go.Bar( y=ylabels, x=vals, orientation="h", name=f"Premiered {label}", marker=dict(color=col, line=dict(width=0)), text=texts, textposition="inside", insidetextanchor="middle", textangle=0, textfont=dict(size=10.5, color=txtcol), hovertemplate=f"%{{y}}
premiered {label}: %{{x:.1f}}% of its Demand Score that day")) # Still-airing caveat as a CLEAN margin note at the caveat platform's row (off the bar): the platform # whose 10y+ block leans most on still-airing series (counted by original premiere) gets one short line # in the right margin beside its row, so the museum slice itself stays a single-figure label. if cav_p is not None: fig.add_annotation(x=1.004, y=ylab[cav_p], xref="paper", yref="y", xanchor="left", text=f"{sa_within[cav_p]:.0f}% of {cav_p}'s {yr - 11}-or-earlier slice
is series still airing new seasons", showarrow=False, align="left", font=dict(size=9.5, color=lib.MUTED)) # Structural-smallness note for the youngest shelf (rule-based: min 10y+ share under 5%). Explicitly # COUNT-framed ('X of its Y titles') and labeled as a CATALOG-count lens, so it can never be read as # the viewing-SHARE figure the headline/bar quote (those are demand-share, a different denominator). min_p = order[-1] if float(pv.loc[min_p, "b3"]) < 5.0: n3 = int(b3rows.loc[min_p, "titles"]) if pd.notna(b3rows.loc[min_p, "titles"]) else 0 fig.add_annotation(x=1.004, y=ylab[min_p], xref="paper", yref="y", xanchor="left", text=f"by catalog count: only {n3} of its
{int(n_tot[min_p]):,} titles premiered {yr - 11} or earlier", showarrow=False, align="left", font=dict(size=9.5, color=lib.MUTED)) b3v = [float(pv.loc[p, "b3"]) for p in order] p1 = order[0] min_v3 = float(pv.loc[min_p, "b3"]) if lib.near_tie(b3v, fmt=lambda v: f"{v:.0f}"): # Practical, layman-readable title (founder 2026-06-19): lead with the concrete fact, no # museum / news-channel metaphor. "or earlier" (not "or older"): one wording for the oldest # band across title, legend, subtitle and footer, so a strict copy check finds no synonym drift. takeaway = (f"{order[0]} and {order[1]} lean hardest on older titles: each draws about " f"{b3v[0]:.0f}% of its viewing from shows and films that premiered {yr - 11} or earlier") else: # Concrete fact in the title, no metaphor (founder 2026-06-19). CLARITY FIX (C-14 2026-06-19): # the old tail '({min_p}) leans almost entirely new (~0% of its viewing)' was ambiguous - the # bare '~0% of its viewing' read as the service's OVERALL viewing being near zero, not its # OLD-TITLE share. Reworded so the ~0% is unmistakably the {yr-11}-or-earlier (old-title) share: # 'almost none of {min_p}'s viewing comes from titles that premiered {yr-11} or earlier'. When the # old-title share is not effectively zero, state the number against the SAME old-title denominator. if min_v3 < 0.5: min_clause = (f"almost none of {min_p}'s viewing comes from titles that premiered " f"{yr - 11} or earlier") else: min_clause = (f"only {min_v3:.0f}% of {min_p}'s viewing comes from titles that premiered " f"{yr - 11} or earlier") takeaway = (f"{p1} leans hardest on older titles: {b3v[0]:.0f}% of its viewing is from shows and " f"films that premiered {yr - 11} or earlier, while {min_clause}") what = ("Each bar splits the Demand Score a service's US streamable catalog earned in one day by the era its " "titles first premiered, oldest era on the left. Subscription and free-with-ads availability only, " "rentals and purchases are excluded.") why = ("Shelf age is strategy: a service whose attention sits in decade-old titles is selling a library that " "holds viewers between hits, while a service living on this year's premieres has to keep shipping new ones.") footer = drop_census(lib.assemble_footer("PC-C-14", ["vintage = premiere year, still-airing series count by their original premiere", "a title carried by several services counts toward each shelf", "band shares are rounded and may not sum to 100"], freshness, ["title_metrics_daily", "categorization_platforms_available", "categorization_imdb_title_basics"], extra=("Demand Score basis (panel viewing, weighted to reflect the broad US streaming " "audience) · 8 curated subscription services · latest panel day"))) lo = lib.layout_v2(takeaway, what, width=1280, height=640, # traceorder normal: stacked-bar legends reverse by default, which would read # newest-first while the bars read oldest-first legend=dict(orientation="h", yanchor="bottom", y=1.0, x=0, font=dict(size=10), traceorder="normal")) lo["margin"]["r"] = 235 # room for the structural note beside the youngest shelf's row # Tufte minimalism: every visible slice now carries its own percent inside the bar and each row # is a full 100%-stacked bar (stated in the subtitle and decoded by the band legend), so a bottom # 0-100% axis would only re-print numbers the in-bar labels already give. Drop the axis ink # (ticks/grid/title) and let the labeled slices be the scale. fig.update_layout(**lo, barmode="stack", xaxis=lib.standard_xaxis("", range=[0, 100.5], showticklabels=False, showgrid=False), yaxis=lib.standard_yaxis(showgrid=False, categoryorder="array", categoryarray=ylabels[::-1])) lib.add_why_and_footer(fig, why, footer) # Deterministic watermark: 100%-stacked bars leave no interior white space, and the auto-fallback # corner mark collides with the WHY line on this layout - pin it in the (empty) top right margin. fig.add_annotation(name="brand_watermark", text=lib.WATERMARK_TEXT, xref="paper", yref="paper", x=1.005, y=0.99, xanchor="left", yanchor="top", showarrow=False, font=dict(family="Arial, sans-serif", size=12.5, color="#8d94a1"), opacity=0.8) csv_df = df.rename(columns={"band": "premiere_band", "band_score": "band_demand_score", "still_airing_score": "still_airing_series_demand_score", "share": "share_of_platform_demand_score_pct"}) band_names = {k: f"premiered {label}" for k, label, _, _ in BANDS} csv_df["premiere_band"] = csv_df["premiere_band"].map(band_names) lib.save_csv(csv_df, output_dir) lib.export_chart(fig, output_dir, width=1280, 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-C-14", intended_lesson_from_spec="Per-platform demand-weighted shelf age (curated 8 SVOD), de-skewed lane", rendered_lesson={"reader_title": takeaway, "reader_subtitle": what, "claim_strength": "strong", "rules_applied": ("full-universe demand_score weights; per-(title,platform) " "flatrate/free/ads gate via canonical names; DISTINCT " "(imdb_id, canonical); vintage = IMDb start_year fallback " "metadata release_year; band boundaries printed in legend; " "anchored to latest non-degraded panel day; still-airing " "caveat printed when 25%+ of a 30pt+ museum block")}, row_count=len(df), freshness_date=freshness, extra={"unknown_vintage_pct": round(float(unk_pct), 3)}, query_seconds=q_sec, render_seconds=time.time() - t0 - q_sec) return {"status": "ok", "rows": len(df)}