spb/firc0001_uqo

1"""N9 - fixed-2010 cohort series and windowed-key first-seen series for Table 14 / Figure 5 (cluster K). Read-only."""2from __future__ import annotations3import sys4from pathlib import Path5sys.path.insert(0, str(Path(__file__).resolve().parent))6from common import *78r = Run("timeseries")9con = connect(SPID_DB)10con.execute("""CREATE TEMP TABLE fy AS SELECT cik, year(filing_date) yr, count(*) n FROM src_filings GROUP BY 1,2""")11con.execute("""CREATE TEMP TABLE cohort AS SELECT cik FROM fy WHERE yr BETWEEN 2010 AND 2025 GROUP BY 1 HAVING count(DISTINCT yr) = 16""")12S = {"cohort_issuers": con.execute("select count(*) from cohort").fetchone()[0], "all_issuers_with_filings": con.execute("select count(distinct cik) from src_filings").fetchone()[0],13     "max_filing_date": str(con.execute("select max(filing_date) from src_filings").fetchone()[0]), "min_filing_date": str(con.execute("select min(filing_date) from src_filings").fetchone()[0]),14     "note": "2010 is left-censored (first_seen cannot precede the corpus start); 2026 is partial (filings to the max date)"}15con.execute("""CREATE TEMP TABLE _k AS SELECT m.*, lower(coalesce(try(locations[1]), '')) AS loc1,16   regexp_extract(lower(regexp_replace(project_name, '[^A-Za-z ]', '', 'g')), '([a-z]{4,})', 1) AS name_tok FROM project_mentions m""")17con.execute("""CREATE TEMP TABLE k AS SELECT *, cik || '|' || project_type || '|' || CASE WHEN loc1 <> '' THEN loc1 ELSE coalesce(nullif(name_tok,''),'general') END AS gkey FROM _k""")18# windowed key: a new episode starts when the gap to the previous mention of the same gkey exceeds kk years19con.execute("""CREATE TEMP TABLE ep AS SELECT gkey, cik, filing_date,20   lag(filing_date) OVER (PARTITION BY gkey ORDER BY filing_date, mention_id) prev FROM k""")21def series(where_cohort: str, label: str):22    return con.execute(f"""23    WITH yrs AS (SELECT unnest(generate_series(2010, 2026)) yr),24         iss AS (SELECT yr, count(DISTINCT cik) issuers_filing FROM fy WHERE {where_cohort.replace('k.cik','fy.cik')} GROUP BY 1),25         men AS (SELECT year(filing_date) yr, count(*) mentions, count(DISTINCT cik) issuers_with_mentions FROM k WHERE {where_cohort} GROUP BY 1),26         fs AS (SELECT year(min(filing_date)) yr, gkey FROM k WHERE {where_cohort} GROUP BY gkey),27         fsy AS (SELECT yr, count(*) first_seen_current FROM fs GROUP BY 1),28         e3 AS (SELECT year(filing_date) yr, count(*) first_seen_k3 FROM ep e WHERE (prev IS NULL OR filing_date - prev > 3*365) AND {where_cohort.replace('k.cik','e.cik')} GROUP BY 1),29         e5 AS (SELECT year(filing_date) yr, count(*) first_seen_k5 FROM ep e WHERE (prev IS NULL OR filing_date - prev > 5*365) AND {where_cohort.replace('k.cik','e.cik')} GROUP BY 1)30    SELECT '{label}' series, yrs.yr, coalesce(iss.issuers_filing,0) issuers_filing, coalesce(men.mentions,0) mentions, coalesce(men.issuers_with_mentions,0) issuers_with_mentions,31           round(coalesce(men.mentions,0)*1.0/nullif(iss.issuers_filing,0),2) mentions_per_issuer_filing,32           coalesce(fsy.first_seen_current,0) first_seen_current_key, round(coalesce(fsy.first_seen_current,0)*1.0/nullif(iss.issuers_filing,0),2) first_seen_per_issuer,33           coalesce(e3.first_seen_k3,0) first_seen_k3, coalesce(e5.first_seen_k5,0) first_seen_k5,34           CASE WHEN yrs.yr = 2010 THEN 'left-censored' WHEN yrs.yr = 2026 THEN 'partial year' ELSE '' END flag35    FROM yrs LEFT JOIN iss USING (yr) LEFT JOIN men USING (yr) LEFT JOIN fsy USING (yr) LEFT JOIN e3 USING (yr) LEFT JOIN e5 USING (yr) ORDER BY 2""").fetchall()36cols = ["series","year","issuers_filing","mentions","issuers_with_mentions","mentions_per_issuer_filing","first_seen_current_key","first_seen_per_issuer","first_seen_k3","first_seen_k5","flag"]37allr = series("TRUE", "all_issuers"); coh = series("k.cik IN (SELECT cik FROM cohort)", "fixed_2010_cohort")38r.write_csv("first_seen_fixed_cohort.csv", cols, allr + coh)39r.write_csv("windowed_key_k3_k5.csv", ["series","year","first_seen_current_key","first_seen_k3","first_seen_k5","flag"], [(x[0], x[1], x[6], x[8], x[9], x[10]) for x in allr + coh])40S["range_2011_2025_first_seen_all"] = [min(x[6] for x in allr if 2011 <= x[1] <= 2025), max(x[6] for x in allr if 2011 <= x[1] <= 2025)]41S["range_2011_2025_first_seen_cohort"] = [min(x[6] for x in coh if 2011 <= x[1] <= 2025), max(x[6] for x in coh if 2011 <= x[1] <= 2025)]42S["first_seen_2010_all"] = [x[6] for x in allr if x[1] == 2010][0]43S["mentions_per_issuer_cohort_2011_vs_2025"] = [[x[1], x[5]] for x in coh if x[1] in (2011, 2015, 2020, 2025)]44S["first_seen_per_issuer_cohort_2011_vs_2025"] = [[x[1], x[7]] for x in coh if x[1] in (2011, 2015, 2020, 2025)]45S["episodes_k3_total"], S["episodes_k5_total"], S["projects_current"] = con.execute("select count(*) filter (where prev is null or filing_date - prev > 3*365), count(*) filter (where prev is null or filing_date - prev > 5*365), count(distinct gkey) from ep").fetchone()46r.write_json("summary.json", S)47r.finish()48