"""SOURCE render logic for TI-C-07 (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_07(reg: dict) -> dict: """Platform Launch Quality Scatter - day-0 DI vs day-30 hold (proxy via DI distribution).""" chart_id = "TI-C-07" output_dir = THIS_DIR / chart_id / ROLE conn = lib.connect_readonly() with conn.cursor() as cur: cur.execute("SET statement_timeout = '180s'") # the deduped release-date join is heavy (mirrors C-10) t0 = time.time() # L-046: a raw day-0 vs day-30 comparison is contaminated by panel-size swings between the two dates, so each # reading is normalized to its OWN day's total US panel demand (share-of-panel), shown per million. df = lib.run_query(conn, """ WITH recent_releases AS ( -- ONE date per title (MIN): un-deduped rows paired a theatrical day-0 with a streaming day-30 SELECT imdb_id, MIN(release_date) AS release_date FROM categorization_release_dates_all WHERE release_date BETWEEN CURRENT_DATE - INTERVAL '730 days' AND CURRENT_DATE - INTERVAL '30 days' GROUP BY imdb_id ), need_dates AS ( SELECT DISTINCT release_date AS d FROM recent_releases UNION SELECT DISTINCT (release_date + INTERVAL '30 days')::date FROM recent_releases ), tot AS ( SELECT t.file_date, SUM(t.di::numeric) AS total_di FROM title_metrics_daily t JOIN need_dates nd ON t.file_date = nd.d WHERE t.geo_code='US' AND t.di IS NOT NULL GROUP BY t.file_date ), d0 AS ( SELECT rr.imdb_id, t.di::numeric / NULLIF(tot.total_di, 0) AS day0_share FROM recent_releases rr JOIN title_metrics_daily t ON t.imdb_id = rr.imdb_id AND t.file_date = rr.release_date JOIN tot ON tot.file_date = rr.release_date WHERE t.geo_code='US' AND t.di IS NOT NULL ), d30 AS ( SELECT rr.imdb_id, t.di::numeric / NULLIF(tot.total_di, 0) AS day30_share FROM recent_releases rr JOIN title_metrics_daily t ON t.imdb_id = rr.imdb_id AND t.file_date = rr.release_date + INTERVAL '30 days' JOIN tot ON tot.file_date = (rr.release_date + INTERVAL '30 days')::date WHERE t.geo_code='US' AND t.di IS NOT NULL ) SELECT d0.imdb_id, d0.day0_share, d30.day30_share FROM d0 JOIN d30 USING (imdb_id) -- tracked-at-launch floor (>= 50 per million): a near-zero day-0 share is a pre-streaming -- artifact that fabricates absurd 'growth' multiples WHERE d0.day0_share >= 0.00005 AND d30.day30_share > 0 ORDER BY (d0.day0_share + d30.day30_share) DESC LIMIT 300; """) q_sec = time.time() - t0 if df.empty: lib.needs_spec_review(output_dir, chart_id, reg["spec_file"], reg["pc_id"], "No d0/d30 matched releases") conn.close(); write_shim(output_dir, chart_id) return {"status": "needs_spec_review"} if len(df) < 40: # Guard (mirrors C-10): an exact release-day + day-30 DI reading is sparse - too few, and biased toward # titles the panel tracked from launch, for a reliable hold chart. C-10 owns post-launch trajectory robustly. lib.needs_spec_review(output_dir, chart_id, reg["spec_file"], reg["pc_id"], f"DATA LIMITATION: only {len(df)} titles have BOTH an exact release-day and a day-30 DI reading - too few " "(and biased toward titles the panel picked up immediately at launch) for a reliable day-0→day-30 hold " "chart. TI-C-10 answers post-launch trajectory robustly (peak-anchored, ≥30 titles). Recommend C-10.") 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 titles = lib.resolve_titles(conn, df["imdb_id"].tolist()) fresh_df = lib.run_query(conn, "SELECT MAX(file_date) AS d FROM title_metrics_daily WHERE di IS NOT NULL AND geo_code='US';") conn.close() freshness = str(fresh_df["d"].iloc[0]) if len(fresh_df) else None df = df.merge(titles[["imdb_id", "display_title"]], on="imdb_id", how="left") # data hygiene: a title the catalog cannot NAME must not ship (a raw tt-id in data.csv or a # hover is a published surface defect, and an unrecognizable dot earns no place on the board) n_unnamed = int(df["display_title"].isna().sum()) df = df[df["display_title"].notna()].copy() # express each day's share of total US panel demand "per million" (readable numbers, panel-size-proof) df["day0_pm"] = df["day0_share"].astype(float) * 1e6 df["day30_pm"] = df["day30_share"].astype(float) * 1e6 df["hold"] = df["day30_pm"] / df["day0_pm"] # hold > 20x indicates the panel only started tracking the title after launch (data leakage), not real growth n_leak = int((df["hold"] > 20).sum()) df = df[df["hold"] <= 20] # Label the top hold-ratio (most growth) and worst hold-ratio (most decay) titles among substantial launches size_floor = float(df[["day0_pm", "day30_pm"]].max(axis=1).quantile(0.5)) df_meaningful = df[(df["day0_pm"] >= size_floor) | (df["day30_pm"] >= size_floor)] if len(df_meaningful) > 0: # direction-aware extremes: growers come from hold>1 and faders from hold<1, so the valence # colors (GREEN = grew, RED = faded - the contract's home turf) match the selection by # construction; navy stays reserved for a leader, which this chart does not crown top_growth = df_meaningful[df_meaningful["hold"] > 1.0].nlargest(3, "hold") biggest_decay = df_meaningful[df_meaningful["hold"] < 1.0].nsmallest(3, "hold") annotated_set = pd.concat([top_growth, biggest_decay]) else: top_growth = biggest_decay = annotated_set = pd.DataFrame() pct_grew = (df["hold"] > 1.0).mean() pct_lost_half = (df["hold"] < 0.5).mean() fig = go.Figure() fig.add_trace(go.Scatter( x=df["day0_pm"], y=df["day30_pm"], mode="markers", marker=dict(size=7, color=lib.PALE_BLUE, opacity=0.6), text=df["display_title"], hovertemplate="%{text}
Launch day: %{x:,.0f} per million of that day's demand
Day 30: %{y:,.0f} per million
Hold ratio %{customdata:.2f}", customdata=df["hold"], showlegend=False, name="All", )) if not annotated_set.empty: # extreme dots carry the direction verdict (green = grew, red = faded). Labels are placed by # a COLLISION-AWARE greedy pass in estimated pixel space: every candidate offset is tested # against all annotated dots and all already-placed label boxes, and the first clear slot # wins (a fixed alternating cycle clipped a neighbour's dot - verified failure: a label # rendered directly beneath another grower's dot with crossing leader lines) fig.add_trace(go.Scatter( x=annotated_set["day0_pm"], y=annotated_set["day30_pm"], mode="markers", marker=dict(size=11, color=[lib.GREEN if float(h) > 1.0 else lib.RED for h in annotated_set["hold"]], line=dict(width=1, color="white")), text=annotated_set["display_title"], hoverinfo="skip", showlegend=False, )) ann = annotated_set.copy() ann["lx"] = np.log10(ann["day0_pm"].astype(float)) ann["ly"] = np.log10(ann["day30_pm"].astype(float)) cx = float(ann["lx"].mean()) lxs_all = np.log10(df["day0_pm"].astype(float)); lys_all = np.log10(df["day30_pm"].astype(float)) lx0, lx1 = float(lxs_all.min()), float(lxs_all.max()) ly0, ly1 = float(lys_all.min()), float(lys_all.max()) plot_w, plot_h = 1110.0, 440.0 # canvas 1200x720 minus margins: close enough for overlap tests def _px(lx, ly): return ((lx - lx0) / max(lx1 - lx0, 1e-9) * plot_w, (ly1 - ly) / max(ly1 - ly0, 1e-9) * plot_h) dot_pts = [(_px(float(r.lx), float(r.ly)), r.Index) for r in ann.itertuples()] def _seg_pt_dist(p0, p1, q): (x0p, y0p), (x1p, y1p), (qx, qy) = p0, p1, q dx, dy = x1p - x0p, y1p - y0p L2 = dx * dx + dy * dy t = 0.0 if L2 == 0 else max(0.0, min(1.0, ((qx - x0p) * dx + (qy - y0p) * dy) / L2)) cxp, cyp = x0p + t * dx, y0p + t * dy return ((qx - cxp) ** 2 + (qy - cyp) ** 2) ** 0.5 placed_boxes = [] CAND = [(92, -42), (92, 42), (92, -74), (92, 74), (92, -10), (128, -26), (128, 26), (128, -58), (128, 58)] for r in ann.sort_values("ly", ascending=False).itertuples(): side = 1 if float(r.lx) >= cx else -1 x_px, y_px = _px(float(r.lx), float(r.ly)) label = lib.ellipsize(r.display_title, 40) w_est = len(label) * 5.6 + 8.0; h_est = 15.0 chosen = CAND[0] for ax_off, ay_off in CAND: axs = ax_off * side # flip side when the box would run off the plot edge s = side if (0 <= x_px + axs + (w_est if side > 0 else -w_est) <= plot_w) else -side axs = ax_off * s bx0 = x_px + axs if s > 0 else x_px + axs - w_est bx1 = bx0 + w_est by0 = y_px + ay_off - h_est / 2; by1 = by0 + h_est clear = True for (dx, dy), idx in dot_pts: if bx0 - 7 <= dx <= bx1 + 7 and by0 - 7 <= dy <= by1 + 7: clear = False; break # the LEADER LINE must clear every OTHER dot too (a label box can clear a # neighbour while its arrow grazes it, which reads as wrong attribution) if idx != r.Index and _seg_pt_dist((x_px, y_px), (x_px + axs, y_px + ay_off), (dx, dy)) < 10: clear = False; break if clear: for ox0, oy0, ox1, oy1 in placed_boxes: if not (bx1 < ox0 - 2 or bx0 > ox1 + 2 or by1 < oy0 - 2 or by0 > oy1 + 2): clear = False; break if clear: chosen = (axs, ay_off) placed_boxes.append((bx0, by0, bx1, by1)) break else: axs = chosen[0] * side bx0 = x_px + axs if side > 0 else x_px + axs - w_est placed_boxes.append((bx0, y_px + chosen[1] - h_est / 2, bx0 + w_est, y_px + chosen[1] + h_est / 2)) chosen = (axs, chosen[1]) fig.add_annotation(x=float(r.lx), y=float(r.ly), text=label, showarrow=True, arrowhead=0, arrowcolor=lib.MUTED, arrowwidth=1, ax=chosen[0], ay=chosen[1], font=dict(size=9, color=lib.INK), bgcolor="rgba(255,255,255,0.82)") min_v = float(min(df["day0_pm"].min(), df["day30_pm"].min())) max_v = float(max(df["day0_pm"].max(), df["day30_pm"].max())) # Parity line as a DATA-coordinate trace spanning the full cloud: layout shapes on log axes # take RAW values in this plotly build, so the old log10-coord shape rendered in the far # bottom-left corner instead of through the data. par_lo, par_hi = max(min_v, 1e-3), max_v fig.add_trace(go.Scatter(x=[par_lo, par_hi], y=[par_lo, par_hi], mode="lines", line=dict(color=lib.MUTED, width=1.4, dash="dash"), showlegend=False, hoverinfo="skip")) fig.add_annotation(x=np.log10(max_v) - 0.25, y=np.log10(max_v) - 0.25, text="same share of the day's US panel demand at day 30 as at launch", showarrow=False, xanchor="right", yanchor="bottom", font=dict(size=10, color=lib.MUTED), bgcolor="rgba(255,255,255,0.8)") # Headline phrased from the data either way (don't claim "most build" unless a majority actually did) if pct_grew >= 0.5: title_lesson = f"Among titles tracked from release day, most build afterward: {pct_grew:.0%} held a larger share of that day's total panel demand at day 30 than on launch day" else: title_lesson = f"Among titles tracked from release day, launch buzz usually fades: only {pct_grew:.0%} held a larger share of that day's total panel demand at day 30 than on launch day" extreme_clause = "" if not annotated_set.empty: bits = [] if len(top_growth): bits.append(f"green dots mark the {len(top_growth)} biggest growers") if len(biggest_decay): bits.append(f"red dots the {len(biggest_decay)} steepest faders") # the rule says exactly what the code does: size = the LARGER of a launch's day-0 and day-30 # shares (under a plain day-0 reading an expert recomputing it would pick different dots) extreme_clause = ("; " + " and ".join(bits) + " among the top half of launches by size (a launch's size = the larger of its two readings)") what = ("Each dot = a recent launch: right = its slice of all US panel demand on release day, up = the same slice 30 days " f"later. Above the dashed line = it grew; below = it faded{extreme_clause}.") why = ("Unlike a movie's opening weekend, streaming demand can build as word spreads and full seasons roll out - so day-30 " f"hold is the real quality signal, not launch buzz. The {pct_lost_half:.0%} holding less than half their launch share at day 30 show how fast launch buzz can evaporate.") fig.update_layout(**lib.layout_v2(title_lesson, what, width=1200, height=720)) # humanized decade ticks (no bare '2 5 2 5' minor-digit clutter) lib.set_log_axis(fig, "x", "Share of launch-day US panel demand, per million →") lib.set_log_axis(fig, "y", "Share of day-30 US panel demand, per million →") # exclusion disclosure = the APPLIED rules verbatim (the old 'launch-day reading unmeasured' # gloss did not match the hold<=20x leakage cap, and the unnamed-catalog-row drop went undisclosed) excl_bits = [] if n_leak: excl_bits.append(f"{n_leak} titles excluded whose day-30 share read over 20x launch (tracking began after launch, not real growth)") if n_unnamed: excl_bits.append(f"{n_unnamed} row{'s' if n_unnamed != 1 else ''} the title catalog cannot name dropped") leak_note = (" · " + " · ".join(excl_bits)) if excl_bits else "" lib.add_why_and_footer(fig, why, lib.assemble_footer("PC-C-07", ["each reading is the title's share of that day's total US panel demand, so panel-size swings cancel"], freshness, ["title_metrics_daily", "categorization_release_dates_all"], f"only titles tracked from release day (skews to anticipated launches) · last 2 years · log axes{leak_note}")) lib.save_csv(df, output_dir); lib.export_chart(fig, output_dir, width=1200, height=720) 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-C-07", intended_lesson_from_spec="Launch quality day-0 vs day-30", rendered_lesson={"reader_title": title_lesson, "reader_subtitle": what, "claim_strength": "strong", "rules_applied": (f"deduped MIN(release_date); day0_share >= 50/million; hold <= 20x " f"({n_leak} leakage rows dropped, {n_unnamed} unnamed catalog rows dropped); " f"{pct_grew:.0%} grew, {pct_lost_half:.0%} lost half; collision-aware extreme labels")}, row_count=len(df), freshness_date=freshness, query_seconds=q_sec, render_seconds=time.time() - t0 - q_sec) return {"status": "ok", "rows": len(df)}