5000 people on Brexit & US Elections

Hector Perez
Oct 3, 2016 · 3 min read
select w.name, a.extent from agreelist.agreements a
join agreelist.individuals i on i.id=a.individual_id
join(
SELECT JSON_EXTRACT_SCALAR(item, ‘$.id’) id,
JSON_EXTRACT_SCALAR(item, ‘$.sitelinks.enwiki.title’) name,
JSON_EXTRACT_SCALAR(item, ‘$.claims.P166[0].mainsnak.datavalue.value.numeric-id’) award1,
JSON_EXTRACT_SCALAR(item, ‘$.claims.P166[1].mainsnak.datavalue.value.numeric-id’) award2,
JSON_EXTRACT_SCALAR(item, ‘$.claims.P166[2].mainsnak.datavalue.value.numeric-id’) award3,
JSON_EXTRACT_SCALAR(item, ‘$.claims.P166[3].mainsnak.datavalue.value.numeric-id’) award4,
JSON_EXTRACT_SCALAR(item, ‘$.claims.P166[4].mainsnak.datavalue.value.numeric-id’) award5,
JSON_EXTRACT_SCALAR(item, ‘$.claims.P166[5].mainsnak.datavalue.value.numeric-id’) award6,
JSON_EXTRACT_SCALAR(item, ‘$.claims.P166[6].mainsnak.datavalue.value.numeric-id’) award7
FROM [fh-bigquery:wikidata.latest_en_v1]
) w ON i.wikidata_id=w.id
WHERE a.statement_id=7 AND (w.award1=”47170" OR w.award2=”47170" OR w.award3=”47170" OR w.award4=”47170" OR w.award5=”47170" OR w.award6=”47170" OR w.award7=”47170")
limit 100
select w.name, a.extent from agreelist.agreements a
join agreelist.individuals i on i.id=a.individual_id
join(
SELECT JSON_EXTRACT_SCALAR(item, ‘$.id’) id,
JSON_EXTRACT_SCALAR(item, ‘$.sitelinks.enwiki.title’) name,
JSON_EXTRACT_SCALAR(item, ‘$.claims.P108[0].mainsnak.datavalue.value.numeric-id’) award1,
JSON_EXTRACT_SCALAR(item, ‘$.claims.P108[1].mainsnak.datavalue.value.numeric-id’) award2,
JSON_EXTRACT_SCALAR(item, ‘$.claims.P108[2].mainsnak.datavalue.value.numeric-id’) award3,
JSON_EXTRACT_SCALAR(item, ‘$.claims.P108[3].mainsnak.datavalue.value.numeric-id’) award4,
JSON_EXTRACT_SCALAR(item, ‘$.claims.P108[4].mainsnak.datavalue.value.numeric-id’) award5,
JSON_EXTRACT_SCALAR(item, ‘$.claims.P108[5].mainsnak.datavalue.value.numeric-id’) award6,
JSON_EXTRACT_SCALAR(item, ‘$.claims.P108[6].mainsnak.datavalue.value.numeric-id’) award7
FROM [fh-bigquery:wikidata.latest_en_v1]
) w ON i.wikidata_id=w.id
WHERE a.statement_id=182 AND (w.award1=”49108" OR w.award2=”49108" OR w.award3=”49108" OR w.award4=”49108" OR w.award5=”49108" OR w.award6=”49108" OR w.award7=”49108")
limit 100

Thanks to Anu Chandra

Hector Perez

Written by

Execution is everything, but I love brainstorming too. Founded a political party and a startup. Ruby dev in London. Telecom engineer and paella lover.

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch
Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore
Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade