-
Notifications
You must be signed in to change notification settings - Fork 2
/
Copy pathcreate_fec.sql
238 lines (217 loc) · 7.59 KB
/
create_fec.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
drop table if exists fec_candidates;
create table fec_candidates (
cycle int,
candidate_id varchar(9),
candidate_name varchar(200),
party varchar(3),
election_year integer,
office_state varchar(2),
office varchar(1),
office_district varchar(2),
incumbent_challenger_open varchar(1),
candidate_status varchar(1),
committee_id varchar(9),
street1 varchar(34),
street2 varchar(34),
city varchar(30),
state varchar(2),
zipcode varchar(9)
);
drop table if exists fec_committees;
create table fec_committees (
cycle int,
committee_id varchar(9),
committee_name varchar(200),
treasurers_name varchar(90),
street1 varchar(34),
street2 varchar(34),
city varchar(30),
state varchar(2),
zipcode varchar(9),
committee_designation varchar(1),
committee_type varchar(1),
committee_party varchar(3),
filing_frequency varchar(1),
interest_group varchar(1),
connected_org varchar(200),
candidate_id varchar(9)
);
drop table if exists fec_indiv;
create table fec_indiv (
cycle int,
filer_id varchar(9),
amendment varchar(1),
report_type varchar(3),
election_type varchar(5),
microfilm_location varchar(11),
transaction_type varchar(3),
entity_type varchar(3),
contributor_name varchar(200),
city varchar(30),
state varchar(2),
zipcode varchar(9),
employer varchar(38),
occupation varchar(38),
date date,
amount numeric(14,2),
other_id varchar(9),
transaction_id varchar(32),
file_num varchar(22),
memo_code varchar(1),
memo_text varchar(100),
fec_record varchar(19)
);
drop table if exists fec_pac2cand;
create table fec_pac2cand (
cycle int,
filer_id varchar(9),
amendment varchar(1),
report_type varchar(3),
election_type varchar(5),
microfilm_location varchar(11),
transaction_type varchar(3),
entity_type varchar(3),
contributor_name varchar(200),
city varchar(30),
state varchar(2),
zipcode varchar(9),
employer varchar(38),
occupation varchar(38),
date date,
amount numeric(14,2),
other_id varchar(9),
candidate_id varchar(9),
transaction_id varchar(32),
file_num varchar(22),
memo_code varchar(1),
memo_text varchar(100),
fec_record varchar(19)
);
drop table if exists fec_pac2pac;
CREATE TABLE fec_pac2pac (
cycle int,
filer_id varchar(9),
amendment varchar(1),
report_type varchar(3),
election_type varchar(5),
microfilm_location varchar(11),
transaction_type varchar(3),
entity_type varchar(3),
contributor_name varchar(200),
city varchar(30),
state varchar(2),
zipcode varchar(9),
employer varchar(38),
occupation varchar(38),
date date,
amount numeric(14,2),
other_id varchar(9),
transaction_id varchar(32),
file_num varchar(22),
memo_code varchar(1),
memo_text varchar(100),
fec_record varchar(19)
);
drop table if exists fec_candidate_summaries;
CREATE TABLE fec_candidate_summaries (
cycle int,
candidate_id varchar(9),
candidate_name varchar(200),
incumbent_challenger_open varchar(1),
party varchar(1),
party_affiliation varchar(3),
total_receipts numeric(14,2), -- 22
authorized_transfers_from numeric(14,2), -- 18
total_disbursements numeric(14,2), -- 30
transfers_to_authorized numeric(14,2), -- 24
beginning_cash numeric(14,2), -- 6
ending_cash numeric(14,2), -- 10
contributions_from_candidate numeric(14,2), -- 17d
loans_from_candidate numeric(14,2), -- 19a
other_loans numeric(14,2), -- 19b
candidate_loan_repayments numeric(14,2), -- 27a
other_loan_repayments numeric(14,2), -- 27b
debts_owed_by numeric(14,2), -- 12
total_individual_contributions numeric(14,2), -- 17a
state varchar(2),
district varchar(2),
special_election_status varchar(1),
primary_election_status varchar(1),
runoff_election_status varchar(1),
general_election_status varchar(1),
general_election_ct numeric(7,4),
contributions_from_other_committees numeric(14,2), -- 17c
contributions_from_party_committees numeric(14,2), -- 17b
ending_date date,
refunds_to_individuals numeric(14,2), -- 28a
refunds_to_committees numeric(14,2) -- 28b & 28c?
);
drop table if exists fec_committee_summaries;
CREATE TABLE fec_committee_summaries (
cycle int,
committee_id varchar(9),
committee_name varchar(200),
committee_type varchar(1),
committee_designation varchar(1),
filing_frequency varchar(1),
total_receipts numeric(14,2),
transfers_from_affiliates numeric(14,2),
individual_contributions numeric(14,2),
contributions_from_other_committees numeric(14,2),
contributions_from_candidate numeric(14,2),
candidate_loans numeric(14,2),
total_loans_received numeric(14,2),
total_disbursements numeric(14,2),
transfers_to_affiliates numeric(14,2),
refunds_to_individuals numeric(14,2),
refunds_to_committees numeric(14,2),
candidate_loan_repayments numeric(14,2),
loan_repayments numeric(14,2),
cash_beginning_of_year numeric(14,2),
cash_close_of_period numeric(14,2),
debts_owed numeric(14,2),
nonfederal_transfers_received numeric(14,2),
contributions_to_committees numeric(14,2),
independent_expenditures_made numeric(14,2),
party_coordinated_expenditures_made numeric(14,2),
nonfederal_expenditure_share numeric(14,2),
through_date date
);
drop view if exists fec_candidate_itemized;
create view fec_candidate_itemized as
select
contributor_name, date, amount, contributor_type, transaction_type,
employer, occupation, i.city, i.state, i.zipcode,
candidate_name, party, office, office_state, office_district, incumbent_challenger_open as status, committee_id, candidate_id
from fec_candidates c
inner join (
select filer_id as committee_id, 'indiv' as contributor_type, contributor_name,
i.city, i.state, i.zipcode, employer, occupation,
date, amount, transaction_type
from fec_indiv i
union all
select other_id, 'pac', committee_name,
t.city, t.state, t.zipcode, connected_org, '',
date, amount, transaction_type
from fec_pac2cand t
inner join fec_committees c on (c.committee_id = t.filer_id)) i using (committee_id);
drop view if exists fec_committee_itemized;
create view fec_committee_itemized as
select
contributor_name, date, amount, contributor_type, contributor_committee_id, transaction_type,
employer, occupation, i.city, i.state, i.zipcode,
committee_name, committee_id, committee_designation, committee_type, committee_party, interest_group, connected_org, candidate_id
from fec_committees c
inner join (
select filer_id as committee_id, 'indiv' as contributor_type, contributor_name, '' as contributor_committee_id,
city, state, zipcode, employer, occupation,
date, amount, transaction_type
from fec_indiv
union all
select filer_id, 'pac', contributor_name, other_id,
city, state, zipcode, '', occupation,
date, amount, transaction_type
from fec_pac2pac) i using (committee_id)
where
-- only transaction types 10-19 are money coming in. 20-29 are money going out, which we're not interested in here.
substring(transaction_type for 2)::integer between 10 and 19;