SELECT * FROM ( SELECT LEFT(date, 7)+'-01' month, recipient, SUM(amount) amount, COUNT(DISTINCT contribid ) c, SUM(c) OVER(PARTITION BY recipient) c_tot, SUM(c) OVER(PARTITION BY month) m_tot, FROM [fh-bigquery:opensecrets.indivs16v2] a JOIN ( SELECT cid, FIRST(party) party FROM [fh-bigquery:opensecrets.cands16] WHERE distidrunfor = 'PRES' GROUP BY 1) b ON a.recipid=b.cid WHERE LEFT(realcode, 2)!='Z9' GROUP BY 1, 2 HAVING c>10 ) WHERE c_tot>8000 AND m_tot>5000 ORDER BY 1, 3 DESC
SELECT *, ROUND(a.amount/b.delegates,0) ratio_delegates, ROUND(a.amount/b.votes,0) ratio_votes FROM ( SELECT recipient, state, FIRST(REGEXP_EXTRACT(recipient, ', (.*)') + ' ' + REGEXP_EXTRACT(recipient, '(.*),')) candidate, SUM(amount) amount, SUM(IF(b.party='R',-1,1)*amount) amount_n, COUNT(DISTINCT contribid ) c, SUM(c) OVER(PARTITION BY recipient) c_tot, FROM [fh-bigquery:opensecrets.indivs16v2] a JOIN ( SELECT cid, FIRST(party) party FROM [fh-bigquery:opensecrets.cands16] WHERE distidrunfor = 'PRES' GROUP BY 1) b ON a.recipid=b.cid WHERE LEFT(realcode, 2)!='Z9' GROUP BY 1,2 HAVING c>10 ) a JOIN [fh-bigquery:opensecrets.primaries_results] b ON a.candidate=b.candidate AND a.state=b.state_abbrev
0 件のコメント :
コメントを投稿