"""SOURCE render logic for TI-C-03 (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_03(reg: dict) -> dict: """Parent Original vs Licensed Demand Quadrant - quadrant via composite_classification.""" chart_id = "TI-C-03" output_dir = THIS_DIR / chart_id / ROLE # purge stale companion images from an earlier axis basis (they contradict the current chart) if output_dir.exists(): for f in output_dir.glob("*.png"): if f.name.endswith(("_top.png", "_mid.png", "_bot.png")): f.unlink() conn = lib.connect_readonly() t0 = time.time() df = lib.run_query(conn, """ WITH latest_d AS (SELECT MAX(file_date) AS file_date FROM title_metrics_daily), latest_p AS (SELECT MAX(snapshot_date) AS snapshot_date FROM categorization_title_x_platform), top_titles AS ( SELECT t.imdb_id, t.demand_score::numeric AS demand FROM title_metrics_daily t JOIN latest_d l USING (file_date) WHERE t.demand_score IS NOT NULL AND t.geo_code='US' ORDER BY t.demand_score DESC LIMIT 1000 ), -- one row per (title, parent): that parent's best-confidence classification for the title tp AS ( SELECT imdb_id, REPLACE(parent_company, '_', ' / ') AS parent_company, composite_classification, ROW_NUMBER() OVER (PARTITION BY imdb_id, REPLACE(parent_company, '_', ' / ') ORDER BY composite_confidence DESC NULLS LAST) AS prk FROM categorization_title_x_platform cxp JOIN latest_p lp USING (snapshot_date) WHERE parent_company IS NOT NULL AND composite_classification IS NOT NULL ), tp1 AS (SELECT imdb_id, parent_company, composite_classification FROM tp WHERE prk = 1), -- distinct parents per title → split each title's demand evenly across them (conserves total demand, no under/over-count) dp AS (SELECT imdb_id, COUNT(*) AS np FROM tp1 GROUP BY imdb_id) SELECT tp1.parent_company, SUM(CASE WHEN tp1.composite_classification='parent_original' THEN tt.demand / dp.np ELSE 0 END) AS original_demand, -- parent_exclusive = "only here, but not made by them" → belongs in the licensed/acquired bucket -- (previously it was silently dropped); only sports stays excluded SUM(CASE WHEN tp1.composite_classification IN ('broad_license','cross_window_acquisition','parent_exclusive') THEN tt.demand / dp.np ELSE 0 END) AS licensed_demand, COUNT(DISTINCT tp1.imdb_id) AS title_count FROM top_titles tt JOIN tp1 USING (imdb_id) JOIN dp USING (imdb_id) GROUP BY tp1.parent_company HAVING COUNT(DISTINCT tp1.imdb_id) >= 5 ORDER BY (SUM(CASE WHEN tp1.composite_classification='parent_original' THEN tt.demand/dp.np ELSE 0 END) + SUM(CASE WHEN tp1.composite_classification IN ('broad_license','cross_window_acquisition','parent_exclusive') THEN tt.demand/dp.np ELSE 0 END)) DESC LIMIT 25; """) fresh_df = lib.run_query(conn, "SELECT MAX(file_date) AS d FROM title_metrics_daily;") 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 V2 classified rows") write_shim(output_dir, chart_id) return {"status": "needs_spec_review"} freshness = str(fresh_df["d"].iloc[0]) if len(fresh_df) else None df["original_demand"] = df["original_demand"].astype(float) df["licensed_demand"] = df["licensed_demand"].astype(float) df["total"] = df["original_demand"] + df["licensed_demand"] df = df[df["total"] > 0] fig = go.Figure() fig.add_trace(go.Scatter( x=df["licensed_demand"], y=df["original_demand"], mode="markers+text", # fixed-size dots: bubble area duplicated x+y (size encoded total = x + y), pure extra ink marker=dict(size=11, color=lib.BLUE, opacity=0.85), text=df["parent_company"], textposition="top center", textfont=dict(size=9), hovertemplate="%{text}
Originals demand %{y:,.0f}
Licensed/acquired demand %{x:,.0f}", showlegend=False, )) # Axes fit the data (no vacant top half) and the parity line spans exactly the visible window x_lim = float(df["licensed_demand"].max()) * 1.08 y_lim = float(df["original_demand"].max()) * 1.30 par_end = min(x_lim, y_lim) fig.add_shape(type="line", x0=0, y0=0, x1=par_end, y1=par_end, line=dict(color=lib.MUTED, width=1.4, dash="dash")) fig.add_annotation(x=par_end, y=par_end, text="equal pull from originals and licensed", showarrow=False, xanchor="left", yanchor="top", xshift=8, font=dict(size=10, color=lib.MUTED), bgcolor="rgba(255,255,255,0.8)") # Identify parent strategy - the honest finding is the ASYMMETRY, not a 50/50 split. # The headline arithmetic is derived from the PLOTTED buckets: originals-led = above parity. df["ratio"] = df["original_demand"] / (df["licensed_demand"] + 1) led_df = df[df["original_demand"] > df["licensed_demand"]].sort_values("ratio", ascending=False) originals_led = led_df["parent_company"].tolist() n_lic = len(df) - len(originals_led) if len(originals_led) > 1: led_names = ", ".join(originals_led[:-1]) + " and " + originals_led[-1] else: led_names = originals_led[0] if originals_led else "none" title_lesson = (f"{n_lic} of {len(df)} streaming owners get most of their current demand from licensed titles - " f"only {led_names} sit on the originals side of the line") what = ("Each dot = a parent company. Up = current demand from its own originals, right = current demand from licensed or acquired " "titles - above the dashed line means originals-heavy.") why = ("Demand Score measures the audience a title actually pulls - panel viewing, weighted to reflect the broad US streaming " "audience. This reveals each company's playbook: build-your-own (expensive, owns the upside) vs. license-others (cheaper, but renews " "on someone else's terms). It's the strategic split of the industry.") footer = lib.assemble_footer(pc_id="PC-C-03", caution_labels=["CL-015 original/licensed is an automated classification", f"owners shown: the {len(df)} biggest parents with 5+ classified titles among today's 1,000 most in-demand US titles"], freshness_date=freshness, sources=["categorization_title_x_platform", "title_metrics_daily"], extra="axis units: Demand Score points (panel audience-size basis)") fig.update_layout(**lib.layout_v2(title_lesson, what, width=1180, height=720), xaxis=lib.standard_xaxis("Demand for licensed / acquired titles (Demand Score points) →", tickformat=",d", range=[-x_lim * 0.02, x_lim]), yaxis=lib.standard_yaxis("Demand for own originals (Demand Score points) →", tickformat=",d", range=[-y_lim * 0.03, y_lim]), ) lib.add_why_and_footer(fig, why, footer) lib.save_csv(df, output_dir) lib.export_chart(fig, output_dir, width=1180, 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-03", intended_lesson_from_spec="Parent original vs licensed demand quadrant", rendered_lesson={"reader_title": title_lesson, "reader_subtitle": what, "claim_strength": "strong", "rules_applied": f"V2 classifier; {n_lic}/{len(df)} licensed-led"}, row_count=len(df), freshness_date=freshness, query_seconds=q_sec, render_seconds=time.time() - t0 - q_sec) return {"status": "ok", "rows": len(df)}