spb/uqo-research-db

1# ============================================================2# SPID — analytics / dashboard aggregations3# ============================================================4from __future__ import annotations56from spid.store import Store789def _rows(store: Store, sql: str) -> list[dict]:10    return store.con.execute(sql).to_arrow_table().to_pylist()111213def overview(store: Store) -> dict:14    con = store.con15    return {16        "companies_with_projects": con.execute("SELECT COUNT(DISTINCT cik) FROM projects").fetchone()[0],17        "total_projects": store.count("projects"),18        "total_mentions": store.count("project_mentions"),19        "total_capex_usd": con.execute("SELECT coalesce(sum(total_amount_usd),0) FROM projects").fetchone()[0],20        "timeline_rows": store.count("project_timeline"),21        "kg_nodes": store.count("kg_nodes"),22        "kg_edges": store.count("kg_edges"),23    }242526def by_type(store: Store) -> list[dict]:27    return _rows(store, """28        SELECT project_type, COUNT(*) AS projects,29               coalesce(sum(total_amount_usd),0) AS total_usd,30               round(avg(avg_confidence),3) AS avg_conf31        FROM projects GROUP BY 1 ORDER BY projects DESC""")323334def by_sector(store: Store) -> list[dict]:35    return _rows(store, """36        SELECT coalesce(sector,'(unknown)') AS sector, COUNT(*) AS projects,37               coalesce(sum(total_amount_usd),0) AS total_usd38        FROM projects GROUP BY 1 ORDER BY projects DESC LIMIT 25""")394041def by_technology(store: Store) -> list[dict]:42    return _rows(store, """43        SELECT t AS technology, COUNT(*) AS projects44        FROM (SELECT unnest(technologies) AS t FROM projects)45        WHERE t <> '' GROUP BY 1 ORDER BY projects DESC LIMIT 25""")464748def by_geography(store: Store) -> list[dict]:49    return _rows(store, """50        SELECT canonical_location AS location, COUNT(*) AS projects,51               coalesce(sum(total_amount_usd),0) AS total_usd52        FROM projects WHERE canonical_location IS NOT NULL AND canonical_location <> ''53        GROUP BY 1 ORDER BY projects DESC LIMIT 25""")545556def capex_by_year(store: Store) -> list[dict]:57    return _rows(store, """58        SELECT EXTRACT(year FROM first_seen) AS year, COUNT(*) AS projects_started,59               coalesce(sum(total_amount_usd),0) AS announced_usd60        FROM projects WHERE first_seen IS NOT NULL GROUP BY 1 ORDER BY 1""")616263def ai_adoption(store: Store) -> list[dict]:64    return _rows(store, """65        SELECT ticker, company_name, sector, COUNT(*) AS ai_projects,66               coalesce(sum(total_amount_usd),0) AS total_usd67        FROM projects68        WHERE project_type = 'ai_initiative' OR list_contains(technologies, 'AI/ML')69        GROUP BY 1,2,3 ORDER BY ai_projects DESC LIMIT 25""")707172def top_companies(store: Store) -> list[dict]:73    return _rows(store, """74        SELECT ticker, company_name, sector, COUNT(*) AS projects,75               coalesce(sum(total_amount_usd),0) AS total_usd76        FROM projects GROUP BY 1,2,3 ORDER BY projects DESC LIMIT 25""")777879def full_report(store: Store) -> dict:80    return {81        "overview": overview(store),82        "by_type": by_type(store),83        "by_sector": by_sector(store),84        "by_technology": by_technology(store),85        "by_geography": by_geography(store),86        "capex_by_year": capex_by_year(store),87        "ai_adoption": ai_adoption(store),88        "top_companies": top_companies(store),89    }90