"""SOURCE render logic for TI-C-02 (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_02(reg: dict) -> dict: """Parent x Service Catalog Power Matrix - credit-normalized DI by parent x service.""" chart_id = "TI-C-02" output_dir = THIS_DIR / chart_id / ROLE conn = lib.connect_readonly() t0 = time.time() # Demand split that CONSERVES each title's Demand Score: a title's demand is divided evenly across the # DISTINCT platforms carrying it (demand / sc per (platform, title) row). The old di/(pc*sc) split leaked # demand for multi-parent titles (rows per title = sc when each platform has one parent, but the divisor was # pc*sc, so only 1/pc of the title's demand survived). Each platform is attributed to ONE owner (its modal # parent label) so (platform, title) rows are unique and per-title contributions sum exactly to demand_score. df = lib.run_query(conn, """ WITH latest_d AS (SELECT MAX(file_date) AS file_date FROM title_metrics_daily WHERE demand_score IS NOT NULL AND geo_code='US'), 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 ), plat_owner AS ( SELECT platform, parent_company FROM ( SELECT cxp.platform, REPLACE(cxp.parent_company, '_', ' / ') AS parent_company, ROW_NUMBER() OVER (PARTITION BY cxp.platform ORDER BY COUNT(*) DESC) AS rk FROM categorization_title_x_platform cxp JOIN latest_p lp USING (snapshot_date) WHERE cxp.parent_company IS NOT NULL AND cxp.platform IS NOT NULL GROUP BY cxp.platform, REPLACE(cxp.parent_company, '_', ' / ') ) z WHERE rk = 1 ), tp AS ( SELECT DISTINCT cxp.imdb_id, cxp.platform FROM categorization_title_x_platform cxp JOIN latest_p lp USING (snapshot_date) WHERE cxp.platform IS NOT NULL ), counts AS (SELECT imdb_id, COUNT(*) AS sc FROM tp GROUP BY imdb_id) SELECT po.parent_company, tp.platform, SUM(tt.demand / NULLIF(c.sc, 0)) AS cell_demand, COUNT(DISTINCT tt.imdb_id) AS n_titles FROM top_titles tt JOIN tp USING (imdb_id) JOIN counts c USING (imdb_id) JOIN plat_owner po USING (platform) GROUP BY po.parent_company, tp.platform HAVING SUM(tt.demand / NULLIF(c.sc, 0)) > 0; """) # TRUE denominator for every printed share: the combined Demand Score of ALL 1,000 most-wanted # titles (not just the slice that lands on a parent-labeled service) - the old service-sum # denominator made shares sum to 100% across the drawn bars while the copy claimed the full # top-1,000, which was not reconstructable as worded. tot_df = lib.run_query(conn, """ WITH latest_d AS (SELECT MAX(file_date) AS file_date FROM title_metrics_daily WHERE demand_score IS NOT NULL AND geo_code='US') SELECT SUM(demand) AS total_demand FROM ( SELECT 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 ) z; """) fresh_df = lib.run_query(conn, "SELECT MAX(file_date) AS d FROM title_metrics_daily WHERE demand_score IS NOT NULL AND geo_code='US';") 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 parent-service data") 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["cell_demand"] = df["cell_demand"].astype(float) df["n_titles"] = df["n_titles"].astype(int) # Reframe: SERVICE-level demand leaderboard, colored by parent owner (clearer than parent x service matrix) svc = df.groupby(["platform", "parent_company"], as_index=False).agg( cell_demand=("cell_demand", "sum"), n_titles=("n_titles", "sum")) # each service's owner (unique by construction via plat_owner) + total svc_total = svc.groupby("platform", as_index=False).agg( svc_demand=("cell_demand", "sum"), n_titles=("n_titles", "sum")) svc_owner = svc.sort_values("cell_demand", ascending=False).drop_duplicates("platform")[["platform", "parent_company"]] svc_total = svc_total.merge(svc_owner, on="platform", how="left") # shares against the TRUE top-1,000 combined Demand Score; the covered fraction is disclosed total_top_demand = (float(tot_df["total_demand"].iloc[0]) if len(tot_df) and pd.notna(tot_df["total_demand"].iloc[0]) else float(svc_total["svc_demand"].sum())) svc_total["share"] = svc_total["svc_demand"] / total_top_demand covered = float(svc_total["svc_demand"].sum()) / total_top_demand top = svc_total.nlargest(15, "svc_demand").sort_values("svc_demand", ascending=True) # Color: NAVY is reserved for the leader (house contract), so the #1 service gets navy; the # ownership story (Disney running two strong services) uses a neutral PURPLE identity hue (the # old teal brushed the green-family reservation); the field stays blue. Decoded by a one-line key. OWNER_HUE = lib.PURPLE leader_platform = top.iloc[-1]["platform"] owners = top["parent_company"].value_counts().index.tolist() fig = go.Figure() for owner in owners: sub = top[top["parent_company"] == owner] colors_svc = [lib.NAVY if p == leader_platform else (OWNER_HUE if owner == "Disney" else lib.BLUE) for p in sub["platform"]] fig.add_trace(go.Bar( x=sub["share"], y=sub["platform"], orientation="h", marker=dict(color=colors_svc), name=owner, showlegend=False, text=[f"{s:.1%}" for s in sub["share"]], textposition="outside", textfont=dict(size=10, color=lib.INK), cliponaxis=False, hovertemplate="%{y} (owned by " + owner + ")
%{x:.1%} of the top-1,000 titles' combined Demand Score", )) fig.update_xaxes(range=[0, float(top["share"].max()) * 1.09]) top_svc = top.iloc[-1] # metric named on every surface ('combined Demand Score', never a bare 'total demand'); # freshness language matches the footer stamp ('latest tracked day', never 'today'); # the headline prints the SAME 1dp number as the leader's own bar label (a .0% headline beside # a .1% bar label showed two different numbers for one mark) title_lesson = f"{top_svc['platform']} commands the most in-demand catalog - {top_svc['share']:.1%} of the top-1,000 titles' combined Demand Score" # service-inclusion rule + denominator coverage SAID OUT LOUD: only parent-labeled services in # our streaming catalog are drawn, and the drawn universe holds a disclosed fraction of the full # top-1,000 combined score (the remainder: unlabeled services or no tracked streaming home) what = ("Each bar is one streaming service (Netflix, Max, Hulu…), sized by its share of the combined Demand Score of the 1,000 " "most-wanted titles on the latest tracked day; a title carried by several services is split between them. " f"The {len(svc_total)} services carrying a parent-company label in our streaming catalog together hold {covered:.0%} of that " "combined score; the rest sits on unlabeled services or has no tracked streaming home yet.") why = ("Services are what viewers actually subscribe to. One parent can run several strong services " "(Disney owns both Disney+ and Hulu, the purple bars), so the company with the most attention isn't always the single biggest app.") footer = lib.assemble_footer(pc_id="PC-C-02", caution_labels=["each title's Demand Score is split across the services carrying it"], freshness_date=freshness, sources=["title_metrics_daily", "categorization_title_x_platform"], extra="") fig.update_layout(**lib.layout_v2(title_lesson, what, width=1240, height=720), barmode="stack", xaxis=lib.standard_xaxis("Share of the top-1,000 titles' combined Demand Score", tickformat=".0%"), yaxis=lib.standard_yaxis(showgrid=False, categoryorder="array", categoryarray=top["platform"].tolist())) lib.add_color_key(fig, f"■ the leader " f"■ Disney-owned (Disney+ and Hulu) " f"■ all other owners", y=1.01) # believability note: Netflix's low rank gets the direct one-clause explanation it begs for, # with the title COUNT printed (and shipped in data.csv) so the causal claim is reconstructable if (top["platform"] == "Netflix").any(): nx = float(top.loc[top["platform"] == "Netflix", "share"].iloc[0]) nx_rank = int((svc_total["svc_demand"] > float(top.loc[top["platform"] == "Netflix", "svc_demand"].iloc[0])).sum()) + 1 nx_n = int(top.loc[top["platform"] == "Netflix", "n_titles"].iloc[0]) fig.add_annotation(x=nx, y="Netflix", xref="x", yref="y", text=(f"Netflix ranks {nx_rank}th here simply because only {nx_n} of the 1,000 most-wanted titles " "sit in its catalog - and shared titles are split between services"), showarrow=False, xanchor="left", xshift=58, font=dict(size=9, color=lib.MUTED)) lib.add_why_and_footer(fig, why, footer) # csv: qualified headers, the title counts behind the Netflix note, and the constant true # denominator so every share (and the disclosed coverage fraction) reconciles from the file csv_out = svc_total.rename(columns={ "svc_demand": "combined_demand_score_on_service", "n_titles": "top1000_titles_in_catalog", "share": "share_of_top1000_combined_demand_score"}) csv_out["total_top1000_combined_demand_score"] = total_top_demand lib.save_csv(csv_out, output_dir) lib.export_chart(fig, output_dir, width=1240, 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-02", intended_lesson_from_spec="Service-level demand leaderboard colored by parent (redesigned from matrix)", rendered_lesson={"reader_title": title_lesson, "reader_subtitle": what, "claim_strength": "strong", "rules_applied": ("service-level; navy = the leader only, purple = Disney-owned, blue = others; " "shares against the FULL top-1,000 combined Demand Score (coverage of the drawn, " f"parent-labeled universe disclosed in the subtitle: {covered:.1%})")}, row_count=len(df), freshness_date=freshness, query_seconds=q_sec, render_seconds=time.time() - t0 - q_sec) return {"status": "ok", "rows": len(df)}