Data Reporting

MPJO-722-01: Data Reporting

Database Skills – Summarizing Data; More Campaign Finance Data

Links

• FEC Data Catalog
• SQL Wildcards
• SQL Group By
• Angle, McMahon led way spending $97 per vote - and lost
• 20 consulting firms churn 80 percent of super PAC cash

Assignments for Feb. 19

SQL: Complete Part 3 of the SQLite tutorial. It’s long; start early.

SQL: Using your existing fec.sqlite file, download this CSV file of expenditures by the Senate campaign of Josh Mandel. Import it into the fec database, creating a table called mandel. Look at the CSV file in Excel before you define the fields in SQLite – be sure to define the zip field as VARCHAR, not INTEGER. Define the amount, month, day and year fields as INTEGER.

Once you’ve done that, write queries to do the following, using wildcards (but not always) and GROUP BY:

1. Show the total amount of money spent in each state.
2. Show the total amount for each purpose, with the largest amount first.
3. Show the total amount of any expenditures related to direct mail.
4. Show the total amount spent for each month and year, with the largest amount first
5. Show the recipients and total amounts for Payroll expenses, but not payroll taxes or fees.

Email me both the fec.sqlite file as an attachment (don't export the data) and the SQL from your queries (I can check the results) by 5:20 p.m. on Feb. 19.