Every figure published in content/research/ traces to one numbered query in this directory. The queries are plain SQL, run directly against the production Postgres database, and take no parameters beyond the literal dates written into them.
A reader with database access should be able to run these in order and reproduce every number in the paper. Where a re-run differs from the published figure, the database has moved on: voters is refreshed weekly from the NC State Board of Elections, so counts drift. Each paper records the load date it was computed against, and each query file records the date it was last run and the row counts it returned.
Running#
The Render MCP query_render_postgres tool cannot reach this database - it fails TLS negotiation. Use psql directly, from a host on the database IP allowlist:
psql "$DATABASE_URL" -f scripts/research/01-registration-composition.sqlor, without a local Postgres client:
docker run --rm -i -v "$PWD/scripts/research":/q postgres:16 \
psql "$DATABASE_URL" -f /q/01-registration-composition.sqlEvery file is read-only. Several set statement_timeout because the vote-history aggregations scan tens of millions of rows; none writes, creates, or drops anything. The heaviest query (04) takes roughly 90 seconds against a pro_16gb instance.
Query index#
| File | Answers |
|---|---|
00-coverage.sql | What is actually loaded, and which datasets are incomplete |
01-registration-composition.sql | Current registration by status, party, race |
02-registration-trend.sql | Party composition at three point-in-time snapshots |
03-eligibility-validity.sql | Whether registr_dt can serve as an eligibility date |
04-midterm-dropoff.sql | 2024 presidential vs 2022 midterm participation, by party, age, race |
05-dropoff-2018-2020.sql | The same comparison one cycle earlier, as a replication |
06-participation-index.sql | Distribution of participation across the four most recent generals |
07-county-turnout.sql | Measured midterm and presidential turnout for all 100 counties |
08-electorate-composition.sql | Who the midterm electorate is, against who is registered |
09-rolloff.sql | Votes cast in countywide races against ballots cast, contested and not |
10-registration-timing.sql | Registrations in the closing window before an election |
11-win-number.sql | The win-number worked example, following the product's own method |
12-midterm-volume-sensitivity.sql | Midterm-to-midterm ballot volume variation, used to bracket the projections in 11 |
13-phone-coverage.sql | Share of the voter file carrying a phone number, statewide, by party, by county |
14-address-sample.sql | Draws the address sample that address-parse-rate.ts measures |
15-precinct-codes.sql | Exports the distinct precinct codes that precinct-join.ts matches to NCSBE boundaries |
Harnesses#
Two GOTV measurements are not expressible in SQL, so a .sql file draws the data and a script measures it. Both are pure readers of exported files and neither connects to the database. Their findings are written up in docs/research/gotv-m0-findings.md.
| File | Answers |
|---|---|
address-parse-rate.ts | Exact / partial / unparseable rates for src/lib/address/parse.ts over the sample from 14 |
precinct-join.ts | Two-way match of voter precinct codes against an NCSBE boundary .dbf, and what each candidate normalization buys |
Data dictionary#
Only the fields these queries read. Full schema in prisma/schema.prisma.
voters (9,203,238 rows as of 2026-08-08)#
One row per registrant in the NC State Board of Elections ncvoter_Statewide file.
| Column | Meaning | Notes for analysis |
|---|---|---|
state_voter_id | Statewide voter identifier (NCSBE calls it ncid) | The join key to voter_history. Unique. |
county_id | County code, 1-100 | Joins to counties.county_id and to registration_snapshot_stats.district_code where district_type = 'COUNTY'. |
voter_status_desc | ACTIVE, INACTIVE, REMOVED, DENIED | Every query here restricts to ACTIVE unless stated. REMOVED and DENIED registrants remain in the file. |
party_cd | DEM, REP, UNA, LIB, GRE, and minor parties | Registration, not vote choice. NC has no party registration requirement to vote in a general. |
race_code | W, B, A, I, M, O, P, U | Self-reported and optional. U is undesignated. Not a census category. |
birth_year | Year of birth | Age bands in these queries are computed as 2026 - birth_year, i.e. age at the 2026 general, applied to historical behavior. |
registr_dt | Registration date in the current county | Not a stable eligibility date. See 03-eligibility-validity.sql. |
confidential_ind | Y for a voter under an address-confidentiality program | 308 active voters. Present in the file; not excluded, and too small to move any published figure. |
removed_at | Set when a voter disappears from the source file | Currently null for every row; the statewide load has run once. |
propensity_score | Product-computed participation score | Do not use. Its basis is drawn from the elections table, which is truncated, so the field is currently degraded. 06-participation-index.sql builds the equivalent from voter_history instead. |
voter_history (35,525,793 rows)#
One row per ballot cast per voter per election, from the NCSBE ncvhis_Statewide file, which carries a ten-year window.
| Column | Meaning | Notes for analysis |
|---|---|---|
state_voter_id | Joins to voters | No foreign key, deliberately. History may reference a voter since removed. |
election_lbl | Election date | The only reliable election key in the database. Use it directly. |
election_desc | Source description string | Free text, inconsistently spelled in the source (11/07/20117 KITTRELL/MIDDLEBURG/MUNICIPAL RUNOFF). Do not parse it. |
county_id | County of registration at the time of the ballot | Used for per-county ballot counts. |
Because the file only carries voters presently in the registration file, participation counted here is subject to survivorship: a 2018 voter who has since died, moved out of state, or been removed is not counted. The bias grows with the age of the election.
registration_snapshot_stats (67,354 rows)#
Aggregate active-registration counts from NCSBE point-in-time snapshot files. Only aggregates are stored; no individual snapshot voter row is ever written.
| Column | Meaning |
|---|---|
snapshot_date | 2022-11-08, 2024-03-05, 2024-11-05. Only these three exist. |
district_type | COUNTY, PRECINCT, NC_HOUSE, NC_SENATE, US_CONGRESS, COUNTY_COMMISSION, NC_SCHOOL_DISTRICT |
district_code | Code within that type |
party_cd | ALL is the all-party rollup; other values are per-party |
active_count | Count of ACTIVE-status registrants only |
This is the correct denominator for a turnout rate: registration as it stood on election day, not registration as it stands today.
contests / contest_results (2,273 / 171,900 rows)#
Precinct-level results from NCSBE ENRS files. Partially loaded. See 00-coverage.sql and the limitations section of any paper that uses them.
| Column | Meaning |
|---|---|
contests.name | Contest name verbatim from the results file |
contests.vote_for | Seats elected. 1 for a single-seat race; roll-off analysis must restrict to these. |
contest_results.choice | Candidate name, or a write-in label |
contest_results.election_day / early_voting / absentee_by_mail / provisional | Vote counts by method. Sum them for total votes. |
elections (2 rows) and district_turnout (9,023 rows)#
Both are truncated by the same ingest defect and neither is used by any published figure. 00-coverage.sql documents their state.
The queries
00-coverage.sql#
-- 00 - Dataset coverage. Run this first: it establishes what is loaded and
-- what is not, and every paper's limitations section quotes it.
-- Last run 2026-08-08 against production.
-- Row counts. Returned 2026-08-08:
-- voters 9203238 | district_turnout 9023 | contest_results 171900
-- contests 2273 | elections 2 | candidate_filings 39037
-- registration_snapshot_stats 67354 | counties 100
-- voter_history is counted per election by the last query in this file rather
-- than here; those ballots summed to 35,525,793.
SELECT 'voters' AS t, count(*) FROM voters
UNION ALL SELECT 'district_turnout', count(*) FROM district_turnout
UNION ALL SELECT 'contest_results', count(*) FROM contest_results
UNION ALL SELECT 'contests', count(*) FROM contests
UNION ALL SELECT 'elections', count(*) FROM elections
UNION ALL SELECT 'candidate_filings', count(*) FROM candidate_filings
UNION ALL SELECT 'registration_snapshot_stats', count(*) FROM registration_snapshot_stats
UNION ALL SELECT 'counties', count(*) FROM counties;
-- Which elections the results loader actually reached. Returned only the 2022
-- general and the 2023 municipal general; the configured list in
-- src/lib/states/nc/results-load.ts names six.
SELECT id, date::date, name, kind, source_prefix FROM elections ORDER BY date;
-- Result rows and county coverage per loaded election. The 2023 municipal
-- general has 981 contests defined and zero result rows.
SELECT e.date::date AS election, e.name,
count(DISTINCT c.id) AS contests,
(SELECT count(*) FROM contest_results r
JOIN contests c2 ON c2.id = r.contest_id WHERE c2.election_id = e.id) AS result_rows,
(SELECT count(DISTINCT r.county_name) FROM contest_results r
JOIN contests c2 ON c2.id = r.contest_id WHERE c2.election_id = e.id) AS counties_present
FROM elections e LEFT JOIN contests c ON c.election_id = e.id
GROUP BY e.id, e.date, e.name ORDER BY e.date;
-- district_turnout inherits the same truncation: it can only cover elections
-- present in `elections`. Returned 9023 rows across 2 elections.
SELECT count(*) AS rows, count(DISTINCT election_lbl) AS elections,
min(election_lbl)::date AS earliest, max(election_lbl)::date AS latest
FROM district_turnout;
-- voter_history is unaffected. Every election the file carries, with ballots.
-- This is the authoritative election list for any turnout analysis.
SELECT election_lbl::date AS election, count(*) AS ballots
FROM voter_history GROUP BY 1 ORDER BY 1;
-- The product's propensity field, which draws its basis from the truncated
-- `elections` table. Returned 0 -> 5375485, 1 -> 3352773, 2 -> 474980: a
-- two-election basis on what should be a four-election scale. Unusable.
SELECT propensity_score, count(*) FROM voters GROUP BY 1 ORDER BY 1 NULLS FIRST;01-registration-composition.sql#
-- 01 - Current registration composition, statewide.
-- Last run 2026-08-08 against production, voter file loaded 2026-08-08.
-- By registration status. Returned:
-- ACTIVE 6752176 | REMOVED 1195671 | INACTIVE 1064995 | DENIED 190396
SELECT voter_status_desc, status_cd, count(*)
FROM voters GROUP BY 1, 2 ORDER BY 3 DESC;
-- By party, active registrants only. Returned:
-- UNA 2649690 | REP 2088295 | DEM 1974401 | LIB 35400 | GRE 4390
SELECT party_cd, count(*) AS n,
round(100.0 * count(*) / sum(count(*)) OVER (), 2) AS pct
FROM voters WHERE voter_status_desc = 'ACTIVE'
GROUP BY 1 ORDER BY 2 DESC;
-- By self-reported race code, active registrants only. Returned:
-- W 4414085 | B 1250712 | U 587818 | O 302573 | A 123221 | I 46016
-- M 26864 | P 886 | (blank) 1
SELECT race_code, count(*) AS n,
round(100.0 * count(*) / sum(count(*)) OVER (), 2) AS pct
FROM voters WHERE voter_status_desc = 'ACTIVE'
GROUP BY 1 ORDER BY 2 DESC;
-- Address-confidentiality registrants. Returned N 6751868, Y 308.
SELECT confidential_ind, count(*) FROM voters
WHERE voter_status_desc = 'ACTIVE' GROUP BY 1 ORDER BY 2 DESC;02-registration-trend.sql#
-- 02 - Party composition at each point-in-time registration snapshot.
-- Summed over all 100 counties, which is how a statewide figure is built:
-- the snapshot files carry no statewide row.
-- Last run 2026-08-08 against production.
--
-- Returned (ALL / UNA / DEM / REP):
-- 2022-11-08 6480268 / 2260119 / 2167559 / 2011664
-- 2024-03-05 6455746 / 2334233 / 2058346 / 2013803
-- 2024-11-05 6986364 / 2600078 / 2159553 / 2155034
--
-- These are ACTIVE-status registrants only, the same basis as the turnout
-- denominators in 07 and 11. It is NOT the "total registered" figure usually
-- quoted in press coverage, which also counts inactive registrants.
WITH totals AS (
SELECT snapshot_date, sum(active_count) AS all_n
FROM registration_snapshot_stats
WHERE district_type = 'COUNTY' AND party_cd = 'ALL'
GROUP BY 1
)
SELECT s.snapshot_date::date AS snap,
s.party_cd,
sum(s.active_count) AS n,
round(100.0 * sum(s.active_count) / t.all_n, 2) AS pct_of_active,
count(DISTINCT s.district_code) AS counties_contributing
FROM registration_snapshot_stats s
JOIN totals t ON t.snapshot_date = s.snapshot_date
WHERE s.district_type = 'COUNTY'
GROUP BY 1, 2, t.all_n
ORDER BY 1, 3 DESC;03-eligibility-validity.sql#
-- 03 - Can registr_dt serve as an eligibility date?
--
-- Voter-file turnout analysis routinely uses the registration date to decide
-- whether a voter could have voted in a prior election. In the NC file
-- registr_dt is the date of registration IN THE CURRENT COUNTY, so it is
-- rewritten when a voter moves across a county line. Anyone treating it as a
-- first-registration date will wrongly drop real prior voters from a cohort.
--
-- This query measures the size of that error directly: among currently-active
-- voters who demonstrably cast a 2022 general ballot, how many carry a
-- registr_dt LATER than that election?
--
-- Last run 2026-08-08. Returned:
-- active_with_2022_history 3498807
-- registr_dt_after_election 190917 (5.46%)
-- registr_dt_null 813
--
-- Consequence, applied in 04, 05 and 06: eligibility for election E is
-- registr_dt <= E OR a voter_history row for any election <= E.
SET statement_timeout = '20min';
WITH h AS (
SELECT state_voter_id FROM voter_history WHERE election_lbl = DATE '2022-11-08'
)
SELECT count(*) AS active_with_2022_history,
count(*) FILTER (WHERE v.registr_dt > DATE '2022-11-08') AS registr_dt_after_election,
count(*) FILTER (WHERE v.registr_dt IS NULL) AS registr_dt_null,
round(100.0 * count(*) FILTER (WHERE v.registr_dt > DATE '2022-11-08')
/ count(*), 2) AS pct_misdated
FROM voters v
JOIN h ON h.state_voter_id = v.state_voter_id
WHERE v.voter_status_desc = 'ACTIVE';04-midterm-dropoff.sql#
-- 04 - Presidential-to-midterm drop-off: 2024-11-05 against 2022-11-08.
--
-- Universe: currently-ACTIVE NC registrants who were eligible to vote in the
-- 2022 general, where eligibility is registr_dt <= 2022-11-08 OR the presence
-- of a vote-history row for any election at or before that date. See 03 for
-- why the second clause is required.
--
-- Both elections are read from voter_history by election_lbl. Neither the
-- `elections` table nor `district_turnout` is consulted; both are truncated.
--
-- Age bands are age at the 2026 general (2026 - birth_year), applied to
-- historical behavior. They describe today's cohorts, not each cohort's age
-- at the time it voted.
--
-- Last run 2026-08-08. Headline row (dim = 'TOTAL'):
-- cohort_n 5282640 | voted_2024 4818361 | voted_2022 3498807
-- voted_both 3367188 | pres_only 1451173
--
-- Runtime roughly 90 seconds on pro_16gb.
SET statement_timeout = '40min';
WITH hist AS (
SELECT state_voter_id,
bool_or(election_lbl <= DATE '2022-11-08') AS pre2022_any,
bool_or(election_lbl = DATE '2022-11-08') AS g2022,
bool_or(election_lbl = DATE '2024-11-05') AS g2024
FROM voter_history
WHERE election_lbl <= DATE '2024-11-05'
GROUP BY state_voter_id
),
cohort AS (
SELECT v.party_cd,
v.race_code,
CASE
WHEN v.birth_year IS NULL THEN 'unknown'
WHEN 2026 - v.birth_year < 25 THEN '18-24'
WHEN 2026 - v.birth_year < 35 THEN '25-34'
WHEN 2026 - v.birth_year < 45 THEN '35-44'
WHEN 2026 - v.birth_year < 55 THEN '45-54'
WHEN 2026 - v.birth_year < 65 THEN '55-64'
WHEN 2026 - v.birth_year < 75 THEN '65-74'
ELSE '75+'
END AS age_band,
COALESCE(h.g2022, false) AS g2022,
COALESCE(h.g2024, false) AS g2024
FROM voters v
LEFT JOIN hist h ON h.state_voter_id = v.state_voter_id
WHERE v.voter_status_desc = 'ACTIVE'
AND (
(v.registr_dt IS NOT NULL AND v.registr_dt <= DATE '2022-11-08')
OR COALESCE(h.pre2022_any, false)
)
),
rolled AS (
SELECT 'TOTAL' AS dim, '' AS val, count(*) AS cohort_n,
count(*) FILTER (WHERE g2024) AS voted_2024,
count(*) FILTER (WHERE g2022) AS voted_2022,
count(*) FILTER (WHERE g2024 AND g2022) AS voted_both,
count(*) FILTER (WHERE g2024 AND NOT g2022) AS pres_only
FROM cohort
UNION ALL
SELECT 'party', COALESCE(party_cd, 'UNK'), count(*),
count(*) FILTER (WHERE g2024), count(*) FILTER (WHERE g2022),
count(*) FILTER (WHERE g2024 AND g2022), count(*) FILTER (WHERE g2024 AND NOT g2022)
FROM cohort GROUP BY 2
UNION ALL
SELECT 'age', age_band, count(*),
count(*) FILTER (WHERE g2024), count(*) FILTER (WHERE g2022),
count(*) FILTER (WHERE g2024 AND g2022), count(*) FILTER (WHERE g2024 AND NOT g2022)
FROM cohort GROUP BY 2
UNION ALL
SELECT 'race', COALESCE(NULLIF(race_code, ''), 'blank'), count(*),
count(*) FILTER (WHERE g2024), count(*) FILTER (WHERE g2022),
count(*) FILTER (WHERE g2024 AND g2022), count(*) FILTER (WHERE g2024 AND NOT g2022)
FROM cohort GROUP BY 2
)
SELECT dim, val, cohort_n, voted_2024, voted_2022, voted_both, pres_only,
round(100.0 * voted_2022 / NULLIF(cohort_n, 0), 1) AS midterm_rate,
round(100.0 * voted_2024 / NULLIF(cohort_n, 0), 1) AS presidential_rate,
round(100.0 * pres_only / NULLIF(voted_2024, 0), 1) AS pct_of_pres_voters_who_skipped
FROM rolled ORDER BY dim, cohort_n DESC;05-dropoff-2018-2020.sql#
-- 05 - The same drop-off measurement one cycle earlier: 2020-11-03 against
-- 2018-11-06. Run as a replication of 04, not as a second headline.
--
-- The survivorship bias is materially worse here. The universe is voters
-- ACTIVE in the 2026 file, so everyone who has died, moved out of state, or
-- been removed since 2018 is absent. Those registrants were on average less
-- engaged, so this estimate of drop-off is biased DOWNWARD relative to 04.
-- Read the two as a bracket, not as a trend.
--
-- Last run 2026-08-08. Returned:
-- cohort_n 4142043 | voted_2020 3867395 | voted_2018 3016750
-- voted_both 2951120 | pres_only 916275
SET statement_timeout = '40min';
WITH hist AS (
SELECT state_voter_id,
bool_or(election_lbl <= DATE '2018-11-06') AS pre2018_any,
bool_or(election_lbl = DATE '2018-11-06') AS g2018,
bool_or(election_lbl = DATE '2020-11-03') AS g2020
FROM voter_history
WHERE election_lbl <= DATE '2020-11-03'
GROUP BY state_voter_id
),
cohort AS (
SELECT COALESCE(h.g2018, false) AS g2018, COALESCE(h.g2020, false) AS g2020
FROM voters v
LEFT JOIN hist h ON h.state_voter_id = v.state_voter_id
WHERE v.voter_status_desc = 'ACTIVE'
AND (
(v.registr_dt IS NOT NULL AND v.registr_dt <= DATE '2018-11-06')
OR COALESCE(h.pre2018_any, false)
)
)
SELECT count(*) AS cohort_n,
count(*) FILTER (WHERE g2020) AS voted_2020,
count(*) FILTER (WHERE g2018) AS voted_2018,
count(*) FILTER (WHERE g2018 AND g2020) AS voted_both,
count(*) FILTER (WHERE g2020 AND NOT g2018) AS pres_only,
round(100.0 * count(*) FILTER (WHERE g2020 AND NOT g2018)
/ NULLIF(count(*) FILTER (WHERE g2020), 0), 1) AS pct_of_pres_voters_who_skipped
FROM cohort;06-participation-index.sql#
-- 06 - Participation across the four most recent November general elections:
-- 2018-11-06, 2020-11-03, 2022-11-08, 2024-11-05.
--
-- This replaces voters.propensity_score, which is currently degraded (see 00).
-- Construction is stated here rather than referenced, so it can be replicated:
-- the index is a simple count of those four elections in which the voter has a
-- voter_history row. It is not weighted, not recency-adjusted, and makes no
-- distinction between voting methods.
--
-- Two universes are reported because the naive one is misleading:
-- all_active every ACTIVE registrant, including people who
-- registered after 2018 and could not have scored 4
-- eligible_all_four ACTIVE registrants eligible in 2018, the only universe
-- in which "0 of 4" and "4 of 4" mean the same thing
--
-- Last run 2026-08-08. Returned:
-- all_active 0:1014481 1:1074563 2:1064457 3:1157315 4:2441360
-- eligible_all_four 0:71115 1:191274 2:559961 3:878333 4:2441360
SET statement_timeout = '40min';
WITH hist AS (
SELECT state_voter_id,
bool_or(election_lbl <= DATE '2018-11-06') AS pre2018_any,
count(*) FILTER (WHERE election_lbl IN
(DATE '2018-11-06', DATE '2020-11-03', DATE '2022-11-08', DATE '2024-11-05')) AS gen4
FROM voter_history
WHERE election_lbl <= DATE '2024-11-05'
GROUP BY state_voter_id
),
active AS (
SELECT COALESCE(h.gen4, 0) AS gen4,
((v.registr_dt IS NOT NULL AND v.registr_dt <= DATE '2018-11-06')
OR COALESCE(h.pre2018_any, false)) AS elig2018
FROM voters v
LEFT JOIN hist h ON h.state_voter_id = v.state_voter_id
WHERE v.voter_status_desc = 'ACTIVE'
)
SELECT 'all_active' AS universe, gen4 AS generals_voted, count(*) AS n,
round(100.0 * count(*) / sum(count(*)) OVER (), 2) AS pct
FROM active GROUP BY 2
UNION ALL
SELECT 'eligible_all_four', gen4, count(*),
round(100.0 * count(*) / sum(count(*)) OVER (), 2)
FROM active WHERE elig2018 GROUP BY 2
ORDER BY 1, 2;07-county-turnout.sql#
-- 07 - Measured turnout for all 100 counties, midterm and presidential.
--
-- Numerator: ballots cast, counted from voter_history by county_id.
-- Denominator: ACTIVE registration on election day, from the NCSBE snapshot
-- for that exact date. Using today's registration as the denominator for a
-- 2022 rate would be wrong in both directions at once.
--
-- County is the only geography in this database whose boundaries are stable
-- across the 2022/2024 comparison. Legislative districts were redrawn between
-- the two elections and voter_history carries no district column, so a
-- district-level rate cannot be computed here and is not attempted.
--
-- Last run 2026-08-08. Statewide rollup:
-- reg_2022 6480268 | bal_2022 3789795 (58.5%)
-- reg_2024 6986364 | bal_2024 5722768 (81.9%)
-- County range of the 2022 rate: 44.9% (Robeson) to 72.9% (Alleghany),
-- median 59.8%.
SET statement_timeout = '30min';
WITH reg AS (
SELECT district_code AS county_id, snapshot_date::date AS snap, active_count
FROM registration_snapshot_stats
WHERE district_type = 'COUNTY' AND party_cd = 'ALL'
),
b22 AS (
SELECT county_id, count(*) AS ballots FROM voter_history
WHERE election_lbl = DATE '2022-11-08' GROUP BY 1
),
b24 AS (
SELECT county_id, count(*) AS ballots FROM voter_history
WHERE election_lbl = DATE '2024-11-05' GROUP BY 1
)
SELECT c.name AS county,
r22.active_count AS reg_2022, b22.ballots AS ballots_2022,
round(100.0 * b22.ballots / NULLIF(r22.active_count, 0), 1) AS midterm_rate_2022,
r24.active_count AS reg_2024, b24.ballots AS ballots_2024,
round(100.0 * b24.ballots / NULLIF(r24.active_count, 0), 1) AS presidential_rate_2024,
round(100.0 * b24.ballots / NULLIF(r24.active_count, 0)
- 100.0 * b22.ballots / NULLIF(r22.active_count, 0), 1) AS drop_points
FROM counties c
JOIN reg r22 ON r22.county_id = c.county_id AND r22.snap = DATE '2022-11-08'
JOIN reg r24 ON r24.county_id = c.county_id AND r24.snap = DATE '2024-11-05'
LEFT JOIN b22 ON b22.county_id = c.county_id
LEFT JOIN b24 ON b24.county_id = c.county_id
ORDER BY midterm_rate_2022 ASC;08-electorate-composition.sql#
-- 08 - Who actually voted, against who is registered.
--
-- 04 measures rates. This measures shares: what fraction of the ballots cast
-- came from each group. A candidate allocating a field program needs the
-- second, not the first.
--
-- Party and age are read from the CURRENT voter file for every election, so a
-- voter who changed party since 2022 is counted under today's registration.
-- The universe is voters ACTIVE today, so these are not full electorate counts
-- for any election; the shares are the reportable quantity, not the totals.
--
-- Last run 2026-08-08. Party shares:
-- 2022 midterm REP 34.75% | UNA 33.41% | DEM 31.49%
-- 2024 presidential UNA 35.87% | REP 33.10% | DEM 30.52%
-- registered now UNA 39.24% | REP 30.93% | DEM 29.24%
-- Age shares (band = age at the 2026 general):
-- 2022 midterm u35 10.29% | 35-49 19.61% | 50-64 28.81% | 65+ 41.29%
-- 2024 presidential u35 19.27% | 35-49 22.50% | 50-64 26.43% | 65+ 31.81%
-- registered now u35 25.71% | 35-49 22.28% | 50-64 24.09% | 65+ 27.92%
SET statement_timeout = '40min';
WITH v AS (
SELECT state_voter_id, COALESCE(party_cd, 'UNK') AS party,
CASE
WHEN birth_year IS NULL THEN 'unknown'
WHEN 2026 - birth_year < 35 THEN 'u35'
WHEN 2026 - birth_year < 50 THEN '35-49'
WHEN 2026 - birth_year < 65 THEN '50-64'
ELSE '65+'
END AS age_band
FROM voters WHERE voter_status_desc = 'ACTIVE'
),
h AS (
SELECT state_voter_id, election_lbl FROM voter_history
WHERE election_lbl IN (DATE '2022-11-08', DATE '2024-11-05', DATE '2026-03-03')
),
counted AS (
SELECT h.election_lbl::date AS election, v.party, v.age_band FROM h
JOIN v ON v.state_voter_id = h.state_voter_id
UNION ALL
-- Sentinel row set standing in for "currently registered", so registration
-- shares are computed on exactly the same basis as the voted shares.
SELECT DATE '9999-01-01', v.party, v.age_band FROM v
)
SELECT election, 'party' AS dim, party AS val, count(*) AS n,
round(100.0 * count(*) / sum(count(*)) OVER (PARTITION BY election), 2) AS pct
FROM counted GROUP BY 1, 3
UNION ALL
SELECT election, 'age', age_band, count(*),
round(100.0 * count(*) / sum(count(*)) OVER (PARTITION BY election), 2)
FROM counted GROUP BY 1, 3
ORDER BY 1, 2, 4 DESC;09-rolloff.sql#
-- 09 - Ballot roll-off in countywide races, 2022 general.
--
-- Roll-off is the gap between ballots cast in an election and votes cast in a
-- given contest on those ballots. It matters to a local candidate because a
-- win number computed from total ballots assumes no roll-off at all.
--
-- Restricted to single-seat races (contests.vote_for = 1). Multi-seat races
-- let a voter cast several votes or fewer than the maximum, so their vote
-- totals are not comparable to a ballot count.
--
-- Split by contested vs uncontested, counting distinct non-write-in choices.
-- Pooling the two produces a roll-off estimate that is mostly a measure of how
-- many races were unopposed.
--
-- USES contest_results, WHICH IS PARTIALLY LOADED. Only the 2022 general has
-- result rows, and for that election the coverage is complete: 171,900 rows
-- across all 100 counties. No other election can be measured this way until
-- the results ingest defect is fixed. See 00.
--
-- Last run 2026-08-08. Returned:
-- SHERIFF contested 45 races 1986306 / 2013026 = 98.7%
-- SHERIFF uncontested 55 races 1363781 / 1776769 = 76.8%
-- CLERK contested 19 races 368668 / 374846 = 98.4%
-- CLERK uncontested 81 races 2450515 / 3414949 = 71.8%
-- REGISTER_OF_DEEDS contested 1 race 17646 / 17766 = 99.3%
-- REGISTER_OF_DEEDS uncontested 23 races 519772 / 685706 = 75.8%
-- Top of ticket for scale: US SENATE drew 3,773,924 of 3,789,795 ballots (99.6%).
SET statement_timeout = '20min';
WITH ballots AS (
SELECT county_id, count(*) AS n FROM voter_history
WHERE election_lbl = DATE '2022-11-08' GROUP BY 1
),
cty AS (SELECT county_id, upper(name) AS uname FROM counties),
race AS (
SELECT c.id, upper(r.county_name) AS uname,
CASE
WHEN c.name LIKE '%SHERIFF%' THEN 'SHERIFF'
WHEN c.name LIKE '%CLERK OF SUPERIOR COURT%' THEN 'CLERK'
WHEN c.name LIKE '%REGISTER OF DEEDS%' THEN 'REGISTER_OF_DEEDS'
END AS office,
count(DISTINCT r.choice) FILTER (WHERE r.choice NOT ILIKE '%WRITE%') AS candidates,
sum(r.election_day + r.early_voting + r.absentee_by_mail + r.provisional) AS votes
FROM contests c JOIN contest_results r ON r.contest_id = c.id
WHERE c.election_id = (SELECT id FROM elections WHERE date = DATE '2022-11-08')
AND c.vote_for = 1
AND (c.name LIKE '%SHERIFF%' OR c.name LIKE '%CLERK OF SUPERIOR COURT%'
OR c.name LIKE '%REGISTER OF DEEDS%')
GROUP BY 1, 2, 3
)
SELECT race.office,
CASE WHEN race.candidates >= 2 THEN 'contested' ELSE 'uncontested' END AS status,
count(*) AS races,
sum(race.votes) AS office_votes,
sum(ballots.n) AS ballots_in_those_counties,
round(100.0 * sum(race.votes) / NULLIF(sum(ballots.n), 0), 1) AS pct_of_ballots
FROM race
JOIN cty ON cty.uname = race.uname
JOIN ballots ON ballots.county_id = cty.county_id
WHERE race.office IS NOT NULL
GROUP BY 1, 2 ORDER BY 1, 2;10-registration-timing.sql#
-- 10 - When people register, relative to the book-closing deadline.
--
-- N.C. Gen. Stat. 163-82.6(d) closes the books 25 days before an election.
-- N.C. Gen. Stat. 163-82.6B then allows same-day registration at an early
-- voting site, so a registration dated after the deadline is not necessarily
-- a registration that missed the election.
--
-- Deadlines used: 2024-10-11 for the 2024 general, 2022-10-14 for the 2022
-- general. Windows run back six months from election day.
--
-- Counts only voters ACTIVE in the current file, so every window undercounts
-- the registrations actually taken at the time. The SHAPE across windows is
-- the reportable quantity; the levels are lower bounds.
--
-- registr_dt is used here as written, and this is the one analysis in the set
-- where its instability (see 03) cannot be worked around: a voter who moved
-- counties after the election carries the later date and lands in the wrong
-- window or outside the range entirely. Treat as approximate.
--
-- Last run 2026-08-08. Returned, 2024:
-- after deadline (0-25d) 169815 registrants, 119058 voted
-- 26-55 days before 170794 registrants, 137828 voted
-- 56-92 days before 98636 registrants, 72624 voted
-- 93-184 days before 143965 registrants, 98008 voted
-- and 2022:
-- after deadline (0-25d) 50943 registrants, 25215 voted
-- 26-55 days before 42024 registrants, 28709 voted
-- 56-92 days before 35370 registrants, 21256 voted
-- 93-184 days before 68851 registrants, 40110 voted
SET statement_timeout = '30min';
WITH v AS (
SELECT state_voter_id, registr_dt FROM voters
WHERE voter_status_desc = 'ACTIVE' AND registr_dt IS NOT NULL
AND registr_dt > DATE '2024-05-05' AND registr_dt <= DATE '2024-11-05'
),
h AS (
SELECT state_voter_id FROM voter_history WHERE election_lbl = DATE '2024-11-05'
)
SELECT '2024-11-05' AS election,
CASE
WHEN v.registr_dt > DATE '2024-10-11' THEN 'a_after_deadline'
WHEN v.registr_dt > DATE '2024-09-11' THEN 'b_26_55d_before'
WHEN v.registr_dt > DATE '2024-08-05' THEN 'c_56_92d_before'
ELSE 'd_93_184d_before'
END AS window,
count(*) AS registrants,
count(h.state_voter_id) AS voted
FROM v LEFT JOIN h ON h.state_voter_id = v.state_voter_id
GROUP BY 1, 2
UNION ALL
SELECT '2022-11-08',
CASE
WHEN v.registr_dt > DATE '2022-10-14' THEN 'a_after_deadline'
WHEN v.registr_dt > DATE '2022-09-14' THEN 'b_26_55d_before'
WHEN v.registr_dt > DATE '2022-08-08' THEN 'c_56_92d_before'
ELSE 'd_93_184d_before'
END,
count(*), count(h.state_voter_id)
FROM (
SELECT state_voter_id, registr_dt FROM voters
WHERE voter_status_desc = 'ACTIVE' AND registr_dt IS NOT NULL
AND registr_dt > DATE '2022-05-08' AND registr_dt <= DATE '2022-11-08'
) v
LEFT JOIN (
SELECT state_voter_id FROM voter_history WHERE election_lbl = DATE '2022-11-08'
) h ON h.state_voter_id = v.state_voter_id
GROUP BY 1, 2
ORDER BY 1, 2;11-win-number.sql#
-- 11 - Win number for a countywide race, following the product's own method.
--
-- src/lib/win-number.ts computes:
-- projectedVotes = round(universe * measuredTurnoutRate)
-- winNumber = floor(projectedVotes / 2) + 1
-- where the rate is measured ballots over measured registration, never a
-- default constant, and the scenario band is the range actually observed
-- across comparable prior elections.
--
-- Only ONE comparable midterm can be measured here. A rate needs both a
-- ballot count and a registration denominator for the same date; NCSBE has
-- published a registration snapshot for 2022-11-08 but not for 2018-11-06.
-- With a single observed election the engine returns a single scenario and no
-- conservative or optimistic case, which is exactly what this query yields.
-- That absence is the honest output, not a gap to be filled with a guess.
--
-- The roll-off-adjusted column applies the pooled contested single-seat
-- countywide roll-off measured in 09 (2,372,620 votes over 2,405,638 ballots
-- = 98.63%). It is shown separately because the shipped engine does NOT apply
-- it; publishing it as if it did would misdescribe the product.
--
-- Last run 2026-08-08. Selected rows:
-- Wake 762492 active, 63.2% -> 482259 ballots -> win number 241130
-- Mecklenburg 700259 active, 52.8% -> 369958 ballots -> win number 184980
-- Alamance 105394 active, 60.6% -> 63917 ballots -> win number 31959
-- Lee 35766 active, 55.9% -> 20004 ballots -> win number 10003
-- Tyrrell 1925 active, 57.2% -> 1100 ballots -> win number 551
SET statement_timeout = '30min';
WITH reg22 AS (
SELECT district_code AS county_id, active_count FROM registration_snapshot_stats
WHERE district_type = 'COUNTY' AND party_cd = 'ALL' AND snapshot_date = DATE '2022-11-08'
),
bal22 AS (
SELECT county_id, count(*) AS n FROM voter_history
WHERE election_lbl = DATE '2022-11-08' GROUP BY 1
),
now_reg AS (
SELECT county_id, count(*) AS active_now FROM voters
WHERE voter_status_desc = 'ACTIVE' GROUP BY 1
),
j AS (
SELECT c.name, n.active_now,
1.0 * b.n / r.active_count AS rate22,
round(n.active_now * (1.0 * b.n / r.active_count)) AS projected_ballots
FROM counties c
JOIN reg22 r ON r.county_id = c.county_id
JOIN bal22 b ON b.county_id = c.county_id
JOIN now_reg n ON n.county_id = c.county_id
)
SELECT name AS county, active_now AS registered_active_now,
round(100 * rate22, 1) AS measured_midterm_rate,
projected_ballots::bigint AS projected_ballots,
floor(projected_ballots / 2)::bigint + 1 AS win_number,
floor(projected_ballots * 0.9863 / 2)::bigint + 1 AS win_number_rolloff_adjusted
FROM j ORDER BY active_now DESC;12-midterm-volume-sensitivity.sql#
-- 12 - Midterm-to-midterm ballot volume variation, used to bracket the
-- projections in 11.
--
-- Only one midterm turnout RATE can be measured (11 explains why: 2018 has no
-- registration snapshot). Ballot VOLUME can be measured for both, so the ratio
-- of 2018 to 2022 ballots per county is the one empirical statement this
-- database supports about how much a county's midterm participation moves
-- between cycles.
--
-- This is a sensitivity range, not a confidence interval. It is derived from a
-- single pair of consecutive midterms, and it absorbs registration change as
-- well as turnout-rate change, so it is wider than a pure rate band. Both 2018
-- and 2022 counts are subject to survivorship (voters removed since are not in
-- the file), and 2018 more so, which pushes the ratio down.
--
-- Last run 2026-08-08. Returned:
-- statewide 2018 3754027 | statewide 2022 3789795 (ratio 0.991)
-- per-county ratio: min 0.850 | p10 0.931 | median 0.994 | p90 1.096 | max 1.169
SET statement_timeout = '30min';
WITH b18 AS (
SELECT county_id, count(*) AS n FROM voter_history
WHERE election_lbl = DATE '2018-11-06' GROUP BY 1
),
b22 AS (
SELECT county_id, count(*) AS n FROM voter_history
WHERE election_lbl = DATE '2022-11-08' GROUP BY 1
),
r AS (
SELECT c.name, b18.n AS ballots_2018, b22.n AS ballots_2022,
1.0 * b18.n / b22.n AS ratio
FROM counties c
JOIN b18 ON b18.county_id = c.county_id
JOIN b22 ON b22.county_id = c.county_id
)
SELECT count(*) AS counties,
sum(ballots_2018) AS statewide_2018,
sum(ballots_2022) AS statewide_2022,
round(1.0 * sum(ballots_2018) / sum(ballots_2022), 3) AS statewide_ratio,
round(min(ratio), 3) AS ratio_min,
round(percentile_cont(0.10) WITHIN GROUP (ORDER BY ratio)::numeric, 3) AS ratio_p10,
round(percentile_cont(0.50) WITHIN GROUP (ORDER BY ratio)::numeric, 3) AS ratio_median,
round(percentile_cont(0.90) WITHIN GROUP (ORDER BY ratio)::numeric, 3) AS ratio_p90,
round(max(ratio), 3) AS ratio_max
FROM r;13-phone-coverage.sql#
-- 13 - Phone coverage on the loaded voter file, statewide, by party, by county.
--
-- Answers whether a phone-banking program has a dialable universe at all.
-- `full_phone_number` is whatever the county board published; the check is a
-- plain count(column), not a regex, so this is one sequential pass over
-- `voters` rather than eight. On the current 1 CPU / 4 GB instance the pass
-- takes roughly 90 seconds. Regex passes over this table do not finish.
--
-- Soft-deleted rows (`removed_at IS NOT NULL`) are excluded throughout. As of
-- the run below there are none, so the exclusion removes nothing; the filter
-- stays because that will not always be true.
--
-- Status matters more than the exclusion does. NCSBE blanks the phone on
-- REMOVED registrants, all 1,195,671 of them, so a statewide rate over the
-- whole file understates what a campaign can dial.
--
-- Last run 2026-08-09 against production, full table, no sampling. Returned:
-- all rows 9,203,238 | with phone 3,929,221 | 42.69%
-- ACTIVE 6,752,176 | with phone 3,323,015 | 49.21%
-- INACTIVE 1,064,995 | with phone 521,088 | 48.93%
-- DENIED 190,396 | with phone 85,118 | 44.71%
-- REMOVED 1,195,671 | with phone 0 | 0.00%
SET statement_timeout = '30min';
-- One pass. Every figure below is an aggregate of this result.
-- 10-digit is broken out because a 7-digit number cannot be dialed from a
-- list; it turned out to be 4,905 rows statewide, which is noise.
SELECT county_id,
coalesce(party_cd, '(null)') AS party_cd,
coalesce(voter_status_desc, '(null)') AS status,
count(*) AS voters,
count(full_phone_number) AS with_phone,
count(*) FILTER (
WHERE full_phone_number IS NOT NULL
AND length(btrim(full_phone_number)) = 10
) AS with_10_digit
FROM voters
WHERE removed_at IS NULL
GROUP BY 1, 2, 3;
-- Statewide and by party, ACTIVE only. Returned 2026-08-09:
-- REP 2,088,295 / 1,047,724 (50.17%) UNA 2,649,690 / 1,292,116 (48.76%)
-- DEM 1,974,401 / 964,413 (48.85%) LIB 35,400 / 16,635 (46.99%)
-- GRE 4,390 / 2,127 (48.45%)
-- Party spread is under two points. Party is not what decides coverage.
SELECT coalesce(party_cd, '(null)') AS party_cd,
count(*) AS active_voters,
count(full_phone_number) AS with_phone,
round(100.0 * count(full_phone_number) / count(*), 2) AS pct
FROM voters
WHERE removed_at IS NULL AND voter_status_desc = 'ACTIVE'
GROUP BY 1
ORDER BY 2 DESC;
-- By county, ACTIVE only. This is the result that matters: coverage is a
-- county-board publishing practice, not a statewide property.
-- Returned 2026-08-09, median county 70.99%, 13 counties under 25%:
-- Wake 762,492 active, 0 with phone (0.00%)
-- Iredell 132,148 active, 20 with phone (0.02%)
-- Camden 7,671 active, 7 with phone (0.09%)
-- Beaufort 30,553 active, 67 with phone (0.22%)
-- Orange 97,045 active, 706 with phone (0.73%)
-- Chowan 9,164 active, 8,153 with phone (88.97%)
-- Lenoir 33,471 active, 28,721 with phone (85.81%)
-- Watauga 37,618 active, 32,212 with phone (85.63%)
SELECT c.name AS county,
count(*) AS active_voters,
count(v.full_phone_number) AS with_phone,
round(100.0 * count(v.full_phone_number) / count(*), 2) AS pct
FROM voters v
JOIN counties c ON c.county_id = v.county_id
WHERE v.removed_at IS NULL AND v.voter_status_desc = 'ACTIVE'
GROUP BY 1
ORDER BY 4;
-- Confirms the zero counties are a publishing choice and not a load failure:
-- every other optional column on those rows is populated. Returned
-- 2026-08-09 for Wake: 762,492 ACTIVE, 0 phone, 586,973 birth_state,
-- 762,492 res_street_address, 762,492 ethnic_code.
SELECT c.name AS county,
v.voter_status_desc AS status,
count(*) AS n,
count(v.full_phone_number) AS phone,
count(v.birth_state) AS birth_state,
count(v.res_street_address) AS res_street_address,
count(v.ethnic_code) AS ethnic_code
FROM voters v
JOIN counties c ON c.county_id = v.county_id
WHERE c.name IN ('Wake', 'Iredell', 'Orange', 'Buncombe') AND v.removed_at IS NULL
GROUP BY 1, 2
ORDER BY 1, 2;14-address-sample.sql#
-- 14 - Exports a sample of real res_street_address values for the address
-- parser to be measured against.
--
-- The parse rate cannot be computed in SQL, so this query only draws the
-- sample; scripts/research/address-parse-rate.ts runs the parser over it and
-- reports the rates. Run this, then:
--
-- npx tsx scripts/research/address-parse-rate.ts /tmp/addr_sample.csv
--
-- BERNOULLI, not SYSTEM. SYSTEM samples whole 8 kB pages, and `voters` is
-- physically clustered by county, so a SYSTEM sample draws each county from a
-- handful of contiguous pages - one neighbourhood at a time. The per-county
-- rates it produced were visibly wrong, including several counties at exactly
-- 100.00%. BERNOULLI samples rows independently at the cost of a full scan,
-- which this table can afford for a single column.
--
-- 3% of 9,203,238 rows is roughly 276,000, which leaves every one of the 100
-- counties with at least 72 live addresses.
--
-- voter_status_desc travels with the sample because REMOVED registrants carry
-- the literal string 'REMOVED' in place of an address, and mixing them in
-- makes the parser look 13 points worse than it is.
--
-- Last run 2026-08-09 against production. Returned 275,673 rows, of which
-- 240,026 have a status other than REMOVED.
SET statement_timeout = '30min';
\copy (SELECT county_id, coalesce(voter_status_desc, '') AS status, coalesce(res_street_address, '') AS res_street_address FROM voters TABLESAMPLE BERNOULLI (3) REPEATABLE (20260809) WHERE removed_at IS NULL) TO '/tmp/addr_sample.csv' CSV HEADER15-precinct-codes.sql#
-- 15 - Exports every distinct (county_id, precinct_abbrv) in `voters`, with
-- the voters behind it, for matching against an NCSBE precinct boundary
-- vintage.
--
-- The comparison itself is not SQL: the boundary codes live in the .dbf of a
-- shapefile on the NCSBE S3 bucket, so nothing is loaded into the database.
-- scripts/research/precinct-join.ts reads the .dbf directly and matches both
-- ways. Run this, then:
--
-- curl -o /tmp/prec.zip \
-- https://s3.amazonaws.com/dl.ncsbe.gov/ShapeFiles/Precinct/SBE_PRECINCTS_20251212.zip
-- unzip -o /tmp/prec.zip -d /tmp/prec
-- npx tsx scripts/research/precinct-join.ts /tmp/prec/SBE_PRECINCTS_20251212.dbf /tmp/voter_precincts.csv
--
-- Grouping to distinct codes first is what keeps this cheap: 2,727 groups
-- cross the wire instead of 9.2 million rows, and one sequential pass builds
-- them.
--
-- ACTIVE is counted alongside the raw total because precinct_abbrv is null on
-- almost every REMOVED registrant, and an unmatched-voter count that is really
-- a count of deregistered people would overstate the problem by 3.5x.
--
-- Last run 2026-08-09 against production, full table, no sampling. Returned
-- 2,727 groups: 2,627 with a precinct code covering 6,751,827 ACTIVE voters,
-- and 100 null-code groups (one per county) covering 1,386,911 voters of whom
-- only 349 are ACTIVE.
SET statement_timeout = '30min';
\copy (SELECT county_id, coalesce(precinct_abbrv, '(null)') AS precinct_abbrv, count(*) AS voters, count(*) FILTER (WHERE voter_status_desc = 'ACTIVE') AS active FROM voters WHERE removed_at IS NULL GROUP BY 1, 2) TO '/tmp/voter_precincts.csv' CSV HEADER
-- Confirms the null precinct codes are deregistered records rather than a gap
-- in live data. Returned 2026-08-09:
-- ACTIVE 6,752,176 rows, 349 null precinct
-- REMOVED 1,195,671 rows, 1,195,660 null precinct
-- INACTIVE 1,064,995 rows, 527 null precinct
-- DENIED 190,396 rows, 190,375 null precinct
SELECT voter_status_desc AS status,
count(*) AS n,
count(*) FILTER (WHERE precinct_abbrv IS NULL) AS null_precinct
FROM voters
WHERE removed_at IS NULL
GROUP BY 1
ORDER BY 2 DESC;