"""SOURCE render logic for TI-Other-04 (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_Other_04(reg: dict) -> dict: """How sticky is the top of the charts - a bump chart of the persistent Top-10 contenders over 12 weeks.""" chart_id = "TI-Other-04" output_dir = THIS_DIR / chart_id / ROLE conn = lib.connect_readonly() t0 = time.time() # Ranks come from the official weekly demand rankings (7-day audience-size basis, cut label 'shows'), # sampled weekly - each sampled rank already reflects a full 7-day demand window. df = lib.run_query(conn, """ WITH wk AS ( SELECT (SELECT MAX(file_date) FROM metrics_demand_rank_history) - (i * INTERVAL '7 days') AS fd, i FROM generate_series(0, 11) AS i ), ranked AS ( SELECT r.imdb_id, w.i AS wk, r.di_rank AS rnk FROM metrics_demand_rank_history r JOIN wk w ON r.file_date = w.fd::date WHERE r.geo_code = 'US' AND r.title_type = 'shows' AND r.di_rank IS NOT NULL ), sel AS ( SELECT imdb_id FROM ranked WHERE rnk <= 40 GROUP BY imdb_id HAVING COUNT(*) >= 10 AND COUNT(*) FILTER (WHERE rnk <= 10) >= 5 ) SELECT r.imdb_id, r.wk, r.rnk, (SELECT MAX(file_date) FROM metrics_demand_rank_history) AS freshness_date FROM ranked r JOIN sel USING (imdb_id) WHERE r.rnk <= 40; """) q_sec = time.time() - t0 if df.empty: lib.needs_spec_review(output_dir, chart_id, reg["spec_file"], reg["pc_id"], "No persistent top titles") conn.close(); return {"status": "needs_spec_review"} df = lib.drop_demand_orphans(df, conn) # hide in-theaters / not-yet-streaming demand-orphans # CURRENT-#1 GUARANTEE (round-4): a chart headlined 'The Top 10' must answer 'who is #1 right # now', but a fast-rising leader can miss the >=10-of-12-week persistence cut and leave the #1 # slot visibly vacant. Rule-based fix: the best-ranked non-orphan show of the LATEST week is # always drawn - dotted, with a 'joined the race' note, when it fails the chart's own cut. lead = lib.run_query(conn, """ SELECT r.imdb_id, r.di_rank AS rnk FROM metrics_demand_rank_history r WHERE r.file_date = (SELECT MAX(file_date) FROM metrics_demand_rank_history) AND r.geo_code = 'US' AND r.title_type = 'shows' AND r.di_rank IS NOT NULL AND r.di_rank <= 40 ORDER BY r.di_rank;""") lead = lib.drop_demand_orphans(lead, conn) leader_id = str(lead["imdb_id"].iloc[0]) if not lead.empty else None leader_trail = pd.DataFrame() if leader_id and leader_id not in set(df["imdb_id"]): leader_trail = lib.run_query(conn, """ WITH wk AS ( SELECT (SELECT MAX(file_date) FROM metrics_demand_rank_history) - (i * INTERVAL '7 days') AS fd, i FROM generate_series(0, 11) AS i ) SELECT r.imdb_id, w.i AS wk, r.di_rank AS rnk, (SELECT MAX(file_date) FROM metrics_demand_rank_history) AS freshness_date FROM metrics_demand_rank_history r JOIN wk w ON r.file_date = w.fd::date WHERE r.geo_code = 'US' AND r.title_type = 'shows' AND r.imdb_id = %s AND r.di_rank IS NOT NULL AND r.di_rank <= 40;""", (leader_id,)) titles = lib.resolve_titles(conn, df["imdb_id"].unique().tolist() + ([leader_id] if leader_id else [])) conn.close() tmap = dict(zip(titles["imdb_id"], titles["display_title"])) freshness = str(df["freshness_date"].iloc[0]) fresh_dt = pd.Timestamp(freshness) df["wk_no"] = 12 - df["wk"].astype(int) # 1 = oldest, 12 = current df["rnk"] = df["rnk"].astype(int) # keep the 7 with the strongest Top-10 presence to avoid spaghetti top10n = df[df["rnk"] <= 10].groupby("imdb_id").size().sort_values(ascending=False) keep = top10n.head(7).index.tolist() df = df[df["imdb_id"].isin(keep)] leader_partial = False if leader_id and leader_id not in keep and not leader_trail.empty: lt = leader_trail.copy() lt["wk_no"] = 12 - lt["wk"].astype(int) lt["rnk"] = lt["rnk"].astype(int) leader_partial = len(lt) < 10 # fails the chart's own persistence cut df = pd.concat([df, lt[df.columns.tolist()]], ignore_index=True) keep = keep + [leader_id] # order titles by their most-recent rank for label spreading order = df.sort_values("wk_no").groupby("imdb_id").tail(1).sort_values("rnk")["imdb_id"].tolist() # NET-DIRECTION COLOR RULE (round-4): line color = the show's net move over the full 12-week # window (color contract: green = net-rising ONLY, red = net-falling ONLY, navy = the current # #1 ONLY; a net-falling series must never render green). Shade steps within each family keep # crossing lines traceable; absence at a window edge counts as rank 41 (outside the Top 40). GREENS = ["#2D8B5F", "#5FAE85", "#1E6B47"] REDS = ["#B0464E", "#C97B82", "#8C353C", "#D9989E", "#702A30", "#BD5F68"] FLATS = [lib.BLUE, lib.PALE_BLUE] def _net_dir(sub: pd.DataFrame) -> int: start_eff = float(sub["rnk"].iloc[0]) if int(sub["wk_no"].iloc[0]) == 1 else 41.0 end_eff = float(sub["rnk"].iloc[-1]) if int(sub["wk_no"].iloc[-1]) == 12 else 41.0 return -1 if end_eff < start_eff else (1 if end_eff > start_eff else 0) colmap, n_used = {}, {-1: 0, 0: 0, 1: 0} for iid in order: if iid == leader_id: colmap[iid] = lib.NAVY continue d = _net_dir(df[df["imdb_id"] == iid].sort_values("wk_no")) fam = GREENS if d == -1 else (REDS if d == 1 else FLATS) colmap[iid] = fam[n_used[d] % len(fam)]; n_used[d] += 1 # label-collision spreader: ensure right-edge labels keep a minimum vertical gap (in rank units) ends = sorted([(iid, df[df["imdb_id"] == iid].sort_values("wk_no").iloc[-1]) for iid in order], key=lambda t: t[1]["rnk"]) label_y, prev = {}, -99.0 for iid, last in ends: ly = max(float(last["rnk"]), prev + 1.5) label_y[iid] = ly; prev = ly fig = go.Figure() for iid in order: sub = df[df["imdb_id"] == iid].sort_values("wk_no") col = colmap[iid]; nm = tmap.get(iid, iid) is_partial_leader = (iid == leader_id and leader_partial) last = sub.iloc[-1] fig.add_trace(go.Scatter(x=sub["wk_no"], y=sub["rnk"], mode="lines+markers", line=dict(color=col, width=2.6, dash="dot" if is_partial_leader else None), marker=dict(size=6, color=col), connectgaps=False, showlegend=False, name=nm, hovertemplate=f"{nm}
week %{{x}} · rank #%{{y}}")) label_txt = f"{lib.ellipsize(nm, 42)}" if iid == leader_id else lib.ellipsize(nm, 42) fig.add_annotation(x=float(last["wk_no"]) + 0.18, y=label_y[iid], text=label_txt, showarrow=False, xanchor="left", yanchor="middle", font=dict(size=10.5, color=col)) # 'joined the race' note at the partial leader's first charted week (answers the vacancy honestly) if leader_id and leader_partial: lsub = df[df["imdb_id"] == leader_id].sort_values("wk_no") first = lsub.iloc[0] wk_ago = 12 - int(first["wk_no"]) joined_txt = "joined the race this week" if wk_ago == 0 else f"joined the race {wk_ago} wk ago" fig.add_annotation(x=float(first["wk_no"]), y=float(first["rnk"]), text=joined_txt, showarrow=True, arrowhead=0, arrowwidth=1, arrowcolor=lib.NAVY, ax=-50, ay=38, font=dict(size=9.5, color=lib.NAVY), bgcolor="rgba(255,255,255,0.9)") N_WK = 12 # weekly samples drawn (wk_no 1..12); the OLDEST sample is N_WK-1 weeks ago, not N_WK title_lesson = "The Top 10 is sticky but not static: a few shows hold near the top for months while others climb and fade" what = ("Each line is a show; its height = that week's US rank by weekly Demand Score among shows (1 = biggest audience that " f"week, top of chart). Flat lines near the top = enduring hits; steep lines = fast movers - read left ({N_WK - 1} weeks ago) to right (now).") why = ("Audience demand has staying power - a hit doesn't evaporate, it anchors the charts for months. That predictability is " "exactly what makes a show worth licensing for years and a franchise worth extending.") fig.update_layout(**lib.layout_v2(title_lesson, what, width=1240, height=760), xaxis=lib.standard_xaxis(f"Weekly snapshots ({N_WK - 1} weeks ago → now)"), yaxis=lib.standard_yaxis("US weekly Demand Score rank (shows)")) fig.update_yaxes(autorange="reversed", tickmode="array", tickvals=[1, 5, 10, 15, 20, 25, 30, 35, 40], ticktext=["#1", "#5", "#10", "#15", "#20", "#25", "#30", "#35", "#40"]) now_lab = f"now ({fresh_dt.strftime('%b')} {fresh_dt.day})" # tick anchoring derived from wk_no (wk_no v = N_WK - v weeks ago), so it can never drift off by one tickvals = [1, 4, 7, 10, N_WK] fig.update_xaxes(range=[0.6, 15.2], tickvals=tickvals, ticktext=[(now_lab if v == N_WK else f"{N_WK - v} wk ago") for v in tickvals]) # shade the Top-10 band (neutral de-emphasis hue, not a verdict color) fig.add_hrect(y0=0.5, y1=10.5, fillcolor=lib.MUTED, opacity=0.07, line_width=0, layer="below") fig.add_annotation(x=0.95, y=10.5, text="Top 10", showarrow=False, xanchor="left", yanchor="top", font=dict(size=10, color=lib.MUTED)) gap_note = "a line starts or ends where the show is outside the top 40 that week" if leader_partial: gap_note += " · dotted = the current #1, shown despite missing the persistence cut" fig.add_annotation(xref="paper", yref="paper", x=0.0, y=0.02, xanchor="left", yanchor="bottom", text=gap_note, showarrow=False, font=dict(size=9.5, color=lib.MUTED)) # self-decoding color key (line color is a verdict here, so it must be decodable on the PNG) fig.layout.margin.t = int(fig.layout.margin.t) + 20 fig.layout.height = int(fig.layout.height) + 20 lib.add_color_key(fig, (f"■ net climb over the 12 weeks " f"■ net slide " f"■ the current #1")) lib.add_why_and_footer(fig, why, lib.assemble_footer("PC-Other-04", ["CL-006 viewer panel (distinct viewers), not census"], freshness, ["metrics_demand_rank_history"], "US shows only, ranked on each week's 7-day Demand Score window; shown: in Top 40 for ≥10 of 12 weeks and Top 10 for ≥5, plus the current #1")) lib.save_csv(df, output_dir); lib.export_chart(fig, output_dir, width=1240, height=760) 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-Other-04", intended_lesson_from_spec="Top-10 stability over time (bump chart, weekly 7d-basis show ranks)", rendered_lesson={"reader_title": title_lesson, "reader_subtitle": what, "claim_strength": "strong", "rules_applied": "bump chart; metrics_demand_rank_history di_rank (7d basis, title_type='shows'); weekly samples x12; " "line color = net direction over the window (green climb / red slide, shade steps for traceability; " "navy = current #1 only); current #1 always drawn, dotted + joined-the-race note when it misses the persistence cut"}, row_count=len(df), freshness_date=freshness, query_seconds=q_sec, render_seconds=time.time() - t0 - q_sec) return {"status": "ok", "rows": len(df)} # TI-Other-08 board constants (interpolated into BOTH the query and rules_applied - never hand-write the rules string) HOT_STREAK_MIN_DAYS = 14 # qualifying days required (of HOT_STREAK_COMPARABLE_DAYS comparable) HOT_STREAK_GROWTH_PCT = 10 # 'up' = at least +10% vs the same weekday last week HOT_STREAK_DS_FLOOR = 100 # latest Demand Score floor: keeps the board to titles with a real current audience HOT_STREAK_WINDOW_DAYS = 28 # generate_series window span (inclusive bounds -> 29 daily readings) # comparable days = the window's daily readings (span + 1, inclusive) minus the 7 lag days HOT_STREAK_COMPARABLE_DAYS = HOT_STREAK_WINDOW_DAYS + 1 - 7