"""SOURCE render logic for TI-A-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_A_07(reg: dict) -> dict: """Single-Day vs Multi-Day Spike Duration - distribution of spike durations.""" chart_id = "TI-A-07" output_dir = THIS_DIR / chart_id / ROLE conn = lib.connect_readonly() t0 = time.time() df = lib.run_query(conn, """ SELECT imdb_id, spike_start_date, spike_end_date, (spike_end_date - spike_start_date) AS duration_days, ratio::numeric AS ratio FROM spike_pulse_events WHERE spike_peak_date >= CURRENT_DATE - INTERVAL '180 days' AND spike_start_date IS NOT NULL AND spike_end_date IS NOT NULL AND ratio IS NOT NULL AND baseline_value >= 20 -- real spikes only (exclude divide-by-near-zero ratio noise) AND ratio >= 2 -- a genuine surge above the title's own norm (aligns "breakout" copy with the data) AND COALESCE((SELECT r.num_votes FROM categorization_imdb_ratings r WHERE r.imdb_id = spike_pulse_events.imdb_id), 0) >= CASE WHEN (SELECT bt.title_type FROM categorization_imdb_title_basics bt WHERE bt.imdb_id = spike_pulse_events.imdb_id) = 'movie' THEN 3000 ELSE 20000 END; -- type-aware corroboration gate (cred>=0.5: films K=3K, shows K=20K) """) q_sec = time.time() - t0 df = lib.drop_demand_orphans(df, conn) # hide in-theaters / not-yet-streaming demand-orphans conn.close() if df.empty: lib.needs_spec_review(output_dir, chart_id, reg["spec_file"], reg["pc_id"], "No closed spike events") write_shim(output_dir, chart_id) return {"status": "needs_spec_review"} df["ratio"] = df["ratio"].astype(float) df["duration_days"] = df["duration_days"].astype(int) def bucket(d): if d <= 1: return "Single day" if d <= 3: return "2-3 days" if d <= 7: return "4-7 days" if d <= 14: return "8-14 days" return "15+ days" df["bucket"] = df["duration_days"].map(bucket) bucket_order = ["Single day", "2-3 days", "4-7 days", "8-14 days", "15+ days"] counts = df["bucket"].value_counts().reindex(bucket_order, fill_value=0) median_ratio = df.groupby("bucket")["ratio"].median().reindex(bucket_order) n_total = int(counts.sum()) pct_single = counts["Single day"] / n_total multi_pct = 1 - pct_single single_med = float(median_ratio["Single day"]) if pd.notna(median_ratio["Single day"]) else 0 multi_med_max = float(median_ratio.drop("Single day").max()) fig = make_subplots(rows=1, cols=2, column_widths=[0.5, 0.5], horizontal_spacing=0.13, subplot_titles=[ "How long did each breakout last? (share of all breakouts)", "...and how big was it? (median spike size)", ]) # Color single-day pale (flashes/noise), multi-day navy (real momentum) cnt_colors = [lib.PALE_BLUE] + [lib.NAVY] * (len(bucket_order) - 1) fig.add_trace(go.Bar(x=bucket_order, y=(counts.values / n_total), marker=dict(color=cnt_colors), text=[f"{v/n_total:.0%}" for v in counts.values], textposition="outside", showlegend=False, cliponaxis=False), row=1, col=1) fig.add_trace(go.Bar(x=bucket_order, y=median_ratio.values, marker=dict(color=cnt_colors), text=[f"{v:.1f}x" for v in median_ratio.values], textposition="outside", showlegend=False, cliponaxis=False), row=1, col=2) fig.update_yaxes(title_text="Share of breakouts", row=1, col=1, gridcolor=lib.GRID, tickformat=".0%") fig.update_yaxes(title_text="Median size (x normal)", row=1, col=2, gridcolor=lib.GRID) fig.update_xaxes(gridcolor=lib.GRID) fresh = str(df["spike_end_date"].max()) longest_med = float(median_ratio["15+ days"]) if pd.notna(median_ratio["15+ days"]) else 0.0 takeaway = f"{pct_single:.0%} of breakouts are one-day flashes - the {multi_pct:.0%} that hold longer are the real hits" # the colored words ARE the color key (legend content stays out of the subtitle prose) what = (f"How long each breakout held. Single-day = a blip (a news mention, a viral post); " f"multi-day = people kept coming back.") why = ("Duration separates noise from real momentum. A one-day flash is usually gone by tomorrow; a multi-day breakout means a title " f"genuinely caught on - and the longest-lived ones spike biggest ({longest_med:.1f}x median). " "Those are the safer bets for what to watch, feature, or market.") footer = lib.assemble_footer(pc_id="PC-A-05", caution_labels=[], freshness_date=fresh, sources=["spike_pulse_events"], extra=f"{n_total:,} breakouts, 180 days · breakout = at least 2x a measurable baseline (20+), corroborated titles only") fig.update_layout(**lib.layout_v2(takeaway, what, width=1240, height=600)) fig.update_annotations(font_size=12) fig.update_yaxes(title_text="Median spike size (times normal)", row=1, col=2) lib.add_why_and_footer(fig, why, footer) lesson = {"reader_title": takeaway, "reader_subtitle": what, "claim_strength": "strong", "rules_applied": "duration buckets, flash vs sustained"} lib.save_csv(df, output_dir) lib.export_chart(fig, output_dir, width=1240, height=600) 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-A-05", intended_lesson_from_spec="Spike duration single vs multi-day", rendered_lesson=lesson, row_count=len(df), freshness_date=fresh, query_seconds=q_sec, render_seconds=time.time() - t0 - q_sec) return {"status": "ok", "rows": len(df)} # =========================================================================== # SECTION 03 - Platform & Catalog Structure (14 charts) # ===========================================================================