"""SOURCE render logic for TI-RC-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_RC_03(reg: dict) -> dict: """The Attention Standings (monthly): the originals RACE - 8 SVOD EXCLUSIVE-demand share over ~10 months. Reads M054 v2 EXCLUSIVE cut (excl_*) per the metric owner: the TOTAL cut double-counts licensed titles (~1.17x) and is NOT a viewing partition, so 'share of all US viewing' would overclaim. The exclusive cut is the valid 'who owns it' read.""" chart_id = "TI-RC-03" output_dir = THIS_DIR / chart_id / ROLE conn = lib.connect_readonly() t0 = time.time() df = lib.run_query(conn, """ WITH monthly AS ( SELECT DISTINCT ON (date_trunc('month', as_of_date)) as_of_date FROM metrics_platform_standings_daily WHERE country_code='US' ORDER BY date_trunc('month', as_of_date), as_of_date DESC ) SELECT s.as_of_date, s.platform, s.excl_share_pct::numeric AS share, s.excl_rnk AS rnk FROM metrics_platform_standings_daily s JOIN monthly m USING (as_of_date) WHERE s.country_code='US' AND s.excl_share_pct IS NOT NULL ORDER BY s.as_of_date, s.excl_rnk; """) q_sec = time.time() - t0 conn.close() if df.empty or df["as_of_date"].nunique() < 3: lib.needs_spec_review(output_dir, chart_id, reg.get("spec_file", "specs/16_recurring.md"), reg.get("pc_id", "PC-RC-03"), "Too little standings history for a trend") write_shim(output_dir, chart_id); return {"status": "needs_spec_review"} df["share"] = df["share"].astype(float) dates_all = sorted(df["as_of_date"].unique()) year_start = dates_all[-1].replace(month=1, day=1) ytd = [d for d in dates_all if d >= year_start] dates = ytd if len(ytd) >= 4 else dates_all[-6:] # current-year race (matches the metric's YTD momentum; fallback last 6mo) df = df[df["as_of_date"].isin(dates)] freshness = str(dates[-1]) cur = df[df["as_of_date"] == dates[-1]].sort_values("rnk") order = cur["platform"].tolist() last = cur.set_index("platform")["share"] first = df[df["as_of_date"] == dates[0]].set_index("platform")["share"] delta = (last - first).reindex(order).fillna(0.0) leader = order[0] challenger = order[1] if len(order) > 1 else leader # the race is #1 vs the #2 challenger ch_gain = float(delta.get(challenger, 0.0)); lead_gain = float(delta.get(leader, 0.0)) gap_now = float(last[leader] - last[challenger]) gap_then = float(first.get(leader, last[leader]) - first.get(challenger, last[challenger])) COLORS = {"Netflix": lib.NAVY, "Prime Video": lib.GREEN, "Hulu": lib.ACCENT, "Max": lib.PURPLE, "Disney+": lib.BLUE, "Paramount+": lib.AMBER, "Peacock": lib.RED, "Apple TV+": "#8A93A6"} hilite = {leader, challenger} fig = go.Figure() for p in order: sub = df[df["platform"] == p].sort_values("as_of_date") col = COLORS.get(p, lib.MUTED); hot = p in hilite fig.add_trace(go.Scatter(x=list(sub["as_of_date"]), y=list(sub["share"]), mode="lines+markers", line=dict(color=col, width=3.4 if hot else 1.8), marker=dict(size=6 if hot else 4, color=col), opacity=1.0 if hot else 0.6, showlegend=False, name=p, hovertemplate=f"{p}
%{{x|%b %Y}}: %{{y:.1f}}%")) # endpoint labels, de-collided vertically (Prime/Max/Hulu sit fractions of a point apart): # one-decimal labels for services within 1.5 pts of a neighbour, tight stagger + thin leader # lines tying each label back to its line end. ends = sorted([(p, float(last[p]), COLORS.get(p, lib.MUTED), p in hilite) for p in order], key=lambda e: -e[1]) vals_sorted = [e[1] for e in ends] def _near(yv): return any(abs(yv - o) < 1.5 for o in vals_sorted if o != yv) gap = max(0.6, float(last.max() - last.min()) * 0.045); prev = None for p, yv, col, hot in ends: ly = yv if prev is None else min(yv, prev - gap); prev = ly lab = f" {p} {yv:.1f}%" if _near(yv) else f" {p} {yv:.0f}%" if abs(ly - yv) > 0.05: fig.add_shape(type="line", x0=dates[-1], y0=yv, x1=dates[-1] + (dates[-1] - dates[0]) * 0.012, y1=ly, line=dict(color="#D7DBE4", width=1)) fig.add_annotation(x=dates[-1], y=ly, text=lab, showarrow=False, xanchor="left", yanchor="middle", font=dict(size=10.5 if hot else 9.5, color=col)) spread_2_4 = float(last[order[1]] - last[order[min(3, len(order) - 1)]]) if len(order) >= 3 else 99.0 if spread_2_4 < 0.7 and len(order) >= 4: takeaway = (f"The originals race: {leader} still owns the most exclusive demand at {last[leader]:.0f}%, while " f"{order[1]}, {order[2]} and {order[3]} sit in a dead heat for second around {last[order[1]]:.0f}%") elif ch_gain > 1.0 and gap_now < gap_then and (len(order) < 3 or (last[challenger] - last[order[2]]) >= 0.7): takeaway = (f"The originals race: {leader} still owns the most exclusive demand at {last[leader]:.0f}%, but " f"{challenger} has closed in, climbing {ch_gain:.1f} points since {dates[0]:%B} to {last[challenger]:.0f}%") elif lead_gain > 1.0: takeaway = f"The originals race: {leader} owns the most exclusive demand at {last[leader]:.0f}% and has pulled further ahead since {dates[0]:%B}" else: takeaway = (f"The originals race: {leader} leads exclusive demand at {last[leader]:.0f}%, with {challenger} " f"second at {last[challenger]:.0f}%") what = ("Each line is a service's share of US demand for the shows you can only watch there, its originals and " "exclusives, month by month this year. Licensed hits available on several services are left out, so this is " "the clean read on who owns must-have demand, not subscriber counts.") why = ("This is the demand scoreboard of the originals arms race: who actually owns the shows audiences cannot get " "anywhere else. A service's full catalog is muddier, because a licensed hit counts for every service that " "carries it, so a platform can look big on total catalog yet small once you isolate what is truly its own.") footer = lib.assemble_footer(pc_id=reg.get("pc_id", "PC-RC-03"), caution_labels=["CL-008 8 curated subscription services; excludes live-TV and free-ad services"], freshness_date=freshness, sources=["metrics_platform_standings_daily"], extra="exclusive/originals demand share · monthly · United States · y-axis zoomed to the race, not zero-based") fig.update_layout(**lib.layout_v2(takeaway, what, width=1240, height=700)) pad = (dates[-1] - dates[0]) * 0.22 last_dt = pd.to_datetime(dates[-1]) mid_month = last_dt.day < 25 # a mid-month snapshot plotted against month-end points ticktext = [] for i, d in enumerate(dates): dt = pd.to_datetime(d) if i == len(dates) - 1 and mid_month: ticktext.append(dt.strftime("%b %d")) # honest label for the month-to-date point elif i == 0: ticktext.append(dt.strftime("%b
%Y")) else: ticktext.append(dt.strftime("%b")) fig.update_xaxes(range=[dates[0], dates[-1] + pad], gridcolor=lib.GRID, zeroline=False, showline=False, tickmode="array", tickvals=list(dates), ticktext=ticktext, tickfont=dict(size=10, color=lib.MUTED)) fig.update_yaxes(gridcolor=lib.GRID, zeroline=False, showline=False, ticksuffix="%", tickfont=dict(size=10, color=lib.MUTED)) if mid_month: fig.add_annotation(x=dates[-1], y=0.0, xref="x", yref="paper", yanchor="top", yshift=-30, text=f"{last_dt.strftime('%B')} = month to date", showarrow=False, font=dict(size=9, color=lib.MUTED)) lib.add_why_and_footer(fig, why, footer) lib.save_csv(df, output_dir) lib.export_chart(fig, output_dir, width=1240, height=700) what_plain = re.sub(r"", "", what) lesson = {"reader_title": takeaway, "reader_subtitle": what_plain, "claim_strength": "strong", "rules_applied": f"M054 v2 EXCLUSIVE cut (excl_share_pct), monthly US, {len(dates)} months; leader+riser highlighted"} lib.write_lesson_design(output_dir, chart_id, f"# {chart_id}\n\n{takeaway}\n\n{what_plain}\n") write_shim(output_dir, chart_id) lib.write_manifest(output_dir, chart_id, "ok", reg.get("spec_file", "specs/16_recurring.md"), pc_id=reg.get("pc_id", "PC-RC-03"), intended_lesson_from_spec="Monthly EXCLUSIVE/originals demand standings race (M054 v2 excl cut)", rendered_lesson=lesson, row_count=len(df), freshness_date=freshness, query_seconds=q_sec, render_seconds=time.time() - t0 - q_sec) return {"status": "ok", "rows": len(df)}