"""SOURCE render logic for TI-Other-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_Other_05(reg: dict) -> dict: """Time-Since-Release Cohort - same as TI-I-04 but cohort grouped.""" chart_id = "TI-Other-05" output_dir = THIS_DIR / chart_id / ROLE conn = lib.connect_readonly() t0 = time.time() df = lib.run_query(conn, """ WITH latest AS (SELECT MAX(file_date) AS file_date FROM title_metrics_daily WHERE demand_score IS NOT NULL AND geo_code='US'), rel AS (SELECT imdb_id, release_date FROM categorization_release_dates_all WHERE release_date IS NOT NULL) SELECT CASE WHEN EXTRACT(YEAR FROM AGE(l.file_date, r.release_date)) < 1 THEN '< 1 yr' WHEN EXTRACT(YEAR FROM AGE(l.file_date, r.release_date)) < 3 THEN '1-3 yr' WHEN EXTRACT(YEAR FROM AGE(l.file_date, r.release_date)) < 5 THEN '3-5 yr' WHEN EXTRACT(YEAR FROM AGE(l.file_date, r.release_date)) < 10 THEN '5-10 yr' WHEN EXTRACT(YEAR FROM AGE(l.file_date, r.release_date)) < 20 THEN '10-20 yr' ELSE '20+ yr' END AS cohort, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY t.demand_score::numeric) AS median_demand, AVG(t.demand_score::numeric) AS mean_demand, COUNT(DISTINCT t.imdb_id) AS titles, (SELECT file_date FROM latest) AS file_date FROM title_metrics_daily t JOIN latest l USING (file_date) JOIN rel r USING (imdb_id) WHERE t.demand_score IS NOT NULL AND t.demand_score > 0 AND t.geo_code='US' AND r.release_date <= l.file_date GROUP BY cohort; """) 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 cohort data") write_shim(output_dir, chart_id); return {"status": "needs_spec_review"} # BASIS, STATED OUT LOUD: the launch premium is genuinely a HIT-WEIGHTED-MEAN story (verified on # live data: cohort MEDIANS are 0.5-1.6 at every age and the 20+yr median even tops year one's - # the typical title barely registers whatever its age). So the bars keep the mean, the headline # SAYS mean, and the house median ships alongside ON the canvas so the typical title stays honest. df["median_demand"] = df["median_demand"].astype(float) df["mean_demand"] = df["mean_demand"].astype(float); df["titles"] = df["titles"].astype(int) order = ['< 1 yr', '1-3 yr', '3-5 yr', '5-10 yr', '10-20 yr', '20+ yr'] df["sort"] = df["cohort"].map({c: i for i, c in enumerate(order)}) df = df.sort_values("sort").reset_index(drop=True) freshness = str(df["file_date"].iloc[0]) # the story is cliff-then-floor: only year one is special, so only year one gets emphasis ink shades = [lib.NAVY] + [lib.PALE_BLUE] * (len(df) - 1) fig = go.Figure() fig.add_trace(go.Bar(x=df["cohort"], y=df["mean_demand"], marker=dict(color=shades), text=[f"{v:,.0f}" for v in df["mean_demand"]], textposition="outside", textfont=dict(size=11, color=lib.INK), cliponaxis=False, showlegend=False, customdata=df[["titles", "median_demand"]], name="hit-weighted average per title", hovertemplate="%{x} old
hit-weighted average Demand Score %{y:,.0f} · median title %{customdata[1]:,.1f} · %{customdata[0]:,} titles")) fig.add_trace(go.Scatter(x=df["cohort"], y=df["median_demand"], mode="markers", marker=dict(symbol="diamond", size=9, color=lib.INK, line=dict(width=1, color="white")), name="median (typical) title", showlegend=True, hovertemplate="%{x} old
median title's Demand Score %{y:,.1f}")) peak = df.loc[df["mean_demand"].idxmax()]; oldest = df.iloc[-1] ratio = peak["mean_demand"] / oldest["mean_demand"] if oldest["mean_demand"] > 0 else 0 fmt_mult = lambda v: f"~{v:.1f}x" if v < 10 else f"~{v:.0f}x" d0 = float(df[df["cohort"] == '< 1 yr']["mean_demand"].iloc[0]) if (df["cohort"] == '< 1 yr').any() else 0 d13 = float(df[df["cohort"] == '1-3 yr']["mean_demand"].iloc[0]) if (df["cohort"] == '1-3 yr').any() else 0 if d0 > 0 and d13 > 0: # no leader line: a vertical arrow from the note to the bar top struck straight through the # bar's own value label; a yshift floats the note just above the label instead fig.add_annotation(x='1-3 yr', y=d13, text=f"the cliff: {d13 / d0 - 1:.0%} vs year one", showarrow=False, yshift=26, yanchor="bottom", font=dict(size=10.5, color=lib.INK), bgcolor="rgba(255,255,255,0.85)") # the median diamonds hug the axis floor at every age - point at them once so the contrast # with the bars reads as the finding it is. The box height is COMPUTED to clear the tallest # non-year-one bar plus its value label (the old fixed -46px offset sat the white box on the # 5-10 yr bar's own label and grazed its neighbours). mid_i = len(df) // 2 peak_mean = float(df["mean_demand"].max()) side_frac = (float(df.loc[df["mean_demand"] < peak_mean, "mean_demand"].max()) / peak_mean if (df["mean_demand"] < peak_mean).any() and peak_mean > 0 else 0.3) ay_px = -(side_frac * 315.0 + 52) # 315px ~ the plot band on this 560px canvas fig.add_annotation(x=df["cohort"].iloc[mid_i], y=float(df["median_demand"].iloc[mid_i]), text="median title (diamonds): only a sliver at every age -
the year-one premium belongs to the hits", showarrow=True, arrowhead=2, ax=40, ay=ay_px, font=dict(size=10, color=lib.INK), bgcolor="rgba(255,255,255,0.88)") # ONE-BREATH HEADLINE with the multiple's basis NAMED (it is computed vs the oldest cohort, # which a bare 'a catalog title' left unguessable); the hit-driven caveat lives in the # subtitle, the diamonds note and WHY, not in the H1 title_lesson = (f"Averaged per title, a new release pulls {fmt_mult(ratio)} the attention of a {oldest['cohort']}-old " f"catalog title, and the premium burns off within 1-3 years") what = ("Bars = the hit-weighted average Demand Score per title, grouped by how long ago the title was released; " "diamonds = the median title. Only the first year is special for the bars; the diamonds show the typical title sits near the floor at every age.") why = ("A snapshot, not one title's journey: it prices the trade-off between chasing launch heat and owning a deep catalog " "whose floor never quite goes away. The bars are hit-weighted - a few current mega-hits carry much of the year-one " "premium - so the median diamonds keep the typical title honest.") fig.update_layout(**lib.layout_v2(title_lesson, what, width=1150, height=560, legend=dict(orientation="h", yanchor="bottom", y=1.0, xanchor="right", x=1, font=dict(size=10))), xaxis=lib.standard_xaxis("Time since release"), yaxis=lib.standard_yaxis("Demand Score per title", tickformat=",d")) # extra trimmed so the Sources line fits ONE wrap line at this width (the long version hung a # lone '· ref PC-Other-05' at the start of a third footer line) lib.add_why_and_footer(fig, why, lib.assemble_footer("PC-Other-05", ["CL-006 viewer panel (distinct viewers), not census", "bars are hit-weighted means (a few mega-hits carry the newest cohort); diamonds = the median title"], freshness, ["title_metrics_daily", "categorization_release_dates_all"], "a snapshot, not one title over time; old cohorts are survivors; measurable Demand Score titles only")) # Deterministic watermark in the structurally empty upper-right corner: the auto-detector's # largest-empty-rect pick floated the mark mid-canvas, where its descender overlapped the # median-diamonds annotation's first line (verified collision). fig.add_annotation(name="brand_watermark", text=lib.WATERMARK_TEXT, xref="paper", yref="paper", x=0.99, y=0.97, xanchor="right", yanchor="top", showarrow=False, font=dict(family="Arial, sans-serif", size=18, color="#9aa1ad"), opacity=0.9) # csv headers carry the qualified metric name (house rule: 'demand' never bare, csv headers included) lib.save_csv(df.rename(columns={"median_demand": "median_demand_score", "mean_demand": "mean_demand_score"}), output_dir) lib.export_chart(fig, output_dir, width=1150, height=540) 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-Other-05", intended_lesson_from_spec="Demand by release-age cohort (cliff-then-floor)", rendered_lesson={"reader_title": title_lesson, "reader_subtitle": what, "claim_strength": "strong", "rules_applied": "6 cohorts; bars = hit-weighted MEAN with the basis stated in headline/subtitle/legend; median diamonds alongside (medians flip the premium story, verified live: 20+yr median tops year one's); year-one emphasis; cliff annotation"}, row_count=len(df), freshness_date=freshness, query_seconds=q_sec, render_seconds=time.time() - t0 - q_sec) return {"status": "ok", "rows": len(df)}