This guide covers sophisticated techniques for detecting coordination, collusion, and unusual voting patterns in congressional data using the rich metadata now automatically extracted by our system.
Our system automatically extracts:
Query: Members who vote together ONLY on specific policy areas
-- Members who vote together on "Health" bills
SELECT m1.first, m1.last, m1.party, m1.state,
COUNT(DISTINCT v1.rollcall_id) as health_vote_agreement
FROM votes v1
JOIN votes v2 ON v1.rollcall_id = v2.rollcall_id
JOIN bills b ON v1.rollcall_id = b.bill_id
JOIN members m1 ON v1.member_id_bioguide = m1.member_id_bioguide
JOIN members m2 ON v2.member_id_bioguide = m2.member_id_bioguide
WHERE b.policy_area = 'Health'
AND v1.vote_code = v2.vote_code
AND m1.member_id_bioguide != m2.member_id_bioguide
GROUP BY m1.member_id_bioguide
HAVING health_vote_agreement > 10;What This Detects:
Pattern: Members who coordinate on one policy area but oppose each other on others
Query: Bills with specific subjects - unusual voting coalitions
-- Bills with "Budget deficits" subject - analyze voting patterns
SELECT b.bill_id, b.title, b.policy_area,
COUNT(DISTINCT v.member_id_bioguide) as total_voters,
COUNT(CASE WHEN v.vote_code = 'Yea' THEN 1 END) as yea_votes,
COUNT(CASE WHEN v.vote_code = 'Nay' THEN 1 END) as nay_votes
FROM bills b
JOIN bill_subjects bs ON b.bill_id = bs.bill_id
JOIN rollcalls r ON b.bill_id = r.bill_id
JOIN votes v ON r.rollcall_id = v.rollcall_id
WHERE bs.subject_term LIKE '%Budget deficit%'
GROUP BY b.bill_id;Key Subject Areas to Monitor:
Query: States that vote together on specific policy areas
-- States coordinating on policy areas
SELECT m1.state, m2.state, b.policy_area,
COUNT(DISTINCT v1.rollcall_id) as agreement_count
FROM votes v1
JOIN votes v2 ON v1.rollcall_id = v2.rollcall_id
JOIN members m1 ON v1.member_id_bioguide = m1.member_id_bioguide
JOIN members m2 ON v2.member_id_bioguide = m2.member_id_bioguide
JOIN bills b ON v1.rollcall_id = b.bill_id
WHERE v1.vote_code = v2.vote_code
AND m1.state != m2.state
GROUP BY m1.state, m2.state, b.policy_area
HAVING agreement_count > 20;Expected Regional Patterns:
Detection: Adjacent states voting identically on local issues
Query: Voting pattern changes near elections
-- Pre/post election voting pattern analysis
SELECT b.policy_area,
COUNT(CASE WHEN b.introduced_date < '2024-11-01' THEN 1 END) as pre_election,
COUNT(CASE WHEN b.introduced_date >= '2024-11-01' THEN 1 END) as post_election,
AVG(CASE WHEN b.introduced_date < '2024-11-01' THEN
(SELECT COUNT(*) FROM votes v JOIN rollcalls r ON v.rollcall_id = r.rollcall_id
WHERE r.bill_id = b.bill_id AND v.vote_code = 'Yea') END) as avg_pre_yea,
AVG(CASE WHEN b.introduced_date >= '2024-11-01' THEN
(SELECT COUNT(*) FROM votes v JOIN rollcalls r ON v.rollcall_id = r.rollcall_id
WHERE r.bill_id = b.bill_id AND v.vote_code = 'Yea') END) as avg_post_yea
FROM bills b
WHERE b.congress = 119
GROUP BY b.policy_area;Patterns to Detect:
Query: Members coordinating across multiple dimensions
-- Multi-dimensional coordination analysis
SELECT m1.first, m1.last, m1.party, m1.state,
COUNT(DISTINCT b.policy_area) as policy_areas_coordinated,
COUNT(DISTINCT bs.subject_term) as subjects_coordinated,
COUNT(DISTINCT m2.state) as states_coordinated_with
FROM votes v1
JOIN votes v2 ON v1.rollcall_id = v2.rollcall_id
JOIN members m1 ON v1.member_id_bioguide = m1.member_id_bioguide
JOIN members m2 ON v2.member_id_bioguide = m2.member_id_bioguide
JOIN bills b ON v1.rollcall_id = b.bill_id
JOIN bill_subjects bs ON b.bill_id = bs.bill_id
WHERE v1.vote_code = v2.vote_code
AND m1.member_id_bioguide != m2.member_id_bioguide
GROUP BY m1.member_id_bioguide
HAVING policy_areas_coordinated > 3
AND subjects_coordinated > 10
AND states_coordinated_with > 5;What This Reveals:
Key Industries to Monitor:
#### Finance and Financial Sector
-- Banking industry influence patterns
SELECT m.party, m.state,
COUNT(CASE WHEN v.vote_code = 'Yea' THEN 1 END) as yea_votes,
COUNT(CASE WHEN v.vote_code = 'Nay' THEN 1 END) as nay_votes
FROM votes v
JOIN members m ON v.member_id_bioguide = m.member_id_bioguide
JOIN bills b ON v.rollcall_id = b.bill_id
WHERE b.policy_area = 'Finance and Financial Sector'
GROUP BY m.party, m.state;#### Energy Sector
-- Oil/gas vs. renewable energy coordination
SELECT m.state, m.party,
COUNT(CASE WHEN v.vote_code = 'Yea' THEN 1 END) as yea_votes
FROM votes v
JOIN members m ON v.member_id_bioguide = m.member_id_bioguide
JOIN bills b ON v.rollcall_id = b.bill_id
JOIN bill_subjects bs ON b.bill_id = bs.bill_id
WHERE b.policy_area = 'Energy'
AND bs.subject_term LIKE '%Oil%'
GROUP BY m.state, m.party;Query: Bills with coordinated amendment patterns
-- Amendment coordination analysis
SELECT b.bill_id, b.title, b.policy_area,
COUNT(DISTINCT a.amendment_id) as amendment_count,
COUNT(DISTINCT a.sponsor_bioguide) as unique_sponsors
FROM bills b
JOIN amendments a ON b.bill_id = a.bill_id
WHERE b.congress = 119
GROUP BY b.bill_id
HAVING amendment_count > 5 AND unique_sponsors > 3;Patterns to Detect:
Query: Members who consistently vote late or abstain
-- Strategic voting timing analysis
SELECT m.first, m.last, m.party,
COUNT(CASE WHEN v.vote_code = 'Present' THEN 1 END) as present_votes,
COUNT(CASE WHEN v.vote_code = 'Not Voting' THEN 1 END) as abstentions,
COUNT(CASE WHEN v.vote_code IN ('Yea', 'Nay') THEN 1 END) as clear_votes
FROM votes v
JOIN members m ON v.member_id_bioguide = m.member_id_bioguide
GROUP BY m.member_id_bioguide
HAVING present_votes > 10 OR abstentions > 20;---
This guide covers the advanced voting pattern analysis capabilities enabled by our automated metadata extraction system. For implementation details, see the API Reference and Database Schema documentation.