Every question, every batch — from scratch

WITH raw AS (SELECT g.ref_number, g.recipient_legal_name, TRY_CAST(g.agreement_value AS DOUBLE) AS av, TRY_CAST(g.agreement_start_date AS DATE) AS asd, ROW_NUMBER() OVER (PARTITION BY g.ref_number ORDER BY TRY_CAST(g.amendment_number AS INT) DESC) AS rn FROM grants.grants AS g), deduped AS (SELECT ref_number, recipient_legal_name, av, YEAR(asd + INTERVAL '9' MONTH) AS fy FROM raw WHERE rn = 1 AND asd >= CAST('2018-04-01' AS DATE) AND NOT av IS NULL), orgs AS (SELECT recipient FROM classification.recipient_class WHERE class = 'ORGANIZATIONAL'), tier AS (SELECT legal_name, MAX(CASE WHEN CAST(is_charity AS TEXT) = '1' OR CAST(is_corporation AS TEXT) = '1' THEN 1 ELSE 0 END) AS is_verified FROM entities.recipient_enriched GROUP BY legal_name), first_seen AS (SELECT d.recipient_legal_name, MIN(d.fy) AS first_fy FROM deduped AS d JOIN orgs AS o ON d.recipient_legal_name = o.recipient GROUP BY d.recipient_legal_name), labeled AS (SELECT d.av, CASE WHEN d.fy = fs.first_fy THEN 'newcomer' ELSE 'incumbent' END AS status, CASE WHEN COALESCE(t.is_verified, 0) = 1 THEN 'registry' ELSE 'name_only' END AS si7_tier FROM deduped AS d JOIN orgs AS o ON d.recipient_legal_name = o.recipient JOIN first_seen AS fs ON d.recipient_legal_name = fs.recipient_legal_name LEFT JOIN tier AS t ON d.recipient_legal_name = t.legal_name WHERE d.fy BETWEEN 2020 AND 2023) SELECT si7_tier, status, COUNT(*) AS n_grants, ROUND(QUANTILE_CONT(av, 0.5 ORDER BY av)) AS median_grant, ROUND(AVG(av)) AS mean_grant, ROUND(SUM(av) / 1e9, 3) AS total_bil FROM labeled GROUP BY si7_tier, status ORDER BY si7_tier, status LIMIT 2000

WITH base AS (SELECT ref_number, recipient_legal_name, TRY_CAST(agreement_value AS DOUBLE) AS av, TRY_CAST(agreement_start_date AS DATE) AS sd, ROW_NUMBER() OVER (PARTITION BY ref_number ORDER BY TRY_CAST(amendment_number AS INT) DESC) AS rn FROM grants.grants), deduped AS (SELECT * FROM base WHERE rn = 1), org_grants AS (SELECT d.ref_number, d.av FROM deduped AS d INNER JOIN classification.recipient_class AS rc ON rc.recipient = d.recipient_legal_name AND rc.class = 'ORGANIZATIONAL' WHERE d.sd >= CAST('2018-04-01' AS DATE)) SELECT COUNT(*) AS base_rows, COUNT(DISTINCT ref_number) AS distinct_refs, ROUND(SUM(av) / 1e9, 3) AS total_bil, ROUND(SUM(av) / 1e6, 1) AS total_mil FROM org_grants LIMIT 2000

WITH base AS (SELECT ref_number, recipient_legal_name, TRY_CAST(agreement_value AS DOUBLE) AS av, TRY_CAST(agreement_start_date AS DATE) AS sd, ROW_NUMBER() OVER (PARTITION BY ref_number ORDER BY TRY_CAST(amendment_number AS INT) DESC) AS rn FROM grants.grants), deduped AS (SELECT * FROM base WHERE rn = 1), org_grants AS (SELECT d.ref_number, d.av, d.recipient_legal_name FROM deduped AS d INNER JOIN classification.recipient_class AS rc ON rc.recipient = d.recipient_legal_name AND rc.class = 'ORGANIZATIONAL' WHERE d.sd >= CAST('2018-04-01' AS DATE)), cat_join AS (SELECT og.ref_number, og.av, cm.category FROM org_grants AS og LEFT JOIN (SELECT legal_name, MAX(category) AS category FROM entities.charity GROUP BY legal_name) AS cm ON cm.legal_name = og.recipient_legal_name) SELECT CASE WHEN category = '0030' THEN 'Religious (0030)' WHEN category = '0160' THEN 'Education/research (0160)' WHEN category IS NULL THEN 'Unmatched/Non-charity' ELSE 'Other CRA charity' END AS cat_group, COUNT(*) AS base_rows, COUNT(DISTINCT ref_number) AS distinct_refs, ROUND(SUM(av) / 1e6, 2) AS total_mil FROM cat_join GROUP BY 1 ORDER BY total_mil DESC LIMIT 2000

WITH base AS (SELECT ref_number, recipient_legal_name, TRY_CAST(agreement_value AS DOUBLE) AS av, TRY_CAST(agreement_start_date AS DATE) AS sd, ROW_NUMBER() OVER (PARTITION BY ref_number ORDER BY TRY_CAST(amendment_number AS INT) DESC) AS rn FROM grants.grants), deduped AS (SELECT * FROM base WHERE rn = 1), org_grants AS (SELECT d.ref_number, d.av, d.recipient_legal_name FROM deduped AS d INNER JOIN classification.recipient_class AS rc ON rc.recipient = d.recipient_legal_name AND rc.class = 'ORGANIZATIONAL' WHERE d.sd >= CAST('2018-04-01' AS DATE)), charity_names AS (SELECT DISTINCT legal_name FROM entities.charity), org_agg AS (SELECT og.recipient_legal_name, MAX(CASE WHEN NOT cn.legal_name IS NULL THEN 1 ELSE 0 END) AS is_matched, SUM(og.av) AS total_dollars FROM org_grants AS og LEFT JOIN charity_names AS cn ON cn.legal_name = og.recipient_legal_name GROUP BY og.recipient_legal_name) SELECT COUNT(*) AS total_orgs, SUM(CASE WHEN is_matched = 1 THEN 1 ELSE 0 END) AS charity_matched, SUM(CASE WHEN is_matched = 0 THEN 1 ELSE 0 END) AS unmatched, ROUND(100.0 * SUM(CASE WHEN is_matched = 0 THEN 1 ELSE 0 END) / COUNT(*), 1) AS pct_unmatched, ROUND(SUM(CASE WHEN is_matched = 1 THEN total_dollars ELSE 0 END) / 1e9, 3) AS matched_bil, ROUND(SUM(CASE WHEN is_matched = 0 THEN total_dollars ELSE 0 END) / 1e9, 3) AS unmatched_bil FROM org_agg LIMIT 2000

SELECT column_name, data_type FROM information_schema.columns WHERE table_schema = 'corpus' AND table_name = 'cra_charities_ident' ORDER BY ordinal_position LIMIT 30

WITH base AS (SELECT ref_number, recipient_legal_name, TRY_CAST(agreement_value AS DOUBLE) AS av, TRY_CAST(agreement_start_date AS DATE) AS sd, ROW_NUMBER() OVER (PARTITION BY ref_number ORDER BY TRY_CAST(amendment_number AS INT) DESC) AS rn FROM grants.grants), deduped AS (SELECT recipient_legal_name, av, sd, CASE WHEN EXTRACT(MONTH FROM sd) >= 4 THEN EXTRACT(YEAR FROM sd) ELSE EXTRACT(YEAR FROM sd) - 1 END AS fy FROM base WHERE rn = 1 AND NOT sd IS NULL AND sd >= CAST('2018-04-01' AS DATE)), classified AS (SELECT recipient FROM classification.recipient_class WHERE class = 'ORGANIZATIONAL'), cat_lookup AS (SELECT legal_name, MAX(category) AS category FROM entities.charity GROUP BY legal_name), org_summary AS (SELECT d.recipient_legal_name, COALESCE(ec.category, 'UNMATCHED') AS category, COUNT(DISTINCT d.fy) AS n_fy, SUM(d.av) AS total_val FROM deduped AS d JOIN classified AS c ON d.recipient_legal_name = c.recipient LEFT JOIN cat_lookup AS ec ON d.recipient_legal_name = ec.legal_name GROUP BY d.recipient_legal_name, ec.category) SELECT category, COUNT(*) AS n_orgs, ROUND(AVG(n_fy), 2) AS avg_yrs_active, SUM(CASE WHEN n_fy >= 3 THEN 1 ELSE 0 END) AS n_recurrent_3plus, ROUND(100.0 * SUM(CASE WHEN n_fy >= 3 THEN 1 ELSE 0 END) / COUNT(*), 1) AS pct_recurrent_3plus, SUM(CASE WHEN n_fy >= 5 THEN 1 ELSE 0 END) AS n_recurrent_5plus, ROUND(100.0 * SUM(CASE WHEN n_fy >= 5 THEN 1 ELSE 0 END) / COUNT(*), 1) AS pct_recurrent_5plus, ROUND(SUM(total_val) / 1e6, 1) AS total_cat_mil, ROUND(AVG(total_val) / 1e3, 0) AS avg_per_org_k FROM org_summary GROUP BY category HAVING COUNT(*) >= 5 ORDER BY pct_recurrent_3plus DESC LIMIT 2000

WITH base AS (SELECT ref_number, recipient_legal_name, TRY_CAST(agreement_value AS DOUBLE) AS av, TRY_CAST(agreement_start_date AS DATE) AS sd, ROW_NUMBER() OVER (PARTITION BY ref_number ORDER BY TRY_CAST(amendment_number AS INT) DESC) AS rn FROM grants.grants), deduped AS (SELECT recipient_legal_name, av, sd FROM base WHERE rn = 1 AND NOT sd IS NULL AND sd >= CAST('2018-04-01' AS DATE)), classified AS (SELECT recipient FROM classification.recipient_class WHERE class = 'ORGANIZATIONAL'), cat_lookup AS (SELECT legal_name, MAX(category) AS category FROM entities.charity GROUP BY legal_name), funded_charity AS (SELECT d.recipient_legal_name, ec.category, ROUND(SUM(d.av) / 1e6, 3) AS total_mil, COUNT(*) AS n_grants, COUNT(DISTINCT CASE WHEN EXTRACT(MONTH FROM d.sd) >= 4 THEN EXTRACT(YEAR FROM d.sd) ELSE EXTRACT(YEAR FROM d.sd) - 1 END) AS n_fy FROM deduped AS d JOIN classified AS c ON d.recipient_legal_name = c.recipient JOIN cat_lookup AS ec ON d.recipient_legal_name = ec.legal_name WHERE ec.category IN ('0030', '0160', '0001', '0013', '0170', '0110') GROUP BY d.recipient_legal_name, ec.category), ranked AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY category ORDER BY total_mil DESC) AS rk FROM funded_charity) SELECT category, recipient_legal_name, total_mil, n_grants, n_fy FROM ranked WHERE rk <= 5 ORDER BY category, total_mil DESC LIMIT 2000

SELECT category, COUNT(DISTINCT bn9) AS n_charities, ROUND(COUNT(DISTINCT bn9) * 100.0 / (SELECT COUNT(DISTINCT bn9) FROM entities.charity WHERE NOT category IS NULL), 1) AS pct_universe FROM entities.charity WHERE NOT category IS NULL GROUP BY category ORDER BY n_charities DESC LIMIT 20

WITH ranked AS (SELECT ref_number, recipient_legal_name, TRY_CAST(agreement_value AS DOUBLE) AS av, TRY_CAST(agreement_start_date AS DATE) AS sd, ROW_NUMBER() OVER (PARTITION BY ref_number ORDER BY TRY_CAST(amendment_number AS INT) DESC) AS rn FROM grants.grants), deduped AS (SELECT ref_number, recipient_legal_name, av FROM ranked WHERE rn = 1 AND CASE WHEN EXTRACT(MONTH FROM sd) >= 4 THEN EXTRACT(YEAR FROM sd) ELSE EXTRACT(YEAR FROM sd) - 1 END >= 2018), org_chrty AS (SELECT d.recipient_legal_name, SUM(d.av) AS org_total, COUNT(*) AS n_grants FROM deduped AS d JOIN classification.recipient_class AS rc ON rc.recipient = d.recipient_legal_name AND rc.class = 'ORGANIZATIONAL' JOIN entities.recipient_enriched AS re ON re.legal_name = d.recipient_legal_name AND CAST(re.is_charity AS TEXT) = '1' GROUP BY d.recipient_legal_name), cat_agg AS (SELECT COALESCE(ec.category, 'UNMATCHED_TO_CHARITY') AS category, COUNT(DISTINCT oc.recipient_legal_name) AS n_funded_orgs, SUM(oc.org_total) AS total_dollars, SUM(oc.n_grants) AS total_grants FROM org_chrty AS oc LEFT JOIN entities.charity AS ec ON ec.legal_name = oc.recipient_legal_name GROUP BY COALESCE(ec.category, 'UNMATCHED_TO_CHARITY')) SELECT category, n_funded_orgs, ROUND(n_funded_orgs * 100.0 / SUM(n_funded_orgs) OVER (), 1) AS pct_funded_orgs, ROUND(total_dollars / 1e9, 3) AS total_bil, ROUND(total_dollars * 100.0 / SUM(total_dollars) OVER (), 1) AS pct_dollars, ROUND(total_dollars / n_funded_orgs / 1000, 0) AS avg_per_org_k, ROUND(total_grants * 1.0 / n_funded_orgs, 1) AS avg_grants_per_org FROM cat_agg ORDER BY total_dollars DESC LIMIT 20

WITH deduped AS (SELECT ref_number, recipient_legal_name, TRY_CAST(agreement_value AS DOUBLE) AS av, CASE WHEN EXTRACT(MONTH FROM TRY_CAST(agreement_start_date AS DATE)) >= 4 THEN EXTRACT(YEAR FROM TRY_CAST(agreement_start_date AS DATE)) ELSE EXTRACT(YEAR FROM TRY_CAST(agreement_start_date AS DATE)) - 1 END AS fy, ROW_NUMBER() OVER (PARTITION BY ref_number ORDER BY TRY_CAST(amendment_number AS INT) DESC) AS rn FROM grants.grants), org_class AS (SELECT DISTINCT recipient FROM classification.recipient_class WHERE class = 'ORGANIZATIONAL'), base AS (SELECT d.recipient_legal_name, d.fy, d.av FROM deduped AS d JOIN org_class AS oc ON oc.recipient = d.recipient_legal_name WHERE d.rn = 1 AND d.fy BETWEEN 2018 AND 2023), org_tier AS (SELECT b.recipient_legal_name, CASE WHEN MAX(CAST(re.is_charity AS TEXT)) = '1' OR MAX(CAST(re.is_corporation AS TEXT)) = '1' THEN 'registry_verified' ELSE 'name_only' END AS id_tier FROM (SELECT DISTINCT recipient_legal_name FROM base) AS b LEFT JOIN entities.recipient_enriched AS re ON re.legal_name = b.recipient_legal_name GROUP BY b.recipient_legal_name), first_seen AS (SELECT recipient_legal_name, MIN(fy) AS first_fy FROM base GROUP BY recipient_legal_name), panel AS (SELECT b.fy, b.av, COALESCE(t.id_tier, 'name_only') AS id_tier, CASE WHEN b.fy = f.first_fy THEN 'newcomer' ELSE 'incumbent' END AS status FROM base AS b JOIN first_seen AS f ON f.recipient_legal_name = b.recipient_legal_name LEFT JOIN org_tier AS t ON t.recipient_legal_name = b.recipient_legal_name) SELECT id_tier, fy, ROUND(SUM(CASE WHEN status = 'incumbent' THEN av ELSE 0 END) / 1e9, 3) AS inc_bil, ROUND(SUM(CASE WHEN status = 'newcomer' THEN av ELSE 0 END) / 1e9, 3) AS new_bil, COUNT(DISTINCT CASE WHEN status = 'incumbent' THEN recipient_legal_name END) AS n_inc, COUNT(DISTINCT CASE WHEN status = 'newcomer' THEN recipient_legal_name END) AS n_new, ROUND(100.0 * SUM(CASE WHEN status = 'incumbent' THEN av ELSE 0 END) / NULLIF(SUM(av), 0), 1) AS inc_pct FROM panel GROUP BY id_tier, fy ORDER BY id_tier, fy LIMIT 2000

WITH deduped AS (SELECT ref_number, recipient_legal_name, TRY_CAST(agreement_value AS DOUBLE) AS av, CASE WHEN EXTRACT(MONTH FROM TRY_CAST(agreement_start_date AS DATE)) >= 4 THEN EXTRACT(YEAR FROM TRY_CAST(agreement_start_date AS DATE)) ELSE EXTRACT(YEAR FROM TRY_CAST(agreement_start_date AS DATE)) - 1 END AS fy, ROW_NUMBER() OVER (PARTITION BY ref_number ORDER BY TRY_CAST(amendment_number AS INT) DESC) AS rn FROM grants.grants), org_class AS (SELECT DISTINCT recipient FROM classification.recipient_class WHERE class = 'ORGANIZATIONAL'), base AS (SELECT d.recipient_legal_name, d.fy, d.av FROM deduped AS d JOIN org_class AS oc ON oc.recipient = d.recipient_legal_name WHERE d.rn = 1 AND d.fy BETWEEN 2018 AND 2023), cohort_2018 AS (SELECT DISTINCT recipient_legal_name FROM base WHERE fy = 2018), persistent AS (SELECT recipient_legal_name FROM base WHERE fy BETWEEN 2018 AND 2023 GROUP BY recipient_legal_name HAVING COUNT(DISTINCT fy) >= 5), annual_total AS (SELECT fy, SUM(av) / 1e9 AS total_bil FROM base GROUP BY fy), annual_2018c AS (SELECT fy, COUNT(DISTINCT recipient_legal_name) AS n_from_2018, SUM(av) / 1e9 AS cohort2018_bil FROM base WHERE recipient_legal_name IN (SELECT recipient_legal_name FROM cohort_2018) GROUP BY fy), annual_persist AS (SELECT fy, COUNT(DISTINCT recipient_legal_name) AS n_persist, SUM(av) / 1e9 AS persist_bil FROM base WHERE recipient_legal_name IN (SELECT recipient_legal_name FROM persistent) GROUP BY fy) SELECT t.fy, ROUND(t.total_bil, 3) AS total_bil, a.n_from_2018, ROUND(a.cohort2018_bil, 3) AS cohort2018_bil, ROUND(100.0 * a.cohort2018_bil / t.total_bil, 1) AS pct_2018c, p.n_persist, ROUND(p.persist_bil, 3) AS persist_bil, ROUND(100.0 * p.persist_bil / t.total_bil, 1) AS pct_persist FROM annual_total AS t LEFT JOIN annual_2018c AS a ON a.fy = t.fy LEFT JOIN annual_persist AS p ON p.fy = t.fy ORDER BY t.fy LIMIT 2000

WITH deduped AS (SELECT ref_number, recipient_legal_name, TRY_CAST(agreement_value AS DOUBLE) AS av, CASE WHEN EXTRACT(MONTH FROM TRY_CAST(agreement_start_date AS DATE)) >= 4 THEN EXTRACT(YEAR FROM TRY_CAST(agreement_start_date AS DATE)) ELSE EXTRACT(YEAR FROM TRY_CAST(agreement_start_date AS DATE)) - 1 END AS fy, ROW_NUMBER() OVER (PARTITION BY ref_number ORDER BY TRY_CAST(amendment_number AS INT) DESC) AS rn FROM grants.grants), org_class AS (SELECT DISTINCT recipient FROM classification.recipient_class WHERE class = 'ORGANIZATIONAL'), base AS (SELECT d.recipient_legal_name, d.fy, d.av FROM deduped AS d JOIN org_class AS oc ON oc.recipient = d.recipient_legal_name WHERE d.rn = 1 AND d.fy BETWEEN 2018 AND 2023), org_stats AS (SELECT recipient_legal_name, COUNT(DISTINCT fy) AS yrs_active, COUNT(*) AS n_grants, SUM(av) AS total_av FROM base GROUP BY recipient_legal_name), classed AS (SELECT *, CASE WHEN yrs_active = 1 THEN '1_yr' WHEN yrs_active BETWEEN 2 AND 3 THEN '2-3yr' WHEN yrs_active BETWEEN 4 AND 5 THEN '4-5yr' ELSE '6yr_all' END AS tenure_class FROM org_stats) SELECT tenure_class, COUNT(*) AS n_orgs, SUM(n_grants) AS total_grants, ROUND(SUM(n_grants) * 1.0 / COUNT(*), 1) AS avg_grants_per_org, ROUND(SUM(total_av) / SUM(n_grants) / 1000, 1) AS avg_grant_k, ROUND(SUM(total_av) / COUNT(*) / 1000, 1) AS avg_total_per_org_k, ROUND(SUM(total_av) / 1e9, 3) AS total_bil, ROUND(100.0 * SUM(total_av) / SUM(SUM(total_av)) OVER (), 1) AS pct_dollars FROM classed GROUP BY tenure_class ORDER BY tenure_class LIMIT 2000

WITH deduped AS (SELECT ref_number, recipient_legal_name, TRY_CAST(agreement_value AS DOUBLE) AS av, ROW_NUMBER() OVER (PARTITION BY ref_number ORDER BY TRY_CAST(amendment_number AS INT) DESC) AS rn FROM grants.grants), clean AS (SELECT ref_number, recipient_legal_name, av FROM deduped WHERE rn = 1), org_class AS (SELECT DISTINCT recipient FROM classification.recipient_class WHERE class = 'ORGANIZATIONAL') SELECT COUNT(*) AS base_rows, COUNT(DISTINCT ref_number) AS distinct_refs, ROUND(SUM(av) / 1e9, 3) AS total_net_bil, COUNT(DISTINCT recipient_legal_name) AS total_recipients, (SELECT COUNT(DISTINCT recipient) FROM classification.recipient_class WHERE class = 'ORGANIZATIONAL') AS org_class_size, COUNT(DISTINCT CASE WHEN NOT oc.recipient IS NULL THEN recipient_legal_name END) AS org_recipients_in_grants, COUNT(CASE WHEN NOT oc.recipient IS NULL THEN 1 END) AS org_grant_rows, ROUND(SUM(CASE WHEN NOT oc.recipient IS NULL THEN av ELSE 0 END) / 1e9, 3) AS org_dollars_bil, ROUND(100.0 * SUM(CASE WHEN NOT oc.recipient IS NULL THEN av ELSE 0 END) / NULLIF(SUM(av), 0), 2) AS org_dollar_pct, ROUND(100.0 * COUNT(CASE WHEN NOT oc.recipient IS NULL THEN 1 END) / NULLIF(COUNT(*), 0), 2) AS org_row_pct FROM clean AS c LEFT JOIN org_class AS oc ON c.recipient_legal_name = oc.recipient LIMIT 2000

WITH deduped AS (SELECT ref_number, recipient_legal_name, TRY_CAST(agreement_value AS DOUBLE) AS av, CASE WHEN EXTRACT(MONTH FROM TRY_CAST(agreement_start_date AS DATE)) >= 4 THEN EXTRACT(YEAR FROM TRY_CAST(agreement_start_date AS DATE)) ELSE EXTRACT(YEAR FROM TRY_CAST(agreement_start_date AS DATE)) - 1 END AS fy, ROW_NUMBER() OVER (PARTITION BY ref_number ORDER BY TRY_CAST(amendment_number AS INT) DESC) AS rn FROM grants.grants), clean AS (SELECT ref_number, recipient_legal_name, av, fy FROM deduped WHERE rn = 1), org_class AS (SELECT DISTINCT recipient FROM classification.recipient_class WHERE class = 'ORGANIZATIONAL'), org_grants AS (SELECT c.recipient_legal_name, c.av, c.fy FROM clean AS c INNER JOIN org_class AS oc ON c.recipient_legal_name = oc.recipient WHERE c.fy >= 2018), first_seen AS (SELECT recipient_legal_name, MIN(fy) AS first_fy FROM org_grants GROUP BY recipient_legal_name), tagged AS (SELECT g.fy, g.recipient_legal_name, g.av, CASE WHEN g.fy = fs.first_fy THEN 'newcomer' ELSE 'incumbent' END AS status FROM org_grants AS g JOIN first_seen AS fs ON g.recipient_legal_name = fs.recipient_legal_name) SELECT fy AS fiscal_year, ROUND(SUM(CASE WHEN status = 'incumbent' THEN av ELSE 0 END) / 1e9, 3) AS incumbent_bil, ROUND(SUM(CASE WHEN status = 'newcomer' THEN av ELSE 0 END) / 1e9, 3) AS newcomer_bil, ROUND(SUM(av) / 1e9, 3) AS total_bil, COUNT(DISTINCT CASE WHEN status = 'incumbent' THEN recipient_legal_name END) AS n_incumbents, COUNT(DISTINCT CASE WHEN status = 'newcomer' THEN recipient_legal_name END) AS n_newcomers, ROUND(100.0 * SUM(CASE WHEN status = 'incumbent' THEN av ELSE 0 END) / NULLIF(SUM(av), 0), 1) AS incumbent_pct FROM tagged GROUP BY fy ORDER BY fy LIMIT 2000

WITH deduped AS (SELECT ref_number, recipient_legal_name, CASE WHEN EXTRACT(MONTH FROM TRY_CAST(agreement_start_date AS DATE)) >= 4 THEN EXTRACT(YEAR FROM TRY_CAST(agreement_start_date AS DATE)) ELSE EXTRACT(YEAR FROM TRY_CAST(agreement_start_date AS DATE)) - 1 END AS fy, ROW_NUMBER() OVER (PARTITION BY ref_number ORDER BY TRY_CAST(amendment_number AS INT) DESC) AS rn FROM grants.grants), org_class AS (SELECT DISTINCT recipient FROM classification.recipient_class WHERE class = 'ORGANIZATIONAL'), org_activity AS (SELECT DISTINCT d.recipient_legal_name, d.fy FROM deduped AS d INNER JOIN org_class AS oc ON d.recipient_legal_name = oc.recipient WHERE d.rn = 1 AND d.fy >= 2018 AND d.fy <= 2023), first_seen AS (SELECT recipient_legal_name, MIN(fy) AS birth_cohort FROM org_activity GROUP BY recipient_legal_name), cohort_sizes AS (SELECT birth_cohort, COUNT(DISTINCT recipient_legal_name) AS cohort_size FROM first_seen WHERE birth_cohort BETWEEN 2018 AND 2022 GROUP BY birth_cohort), cohort_activity AS (SELECT fs.birth_cohort, oa.fy AS active_fy, COUNT(DISTINCT oa.recipient_legal_name) AS n_active FROM first_seen AS fs JOIN org_activity AS oa ON fs.recipient_legal_name = oa.recipient_legal_name WHERE fs.birth_cohort BETWEEN 2018 AND 2022 GROUP BY fs.birth_cohort, oa.fy) SELECT ca.birth_cohort, ca.active_fy AS measure_fy, cs.cohort_size, ca.n_active AS n_surviving, ROUND(100.0 * ca.n_active / NULLIF(cs.cohort_size, 0), 1) AS survival_pct, ca.active_fy - ca.birth_cohort AS years_elapsed FROM cohort_activity AS ca JOIN cohort_sizes AS cs ON ca.birth_cohort = cs.birth_cohort ORDER BY ca.birth_cohort, ca.active_fy LIMIT 2000

WITH org_class AS (SELECT DISTINCT recipient FROM classification.recipient_class WHERE class = 'ORGANIZATIONAL'), deduped AS (SELECT ref_number, recipient_legal_name, TRY_CAST(agreement_value AS DOUBLE) AS av, ROW_NUMBER() OVER (PARTITION BY ref_number ORDER BY TRY_CAST(amendment_number AS INT) DESC) AS rn FROM grants.grants), clean AS (SELECT recipient_legal_name, av FROM deduped WHERE rn = 1), funded_org_totals AS (SELECT c.recipient_legal_name, SUM(c.av) AS total_av FROM clean AS c INNER JOIN org_class AS oc ON c.recipient_legal_name = oc.recipient GROUP BY c.recipient_legal_name), funded_with_bn9 AS (SELECT fo.recipient_legal_name, fo.total_av, er.bn9 FROM funded_org_totals AS fo JOIN entities.recipient AS er ON fo.recipient_legal_name = er.legal_name WHERE NOT er.bn9 IS NULL), deduped_charity AS (SELECT bn9, MAX(category) AS category FROM entities.charity WHERE NOT category IS NULL AND NOT bn9 IS NULL GROUP BY bn9), funded_categories AS (SELECT dc.category, COUNT(DISTINCT fw.recipient_legal_name) AS n_funded, ROUND(SUM(fw.total_av) / 1e6, 2) AS funded_mil FROM funded_with_bn9 AS fw JOIN deduped_charity AS dc ON fw.bn9 = dc.bn9 GROUP BY dc.category), all_cat AS (SELECT category, COUNT(DISTINCT bn9) AS n_all FROM entities.charity WHERE NOT category IS NULL AND NOT bn9 IS NULL GROUP BY category) SELECT COALESCE(fc.category, ac.category) AS category, COALESCE(fc.n_funded, 0) AS n_funded_org, COALESCE(fc.funded_mil, 0) AS funded_mil, COALESCE(ac.n_all, 0) AS n_all_charities, ROUND(100.0 * COALESCE(fc.n_funded, 0) / NULLIF(SUM(COALESCE(fc.n_funded, 0)) OVER (), 0), 1) AS pct_of_funded, ROUND(100.0 * COALESCE(ac.n_all, 0) / NULLIF(SUM(COALESCE(ac.n_all, 0)) OVER (), 0), 1) AS pct_of_all FROM funded_categories AS fc FULL OUTER JOIN all_cat AS ac ON fc.category = ac.category ORDER BY n_funded_org DESC LIMIT 2000

WITH latest AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY ref_number ORDER BY TRY_CAST(amendment_number AS INT) DESC) AS rn FROM grants.grants WHERE LOWER(COALESCE(recipient_legal_name, '')) NOT LIKE '%batch report%'), deduped AS (SELECT * FROM latest WHERE rn = 1), txt AS (SELECT ref_number, recipient_legal_name, TRY_CAST(agreement_value AS DOUBLE) AS val, TRY_CAST(agreement_start_date AS DATE) AS sd, TRY_CAST(agreement_end_date AS DATE) AS ed, LOWER(COALESCE(description_en, '') || ' ' || COALESCE(prog_purpose_en, '')) AS combo FROM deduped), classified AS (SELECT *, CASE WHEN (combo LIKE '%disabilit%' OR combo LIKE '%veteran%' OR combo LIKE '%cancer%' OR combo LIKE '%alzheimer%' OR combo LIKE '%palliative%' OR combo LIKE '%parkinson%' OR combo LIKE '%chronic ill%' OR combo LIKE '%rare disease%') AND NOT (combo LIKE '%homeless%' OR combo LIKE '%poverty%' OR combo LIKE '%addiction%' OR combo LIKE '%substance use%' OR combo LIKE '%incarcerat%' OR combo LIKE '%indigenous%' OR combo LIKE '%first nation%' OR combo LIKE '%inuit%' OR combo LIKE '%metis%' OR combo LIKE '%aboriginal%' OR combo LIKE '%newcomer%' OR combo LIKE '%immigrant%' OR combo LIKE '%refugee%' OR combo LIKE '%mental health%' OR combo LIKE '%psychiatr%') THEN 'DESERVING_ONLY' WHEN (combo LIKE '%homeless%' OR combo LIKE '%poverty%' OR combo LIKE '%addiction%' OR combo LIKE '%substance use%' OR combo LIKE '%incarcerat%' OR combo LIKE '%reintegrat%' OR combo LIKE '%drug use%' OR combo LIKE '%street youth%') AND NOT (combo LIKE '%disabilit%' OR combo LIKE '%veteran%' OR combo LIKE '%cancer%' OR combo LIKE '%alzheimer%' OR combo LIKE '%palliative%' OR combo LIKE '%parkinson%' OR combo LIKE '%chronic ill%' OR combo LIKE '%indigenous%' OR combo LIKE '%first nation%' OR combo LIKE '%inuit%' OR combo LIKE '%metis%' OR combo LIKE '%aboriginal%' OR combo LIKE '%newcomer%' OR combo LIKE '%immigrant%' OR combo LIKE '%refugee%' OR combo LIKE '%mental health%' OR combo LIKE '%psychiatr%') THEN 'UNDESERVING_ONLY' WHEN combo LIKE '%indigenous%' OR combo LIKE '%first nation%' OR combo LIKE '%inuit%' OR combo LIKE '%metis%' OR combo LIKE '%aboriginal%' THEN 'INDIGENOUS_INCL' WHEN combo LIKE '%newcomer%' OR combo LIKE '%immigrant%' OR combo LIKE '%refugee%' OR combo LIKE '%asylum%' THEN 'NEWCOMERS_INCL' WHEN combo LIKE '%mental health%' OR combo LIKE '%psychiatr%' THEN 'MENTAL_HEALTH_INCL' ELSE 'NO_SIGNAL' END AS pop_frame FROM txt) SELECT pop_frame, COUNT(*) AS n, COUNT(DISTINCT recipient_legal_name) AS unique_recip, ROUND(1.0 * COUNT(*) / NULLIF(COUNT(DISTINCT recipient_legal_name), 0), 2) AS avg_grants_per_recip, COUNT(*) FILTER(WHERE NOT ed IS NULL AND ed > sd) AS has_end, ROUND(QUANTILE_CONT(CASE WHEN NOT ed IS NULL AND ed > sd THEN CAST(DATE_DIFF('DAY', sd, ed) AS DOUBLE) ELSE NULL END, 0.5 ORDER BY CASE WHEN NOT ed IS NULL AND ed > sd THEN CAST(DATE_DIFF('DAY', sd, ed) AS DOUBLE) ELSE NULL END), 0) AS med_dur_days, ROUND(AVG(CASE WHEN NOT ed IS NULL AND ed > sd THEN DATE_DIFF('DAY', sd, ed) ELSE NULL END), 0) AS avg_dur_days, ROUND(AVG(val) / 1e3, 1) AS avg_K, ROUND(QUANTILE_CONT(val, 0.5 ORDER BY val) / 1e3, 1) AS med_K FROM classified GROUP BY pop_frame ORDER BY n DESC LIMIT 2000

WITH latest AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY ref_number ORDER BY TRY_CAST(amendment_number AS INT) DESC) AS rn FROM grants.grants WHERE LOWER(COALESCE(recipient_legal_name, '')) NOT LIKE '%batch report%'), deduped AS (SELECT * FROM latest WHERE rn = 1), txt AS (SELECT owner_org, TRY_CAST(agreement_value AS DOUBLE) AS val, LOWER(COALESCE(description_en, '') || ' ' || COALESCE(prog_purpose_en, '')) AS combo FROM deduped), classified AS (SELECT owner_org, val, CASE WHEN (combo LIKE '%disabilit%' OR combo LIKE '%veteran%' OR combo LIKE '%cancer%' OR combo LIKE '%alzheimer%' OR combo LIKE '%palliative%' OR combo LIKE '%parkinson%' OR combo LIKE '%chronic ill%' OR combo LIKE '%rare disease%') AND NOT (combo LIKE '%homeless%' OR combo LIKE '%poverty%' OR combo LIKE '%addiction%' OR combo LIKE '%substance use%' OR combo LIKE '%incarcerat%' OR combo LIKE '%indigenous%' OR combo LIKE '%first nation%' OR combo LIKE '%inuit%' OR combo LIKE '%metis%' OR combo LIKE '%aboriginal%' OR combo LIKE '%newcomer%' OR combo LIKE '%immigrant%' OR combo LIKE '%refugee%' OR combo LIKE '%mental health%' OR combo LIKE '%psychiatr%') THEN 'DESERVING_ONLY' WHEN (combo LIKE '%homeless%' OR combo LIKE '%poverty%' OR combo LIKE '%addiction%' OR combo LIKE '%substance use%' OR combo LIKE '%incarcerat%' OR combo LIKE '%reintegrat%' OR combo LIKE '%drug use%' OR combo LIKE '%street youth%') AND NOT (combo LIKE '%disabilit%' OR combo LIKE '%veteran%' OR combo LIKE '%cancer%' OR combo LIKE '%alzheimer%' OR combo LIKE '%palliative%' OR combo LIKE '%parkinson%' OR combo LIKE '%chronic ill%' OR combo LIKE '%indigenous%' OR combo LIKE '%first nation%' OR combo LIKE '%inuit%' OR combo LIKE '%metis%' OR combo LIKE '%aboriginal%' OR combo LIKE '%newcomer%' OR combo LIKE '%immigrant%' OR combo LIKE '%refugee%' OR combo LIKE '%mental health%' OR combo LIKE '%psychiatr%') THEN 'UNDESERVING_ONLY' ELSE NULL END AS pop_frame FROM txt), agg AS (SELECT pop_frame, owner_org, COUNT(*) AS n, ROUND(SUM(val) / 1e6, 1) AS total_M, ROW_NUMBER() OVER (PARTITION BY pop_frame ORDER BY SUM(val) DESC) AS rnk FROM classified WHERE NOT pop_frame IS NULL GROUP BY pop_frame, owner_org) SELECT pop_frame, owner_org, n, total_M FROM agg WHERE rnk <= 8 ORDER BY pop_frame, rnk LIMIT 2000

WITH latest AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY ref_number ORDER BY TRY_CAST(amendment_number AS INT) DESC) AS rn FROM grants.grants WHERE LOWER(COALESCE(recipient_legal_name, '')) NOT LIKE '%batch report%' AND LOWER(COALESCE(recipient_legal_name, '')) NOT LIKE '%rapport en lots%'), base AS (SELECT ref_number, TRY_CAST(agreement_value AS DOUBLE) AS val, description_en, prog_purpose_en, description_fr, prog_purpose_fr FROM latest WHERE rn = 1) SELECT COUNT(*) AS base_rows, COUNT(DISTINCT ref_number) AS distinct_refs, ROUND(SUM(val) / 1e9, 2) AS total_net_B, SUM(CASE WHEN NOT description_en IS NULL AND LENGTH(TRIM(description_en)) > 20 THEN 1 ELSE 0 END) AS desc_en_n /* description_en coverage */, ROUND(100.0 * SUM(CASE WHEN NOT description_en IS NULL AND LENGTH(TRIM(description_en)) > 20 THEN 1 ELSE 0 END) / COUNT(*), 1) AS desc_en_pct, ROUND(100.0 * SUM(CASE WHEN NOT description_en IS NULL AND LENGTH(TRIM(description_en)) > 20 THEN val ELSE 0 END) / SUM(val), 1) AS desc_en_val_pct, SUM(CASE WHEN NOT prog_purpose_en IS NULL AND LENGTH(TRIM(prog_purpose_en)) > 20 THEN 1 ELSE 0 END) AS prog_en_n /* prog_purpose_en coverage */, ROUND(100.0 * SUM(CASE WHEN NOT prog_purpose_en IS NULL AND LENGTH(TRIM(prog_purpose_en)) > 20 THEN 1 ELSE 0 END) / COUNT(*), 1) AS prog_en_pct, ROUND(100.0 * SUM(CASE WHEN NOT prog_purpose_en IS NULL AND LENGTH(TRIM(prog_purpose_en)) > 20 THEN val ELSE 0 END) / SUM(val), 1) AS prog_en_val_pct, SUM(CASE WHEN (NOT description_en IS NULL AND LENGTH(TRIM(description_en)) > 20) OR (NOT prog_purpose_en IS NULL AND LENGTH(TRIM(prog_purpose_en)) > 20) THEN 1 ELSE 0 END) AS any_en_n /* union coverage (either EN field) */, ROUND(100.0 * SUM(CASE WHEN (NOT description_en IS NULL AND LENGTH(TRIM(description_en)) > 20) OR (NOT prog_purpose_en IS NULL AND LENGTH(TRIM(prog_purpose_en)) > 20) THEN 1 ELSE 0 END) / COUNT(*), 1) AS any_en_pct, ROUND(100.0 * SUM(CASE WHEN (NOT description_en IS NULL AND LENGTH(TRIM(description_en)) > 20) OR (NOT prog_purpose_en IS NULL AND LENGTH(TRIM(prog_purpose_en)) > 20) THEN val ELSE 0 END) / SUM(val), 1) AS any_en_val_pct, ROUND(MEDIAN(CASE WHEN NOT description_en IS NULL THEN LENGTH(description_en) ELSE 0 END), 0) AS med_desc_chars /* text length distribution for description_en (quality signal) */, ROUND(MEDIAN(CASE WHEN NOT prog_purpose_en IS NULL THEN LENGTH(prog_purpose_en) ELSE 0 END), 0) AS med_prog_chars, COUNT(DISTINCT CASE WHEN NOT prog_purpose_en IS NULL AND LENGTH(TRIM(prog_purpose_en)) > 20 THEN prog_purpose_en END) AS prog_en_distinct_texts /* prog_purpose_en cardinality (repeat text = program-level, not grant-level) */ FROM base LIMIT 2000

WITH latest AS (
  SELECT *,
    ROW_NUMBER() OVER (
      PARTITION BY ref_number
      ORDER BY TRY_CAST(amendment_number AS INTEGER) DESC NULLS LAST
    ) AS rn
  FROM grants.grants
  WHERE LOWER(COALESCE(recipient_legal_name,'')) NOT LIKE '%batch report%'
    AND LOWER(COALESCE(recipient_legal_name,'')) NOT LIKE '%rapport en lots%'
),
base AS (
  SELECT
    ref_number,
    TRY_CAST(agreement_value AS DOUBLE) AS val,
    LOWER(COALESCE(description_en,'') || ' ' || COALESCE(prog_purpose_en,'')) AS txt
  FROM latest WHERE rn = 1
),
cats AS (
  SELECT ref_number, val,
    -- DESERVING tier
    CASE WHEN txt LIKE '%disab%' THEN 1 ELSE 0 END AS disability,
    CASE WHEN txt LIKE '%illness%' OR txt LIKE '%disease%' OR txt LIKE '%cancer%' OR txt LIKE '%chronic condition%' THEN 1 ELSE 0 END AS illness,
    CASE WHEN txt LIKE '%veteran%' OR txt LIKE '%armed forces%' OR txt LIKE '%military famil%' THEN 1 ELSE 0 END AS veteran,
    CASE WHEN txt LIKE '%senior%' OR txt LIKE '% elder %' OR txt LIKE '%older adult%' OR txt LIKE '%ageing%' OR txt LIKE '%aging%' THEN 1 ELSE 0 END AS senior,
    -- UNDESERVING tier
    CASE WHEN txt LIKE '%homeless%' OR txt LIKE '%sans-abri%' OR txt LIKE '%unsheltered%' THEN 1 ELSE 0 END AS homeless,
    CASE WHEN txt LIKE '%poverty%' OR txt LIKE '% low-income%' OR txt LIKE '% low income%' OR txt LIKE '%pauvret%' OR txt LIKE '%impoverish%' THEN 1 ELSE 0 END AS poverty,
    CASE WHEN txt LIKE '%addiction%' OR txt LIKE '%substance use%' OR txt LIKE '%substance abuse%' OR txt LIKE '%opioid%' OR txt LIKE '%harm reduction%' THEN 1 ELSE 0 END AS addiction,
    CASE WHEN txt LIKE '%offend%' OR txt LIKE '%incarcerat%' OR txt LIKE '%criminal record%' OR txt LIKE '%reintegrat%' OR txt LIKE '% ex-offend%' THEN 1 ELSE 0 END AS criminal_justice,
    -- AMBIGUOUS tier
    CASE WHEN txt LIKE '%mental health%' OR txt LIKE '%santé mentale%' THEN 1 ELSE 0 END AS mental_health,
    CASE WHEN txt LIKE '%indigenous%' OR txt LIKE '%first nation%' OR txt LIKE '%aboriginal%' OR txt LIKE '%autochtone%' OR txt LIKE '%métis%' OR txt LIKE '%inuit%' THEN 1 ELSE 0 END AS indigenous,
    CASE WHEN txt LIKE '%immigrant%' OR txt LIKE '%newcomer%' OR txt LIKE '%refugee%' OR txt LIKE '%asylum%' OR txt LIKE '%réfugié%' THEN 1 ELSE 0 END AS newcomers
  FROM base
)
SELECT 'disability (DESERVING)' AS category,
  SUM(disability) AS n_grants,
  ROUND(SUM(disability*val)/1e6,1) AS total_M,
  ROUND(SUM(disability*val)/NULLIF(SUM(disability),0)/1e3,1) AS avg_grant_K,
  ROUND(PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY CASE WHEN disability=1 THEN val END)/1e3,1) AS median_grant_K
FROM cats
UNION ALL
SELECT 'illness/cancer (DESERVING)',
  SUM(illness), ROUND(SUM(illness*val)/1e6,1),
  ROUND(SUM(illness*val)/NULLIF(SUM(illness),0)/1e3,1),
  ROUND(PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY CASE WHEN illness=1 THEN val END)/1e3,1)
FROM cats
UNION ALL
SELECT 'veteran (DESERVING)',
  SUM(veteran), ROUND(SUM(veteran*val)/1e6,1),
  ROUND(SUM(veteran*val)/NULLIF(SUM(veteran),0)/1e3,1),
  ROUND(PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY CASE WHEN veteran=1 THEN val END)/1e3,1)
FROM cats
UNION ALL
SELECT 'senior/elder (DESERVING)',
  SUM(senior), ROUND(SUM(senior*val)/1e6,1),
  ROUND(SUM(senior*val)/NULLIF(SUM(senior),0)/1e3,1),
  ROUND(PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY CASE WHEN senior=1 THEN val END)/1e3,1)
FROM cats
UNION ALL
SELECT 'homeless (UNDESERVING)',
  SUM(homeless), ROUND(SUM(homeless*val)/1e6,1),
  ROUND(SUM(homeless*val)/NULLIF(SUM(homeless),0)/1e3,1),
  ROUND(PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY CASE WHEN homeless=1 THEN val END)/1e3,1)
FROM cats
UNION ALL
SELECT 'poverty/low-income (UNDESERVING)',
  SUM(poverty), ROUND(SUM(poverty*val)/1e6,1),
  ROUND(SUM(poverty*val)/NULLIF(SUM(poverty),0)/1e3,1),
  ROUND(PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY CASE WHEN poverty=1 THEN val END)/1e3,1)
FROM cats
UNION ALL
SELECT 'addiction/substance (UNDESERVING)',
  SUM(addiction), ROUND(SUM(addiction*val)/1e6,1),
  ROUND(SUM(addiction*val)/NULLIF(SUM(addiction),0)/1e3,1),
  ROUND(PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY CASE WHEN addiction=1 THEN val END)/1e3,1)
FROM cats
UNION ALL
SELECT 'criminal justice (UNDESERVING)',
  SUM(criminal_justice), ROUND(SUM(criminal_justice*val)/1e6,1),
  ROUND(SUM(criminal_justice*val)/NULLIF(SUM(criminal_justice),0)/1e3,1),
  ROUND(PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY CASE WHEN criminal_justice=1 THEN val END)/1e3,1)
FROM cats
UNION ALL
SELECT 'mental health (AMBIGUOUS)',
  SUM(mental_health), ROUND(SUM(mental_health*val)/1e6,1),
  ROUND(SUM(mental_health*val)/NULLIF(SUM(mental_health),0)/1e3,1),
  ROUND(PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY CASE WHEN mental_health=1 THEN val END)/1e3,1)
FROM cats
UNION ALL
SELECT 'indigenous (AMBIGUOUS)',
  SUM(indigenous), ROUND(SUM(indigenous*val)/1e6,1),
  ROUND(SUM(indigenous*val)/NULLIF(SUM(indigenous),0)/1e3,1),
  ROUND(PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY CASE WHEN indigenous=1 THEN val END)/1e3,1)
FROM cats
UNION ALL
SELECT 'newcomers/refugees (AMBIGUOUS)',
  SUM(newcomers), ROUND(SUM(newcomers*val)/1e6,1),
  ROUND(SUM(newcomers*val)/NULLIF(SUM(newcomers),0)/1e3,1),
  ROUND(PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY CASE WHEN newcomers=1 THEN val END)/1e3,1)
FROM cats
ORDER BY total_M DESC

WITH latest AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY ref_number ORDER BY TRY_CAST(amendment_number AS INT) DESC) AS rn FROM grants.grants WHERE LOWER(COALESCE(recipient_legal_name, '')) NOT LIKE '%batch report%' AND LOWER(COALESCE(recipient_legal_name, '')) NOT LIKE '%rapport en lots%'), base AS (SELECT ref_number, TRY_CAST(agreement_value AS DOUBLE) AS val, LOWER(COALESCE(description_en, '') || ' ' || COALESCE(prog_purpose_en, '')) AS txt FROM latest WHERE rn = 1), cats AS (SELECT ref_number, val, (CASE WHEN txt LIKE '%disab%' THEN 1 ELSE 0 END + CASE WHEN txt LIKE '%illness%' OR txt LIKE '%disease%' OR txt LIKE '%cancer%' THEN 1 ELSE 0 END + CASE WHEN txt LIKE '%veteran%' OR txt LIKE '%armed forces%' THEN 1 ELSE 0 END + CASE WHEN txt LIKE '%senior%' OR txt LIKE '% elder %' OR txt LIKE '%older adult%' THEN 1 ELSE 0 END) AS n_deserving, (CASE WHEN txt LIKE '%homeless%' OR txt LIKE '%sans-abri%' THEN 1 ELSE 0 END + CASE WHEN txt LIKE '%poverty%' OR txt LIKE '% low-income%' OR txt LIKE '% low income%' THEN 1 ELSE 0 END + CASE WHEN txt LIKE '%addiction%' OR txt LIKE '%substance use%' OR txt LIKE '%substance abuse%' OR txt LIKE '%opioid%' OR txt LIKE '%harm reduction%' THEN 1 ELSE 0 END + CASE WHEN txt LIKE '%offend%' OR txt LIKE '%incarcerat%' OR txt LIKE '%criminal record%' THEN 1 ELSE 0 END) AS n_undeserving, CASE WHEN txt LIKE '%mental health%' THEN 1 ELSE 0 END AS mental_health, CASE WHEN txt LIKE '%indigenous%' OR txt LIKE '%first nation%' OR txt LIKE '%aboriginal%' OR txt LIKE '%autochtone%' THEN 1 ELSE 0 END AS indigenous, CASE WHEN txt LIKE '%newcomer%' OR txt LIKE '%refugee%' OR txt LIKE '%immigrant%' THEN 1 ELSE 0 END AS newcomers FROM base), framed AS (SELECT ref_number, val, CASE WHEN n_deserving > 0 AND n_undeserving = 0 AND mental_health = 0 AND indigenous = 0 THEN 'DESERVING_ONLY' WHEN n_undeserving > 0 AND n_deserving = 0 AND mental_health = 0 AND indigenous = 0 THEN 'UNDESERVING_ONLY' WHEN n_deserving > 0 AND n_undeserving > 0 THEN 'MIXED_DESERVING_UNDESERVING' WHEN mental_health = 1 AND n_deserving = 0 AND n_undeserving = 0 AND indigenous = 0 THEN 'MENTAL_HEALTH_ONLY' WHEN indigenous = 1 AND n_deserving = 0 AND n_undeserving = 0 AND mental_health = 0 THEN 'INDIGENOUS_ONLY' WHEN newcomers = 1 AND n_deserving = 0 AND n_undeserving = 0 AND mental_health = 0 AND indigenous = 0 THEN 'NEWCOMERS_ONLY' WHEN (mental_health = 1 OR indigenous = 1 OR newcomers = 1) AND (n_deserving > 0 OR n_undeserving > 0) THEN 'MULTI_CATEGORY' ELSE 'NO_POPULATION_SIGNAL' END AS pop_frame FROM cats) SELECT pop_frame, COUNT(*) AS n_grants, ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 1) AS pct_of_count, ROUND(SUM(val) / 1e6, 1) AS total_M, ROUND(100.0 * SUM(val) / SUM(SUM(val)) OVER (), 1) AS pct_of_dollars, ROUND(AVG(val) / 1e3, 1) AS avg_grant_K, ROUND(MEDIAN(val) / 1e3, 1) AS median_grant_K, ROUND(QUANTILE_CONT(val, 0.75 ORDER BY val) / 1e3, 1) AS p75_grant_K FROM framed GROUP BY pop_frame ORDER BY n_grants DESC LIMIT 2000

WITH ranked AS (SELECT CASE WHEN owner_org IN ('wage', 'swc-cfc') THEN 'wage-swc' ELSE owner_org END AS dept, ref_number, TRY_CAST(agreement_value AS DOUBLE) AS val, ROW_NUMBER() OVER (PARTITION BY ref_number ORDER BY TRY_CAST(amendment_number AS INT) DESC) AS rn FROM grants.grants WHERE owner_org IN ('esdc-edsc', 'isc-sac', 'wage', 'swc-cfc') AND TRY_CAST(SUBSTRING(agreement_start_date, 1, 4) AS INT) >= 2018 AND LOWER(recipient_legal_name) NOT LIKE '%batch report%' AND LOWER(recipient_legal_name) NOT LIKE '%rapport en lots%'), deduped AS (SELECT dept, ref_number, val FROM ranked WHERE rn = 1) SELECT dept, COUNT(*) AS base_rows, COUNT(DISTINCT ref_number) AS distinct_refs, ROUND(SUM(val) / 1e6, 2) AS total_grants_m FROM deduped GROUP BY dept ORDER BY dept LIMIT 2000

WITH deduped AS (SELECT ref_number, CASE WHEN owner_org IN ('wage', 'swc-cfc') THEN 'wage-swc' ELSE owner_org END AS dept, recipient_legal_name, TRY_CAST(agreement_value AS DOUBLE) AS rv, ROW_NUMBER() OVER (PARTITION BY ref_number ORDER BY TRY_CAST(amendment_number AS INT) DESC) AS rn FROM grants.grants WHERE TRY_CAST(SUBSTRING(agreement_start_date, 1, 4) AS INT) >= 2018 AND recipient_legal_name NOT LIKE '%batch report%' AND recipient_legal_name NOT LIKE '%rapport en lots%'), filtered AS (SELECT dept, ref_number, recipient_legal_name, rv FROM deduped WHERE rn = 1 AND dept IN ('wage-swc', 'esdc-edsc', 'isc-sac')) SELECT dept, COUNT(*) AS base_rows, COUNT(DISTINCT ref_number) AS distinct_refs, COUNT(DISTINCT recipient_legal_name) AS n_distinct_recipients, ROUND(SUM(rv) / 1e6, 2) AS total_m, ROUND(QUANTILE_CONT(rv, 0.5 ORDER BY rv) / 1e3, 1) AS median_award_k FROM filtered GROUP BY dept ORDER BY dept LIMIT 2000

WITH deduped AS (SELECT ref_number, CASE WHEN owner_org IN ('wage', 'swc-cfc') THEN 'wage-swc' ELSE owner_org END AS dept, recipient_legal_name, TRY_CAST(agreement_value AS DOUBLE) AS val, ROW_NUMBER() OVER (PARTITION BY ref_number ORDER BY TRY_CAST(amendment_number AS INT) DESC) AS rn FROM grants.grants WHERE owner_org IN ('pch-pc', 'esdc-edsc', 'cihr-irsc', 'nserc-crsng', 'sshrc-crsh', 'wage', 'swc-cfc', 'isc-sac') AND TRY_CAST(SUBSTRING(agreement_start_date, 1, 4) AS INT) >= 2018 AND recipient_legal_name NOT LIKE '%batch report%' AND recipient_legal_name NOT LIKE '%rapport en lots%'), clean AS (SELECT * FROM deduped WHERE rn = 1 AND NOT val IS NULL AND val > 0), ra AS (SELECT dept, recipient_legal_name, SUM(val) AS rv FROM clean GROUP BY dept, recipient_legal_name), ranked AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY dept ORDER BY rv DESC) AS rk FROM ra), dt AS (SELECT dept, SUM(rv) AS total, COUNT(*) AS n FROM ra GROUP BY dept) SELECT r.dept, d.n AS n_recip, ROUND(d.total / 1e6, 1) AS total_m, ROUND(SUM(POWER(r.rv / d.total, 2)), 5) AS hhi, ROUND(SUM(CASE WHEN r.rk <= 5 THEN r.rv ELSE 0 END) / d.total, 4) AS top5_pct, ROUND(QUANTILE_CONT(r.rv, 0.5 ORDER BY r.rv) / 1000, 1) AS median_recip_k FROM ranked AS r JOIN dt AS d ON r.dept = d.dept GROUP BY r.dept, d.n, d.total ORDER BY hhi DESC LIMIT 2000

WITH deduped AS (SELECT ref_number, CASE WHEN owner_org IN ('wage', 'swc-cfc') THEN 'wage-swc' ELSE owner_org END AS dept, recipient_legal_name, TRY_CAST(SUBSTRING(agreement_start_date, 1, 4) AS INT) AS yr, ROW_NUMBER() OVER (PARTITION BY ref_number ORDER BY TRY_CAST(amendment_number AS INT) DESC) AS rn FROM grants.grants WHERE owner_org IN ('pch-pc', 'esdc-edsc', 'cihr-irsc', 'nserc-crsng', 'sshrc-crsh', 'wage', 'swc-cfc', 'isc-sac') AND TRY_CAST(SUBSTRING(agreement_start_date, 1, 4) AS INT) BETWEEN 2018 AND 2023 AND recipient_legal_name NOT LIKE '%batch report%' AND recipient_legal_name NOT LIKE '%rapport en lots%'), clean AS (SELECT DISTINCT dept, recipient_legal_name, yr FROM deduped WHERE rn = 1), y18 AS (SELECT dept, recipient_legal_name FROM clean WHERE yr = 2018), post18 AS (SELECT DISTINCT dept, recipient_legal_name FROM clean WHERE yr BETWEEN 2019 AND 2023) SELECT p.dept, COUNT(*) AS post18_recips, SUM(CASE WHEN y.recipient_legal_name IS NULL THEN 1 ELSE 0 END) AS new_entrants, ROUND(1.0 * SUM(CASE WHEN y.recipient_legal_name IS NULL THEN 1 ELSE 0 END) / NULLIF(COUNT(*), 0), 3) AS new_rate FROM post18 AS p LEFT JOIN y18 AS y ON p.dept = y.dept AND p.recipient_legal_name = y.recipient_legal_name GROUP BY p.dept ORDER BY new_rate LIMIT 2000

SELECT t.org_id, t.org_name, ROUND(SUM(TRY_CAST(t.expenditures AS DOUBLE)) / 1e9, 3) AS gc_b, ROUND(MAX(e.total_b) / 1e9, 3) AS total_b, ROUND(100.0 * SUM(TRY_CAST(t.expenditures AS DOUBLE)) / NULLIF(MAX(e.total_b), 0), 1) AS gc_pct, MIN(t.fy_ef) AS min_fy, MAX(t.fy_ef) AS max_fy, COUNT(DISTINCT t.fy_ef) AS n_fy_years FROM corpus.gc_infobase_tp_pt AS t LEFT JOIN (SELECT CAST(org_id AS TEXT) AS org_id, SUM(TRY_CAST(expenditures AS DOUBLE)) AS total_b FROM corpus.gc_infobase_eav_eac GROUP BY CAST(org_id AS TEXT)) AS e ON CAST(t.org_id AS TEXT) = e.org_id GROUP BY t.org_id, t.org_name HAVING SUM(TRY_CAST(t.expenditures AS DOUBLE)) > 0 ORDER BY gc_pct DESC LIMIT 25

WITH gc_num AS (SELECT org_id, org_name, SUM(TRY_CAST(expenditures AS DOUBLE)) AS gc_exp FROM corpus.gc_infobase_tp_pt WHERE fy_ef >= 'FY 2018-19' GROUP BY org_id, org_name), total_denom AS (SELECT org_id, org_name, SUM(TRY_CAST(expenditures AS DOUBLE)) AS total_exp FROM corpus.gc_infobase_eav_eac WHERE fy_ef >= 'FY 2018-19' GROUP BY org_id, org_name) SELECT t.org_id, t.org_name, ROUND(t.total_exp / 1e9, 2) AS total_b, ROUND(COALESCE(g.gc_exp, 0) / 1e9, 2) AS gc_b, ROUND(100.0 * COALESCE(g.gc_exp, 0) / NULLIF(t.total_exp, 0), 1) AS gc_pct FROM total_denom AS t LEFT JOIN gc_num AS g ON t.org_id = g.org_id WHERE t.total_exp > 0 ORDER BY gc_pct DESC LIMIT 50

SELECT DISTINCT owner_org FROM grants.grants WHERE LOWER(owner_org) LIKE '%wage%' OR LOWER(owner_org) LIKE '%fegc%' OR LOWER(owner_org) LIKE '%swc%' OR LOWER(owner_org) LIKE '%cfc%' OR LOWER(owner_org) LIKE '%gender%' OR LOWER(owner_org) LIKE '%women%' OR LOWER(owner_org) LIKE '%status%' ORDER BY owner_org LIMIT 2000

WITH deduped AS (SELECT ref_number, owner_org, recipient_legal_name, TRY_CAST(agreement_value AS DOUBLE) AS val, ROW_NUMBER() OVER (PARTITION BY ref_number ORDER BY TRY_CAST(amendment_number AS INT) DESC) AS rn FROM grants.grants WHERE TRY_CAST(SUBSTRING(agreement_start_date, 1, 4) AS INT) >= 2018 AND owner_org IN ('pch', 'esdc-edsc', 'cihr-irsc', 'nserc-crsng', 'sshrc-crsh', 'swc-cfc', 'wage-fegc') AND LOWER(recipient_legal_name) NOT LIKE '%batch report%' AND LOWER(recipient_legal_name) NOT LIKE '%rapport en lots%'), f AS (SELECT * FROM deduped WHERE rn = 1), dept_total AS (SELECT owner_org, SUM(val) AS dt FROM f GROUP BY owner_org), recip_rank AS (SELECT f.owner_org, f.recipient_legal_name, SUM(f.val) AS rv, ROW_NUMBER() OVER (PARTITION BY f.owner_org ORDER BY SUM(f.val) DESC) AS rk FROM f GROUP BY f.owner_org, f.recipient_legal_name) SELECT r.owner_org, r.rk, r.recipient_legal_name, ROUND(r.rv / 1e6, 1) AS recip_m, ROUND(100.0 * r.rv / d.dt, 1) AS pct_of_dept FROM recip_rank AS r JOIN dept_total AS d ON r.owner_org = d.owner_org WHERE r.rk <= 5 ORDER BY r.owner_org, r.rk LIMIT 2000

SELECT description, COUNT(DISTINCT org_id) AS n_orgs, COUNT(*) AS n_rows, ROUND(SUM(TRY_CAST(expenditures AS DOUBLE)) / 1e9, 2) AS total_b FROM corpus.gc_infobase_eav_eac GROUP BY description ORDER BY total_b DESC LIMIT 30

SELECT * FROM corpus.gc_infobase_tp_pt LIMIT 3

WITH deduped AS (SELECT ref_number, owner_org, recipient_legal_name, TRY_CAST(agreement_value AS DOUBLE) AS val, ROW_NUMBER() OVER (PARTITION BY ref_number ORDER BY TRY_CAST(amendment_number AS INT) DESC) AS rn FROM grants.grants WHERE recipient_legal_name NOT LIKE '%batch report%' AND recipient_legal_name NOT LIKE '%rapport en lots%' AND TRY_CAST(SUBSTRING(agreement_start_date, 1, 4) AS INT) >= 2018), latest AS (SELECT ref_number, owner_org, recipient_legal_name, val FROM deduped WHERE rn = 1 AND val > 0), dept_recip AS (SELECT owner_org, recipient_legal_name, SUM(val) AS r_total FROM latest GROUP BY owner_org, recipient_legal_name), dept_stats AS (SELECT owner_org, SUM(r_total) AS dept_total, COUNT(*) AS n_recip FROM dept_recip GROUP BY owner_org), hhi_calc AS (SELECT dr.owner_org, SUM(POWER(dr.r_total / ds.dept_total, 2)) AS hhi, MAX(ds.n_recip) AS n_recip, MAX(ds.dept_total) AS dept_total FROM dept_recip AS dr JOIN dept_stats AS ds ON dr.owner_org = ds.owner_org GROUP BY dr.owner_org), top5 AS (SELECT owner_org, SUM(r_total) AS top5_total FROM (SELECT owner_org, r_total, ROW_NUMBER() OVER (PARTITION BY owner_org ORDER BY r_total DESC) AS rnk FROM dept_recip) AS t WHERE rnk <= 5 GROUP BY owner_org), medians AS (SELECT owner_org, QUANTILE_CONT(val, 0.5 ORDER BY val) AS med_val FROM latest GROUP BY owner_org) SELECT h.owner_org, h.n_recip, ROUND(h.dept_total / 1e9, 3) AS total_b, ROUND(h.hhi, 4) AS hhi, ROUND(t5.top5_total / h.dept_total * 100, 1) AS top5_pct, ROUND(m.med_val / 1000, 1) AS median_k FROM hhi_calc AS h JOIN top5 AS t5 ON h.owner_org = t5.owner_org JOIN medians AS m ON h.owner_org = m.owner_org ORDER BY h.dept_total DESC LIMIT 20

WITH presence AS (SELECT DISTINCT owner_org, recipient_legal_name, TRY_CAST(SUBSTRING(agreement_start_date, 1, 4) AS INT) AS yr FROM grants.grants WHERE TRY_CAST(SUBSTRING(agreement_start_date, 1, 4) AS INT) BETWEEN 2018 AND 2023 AND recipient_legal_name NOT LIKE '%batch report%' AND recipient_legal_name NOT LIKE '%rapport en lots%'), first_seen AS (SELECT owner_org, recipient_legal_name, MIN(yr) AS first_yr FROM presence GROUP BY owner_org, recipient_legal_name), yr_new AS (SELECT p.owner_org, p.yr, COUNT(DISTINCT p.recipient_legal_name) AS total_r, COUNT(DISTINCT CASE WHEN f.first_yr = p.yr THEN p.recipient_legal_name END) AS new_r FROM presence AS p JOIN first_seen AS f ON p.owner_org = f.owner_org AND p.recipient_legal_name = f.recipient_legal_name WHERE p.yr >= 2019 GROUP BY p.owner_org, p.yr) SELECT owner_org, ROUND(AVG(100.0 * new_r / NULLIF(total_r, 0)), 1) AS avg_new_pct, SUM(new_r) AS total_new, SUM(total_r) AS total_recip_yr FROM yr_new GROUP BY owner_org ORDER BY total_recip_yr DESC LIMIT 20

SELECT database_name, schema_name, table_name, 'TABLE' AS obj_type FROM duckdb_tables() WHERE LOWER(table_name) LIKE '%infobase%' OR LOWER(table_name) LIKE '%eav_eac%' OR LOWER(table_name) LIKE '%tp_pt%' UNION ALL SELECT database_name, schema_name, view_name, 'VIEW' FROM duckdb_views() WHERE LOWER(view_name) LIKE '%infobase%' OR LOWER(view_name) LIKE '%eav_eac%' OR LOWER(view_name) LIKE '%tp_pt%' ORDER BY obj_type, database_name, schema_name, table_name

SELECT owner_org, COUNT(*) AS n_rows, COUNT(DISTINCT recipient_legal_name) AS n_recip, ROUND(SUM(TRY_CAST(agreement_value AS DOUBLE)) / 1e9, 3) AS total_bil FROM grants WHERE TRY_CAST(SUBSTRING(agreement_start_date, 1, 4) AS INT) >= 2018 AND recipient_legal_name NOT LIKE 'batch report%rapport en lots' GROUP BY owner_org ORDER BY n_rows DESC LIMIT 2000

WITH base AS (SELECT ref_number, owner_org, recipient_legal_name, TRY_CAST(agreement_value AS DOUBLE) AS val, ROW_NUMBER() OVER (PARTITION BY ref_number ORDER BY TRY_CAST(amendment_number AS INT) DESC) AS rn FROM grants WHERE TRY_CAST(SUBSTRING(agreement_start_date, 1, 4) AS INT) >= 2018 AND recipient_legal_name NOT LIKE 'batch report%rapport en lots'), clean AS (SELECT owner_org, recipient_legal_name, SUM(val) AS rv FROM base WHERE rn = 1 GROUP BY owner_org, recipient_legal_name), dt AS (SELECT owner_org, SUM(rv) AS dv, COUNT(DISTINCT recipient_legal_name) AS nr FROM clean GROUP BY owner_org), sh AS (SELECT c.owner_org, c.recipient_legal_name, c.rv / d.dv AS share, ROW_NUMBER() OVER (PARTITION BY c.owner_org ORDER BY c.rv DESC) AS rnk FROM clean AS c JOIN dt AS d ON c.owner_org = d.owner_org), hhi AS (SELECT owner_org, SUM(POWER(share, 2)) AS hhi FROM sh GROUP BY owner_org), t5 AS (SELECT owner_org, SUM(share) AS top5_share FROM sh WHERE rnk <= 5 GROUP BY owner_org), t1 AS (SELECT owner_org, MAX(share) AS top1_share FROM sh WHERE rnk = 1 GROUP BY owner_org) SELECT d.owner_org, ROUND(d.dv / 1e9, 3) AS dept_bil, d.nr AS n_recip, ROUND(h.hhi, 4) AS hhi, ROUND(t5.top5_share, 3) AS top5_share, ROUND(t1.top1_share, 3) AS top1_share FROM dt AS d JOIN hhi AS h ON d.owner_org = h.owner_org JOIN t5 ON d.owner_org = t5.owner_org JOIN t1 ON d.owner_org = t1.owner_org ORDER BY d.dv DESC LIMIT 25

WITH base AS (SELECT ref_number, owner_org, TRY_CAST(agreement_value AS DOUBLE) AS val, ROW_NUMBER() OVER (PARTITION BY ref_number ORDER BY TRY_CAST(amendment_number AS INT) DESC) AS rn FROM grants WHERE TRY_CAST(SUBSTRING(agreement_start_date, 1, 4) AS INT) >= 2018 AND recipient_legal_name NOT LIKE 'batch report%rapport en lots') SELECT owner_org, COUNT(*) AS n_awards, ROUND(QUANTILE_CONT(val, 0.1 ORDER BY val)) AS p10, ROUND(QUANTILE_CONT(val, 0.25 ORDER BY val)) AS p25, ROUND(QUANTILE_CONT(val, 0.5 ORDER BY val)) AS median_award, ROUND(QUANTILE_CONT(val, 0.75 ORDER BY val)) AS p75, ROUND(QUANTILE_CONT(val, 0.9 ORDER BY val)) AS p90, ROUND(AVG(val)) AS mean_award, ROUND(SUM(val) / 1e9, 3) AS total_bil FROM base WHERE rn = 1 GROUP BY owner_org ORDER BY total_bil DESC LIMIT 20

WITH presence AS (SELECT DISTINCT owner_org, recipient_legal_name, TRY_CAST(SUBSTRING(agreement_start_date, 1, 4) AS INT) AS yr FROM grants WHERE TRY_CAST(SUBSTRING(agreement_start_date, 1, 4) AS INT) BETWEEN 2018 AND 2023 AND recipient_legal_name NOT LIKE 'batch report%rapport en lots' AND owner_org IN ('esdc-edsc', 'pch', 'wage-feme', 'wgsc-feme', 'nserc-crsng', 'cihr-irsc', 'sshrc-crsh', 'isc-sac', 'dfatd-maecd', 'hc-sc', 'tc', 'aafc-aac', 'acoa-apeca', 'ced-dec', 'wd-deo')), first_seen AS (SELECT owner_org, recipient_legal_name, MIN(yr) AS first_yr FROM presence GROUP BY owner_org, recipient_legal_name), annual AS (SELECT p.owner_org, p.yr, COUNT(DISTINCT p.recipient_legal_name) AS total_recip, COUNT(DISTINCT CASE WHEN f.first_yr = p.yr THEN p.recipient_legal_name END) AS new_recip FROM presence AS p JOIN first_seen AS f ON p.owner_org = f.owner_org AND p.recipient_legal_name = f.recipient_legal_name WHERE p.yr >= 2019 GROUP BY p.owner_org, p.yr) SELECT owner_org, yr, total_recip, new_recip, ROUND(1.0 * new_recip / total_recip, 3) AS new_entrant_rate FROM annual ORDER BY owner_org, yr LIMIT 2000

SELECT table_schema, table_name FROM information_schema.tables WHERE table_type = 'BASE TABLE' ORDER BY table_schema, table_name LIMIT 2000

SELECT table_name, column_name, data_type, ordinal_position FROM information_schema.columns WHERE table_name IN ('gc_infobase_eav_eac', 'gc_infobase_tp_pt') ORDER BY table_name, ordinal_position LIMIT 2000

SELECT owner_org, COUNT(*) AS n_rows, COUNT(DISTINCT recipient_legal_name) AS n_recipients, ROUND(SUM(TRY_CAST(agreement_value AS DOUBLE)) / 1e9, 3) AS total_bil FROM grants.grants WHERE TRY_CAST(SUBSTRING(agreement_start_date, 1, 4) AS INT) >= 2018 AND recipient_legal_name <> 'batch report│rapport en lots' GROUP BY owner_org ORDER BY n_rows DESC LIMIT 30

SELECT description, COUNT(DISTINCT org_id) AS n_orgs, COUNT(*) AS n_rows FROM gc_infobase_eav_eac GROUP BY description ORDER BY n_rows DESC LIMIT 25

SELECT DISTINCT org_id FROM gc_infobase_eav_eac ORDER BY org_id LIMIT 50

WITH ct_deduped AS (SELECT reference_number, vendor_name, TRY_CAST(contract_value AS DOUBLE) AS cv, ROW_NUMBER() OVER (PARTITION BY reference_number ORDER BY TRY_CAST(contract_value AS DOUBLE) DESC, contract_date DESC) AS rn FROM corpus.contracts_contracts WHERE TRY_CAST(contract_date AS DATE) >= '2018-01-01' AND NOT vendor_name IS NULL AND TRIM(vendor_name) <> '' AND vendor_name <> 'batch report|rapport en lots'), ct_base AS (SELECT reference_number, vendor_name, cv FROM ct_deduped WHERE rn = 1), classified AS (SELECT DISTINCT cb.reference_number, cb.cv, rc.class FROM ct_base AS cb INNER JOIN classification.recipient_class AS rc ON rc.recipient = cb.vendor_name WHERE NOT rc.class IN ('QUARANTINED')) SELECT class, COUNT(*) AS base_rows, COUNT(DISTINCT reference_number) AS distinct_refs, ROUND(SUM(cv) / 1e9, 3) AS contracts_B, ROUND(AVG(cv) / 1e3, 1) AS avg_contract_K FROM classified GROUP BY class ORDER BY contracts_B DESC LIMIT 2000

WITH deduped AS (SELECT sub.recipient_legal_name, sub.ref_number, sub.agreement_type, TRY_CAST(sub.agreement_value AS DOUBLE) AS val, ROW_NUMBER() OVER (PARTITION BY sub.ref_number ORDER BY TRY_CAST(sub.amendment_number AS INT) DESC) AS rn FROM grants.grants AS sub WHERE TRY_CAST(sub.agreement_start_date AS DATE) >= '2018-01-01' AND sub.recipient_legal_name <> 'batch report│rapport en lots'), latest AS (SELECT * FROM deduped WHERE rn = 1), pivoted AS (SELECT rc.class, SUM(CASE WHEN lat.agreement_type = 'G' THEN lat.val ELSE 0 END) AS grants_raw, SUM(CASE WHEN lat.agreement_type = 'C' THEN lat.val ELSE 0 END) AS contrib_raw, SUM(CASE WHEN NOT lat.agreement_type IN ('G', 'C') THEN lat.val ELSE 0 END) AS other_raw, SUM(lat.val) AS total_raw, COUNT(*) AS base_rows, COUNT(DISTINCT lat.ref_number) AS distinct_refs FROM latest AS lat JOIN classification.recipient_class AS rc ON rc.recipient = lat.recipient_legal_name WHERE rc.class IN ('COMMERCIAL', 'INSTITUTIONAL', 'ORGANIZATIONAL', 'GOVERNMENT') GROUP BY rc.class) SELECT class, base_rows, distinct_refs, ROUND(grants_raw / 1e9, 3) AS grants_B, ROUND(contrib_raw / 1e9, 3) AS contrib_B, ROUND(other_raw / 1e9, 3) AS other_B, ROUND(total_raw / 1e9, 3) AS total_B, ROUND(100.0 * grants_raw / NULLIF(total_raw, 0), 1) AS pct_pure_grants, ROUND(100.0 * contrib_raw / NULLIF(total_raw, 0), 1) AS pct_contributions FROM pivoted ORDER BY class LIMIT 2000

WITH gc_classified AS (SELECT rc.class, COUNT(*) AS gc_base_rows, COUNT(DISTINCT lat.ref_number) AS gc_distinct_refs, ROUND(SUM(lat.val) / 1e9, 2) AS gc_B FROM (SELECT ref_number, recipient_legal_name, TRY_CAST(agreement_value AS DOUBLE) AS val, ROW_NUMBER() OVER (PARTITION BY ref_number ORDER BY TRY_CAST(amendment_number AS INT) DESC) AS rn FROM grants.grants WHERE TRY_CAST(agreement_start_date AS DATE) >= '2018-01-01' AND recipient_legal_name <> 'batch report│rapport en lots') AS lat JOIN classification.recipient_class AS rc ON rc.recipient = lat.recipient_legal_name WHERE lat.rn = 1 GROUP BY rc.class), contracts_classified AS (SELECT rc.class, COUNT(*) AS ct_base_rows, COUNT(DISTINCT csub.reference_number) AS ct_distinct_refs, ROUND(SUM(TRY_CAST(csub.contract_value AS DOUBLE)) / 1e9, 2) AS ct_B FROM corpus.contracts_contracts AS csub JOIN classification.recipient_class AS rc ON rc.recipient = csub.vendor_name WHERE TRY_CAST(csub.contract_date AS DATE) >= '2018-01-01' GROUP BY rc.class) SELECT COALESCE(g.class, c.class) AS class, COALESCE(g.gc_B, 0) AS gc_B, COALESCE(c.ct_B, 0) AS contracts_B, ROUND(COALESCE(g.gc_B, 0) + COALESCE(c.ct_B, 0), 2) AS combined_B, COALESCE(g.gc_base_rows, 0) AS gc_base_rows, COALESCE(g.gc_distinct_refs, 0) AS gc_distinct_refs, COALESCE(c.ct_base_rows, 0) AS ct_base_rows, COALESCE(c.ct_distinct_refs, 0) AS ct_distinct_refs FROM gc_classified AS g FULL OUTER JOIN contracts_classified AS c ON g.class = c.class ORDER BY combined_B DESC LIMIT 2000

WITH org_gc AS (SELECT lat.recipient_legal_name, SUM(CASE WHEN lat.agreement_type = 'G' THEN lat.val ELSE 0 END) AS gc_grants, SUM(CASE WHEN lat.agreement_type = 'C' THEN lat.val ELSE 0 END) AS gc_contrib, SUM(lat.val) AS gc_total, COUNT(DISTINCT lat.ref_number) AS gc_refs FROM (SELECT ref_number, recipient_legal_name, agreement_type, TRY_CAST(agreement_value AS DOUBLE) AS val, ROW_NUMBER() OVER (PARTITION BY ref_number ORDER BY TRY_CAST(amendment_number AS INT) DESC) AS rn FROM grants.grants WHERE TRY_CAST(agreement_start_date AS DATE) >= '2018-01-01' AND recipient_legal_name <> 'batch report│rapport en lots') AS lat JOIN classification.recipient_class AS rc ON rc.recipient = lat.recipient_legal_name AND rc.class = 'ORGANIZATIONAL' WHERE lat.rn = 1 GROUP BY lat.recipient_legal_name), org_ct AS (SELECT csub.vendor_name, COUNT(*) AS ct_rows, COUNT(DISTINCT csub.reference_number) AS ct_refs, SUM(TRY_CAST(csub.contract_value AS DOUBLE)) AS ct_total FROM corpus.contracts_contracts AS csub JOIN classification.recipient_class AS rc ON rc.recipient = csub.vendor_name AND rc.class = 'ORGANIZATIONAL' WHERE TRY_CAST(csub.contract_date AS DATE) >= '2018-01-01' GROUP BY csub.vendor_name) SELECT COUNT(DISTINCT g.recipient_legal_name) AS org_gc_entities, COUNT(DISTINCT CASE WHEN NOT c.vendor_name IS NULL THEN g.recipient_legal_name END) AS org_both_entities, COUNT(*) AS base_rows, COUNT(DISTINCT g.recipient_legal_name) AS distinct_refs, ROUND(SUM(g.gc_total) / 1e9, 3) AS gc_total_B, ROUND(SUM(CASE WHEN NOT c.vendor_name IS NULL THEN g.gc_total ELSE 0 END) / 1e9, 3) AS gc_cross_appearing_B, ROUND(SUM(CASE WHEN NOT c.vendor_name IS NULL THEN g.gc_grants ELSE 0 END) / 1e9, 3) AS gc_grants_cross_B, ROUND(SUM(CASE WHEN NOT c.vendor_name IS NULL THEN g.gc_contrib ELSE 0 END) / 1e9, 3) AS gc_contrib_cross_B, ROUND(SUM(COALESCE(c.ct_total, 0)) / 1e9, 3) AS ct_total_B, ROUND(100.0 * SUM(CASE WHEN NOT c.vendor_name IS NULL THEN g.gc_grants ELSE 0 END) / NULLIF(SUM(CASE WHEN NOT c.vendor_name IS NULL THEN g.gc_total ELSE 0 END), 0), 1) AS cross_pct_grants FROM org_gc AS g LEFT JOIN org_ct AS c ON c.vendor_name = g.recipient_legal_name LIMIT 2000

WITH deduped AS (SELECT recipient_legal_name, ref_number, TRY_CAST(agreement_value AS DOUBLE) AS val, ROW_NUMBER() OVER (PARTITION BY ref_number ORDER BY TRY_CAST(amendment_number AS INT) DESC) AS rn FROM grants.grants WHERE TRY_CAST(agreement_start_date AS DATE) >= '2018-01-01' AND recipient_legal_name <> 'batch report|rapport en lots'), base AS (SELECT recipient_legal_name, ref_number, val FROM deduped WHERE rn = 1) SELECT COALESCE(rc.class, 'UNCLASSIFIED') AS class, COUNT(*) AS base_rows, COUNT(DISTINCT b.ref_number) AS distinct_refs, ROUND(SUM(b.val) / 1e9, 2) AS total_B, ROUND(SUM(b.val) / SUM(SUM(b.val)) OVER () * 100, 1) AS pct_of_all, ROUND(AVG(b.val) / 1e3, 1) AS avg_K, ROUND(MEDIAN(b.val) / 1e3, 2) AS median_K FROM base AS b LEFT JOIN classification.recipient_class AS rc ON rc.recipient = b.recipient_legal_name GROUP BY 1 ORDER BY total_B DESC LIMIT 2000

WITH deduped AS (SELECT recipient_legal_name, ref_number, agreement_type, TRY_CAST(agreement_value AS DOUBLE) AS val, ROW_NUMBER() OVER (PARTITION BY ref_number ORDER BY TRY_CAST(amendment_number AS INT) DESC) AS rn FROM grants.grants WHERE TRY_CAST(agreement_start_date AS DATE) >= '2018-01-01' AND recipient_legal_name <> 'batch report|rapport en lots'), base AS (SELECT b.recipient_legal_name, b.ref_number, b.agreement_type, b.val FROM deduped AS b JOIN classification.recipient_class AS rc ON rc.recipient = b.recipient_legal_name AND rc.class = 'ORGANIZATIONAL' WHERE b.rn = 1) SELECT agreement_type, COUNT(*) AS base_rows, COUNT(DISTINCT ref_number) AS distinct_refs, ROUND(SUM(val) / 1e9, 3) AS total_B, ROUND(SUM(val) / SUM(SUM(val)) OVER () * 100, 1) AS pct_of_org_dollars, ROUND(AVG(val) / 1e3, 1) AS avg_K, ROUND(MEDIAN(val) / 1e3, 2) AS median_K FROM base GROUP BY 1 ORDER BY total_B DESC LIMIT 2000

SELECT rc.class, COUNT(*) AS n_contracts, COUNT(DISTINCT c.reference_number) AS distinct_refs, COUNT(DISTINCT c.vendor_name) AS distinct_vendors, ROUND(SUM(TRY_CAST(c.contract_value AS DOUBLE)) / 1e9, 3) AS total_B, ROUND(AVG(TRY_CAST(c.contract_value AS DOUBLE)) / 1e3, 1) AS avg_K, ROUND(MEDIAN(TRY_CAST(c.contract_value AS DOUBLE)) / 1e3, 2) AS median_K FROM corpus.contracts_contracts AS c JOIN classification.recipient_class AS rc ON rc.recipient = c.vendor_name GROUP BY 1 ORDER BY total_B DESC LIMIT 2000

WITH base AS (SELECT recipient_legal_name, agreement_type, TRY_CAST(agreement_value AS DOUBLE) AS val FROM (SELECT recipient_legal_name, agreement_type, agreement_value, agreement_start_date, ROW_NUMBER() OVER (PARTITION BY ref_number ORDER BY TRY_CAST(amendment_number AS INT) DESC) AS rn FROM grants.grants WHERE recipient_legal_name <> 'batch report│rapport en lots') AS inner_q WHERE rn = 1 AND TRY_CAST(agreement_start_date AS DATE) >= '2018-01-01'), classed AS (SELECT b.agreement_type, b.val, rc.class FROM base AS b INNER JOIN classification.recipient_class AS rc ON rc.recipient = b.recipient_legal_name WHERE NOT rc.class IN ('QUARANTINED', 'OPEN')) SELECT class, agreement_type, COUNT(*) AS n_awards, ROUND(SUM(val) / 1e9, 3) AS total_B, ROUND(100.0 * SUM(val) / SUM(SUM(val)) OVER (PARTITION BY class), 1) AS pct_of_class_dollars, ROUND(AVG(val) / 1e3, 1) AS avg_K, ROUND(MEDIAN(val) / 1e3, 2) AS median_K FROM classed GROUP BY class, agreement_type ORDER BY class, total_B DESC LIMIT 2000

SELECT rc.class, COUNT(*) AS n_contracts, COUNT(DISTINCT vendor_name) AS distinct_vendors, ROUND(SUM(TRY_CAST(c.contract_value AS DOUBLE)) / 1e9, 2) AS total_B, ROUND(AVG(TRY_CAST(c.contract_value AS DOUBLE)) / 1e3, 1) AS avg_K, ROUND(MEDIAN(TRY_CAST(c.contract_value AS DOUBLE)) / 1e3, 2) AS median_K FROM corpus.contracts_contracts AS c INNER JOIN classification.recipient_class AS rc ON rc.recipient = c.vendor_name WHERE TRY_CAST(c.contract_date AS DATE) >= '2018-01-01' AND NOT rc.class IN ('QUARANTINED', 'OPEN') GROUP BY rc.class ORDER BY total_B DESC LIMIT 2000

WITH base AS (SELECT recipient_legal_name, TRY_CAST(agreement_value AS DOUBLE) AS val FROM (SELECT recipient_legal_name, agreement_value, agreement_start_date, ROW_NUMBER() OVER (PARTITION BY ref_number ORDER BY TRY_CAST(amendment_number AS INT) DESC) AS rn FROM grants.grants WHERE recipient_legal_name <> 'batch report│rapport en lots') AS inner_q WHERE rn = 1 AND TRY_CAST(agreement_start_date AS DATE) >= '2018-01-01'), class_totals AS (SELECT COALESCE(rc.class, 'UNCLASSIFIED') AS class, SUM(b.val) AS class_total, COUNT(*) AS class_n FROM base AS b LEFT JOIN classification.recipient_class AS rc ON rc.recipient = b.recipient_legal_name GROUP BY COALESCE(rc.class, 'UNCLASSIFIED')) SELECT class, class_n AS n_awards, ROUND(class_total / 1e9, 2) AS total_B, ROUND(100.0 * class_total / SUM(class_total) OVER (), 1) AS pct_of_all_grants_2018 FROM class_totals ORDER BY total_B DESC LIMIT 12

WITH deduped AS (SELECT g.recipient_legal_name, g.agreement_type, TRY_CAST(g.agreement_value AS DOUBLE) AS val FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY ref_number ORDER BY TRY_CAST(amendment_number AS INT) DESC) AS rn FROM grants.grants WHERE agreement_start_date >= '2018-01-01' AND recipient_legal_name <> 'batch report|rapport en lots') AS x WHERE x.rn = 1), classified AS (SELECT d.agreement_type, d.val, rc.class FROM deduped AS d INNER JOIN classification.recipient_class AS rc ON rc.recipient = d.recipient_legal_name WHERE rc.class IN ('ORGANIZATIONAL', 'COMMERCIAL', 'GOVERNMENT', 'INSTITUTIONAL')) SELECT class, agreement_type, COUNT(*) AS agreements, ROUND(SUM(val) / 1e9, 2) AS dollars_B, ROUND(SUM(val) * 100.0 / SUM(SUM(val)) OVER (PARTITION BY class), 1) AS pct_within_class FROM classified GROUP BY class, agreement_type ORDER BY class, dollars_B DESC LIMIT 2000

SELECT COUNT(*) AS total_contract_rows, COUNT(DISTINCT c.vendor_name) AS distinct_org_vendors, ROUND(SUM(TRY_CAST(c.contract_value AS DOUBLE)) / 1e9, 2) AS total_contract_B, ROUND(AVG(TRY_CAST(c.contract_value AS DOUBLE)) / 1000, 1) AS avg_contract_K, ROUND(QUANTILE_CONT(TRY_CAST(c.contract_value AS DOUBLE), 0.5 ORDER BY TRY_CAST(c.contract_value AS DOUBLE)) / 1000, 1) AS median_contract_K FROM corpus.contracts_contracts AS c INNER JOIN classification.recipient_class AS rc ON rc.recipient = c.vendor_name AND rc.class = 'ORGANIZATIONAL' WHERE c.contract_date >= '2018-01-01' LIMIT 2000

WITH deduped AS (SELECT g.recipient_legal_name, TRY_CAST(g.agreement_value AS DOUBLE) AS val FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY ref_number ORDER BY TRY_CAST(amendment_number AS INT) DESC) AS rn FROM grants.grants WHERE agreement_start_date >= '2018-01-01' AND recipient_legal_name <> 'batch report|rapport en lots') AS x WHERE x.rn = 1), org_grants AS (SELECT d.recipient_legal_name, d.val FROM deduped AS d INNER JOIN classification.recipient_class AS rc ON rc.recipient = d.recipient_legal_name AND rc.class = 'ORGANIZATIONAL'), enriched_tier AS (SELECT re.legal_name, MAX(CASE WHEN CAST(re.is_charity AS TEXT) = '1' OR CAST(re.is_corporation AS TEXT) = '1' THEN 1 ELSE 0 END) AS is_registry_matched FROM entities.recipient_enriched AS re INNER JOIN classification.recipient_class AS rc2 ON rc2.recipient = re.legal_name AND rc2.class = 'ORGANIZATIONAL' GROUP BY re.legal_name) SELECT CASE WHEN et.is_registry_matched = 1 THEN 'bn9-exact (registry-verified)' WHEN et.is_registry_matched = 0 THEN 'name-only (unverified)' ELSE 'no-enriched-match' END AS si7_tier, COUNT(*) AS agreements, COUNT(DISTINCT og.recipient_legal_name) AS distinct_recipients, ROUND(SUM(og.val) / 1e9, 2) AS dollars_B, ROUND(SUM(og.val) * 100.0 / SUM(SUM(og.val)) OVER (), 1) AS pct_of_org_dollars FROM org_grants AS og LEFT JOIN enriched_tier AS et ON et.legal_name = og.recipient_legal_name GROUP BY si7_tier ORDER BY dollars_B DESC LIMIT 2000

SELECT class, COUNT(*) AS cnt, ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (), 1) AS pct FROM classification.recipient_class GROUP BY class ORDER BY cnt DESC LIMIT 2000

SELECT column_name, data_type FROM information_schema.columns WHERE table_schema = 'grants' AND table_name = 'grants' ORDER BY ordinal_position LIMIT 2000

WITH deduped AS (SELECT recipient_legal_name, TRY_CAST(agreement_value AS DOUBLE) AS val FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY ref_number ORDER BY TRY_CAST(amendment_number AS INT) DESC, amendment_date DESC) AS rn FROM grants.grants WHERE TRY_CAST(agreement_start_date AS DATE) >= CAST('2018-01-01' AS DATE) AND recipient_legal_name NOT LIKE '%batch report%' AND recipient_legal_name NOT LIKE '%rapport en lots%') AS t WHERE rn = 1) SELECT COALESCE(rc.class, 'UNCLASSIFIED') AS class, COUNT(*) AS agreements, COUNT(DISTINCT d.recipient_legal_name) AS distinct_recipients, ROUND(SUM(d.val) / 1e9, 2) AS total_dollars_B, ROUND(SUM(d.val) * 100.0 / SUM(SUM(d.val)) OVER (), 1) AS pct_of_total_dollars FROM deduped AS d LEFT JOIN classification.recipient_class AS rc ON d.recipient_legal_name = rc.recipient GROUP BY COALESCE(rc.class, 'UNCLASSIFIED') ORDER BY SUM(d.val) DESC LIMIT 2000

WITH deduped AS (SELECT recipient_legal_name, agreement_type, TRY_CAST(agreement_value AS DOUBLE) AS val FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY ref_number ORDER BY TRY_CAST(amendment_number AS INT) DESC, amendment_date DESC) AS rn FROM grants.grants WHERE TRY_CAST(agreement_start_date AS DATE) >= CAST('2018-01-01' AS DATE) AND recipient_legal_name NOT LIKE '%batch report%' AND recipient_legal_name NOT LIKE '%rapport en lots%') AS t WHERE rn = 1) SELECT d.agreement_type, COUNT(*) AS agreements, COUNT(DISTINCT d.recipient_legal_name) AS distinct_org_recipients, ROUND(SUM(val) / 1e6, 1) AS total_dollars_M, ROUND(AVG(val) / 1e3, 1) AS avg_dollars_K, ROUND(MEDIAN(val) / 1e3, 1) AS median_dollars_K, ROUND(SUM(val) * 100.0 / SUM(SUM(val)) OVER (), 1) AS pct_of_org_dollars FROM deduped AS d JOIN classification.recipient_class AS rc ON d.recipient_legal_name = rc.recipient WHERE rc.class = 'ORGANIZATIONAL' GROUP BY d.agreement_type ORDER BY SUM(val) DESC LIMIT 2000

SELECT COUNT(*) AS total_rows, COUNT(DISTINCT vendor_name) AS distinct_vendors, ROUND(SUM(TRY_CAST(contract_value AS DOUBLE)) / 1e9, 2) AS total_value_B, ROUND(AVG(TRY_CAST(contract_value AS DOUBLE)) / 1e3, 1) AS avg_value_K, ROUND(MEDIAN(TRY_CAST(contract_value AS DOUBLE)) / 1e3, 1) AS median_value_K FROM corpus.contracts_contracts WHERE TRY_CAST(contract_date AS DATE) >= CAST('2018-01-01' AS DATE) LIMIT 2000

WITH deduped AS (SELECT ref_number, TRY_CAST(amendment_number AS INT) AS amend_n, TRY_CAST(agreement_value AS DOUBLE) AS award_val FROM grants.grants WHERE recipient_legal_name NOT LIKE '%batch report%' AND recipient_legal_name NOT LIKE '%rapport en lots%' QUALIFY ROW_NUMBER() OVER (PARTITION BY ref_number ORDER BY TRY_CAST(amendment_number AS INT) DESC) = 1), banded AS (SELECT *, CASE WHEN award_val < 0 THEN 'Z_negative' WHEN award_val < 25000 THEN 'A_sub25K' WHEN award_val < 50000 THEN 'B_25K_50K' WHEN award_val < 100000 THEN 'C_50K_100K' WHEN award_val < 250000 THEN 'D_100K_250K' WHEN award_val < 500000 THEN 'E_250K_500K' WHEN award_val < 1000000 THEN 'F_500K_1M' WHEN award_val < 5000000 THEN 'G_1M_5M' WHEN award_val < 25000000 THEN 'H_5M_25M' WHEN award_val < 100000000 THEN 'I_25M_100M' ELSE 'J_100Mplus' END AS band FROM deduped) SELECT band, COUNT(*) AS n_agreements, ROUND(AVG(amend_n), 3) AS avg_amend, MEDIAN(amend_n) AS median_amend, ROUND(SUM(CASE WHEN amend_n = 0 THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 1) AS pct_unamended, ROUND(SUM(CASE WHEN amend_n >= 3 THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 1) AS pct_3plus, ROUND(AVG(award_val) / 1000, 1) AS avg_K, ROUND(MEDIAN(award_val) / 1000, 1) AS median_K, ROUND(SUM(award_val) / 1e9, 3) AS total_B, ROUND(SUM(CAST(amend_n AS DOUBLE)) / NULLIF(SUM(CASE WHEN award_val > 0 THEN award_val ELSE 0 END) / 1e6, 0), 4) AS amend_per_M_awarded /* Amendment count per million dollars of positive award value (burden per dollar metric) */ FROM banded GROUP BY band ORDER BY band LIMIT 2000

/* CENSORED DURATION PROXY (85.6% sub-analysis) — fixed empty-string date trap */ WITH deduped AS (SELECT ref_number, TRY_CAST(amendment_number AS INT) AS amend_n, TRY_CAST(agreement_value AS DOUBLE) AS award_val, TRY_CAST(NULLIF(TRIM(CAST(agreement_start_date AS TEXT)), '') AS DATE) AS start_dt, TRY_CAST(NULLIF(TRIM(CAST(agreement_end_date AS TEXT)), '') AS DATE) AS end_dt FROM grants.grants WHERE recipient_legal_name NOT LIKE '%batch report%' AND recipient_legal_name NOT LIKE '%rapport en lots%' QUALIFY ROW_NUMBER() OVER (PARTITION BY ref_number ORDER BY TRY_CAST(amendment_number AS INT) DESC) = 1), banded AS (SELECT *, CASE WHEN award_val < 25000 THEN 'A_sub25K' WHEN award_val < 100000 THEN 'B_25K_100K' WHEN award_val < 500000 THEN 'C_100K_500K' WHEN award_val < 1000000 THEN 'D_500K_1M' WHEN award_val < 5000000 THEN 'E_1M_5M' WHEN award_val < 25000000 THEN 'F_5M_25M' ELSE 'G_25Mplus' END AS band, CASE WHEN NOT end_dt IS NULL AND NOT start_dt IS NULL AND end_dt >= start_dt THEN DATE_DIFF('DAY', start_dt, end_dt) ELSE NULL END AS dur_days FROM deduped WHERE award_val >= 0 /* exclude negative-value artifacts from duration analysis */) SELECT band, COUNT(*) AS n_total, SUM(CASE WHEN NOT dur_days IS NULL THEN 1 ELSE 0 END) AS n_with_dur, ROUND(SUM(CASE WHEN NOT dur_days IS NULL THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 1) AS pct_dur_coverage, ROUND(AVG(dur_days) / 365.25, 2) AS avg_dur_years, ROUND(MEDIAN(dur_days) / 365.25, 2) AS median_dur_years, ROUND(QUANTILE_CONT(dur_days, 0.75 ORDER BY dur_days) / 365.25, 2) AS p75_dur_years, ROUND(AVG(amend_n), 3) AS avg_amend_all, ROUND(AVG(CASE WHEN NOT dur_days IS NULL THEN CAST(amend_n AS DOUBLE) END), 3) AS avg_amend_dur_subset FROM banded GROUP BY band ORDER BY band LIMIT 2000

/* SANITY: HC4 dedupe count, SI-9 exclusion, value/end-date coverage, overall amendment baseline */ WITH deduped AS (SELECT ref_number, TRY_CAST(amendment_number AS INT) AS amend_num, TRY_CAST(agreement_value AS DOUBLE) AS award_value, agreement_end_date, ROW_NUMBER() OVER (PARTITION BY ref_number ORDER BY TRY_CAST(amendment_number AS INT) DESC) AS rn FROM grants.grants WHERE recipient_legal_name NOT LIKE '%batch report%' AND recipient_legal_name NOT LIKE '%rapport en lots%') SELECT COUNT(*) AS base_rows, COUNT(DISTINCT ref_number) AS distinct_refs, SUM(CASE WHEN NOT award_value IS NULL THEN 1 ELSE 0 END) AS value_populated, SUM(CASE WHEN award_value < 0 THEN 1 ELSE 0 END) AS negative_value_count, SUM(CASE WHEN award_value BETWEEN 0 AND 24999 THEN 1 ELSE 0 END) AS sub_25K_count, SUM(CASE WHEN NOT agreement_end_date IS NULL THEN 1 ELSE 0 END) AS end_date_populated, ROUND(100.0 * SUM(CASE WHEN NOT agreement_end_date IS NULL THEN 1 ELSE 0 END) / COUNT(*), 1) AS pct_end_date, MIN(award_value) AS min_value, MAX(award_value) AS max_value, ROUND(AVG(amend_num), 4) AS avg_amend_num_at_latest, SUM(CASE WHEN amend_num = 0 THEN 1 ELSE 0 END) AS n_unamended, ROUND(100.0 * SUM(CASE WHEN amend_num = 0 THEN 1 ELSE 0 END) / COUNT(*), 1) AS pct_unamended FROM deduped WHERE rn = 1 LIMIT 2000

/* PRIMARY Q6: amendment count distribution by award size band (100% coverage) */ WITH deduped AS (SELECT ref_number, TRY_CAST(amendment_number AS INT) AS amend_num, TRY_CAST(agreement_value AS DOUBLE) AS award_value, ROW_NUMBER() OVER (PARTITION BY ref_number ORDER BY TRY_CAST(amendment_number AS INT) DESC) AS rn FROM grants.grants WHERE recipient_legal_name NOT LIKE '%batch report%' AND recipient_legal_name NOT LIKE '%rapport en lots%'), base AS (SELECT ref_number, amend_num, award_value, CASE WHEN award_value < 0 THEN 'Z_negative' WHEN award_value < 25000 THEN 'A_sub25K' WHEN award_value < 50000 THEN 'B_25K_50K' WHEN award_value < 100000 THEN 'C_50K_100K' WHEN award_value < 250000 THEN 'D_100K_250K' WHEN award_value < 500000 THEN 'E_250K_500K' WHEN award_value < 1000000 THEN 'F_500K_1M' WHEN award_value < 5000000 THEN 'G_1M_5M' WHEN award_value < 25000000 THEN 'H_5M_25M' ELSE 'I_25M_plus' END AS award_band FROM deduped WHERE rn = 1 AND NOT award_value IS NULL) SELECT award_band, COUNT(*) AS n_agreements, ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 1) AS pct_of_all, ROUND(AVG(amend_num), 3) AS avg_amendment_count, ROUND(MEDIAN(amend_num), 0) AS median_amendment_count, SUM(CASE WHEN amend_num = 0 THEN 1 ELSE 0 END) AS n_unamended, ROUND(100.0 * SUM(CASE WHEN amend_num = 0 THEN 1 ELSE 0 END) / COUNT(*), 1) AS pct_unamended, SUM(CASE WHEN amend_num >= 3 THEN 1 ELSE 0 END) AS n_three_plus, ROUND(100.0 * SUM(CASE WHEN amend_num >= 3 THEN 1 ELSE 0 END) / COUNT(*), 1) AS pct_three_plus, SUM(CASE WHEN amend_num >= 5 THEN 1 ELSE 0 END) AS n_five_plus, ROUND(SUM(award_value) / 1e9, 3) AS total_value_B, ROUND(AVG(award_value) / 1000, 1) AS avg_award_K, ROUND(MEDIAN(award_value) / 1000, 1) AS median_award_K FROM base GROUP BY 1 ORDER BY 1 LIMIT 2000

/* INVERSE VIEW: for each amendment-count tier, what is the award size distribution? */ WITH deduped AS (SELECT ref_number, TRY_CAST(amendment_number AS INT) AS amend_num, TRY_CAST(agreement_value AS DOUBLE) AS award_value, ROW_NUMBER() OVER (PARTITION BY ref_number ORDER BY TRY_CAST(amendment_number AS INT) DESC) AS rn FROM grants.grants WHERE recipient_legal_name NOT LIKE '%batch report%' AND recipient_legal_name NOT LIKE '%rapport en lots%'), base AS (SELECT ref_number, amend_num, award_value FROM deduped WHERE rn = 1 AND NOT award_value IS NULL AND amend_num BETWEEN 0 AND 15) SELECT amend_num AS amendment_count, COUNT(*) AS n_agreements, ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 2) AS pct_of_total, ROUND(AVG(award_value) / 1000, 1) AS avg_award_K, ROUND(MEDIAN(award_value) / 1000, 1) AS median_award_K, ROUND(QUANTILE_CONT(award_value, 0.25 ORDER BY award_value) / 1000, 1) AS p25_award_K, ROUND(QUANTILE_CONT(award_value, 0.75 ORDER BY award_value) / 1000, 1) AS p75_award_K, ROUND(SUM(award_value) / 1e9, 3) AS total_value_B FROM base GROUP BY 1 ORDER BY 1 LIMIT 2000

/* DURATION PROXY (CENSORED 85.6% SUB-ANALYSIS): agreement duration by award size band */ WITH deduped AS (SELECT ref_number, TRY_CAST(amendment_number AS INT) AS amend_num, TRY_CAST(agreement_value AS DOUBLE) AS award_value, agreement_start_date, agreement_end_date, ROW_NUMBER() OVER (PARTITION BY ref_number ORDER BY TRY_CAST(amendment_number AS INT) DESC) AS rn FROM grants.grants WHERE recipient_legal_name NOT LIKE '%batch report%' AND recipient_legal_name NOT LIKE '%rapport en lots%'), base AS (SELECT ref_number, amend_num, award_value, DATE_DIFF('DAY', CAST(agreement_start_date AS DATE), CAST(agreement_end_date AS DATE)) AS duration_days, CASE WHEN award_value < 0 THEN 'Z_negative' WHEN award_value < 25000 THEN 'A_sub25K' WHEN award_value < 50000 THEN 'B_25K_50K' WHEN award_value < 100000 THEN 'C_50K_100K' WHEN award_value < 250000 THEN 'D_100K_250K' WHEN award_value < 500000 THEN 'E_250K_500K' WHEN award_value < 1000000 THEN 'F_500K_1M' WHEN award_value < 5000000 THEN 'G_1M_5M' WHEN award_value < 25000000 THEN 'H_5M_25M' ELSE 'I_25M_plus' END AS award_band FROM deduped WHERE rn = 1 AND NOT award_value IS NULL AND NOT agreement_start_date IS NULL AND NOT agreement_end_date IS NULL AND CAST(agreement_end_date AS DATE) >= CAST(agreement_start_date AS DATE)) SELECT award_band, COUNT(*) AS n_with_duration, ROUND(AVG(duration_days) / 365.25, 2) AS avg_duration_years, ROUND(MEDIAN(duration_days) / 365.25, 2) AS median_duration_years, ROUND(AVG(duration_days), 0) AS avg_duration_days, ROUND(MEDIAN(duration_days), 0) AS median_duration_days, SUM(CASE WHEN duration_days > 3650 THEN 1 ELSE 0 END) AS n_over_10yr, MIN(duration_days) AS min_days, MAX(duration_days) AS max_days FROM base GROUP BY 1 ORDER BY 1 LIMIT 2000

WITH latest AS (SELECT bn, fpe, TRY_CAST("5010_num" AS DOUBLE) AS mgmt, TRY_CAST("5100_num" AS DOUBLE) AS total, ROW_NUMBER() OVER (PARTITION BY bn, fpe ORDER BY form_id DESC) AS rn FROM corpus.cra_charities_financial_d_and_schedule_6), valid AS (SELECT bn, mgmt / total * 100 AS admin_pct FROM latest WHERE rn = 1 AND total > 0 AND mgmt >= 0 AND mgmt / total <= 1.0 AND SUBSTRING(fpe, 1, 4) IN ('2020', '2021', '2022', '2023', '2024')) SELECT COUNT(*) AS n_charity_years, COUNT(DISTINCT bn) AS distinct_bns, ROUND(QUANTILE_CONT(admin_pct, 0.25 ORDER BY admin_pct), 1) AS p25, ROUND(QUANTILE_CONT(admin_pct, 0.5 ORDER BY admin_pct), 1) AS median_pct, ROUND(AVG(admin_pct), 1) AS mean_pct, ROUND(QUANTILE_CONT(admin_pct, 0.75 ORDER BY admin_pct), 1) AS p75, ROUND(QUANTILE_CONT(admin_pct, 0.90 ORDER BY admin_pct), 1) AS p90, ROUND(AVG(CASE WHEN admin_pct > 20 THEN 1.0 ELSE 0.0 END) * 100, 1) AS pct_over_20, ROUND(AVG(CASE WHEN admin_pct > 30 THEN 1.0 ELSE 0.0 END) * 100, 1) AS pct_over_30 FROM valid LIMIT 2000

WITH org_year AS (SELECT organization_id, MAX(CAST(organization AS TEXT)) AS org_name, CAST(fy_ef AS TEXT) AS fy, SUM(TRY_CAST(REPLACE(REPLACE(NULLIF(CAST(actual_spending AS TEXT), ''), ' ', ''), ',', '') AS DOUBLE)) AS total_spend, SUM(CASE WHEN TRIM(CAST(core_responsibility AS TEXT)) = 'Internal Services' THEN TRY_CAST(REPLACE(REPLACE(NULLIF(CAST(actual_spending AS TEXT), ''), ' ', ''), ',', '') AS DOUBLE) ELSE 0 END) AS is_spend FROM corpus.gc_infobase_rbpo_rppo WHERE CAST(fy_ef AS TEXT) IN ('2020-21', '2021-22', '2022-23', '2023-24') GROUP BY organization_id, CAST(fy_ef AS TEXT)), org_avg AS (SELECT organization_id, org_name, COUNT(DISTINCT fy) AS n_years, ROUND(AVG(CASE WHEN total_spend > 0 THEN is_spend / total_spend * 100 END), 1) AS avg_is_pct FROM org_year WHERE total_spend > 0 GROUP BY organization_id, org_name) SELECT org_name, avg_is_pct, n_years FROM org_avg WHERE avg_is_pct >= 20 AND n_years >= 2 ORDER BY avg_is_pct DESC LIMIT 30

WITH base AS (SELECT organization_id, organization, fy_ef, core_responsibility, TRY_CAST(REPLACE(REPLACE(NULLIF(CAST(actual_spending AS TEXT), ''), ',', ''), '$', '') AS DOUBLE) AS spend FROM corpus.gc_infobase_rbpo_rppo WHERE fy_ef IN ('2020-2021', '2021-2022', '2022-2023', '2023-2024')), dept_yr AS (SELECT organization_id, organization, fy_ef, SUM(CASE WHEN TRIM(core_responsibility) = 'Internal Services' THEN COALESCE(spend, 0) ELSE 0 END) AS is_spend, SUM(COALESCE(spend, 0)) AS total_spend FROM base GROUP BY organization_id, organization, fy_ef HAVING total_spend > 0), dept_pooled AS (SELECT organization_id, organization, SUM(is_spend) AS is_total, SUM(total_spend) AS total_total, CASE WHEN SUM(total_spend) > 0 THEN SUM(is_spend) / SUM(total_spend) ELSE NULL END AS is_ratio FROM dept_yr GROUP BY organization_id, organization HAVING SUM(is_spend) > 0 AND SUM(total_spend) > 0) SELECT CASE WHEN is_ratio < 0.05 THEN 'A_lt5pct' WHEN is_ratio < 0.10 THEN 'B_5to10pct' WHEN is_ratio < 0.20 THEN 'C_10to20pct' WHEN is_ratio < 0.30 THEN 'D_20to30pct' WHEN is_ratio < 0.40 THEN 'E_30to40pct' WHEN is_ratio < 1.00 THEN 'F_40to100pct' ELSE 'G_over100pct' END AS is_tier, COUNT(*) AS n_depts, ROUND(MEDIAN(is_ratio) * 100, 1) AS median_pct_in_tier, ROUND(MIN(is_ratio) * 100, 1) AS min_pct, ROUND(MAX(is_ratio) * 100, 1) AS max_pct FROM dept_pooled GROUP BY 1 ORDER BY 1 LIMIT 2000

WITH base AS (SELECT organization_id, organization, fy_ef, core_responsibility, TRY_CAST(REPLACE(REPLACE(NULLIF(CAST(actual_spending AS TEXT), ''), ',', ''), '$', '') AS DOUBLE) AS spend FROM corpus.gc_infobase_rbpo_rppo WHERE fy_ef IN ('2020-2021', '2021-2022', '2022-2023', '2023-2024')), dept_yr AS (SELECT organization_id, organization, fy_ef, SUM(CASE WHEN TRIM(core_responsibility) = 'Internal Services' THEN COALESCE(spend, 0) ELSE 0 END) AS is_spend, SUM(COALESCE(spend, 0)) AS total_spend FROM base GROUP BY organization_id, organization, fy_ef HAVING total_spend > 0), dept_pooled AS (SELECT organization_id, organization, SUM(is_spend) AS is_total, SUM(total_spend) AS total_total, CASE WHEN SUM(total_spend) > 0 THEN SUM(is_spend) / SUM(total_spend) ELSE NULL END AS is_ratio FROM dept_yr GROUP BY organization_id, organization HAVING SUM(is_spend) > 0 AND SUM(total_spend) > 0) SELECT COUNT(*) AS total_depts, ROUND(MEDIAN(is_ratio) * 100, 1) AS median_is_pct, ROUND(AVG(is_ratio) * 100, 1) AS mean_is_pct, COUNT(CASE WHEN is_ratio >= 0.20 THEN 1 END) AS n_gte_20pct, ROUND(COUNT(CASE WHEN is_ratio >= 0.20 THEN 1 END) * 100.0 / COUNT(*), 1) AS share_gte_20pct, COUNT(CASE WHEN is_ratio >= 0.30 THEN 1 END) AS n_gte_30pct, COUNT(CASE WHEN is_ratio >= 0.40 THEN 1 END) AS n_gte_40pct, ROUND(MEDIAN(CASE WHEN is_ratio < 1.0 THEN is_ratio END) * 100, 1) AS median_is_pct_excl_outliers /* Exclude the CGC cost-recovery outlier (>100%) for clean median */ FROM dept_pooled LIMIT 2000

SELECT SUBSTRING(CAST(start_date AS TEXT), 1, 4) AS yr, COUNT(*) AS n_trips, ROUND(SUM(TRY_CAST(REPLACE(REPLACE(NULLIF(CAST(total AS TEXT), ''), ',', ''), '$', '') AS DOUBLE)) / 1e6, 2) AS total_M, ROUND(AVG(TRY_CAST(REPLACE(REPLACE(NULLIF(CAST(total AS TEXT), ''), ',', ''), '$', '') AS DOUBLE)), 0) AS avg_per_trip_cad, ROUND(SUM(TRY_CAST(REPLACE(REPLACE(NULLIF(CAST(airfare AS TEXT), ''), ',', ''), '$', '') AS DOUBLE)) / 1e6, 2) AS airfare_M, ROUND(SUM(TRY_CAST(REPLACE(REPLACE(NULLIF(CAST(lodging AS TEXT), ''), ',', ''), '$', '') AS DOUBLE)) / 1e6, 2) AS lodging_M FROM corpus.travel_expenses_travelq WHERE SUBSTRING(CAST(start_date AS TEXT), 1, 4) BETWEEN '2019' AND '2024' GROUP BY 1 ORDER BY 1 LIMIT 2000

SELECT table_schema, table_name FROM information_schema.tables WHERE LOWER(table_name) LIKE '%infobase%' OR LOWER(table_name) LIKE '%rbpo%' OR LOWER(table_name) LIKE '%rppo%' OR LOWER(table_name) LIKE '%travel%' ORDER BY table_schema, table_name LIMIT 30

WITH dept_total AS (SELECT fy_ef, organization_id, organization, SUM(TRY_CAST(REPLACE(REPLACE(NULLIF(CAST(actual_spending AS TEXT), ''), ',', ''), ' ', '') AS DOUBLE)) AS total_spend FROM corpus.gc_infobase_rbpo_rppo GROUP BY fy_ef, organization_id, organization), dept_is AS (SELECT fy_ef, organization_id, SUM(TRY_CAST(REPLACE(REPLACE(NULLIF(CAST(actual_spending AS TEXT), ''), ',', ''), ' ', '') AS DOUBLE)) AS is_spend FROM corpus.gc_infobase_rbpo_rppo WHERE CAST(core_responsibility AS TEXT) = 'Internal Services' GROUP BY fy_ef, organization_id) SELECT d.fy_ef, COUNT(DISTINCT d.organization_id) AS n_depts, ROUND(SUM(d.total_spend) / 1e9, 2) AS total_spend_B, ROUND(SUM(di.is_spend) / 1e9, 2) AS is_spend_B, ROUND(100.0 * SUM(di.is_spend) / NULLIF(SUM(d.total_spend), 0), 1) AS is_pct_of_total FROM dept_total AS d LEFT JOIN dept_is AS di ON d.fy_ef = di.fy_ef AND d.organization_id = di.organization_id GROUP BY d.fy_ef ORDER BY d.fy_ef LIMIT 2000

WITH dept_total AS (SELECT organization_id, organization, SUM(TRY_CAST(REPLACE(REPLACE(NULLIF(CAST(actual_spending AS TEXT), ''), ',', ''), ' ', '') AS DOUBLE)) AS total_spend FROM corpus.gc_infobase_rbpo_rppo GROUP BY organization_id, organization), dept_is AS (SELECT organization_id, SUM(TRY_CAST(REPLACE(REPLACE(NULLIF(CAST(actual_spending AS TEXT), ''), ',', ''), ' ', '') AS DOUBLE)) AS is_spend FROM corpus.gc_infobase_rbpo_rppo WHERE CAST(core_responsibility AS TEXT) = 'Internal Services' GROUP BY organization_id) SELECT d.organization, ROUND(d.total_spend / 1e6, 1) AS total_M, ROUND(di.is_spend / 1e6, 1) AS is_M, ROUND(100.0 * di.is_spend / NULLIF(d.total_spend, 0), 1) AS is_pct FROM dept_total AS d JOIN dept_is AS di ON d.organization_id = di.organization_id WHERE d.total_spend > 0 ORDER BY is_pct DESC LIMIT 30

SELECT SUBSTRING(CAST(start_date AS TEXT), 1, 4) AS yr, disclosure_group, COUNT(*) AS n_trips, ROUND(SUM(TRY_CAST(REPLACE(REPLACE(NULLIF(CAST(total AS TEXT), ''), ',', ''), '$', '') AS DOUBLE)) / 1e6, 2) AS total_M, ROUND(AVG(TRY_CAST(REPLACE(REPLACE(NULLIF(CAST(total AS TEXT), ''), ',', ''), '$', '') AS DOUBLE)), 0) AS avg_per_trip FROM corpus.travel_expenses_travelq WHERE SUBSTRING(CAST(start_date AS TEXT), 1, 4) BETWEEN '2018' AND '2025' GROUP BY yr, disclosure_group ORDER BY yr, total_M DESC LIMIT 40

WITH dept_total AS (SELECT fy_ef, organization_id, organization, SUM(TRY_CAST(REPLACE(NULLIF(actual_spending, ''), ',', '') AS DOUBLE)) AS total_spend FROM gc_infobase_rbpo_rppo GROUP BY fy_ef, organization_id, organization), dept_is AS (SELECT fy_ef, organization_id, SUM(TRY_CAST(REPLACE(NULLIF(actual_spending, ''), ',', '') AS DOUBLE)) AS is_spend FROM gc_infobase_rbpo_rppo WHERE core_responsibility = 'Internal Services' GROUP BY fy_ef, organization_id), ratios AS (SELECT t.fy_ef, t.organization_id, t.organization, COALESCE(t.total_spend, 0) AS total_spend, COALESCE(i.is_spend, 0) AS is_spend, CASE WHEN COALESCE(t.total_spend, 0) > 0 THEN 100.0 * COALESCE(i.is_spend, 0) / t.total_spend ELSE NULL END AS is_pct FROM dept_total AS t LEFT JOIN dept_is AS i ON t.fy_ef = i.fy_ef AND t.organization_id = i.organization_id WHERE COALESCE(t.total_spend, 0) > 0) SELECT fy_ef, COUNT(DISTINCT organization_id) AS n_depts, ROUND(SUM(is_spend) / 1e9, 2) AS total_is_B, ROUND(SUM(total_spend) / 1e9, 2) AS total_all_B, ROUND(100.0 * SUM(is_spend) / NULLIF(SUM(total_spend), 0), 1) AS govt_wide_is_pct, ROUND(AVG(is_pct), 1) AS mean_dept_is_pct, ROUND(QUANTILE_CONT(is_pct, 0.5 ORDER BY is_pct), 1) AS median_dept_is_pct, ROUND(QUANTILE_CONT(is_pct, 0.75 ORDER BY is_pct), 1) AS p75_dept_is_pct, COUNT(CASE WHEN is_pct > 20 THEN 1 END) AS n_depts_over_20pct FROM ratios GROUP BY fy_ef ORDER BY fy_ef LIMIT 2000

WITH dept_total AS (SELECT fy_ef, organization_id, organization, SUM(TRY_CAST(REPLACE(NULLIF(actual_spending, ''), ',', '') AS DOUBLE)) AS total_spend FROM gc_infobase_rbpo_rppo WHERE fy_ef = '2023-2024' GROUP BY fy_ef, organization_id, organization), dept_is AS (SELECT fy_ef, organization_id, SUM(TRY_CAST(REPLACE(NULLIF(actual_spending, ''), ',', '') AS DOUBLE)) AS is_spend FROM gc_infobase_rbpo_rppo WHERE fy_ef = '2023-2024' AND core_responsibility = 'Internal Services' GROUP BY fy_ef, organization_id) SELECT t.organization, ROUND(t.total_spend / 1e6, 1) AS total_M, ROUND(COALESCE(i.is_spend, 0) / 1e6, 1) AS is_M, ROUND(100.0 * COALESCE(i.is_spend, 0) / NULLIF(t.total_spend, 0), 1) AS is_pct FROM dept_total AS t LEFT JOIN dept_is AS i ON t.organization_id = i.organization_id WHERE t.total_spend > 50e6 ORDER BY is_pct DESC LIMIT 30

SELECT SUBSTRING(CAST(start_date AS TEXT), 1, 4) AS year, COUNT(*) AS n_trips, ROUND(SUM(TRY_CAST(REPLACE(NULLIF(total, ''), ',', '') AS DOUBLE)) / 1e6, 2) AS total_travel_M, ROUND(AVG(TRY_CAST(REPLACE(NULLIF(total, ''), ',', '') AS DOUBLE)), 0) AS avg_per_trip, ROUND(QUANTILE_CONT(TRY_CAST(REPLACE(NULLIF(total, ''), ',', '') AS DOUBLE), 0.5 ORDER BY TRY_CAST(REPLACE(NULLIF(total, ''), ',', '') AS DOUBLE)), 0) AS median_per_trip, ROUND(SUM(TRY_CAST(REPLACE(NULLIF(airfare, ''), ',', '') AS DOUBLE)) / 1e6, 2) AS airfare_M, ROUND(SUM(TRY_CAST(REPLACE(NULLIF(lodging, ''), ',', '') AS DOUBLE)) / 1e6, 2) AS lodging_M FROM travel_expenses_travelq WHERE NOT total IS NULL AND total <> '' AND total <> '0.00' AND total <> '0' GROUP BY SUBSTRING(CAST(start_date AS TEXT), 1, 4) ORDER BY year LIMIT 2000

WITH ratios AS (SELECT SUBSTRING(CAST(fpe AS TEXT), 1, 4) AS fpe_year, bn, TRY_CAST("5010_num" AS DOUBLE) AS admin_exp, TRY_CAST("5100_num" AS DOUBLE) AS total_exp FROM corpus.cra_charities_financial_d_and_schedule_6), valid AS (SELECT fpe_year, bn, admin_exp / total_exp AS admin_ratio FROM ratios WHERE NOT admin_exp IS NULL AND total_exp > 0 AND admin_exp >= 0 AND admin_exp <= total_exp) SELECT fpe_year, COUNT(*) AS n_charities, ROUND(AVG(admin_ratio) * 100, 1) AS mean_pct, ROUND(QUANTILE_CONT(admin_ratio, 0.10 ORDER BY admin_ratio) * 100, 1) AS p10_pct, ROUND(QUANTILE_CONT(admin_ratio, 0.25 ORDER BY admin_ratio) * 100, 1) AS p25_pct, ROUND(QUANTILE_CONT(admin_ratio, 0.50 ORDER BY admin_ratio) * 100, 1) AS median_pct, ROUND(QUANTILE_CONT(admin_ratio, 0.75 ORDER BY admin_ratio) * 100, 1) AS p75_pct, ROUND(QUANTILE_CONT(admin_ratio, 0.90 ORDER BY admin_ratio) * 100, 1) AS p90_pct, ROUND(COUNT(CASE WHEN admin_ratio > 0.20 THEN 1 END) * 100.0 / COUNT(*), 1) AS pct_over_20pct, ROUND(COUNT(CASE WHEN admin_ratio = 0.0 THEN 1 END) * 100.0 / COUNT(*), 1) AS pct_zero_admin FROM valid GROUP BY fpe_year ORDER BY fpe_year LIMIT 2000

SELECT core_responsibility, COUNT(*) AS rows, COUNT(DISTINCT organization_id) AS orgs, COUNT(DISTINCT fy_ef) AS years, MIN(fy_ef) AS min_fy, MAX(fy_ef) AS max_fy, ROUND(SUM(TRY_CAST(REPLACE(actual_spending, ',', '') AS DOUBLE)) / 1e9, 2) AS total_spend_B FROM corpus.gc_infobase_rbpo_rppo WHERE NOT actual_spending IS NULL AND NOT TRIM(actual_spending) IN ('', 'N/A', '-', '—') AND NOT TRY_CAST(REPLACE(actual_spending, ',', '') AS DOUBLE) IS NULL GROUP BY core_responsibility ORDER BY total_spend_B DESC LIMIT 25

WITH dept_year AS (SELECT fy_ef, organization_id, SUM(TRY_CAST(REPLACE(actual_spending, ',', '') AS DOUBLE)) AS total_spend, SUM(CASE WHEN LOWER(core_responsibility) LIKE '%internal service%' THEN TRY_CAST(REPLACE(actual_spending, ',', '') AS DOUBLE) ELSE 0 END) AS internal_svcs_spend FROM corpus.gc_infobase_rbpo_rppo WHERE NOT actual_spending IS NULL AND NOT TRIM(actual_spending) IN ('', 'N/A', '-', '—') AND NOT TRY_CAST(REPLACE(actual_spending, ',', '') AS DOUBLE) IS NULL GROUP BY fy_ef, organization_id HAVING SUM(TRY_CAST(REPLACE(actual_spending, ',', '') AS DOUBLE)) > 0), ratios AS (SELECT fy_ef, organization_id, CASE WHEN total_spend > 0 THEN internal_svcs_spend / total_spend ELSE NULL END AS is_ratio FROM dept_year) SELECT fy_ef, COUNT(*) AS dept_count, ROUND(AVG(is_ratio) * 100, 1) AS mean_pct, ROUND(QUANTILE_CONT(is_ratio, 0.25 ORDER BY is_ratio) * 100, 1) AS p25_pct, ROUND(QUANTILE_CONT(is_ratio, 0.50 ORDER BY is_ratio) * 100, 1) AS median_pct, ROUND(QUANTILE_CONT(is_ratio, 0.75 ORDER BY is_ratio) * 100, 1) AS p75_pct, ROUND(COUNT(CASE WHEN is_ratio > 0.20 THEN 1 END) * 100.0 / COUNT(*), 1) AS pct_dept_over_20pct, ROUND(SUM(total_spend) / 1e9, 1) AS total_govt_spend_B FROM ratios GROUP BY fy_ef ORDER BY fy_ef LIMIT 2000

SELECT column_name, data_type FROM information_schema.columns WHERE table_name = 'travel_expenses_travelq' ORDER BY ordinal_position LIMIT 30

SELECT SUBSTRING(CAST(fpe AS TEXT), 1, 4) AS fpe_year, COUNT(*) AS total_rows, COUNT(DISTINCT bn) AS distinct_bns, COUNT(DISTINCT bn || '|' || CAST(fpe AS TEXT)) AS distinct_bn_fpe_combos, SUM(CASE WHEN NOT "5010_num" IS NULL THEN 1 ELSE 0 END) AS admin_exp_populated, SUM(CASE WHEN NOT "5100_num" IS NULL AND TRY_CAST("5100_num" AS DOUBLE) > 0 THEN 1 ELSE 0 END) AS total_exp_nonzero, SUM(CASE WHEN NOT "5010_num" IS NULL AND NOT "5100_num" IS NULL AND TRY_CAST("5100_num" AS DOUBLE) > 0 THEN 1 ELSE 0 END) AS both_fields_computable FROM corpus.cra_charities_financial_d_and_schedule_6 GROUP BY 1 ORDER BY 1 LIMIT 2000

SELECT multi_flag, COUNT(*) AS bn_fpe_combos, ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 2) AS pct FROM (SELECT bn, fpe, CASE WHEN COUNT(DISTINCT form_id) > 1 THEN 'multi_form_needs_dedupe' ELSE 'single_form_clean' END AS multi_flag FROM corpus.cra_charities_financial_d_and_schedule_6 GROUP BY bn, fpe) GROUP BY 1 ORDER BY 1 LIMIT 2000

SELECT column_name, data_type FROM information_schema.columns WHERE table_name = 'gc_infobase_rbpo_rppo' ORDER BY ordinal_position LIMIT 40

SELECT column_name, data_type FROM information_schema.columns WHERE table_name = 'gc_infobase_eav_eac' ORDER BY ordinal_position LIMIT 40

SELECT table_name FROM information_schema.tables WHERE table_catalog IN ('corpus', 'grants', 'entities') AND (LOWER(table_name) LIKE '%travel%' OR LOWER(table_name) LIKE '%hospital%' OR LOWER(table_name) LIKE '%proactive%') ORDER BY table_catalog, table_name LIMIT 2000

WITH base AS (SELECT ref_number, COALESCE(NULLIF(TRIM(recipient_province), ''), 'UNKNOWN') AS prov, TRY_CAST(agreement_value AS DOUBLE) AS val FROM grants.grants WHERE LOWER(recipient_legal_name) NOT LIKE '%batch report%' AND YEAR(TRY_CAST(agreement_start_date AS DATE)) >= 2018 QUALIFY ROW_NUMBER() OVER (PARTITION BY ref_number ORDER BY TRY_CAST(amendment_number AS INT) DESC) = 1) SELECT prov, COUNT(*) AS base_rows, COUNT(DISTINCT ref_number) AS distinct_refs, ROUND(SUM(val) / 1e9, 2) AS total_bn, ROUND(100.0 * SUM(val) / SUM(SUM(val)) OVER (), 2) AS pct_total FROM base GROUP BY prov ORDER BY total_bn DESC LIMIT 20

WITH base AS (SELECT ref_number, TRY_CAST(agreement_value AS DOUBLE) AS val FROM grants.grants WHERE LOWER(recipient_legal_name) NOT LIKE '%batch report%' AND YEAR(TRY_CAST(agreement_start_date AS DATE)) >= 2018 QUALIFY ROW_NUMBER() OVER (PARTITION BY ref_number ORDER BY TRY_CAST(amendment_number AS INT) DESC) = 1) SELECT COUNT(*) AS base_rows, COUNT(DISTINCT ref_number) AS distinct_refs, ROUND(SUM(val) / 1e9, 2) AS grand_total_bn FROM base LIMIT 2000