Skip to content

Instantly share code, notes, and snippets.

@ThisIsJohnBrown

ThisIsJohnBrown/data.json

Created September 7, 2016 20:11
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save ThisIsJohnBrown/2bf4fcd4fe88afdf095755435d85e149 to your computer and use it in GitHub Desktop.
Save ThisIsJohnBrown/2bf4fcd4fe88afdf095755435d85e149 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
data.json
{
"questions": [
{
"top10": [
{
"label": "Benin",
"number": 9.946524064171124
},
{
"label": "Liechtenstein",
"number": 6.77052127022169
},
{
"label": "Niger",
"number": 5.215047788397421
},
{
"label": "Djibouti",
"number": 5.116416150898909
},
{
"label": "Brunei",
"number": 5.067594592432606
},
{
"label": "Iran",
"number": 4.6967974965359405
},
{
"label": "Cameroon",
"number": 4.685465350285295
},
{
"label": "Aruba",
"number": 4.253112033195021
},
{
"label": "Germany",
"number": 4.132632219656246
},
{
"label": "Russia",
"number": 4.025043069527008
}
],
"answer": "Benin",
"answerValue": 9.946524064171124,
"question": "Which country was the most cooperative last year?",
"rowIndex": 2,
"approved": true,
"params": {
"codes": "('031', '030')",
"relation": "Actor1CountryCode",
"year": 2015,
"per": 100,
"min_events": 1000
},
"sql": "\nSELECT\n codes.country,\n SUM(CASE WHEN events.EventCode IN ('031', '030') THEN 1 ELSE 0 END) * 100 / COUNT(*) as per,\n COUNT(*) as total\nFROM\n [gdelt-bq:full.events] events\nJOIN\n [gdelt-bq:extra.countryinfo] codes\nON\n events.Actor1CountryCode = codes.iso3\nWHERE\n Year = 2015\nGROUP BY\n codes.country\nHAVING\n total > 1000\nORDER BY\n per DESC\nIGNORE CASE\n",
"inputSQL": "\nSELECT\n codes.country,\n SUM(CASE WHEN events.EventCode IN ('031', '030') THEN 1 ELSE 0 END) * 100 / COUNT(*) as per,\n COUNT(*) as total\nFROM\n [gdelt-bq:full.events] events\nJOIN\n [gdelt-bq:extra.countryinfo] codes\nON\n events.Actor1CountryCode = codes.iso3\nJOIN (\n SELECT\n *\n FROM (\n SELECT\n sub,\n obj AS alias,\n CASE WHEN obj = 'userInput' THEN 1 ELSE 0 END AS input,\n SUM(input) OVER (PARTITION BY sub) AS input_alias\n FROM\n [fh-bigquery:freebase20140119.triples_lang_en]\n WHERE\n (pred = '/common/topic/alias'\n OR pred = '/type/object/name'))\n WHERE\n input_alias > 0) aliases\nON\n aliases.alias = codes.country\nWHERE\n Year = 2015\nGROUP BY\n codes.country\nHAVING\n total > 1000\nORDER BY\n per DESC\nIGNORE CASE\n",
"databases": "GDELT",
"units": "% of Events",
"hasPassingInputQuery": true,
"rowsSearched": 380821133,
"warnings": {}
},
{
"top10": [
{
"label": "Benin",
"number": 3.1372549019607843
},
{
"label": "Armenia",
"number": 2.715272972523189
},
{
"label": "Niger",
"number": 1.9726605912424984
},
{
"label": "Cameroon",
"number": 1.7520183725169876
},
{
"label": "Chad",
"number": 1.6296712559707782
},
{
"label": "Iceland",
"number": 0.9528346831824679
},
{
"label": "Belarus",
"number": 0.805189675341446
},
{
"label": "Kazakhstan",
"number": 0.6832371304040523
},
{
"label": "Tajikistan",
"number": 0.662361318099023
},
{
"label": "Kyrgyzstan",
"number": 0.5552813425468904
}
],
"answer": "Benin",
"answerValue": 3.1372549019607843,
"question": "Which country had the most cooperative military last year?",
"rowIndex": 14,
"approved": true,
"params": {
"codes": "('0312')",
"relation": "Actor1CountryCode",
"year": 2015,
"per": 1000,
"min_events": 1
},
"sql": "\nSELECT\n codes.country,\n SUM(CASE WHEN events.EventCode IN ('0312') THEN 1 ELSE 0 END) * 1000 / COUNT(*) as per,\n COUNT(*) as total\nFROM\n [gdelt-bq:full.events] events\nJOIN\n [gdelt-bq:extra.countryinfo] codes\nON\n events.Actor1CountryCode = codes.iso3\nWHERE\n Year = 2015\nGROUP BY\n codes.country\nHAVING\n total > 1\nORDER BY\n per DESC\nIGNORE CASE\n",
"inputSQL": "\nSELECT\n codes.country,\n SUM(CASE WHEN events.EventCode IN ('0312') THEN 1 ELSE 0 END) * 1000 / COUNT(*) as per,\n COUNT(*) as total\nFROM\n [gdelt-bq:full.events] events\nJOIN\n [gdelt-bq:extra.countryinfo] codes\nON\n events.Actor1CountryCode = codes.iso3\nJOIN (\n SELECT\n *\n FROM (\n SELECT\n sub,\n obj AS alias,\n CASE WHEN obj = 'userInput' THEN 1 ELSE 0 END AS input,\n SUM(input) OVER (PARTITION BY sub) AS input_alias\n FROM\n [fh-bigquery:freebase20140119.triples_lang_en]\n WHERE\n (pred = '/common/topic/alias'\n OR pred = '/type/object/name'))\n WHERE\n input_alias > 0) aliases\nON\n aliases.alias = codes.country\nWHERE\n Year = 2015\nGROUP BY\n codes.country\nHAVING\n total > 1\nORDER BY\n per DESC\nIGNORE CASE\n",
"databases": "GDELT",
"units": "Events per 1,000",
"hasPassingInputQuery": true,
"rowsSearched": 380579334,
"warnings": {}
},
{
"top10": [
{
"label": "Ivory Coast",
"number": 4.228673660242335
},
{
"label": "Guatemala",
"number": 3.5446125700346838
},
{
"label": "Guinea",
"number": 3.1391014322150284
},
{
"label": "Burkina Faso",
"number": 1.8883415435139572
},
{
"label": "Burundi",
"number": 1.857552276828362
},
{
"label": "Togo",
"number": 1.6151361329026304
},
{
"label": "Moldova",
"number": 1.5679323743949827
},
{
"label": "Honduras",
"number": 1.4513788098693758
},
{
"label": "Senegal",
"number": 1.2675036214389184
},
{
"label": "Antigua and Barbuda",
"number": 1.2619433928249504
}
],
"answer": "Ivory Coast",
"answerValue": 4.228673660242335,
"question": "Which country called for leadership change the most last year?",
"rowIndex": 8,
"approved": true,
"params": {
"codes": "('0241')",
"relation": "Actor1CountryCode",
"year": 2015,
"per": 1000,
"min_events": 1
},
"sql": "\nSELECT\n codes.country,\n SUM(CASE WHEN events.EventCode IN ('0241') THEN 1 ELSE 0 END) * 1000 / COUNT(*) as per,\n COUNT(*) as total\nFROM\n [gdelt-bq:full.events] events\nJOIN\n [gdelt-bq:extra.countryinfo] codes\nON\n events.Actor1CountryCode = codes.iso3\nWHERE\n Year = 2015\nGROUP BY\n codes.country\nHAVING\n total > 1\nORDER BY\n per DESC\nIGNORE CASE\n",
"inputSQL": "\nSELECT\n codes.country,\n SUM(CASE WHEN events.EventCode IN ('0241') THEN 1 ELSE 0 END) * 1000 / COUNT(*) as per,\n COUNT(*) as total\nFROM\n [gdelt-bq:full.events] events\nJOIN\n [gdelt-bq:extra.countryinfo] codes\nON\n events.Actor1CountryCode = codes.iso3\nJOIN (\n SELECT\n *\n FROM (\n SELECT\n sub,\n obj AS alias,\n CASE WHEN obj = 'userInput' THEN 1 ELSE 0 END AS input,\n SUM(input) OVER (PARTITION BY sub) AS input_alias\n FROM\n [fh-bigquery:freebase20140119.triples_lang_en]\n WHERE\n (pred = '/common/topic/alias'\n OR pred = '/type/object/name'))\n WHERE\n input_alias > 0) aliases\nON\n aliases.alias = codes.country\nWHERE\n Year = 2015\nGROUP BY\n codes.country\nHAVING\n total > 1\nORDER BY\n per DESC\nIGNORE CASE\n",
"databases": "GDELT",
"units": "Events per 1,000",
"hasPassingInputQuery": true,
"rowsSearched": 380579334,
"warnings": {}
},
{
"top10": [
{
"label": "Brazil",
"number": 1.5230109029662877
},
{
"label": "Grenada",
"number": 1.036699149906697
},
{
"label": "Switzerland",
"number": 1.0303592317156693
},
{
"label": "Bangladesh",
"number": 0.9906644444703442
},
{
"label": "Zimbabwe",
"number": 0.6752080484799379
},
{
"label": "Lithuania",
"number": 0.669882100750268
},
{
"label": "Chile",
"number": 0.5642279480910288
},
{
"label": "Chad",
"number": 0.561955605507165
},
{
"label": "Rwanda",
"number": 0.4202828503582911
},
{
"label": "Nepal",
"number": 0.3592760587416356
}
],
"answer": "Brazil",
"answerValue": 1.5230109029662877,
"question": "Which country willingly shared the most intelligence last year?",
"rowIndex": 16,
"approved": true,
"params": {
"codes": "('0314')",
"relation": "Actor1CountryCode",
"year": 2015,
"per": 10000,
"min_events": 1
},
"sql": "\nSELECT\n codes.country,\n SUM(CASE WHEN events.EventCode IN ('0314') THEN 1 ELSE 0 END) * 10000 / COUNT(*) as per,\n COUNT(*) as total\nFROM\n [gdelt-bq:full.events] events\nJOIN\n [gdelt-bq:extra.countryinfo] codes\nON\n events.Actor1CountryCode = codes.iso3\nWHERE\n Year = 2015\nGROUP BY\n codes.country\nHAVING\n total > 1\nORDER BY\n per DESC\nIGNORE CASE\n",
"inputSQL": "\nSELECT\n codes.country,\n SUM(CASE WHEN events.EventCode IN ('0314') THEN 1 ELSE 0 END) * 10000 / COUNT(*) as per,\n COUNT(*) as total\nFROM\n [gdelt-bq:full.events] events\nJOIN\n [gdelt-bq:extra.countryinfo] codes\nON\n events.Actor1CountryCode = codes.iso3\nJOIN (\n SELECT\n *\n FROM (\n SELECT\n sub,\n obj AS alias,\n CASE WHEN obj = 'userInput' THEN 1 ELSE 0 END AS input,\n SUM(input) OVER (PARTITION BY sub) AS input_alias\n FROM\n [fh-bigquery:freebase20140119.triples_lang_en]\n WHERE\n (pred = '/common/topic/alias'\n OR pred = '/type/object/name'))\n WHERE\n input_alias > 0) aliases\nON\n aliases.alias = codes.country\nWHERE\n Year = 2015\nGROUP BY\n codes.country\nHAVING\n total > 1\nORDER BY\n per DESC\nIGNORE CASE\n",
"databases": "GDELT",
"units": "Events per 10,000",
"hasPassingInputQuery": true,
"rowsSearched": 380579334,
"warnings": {}
},
{
"top10": [
{
"label": "China",
"number": 23087
},
{
"label": "Russia",
"number": 12343
},
{
"label": "Iran",
"number": 9079
},
{
"label": "Australia",
"number": 8981
},
{
"label": "United Kingdom",
"number": 8428
},
{
"label": "Canada",
"number": 8078
},
{
"label": "Japan",
"number": 7759
},
{
"label": "Vietnam",
"number": 5573
},
{
"label": "Turkey",
"number": 5503
},
{
"label": "Germany",
"number": 4727
}
],
"answer": "China",
"answerValue": 23087,
"question": "Which country, besides the United States, provided the most economic support to other countries last year?",
"rowIndex": 17,
"approved": true,
"params": {
"codes": "('061')",
"relation": "Actor1CountryCode",
"year": 2015,
"per": 100,
"filter_us": true
},
"sql": "\nSELECT\n codes.country,\n SUM(CASE WHEN events.EventCode IN ('061') THEN 1 ELSE 0 END) total\nFROM\n [gdelt-bq:full.events] events\nJOIN\n [gdelt-bq:extra.countryinfo] codes\nON\n events.Actor1CountryCode = codes.iso3\nWHERE\n Year = 2015 \n AND codes.country != 'United States'\nGROUP BY\n codes.country\nORDER BY\n total DESC\nIGNORE CASE\n",
"inputSQL": "\nSELECT\n 'userInput',\n COUNT(*)\nFROM (\n SELECT\n events.GLOBALEVENTID,\n FROM\n [gdelt-bq:full.events] events\n JOIN\n [gdelt-bq:extra.countryinfo] codes\n ON\n events.Actor1CountryCode = codes.iso3\n JOIN (\n SELECT\n *\n FROM (\n SELECT\n sub,\n obj AS alias,\n CASE WHEN obj = 'userInput' THEN 1 ELSE 0 END AS input,\n SUM(input) OVER (PARTITION BY sub) AS input_alias\n FROM\n [fh-bigquery:freebase20140119.triples_lang_en]\n WHERE\n (pred = '/common/topic/alias'\n OR pred = '/type/object/name'))\n WHERE\n input_alias > 0) aliases\n ON\n aliases.alias = codes.country\n WHERE\n Year = 2015\n AND events.EventCode IN ('061') \n AND codes.country != 'United States'\n GROUP BY\n events.GLOBALEVENTID)\n IGNORE CASE\n",
"databases": "GDELT",
"units": "Events",
"hasPassingInputQuery": true,
"rowsSearched": 380579150,
"warnings": {}
},
{
"top10": [
{
"label": "United Kingdom",
"number": 148231
},
{
"label": "China",
"number": 129654
},
{
"label": "Iran",
"number": 113828
},
{
"label": "Russia",
"number": 110351
},
{
"label": "Canada",
"number": 105030
},
{
"label": "France",
"number": 104969
},
{
"label": "Israel",
"number": 86381
},
{
"label": "Cuba",
"number": 77636
},
{
"label": "Mexico",
"number": 75061
},
{
"label": "Iraq",
"number": 65497
}
],
"answer": "United Kingdom",
"answerValue": 148231,
"question": "Which country was mentioned the most with the United States last year?",
"rowIndex": 20,
"approved": true,
"params": {
"country": "United States",
"year": 2015
},
"sql": "\nSELECT\n other.country,\n COUNT(*) AS total\nFROM (\n SELECT\n ccode,\n other\n FROM (\n SELECT\n Actor1Code AS ccode,\n Actor2Code AS other,\n Year\n FROM\n [gdelt-bq:full.events]) cc1,\n (\n SELECT\n Actor1Code AS other,\n Actor2Code AS ccode,\n Year\n FROM\n [gdelt-bq:full.events]) cc2\n WHERE\n Year = 2015) events\nJOIN\n [gdelt-bq:extra.countryinfo] country\nON\n country.iso3 = events.ccode\nJOIN\n [gdelt-bq:extra.countryinfo] other\nON\n events.other = other.iso3\nWHERE\n country.country = 'United States' \nGROUP BY\n country.country,\n other.country\nORDER BY\n total DESC\nIGNORE CASE\n",
"inputSQL": "\nSELECT\n other.country,\n COUNT(UNIQUE(events.id)) AS total\nFROM (\n SELECT\n ccode,\n other,\n id\n FROM (\n SELECT\n Actor1Code AS ccode,\n Actor2Code AS other,\n Year,\n GLOBALEVENTID as id\n FROM\n [gdelt-bq:full.events]) cc1,\n (\n SELECT\n Actor1Code AS other,\n Actor2Code AS ccode,\n Year,\n GLOBALEVENTID as id\n FROM\n [gdelt-bq:full.events]) cc2\n WHERE\n Year = 2015) events\nJOIN\n [gdelt-bq:extra.countryinfo] country\nON\n country.iso3 = events.ccode\nJOIN\n [gdelt-bq:extra.countryinfo] other\nON\n events.other = other.iso3\nJOIN (\n SELECT\n *\n FROM (\n SELECT\n sub,\n obj AS alias,\n CASE WHEN obj = 'userInput' THEN 1 ELSE 0 END AS input,\n SUM(input) OVER (PARTITION BY sub) AS input_alias\n FROM\n [fh-bigquery:freebase20140119.triples_lang_en]\n WHERE\n (pred = '/common/topic/alias'\n OR pred = '/type/object/name'))\n WHERE\n input_alias > 0) aliases\nON\n aliases.alias = other.country\nWHERE\n country.country = 'United States' \nGROUP BY\n country.country,\n other.country\nORDER BY\n total DESC\nIGNORE CASE\n",
"databases": "GDELT",
"units": "Events",
"hasPassingInputQuery": true,
"rowsSearched": 761343835,
"warnings": {}
},
{
"top10": [
{
"label": "Haiti",
"number": 5.0675675675675675
},
{
"label": "Macao",
"number": 4.459308807134894
},
{
"label": "Nepal",
"number": 3.646651996227601
},
{
"label": "Saint Vincent and the Grenadines",
"number": 3.5655512890839276
},
{
"label": "Malawi",
"number": 2.5917199596095593
},
{
"label": "Papua New Guinea",
"number": 2.332814930015552
},
{
"label": "Kiribati",
"number": 2.0796197266785503
},
{
"label": "Tanzania",
"number": 2.065867062457379
},
{
"label": "Nicaragua",
"number": 2.061105722599418
},
{
"label": "United States",
"number": 1.877092553721561
}
],
"answer": "Haiti",
"answerValue": 5.0675675675675675,
"question": "Which country appealed the most for economic aid last year?",
"rowIndex": 9,
"approved": true,
"params": {
"codes": "('0231')",
"relation": "Actor1CountryCode",
"year": 2015,
"per": 1000,
"min_events": 1
},
"sql": "\nSELECT\n codes.country,\n SUM(CASE WHEN events.EventCode IN ('0231') THEN 1 ELSE 0 END) * 1000 / COUNT(*) as per,\n COUNT(*) as total\nFROM\n [gdelt-bq:full.events] events\nJOIN\n [gdelt-bq:extra.countryinfo] codes\nON\n events.Actor1CountryCode = codes.iso3\nWHERE\n Year = 2015\nGROUP BY\n codes.country\nHAVING\n total > 1\nORDER BY\n per DESC\nIGNORE CASE\n",
"inputSQL": "\nSELECT\n codes.country,\n SUM(CASE WHEN events.EventCode IN ('0231') THEN 1 ELSE 0 END) * 1000 / COUNT(*) as per,\n COUNT(*) as total\nFROM\n [gdelt-bq:full.events] events\nJOIN\n [gdelt-bq:extra.countryinfo] codes\nON\n events.Actor1CountryCode = codes.iso3\nJOIN (\n SELECT\n *\n FROM (\n SELECT\n sub,\n obj AS alias,\n CASE WHEN obj = 'userInput' THEN 1 ELSE 0 END AS input,\n SUM(input) OVER (PARTITION BY sub) AS input_alias\n FROM\n [fh-bigquery:freebase20140119.triples_lang_en]\n WHERE\n (pred = '/common/topic/alias'\n OR pred = '/type/object/name'))\n WHERE\n input_alias > 0) aliases\nON\n aliases.alias = codes.country\nWHERE\n Year = 2015\nGROUP BY\n codes.country\nHAVING\n total > 1\nORDER BY\n per DESC\nIGNORE CASE\n",
"databases": "GDELT",
"units": "Events per 1,000",
"hasPassingInputQuery": true,
"rowsSearched": 380579334,
"warnings": {}
},
{
"top10": [
{
"label": "Pakistan",
"number": 24881
},
{
"label": "United States",
"number": 10570
},
{
"label": "China",
"number": 7851
},
{
"label": "Bangladesh",
"number": 6869
},
{
"label": "United Kingdom",
"number": 6552
},
{
"label": "Nepal",
"number": 4762
},
{
"label": "Sri Lanka",
"number": 3572
},
{
"label": "Japan",
"number": 3018
},
{
"label": "France",
"number": 2876
},
{
"label": "Senegal",
"number": 2366
}
],
"answer": "Pakistan",
"answerValue": 24881,
"question": "Which country was mentioned the most in the news with India last year?",
"rowIndex": 34,
"approved": true,
"params": {
"country": "India",
"year": 2015
},
"sql": "\nSELECT\n other.country,\n COUNT(*) AS total\nFROM (\n SELECT\n ccode,\n other\n FROM (\n SELECT\n Actor1Code AS ccode,\n Actor2Code AS other,\n Year\n FROM\n [gdelt-bq:full.events]) cc1,\n (\n SELECT\n Actor1Code AS other,\n Actor2Code AS ccode,\n Year\n FROM\n [gdelt-bq:full.events]) cc2\n WHERE\n Year = 2015) events\nJOIN\n [gdelt-bq:extra.countryinfo] country\nON\n country.iso3 = events.ccode\nJOIN\n [gdelt-bq:extra.countryinfo] other\nON\n events.other = other.iso3\nWHERE\n country.country = 'India' \nGROUP BY\n country.country,\n other.country\nORDER BY\n total DESC\nIGNORE CASE\n",
"inputSQL": "\nSELECT\n other.country,\n COUNT(UNIQUE(events.id)) AS total\nFROM (\n SELECT\n ccode,\n other,\n id\n FROM (\n SELECT\n Actor1Code AS ccode,\n Actor2Code AS other,\n Year,\n GLOBALEVENTID as id\n FROM\n [gdelt-bq:full.events]) cc1,\n (\n SELECT\n Actor1Code AS other,\n Actor2Code AS ccode,\n Year,\n GLOBALEVENTID as id\n FROM\n [gdelt-bq:full.events]) cc2\n WHERE\n Year = 2015) events\nJOIN\n [gdelt-bq:extra.countryinfo] country\nON\n country.iso3 = events.ccode\nJOIN\n [gdelt-bq:extra.countryinfo] other\nON\n events.other = other.iso3\nJOIN (\n SELECT\n *\n FROM (\n SELECT\n sub,\n obj AS alias,\n CASE WHEN obj = 'userInput' THEN 1 ELSE 0 END AS input,\n SUM(input) OVER (PARTITION BY sub) AS input_alias\n FROM\n [fh-bigquery:freebase20140119.triples_lang_en]\n WHERE\n (pred = '/common/topic/alias'\n OR pred = '/type/object/name'))\n WHERE\n input_alias > 0) aliases\nON\n aliases.alias = other.country\nWHERE\n country.country = 'India' \nGROUP BY\n country.country,\n other.country\nORDER BY\n total DESC\nIGNORE CASE\n",
"databases": "GDELT",
"units": "Events",
"hasPassingInputQuery": true,
"rowsSearched": 761318140,
"warnings": {}
},
{
"top10": [
{
"label": "Nebraska",
"number": 2.2900763358778624
},
{
"label": "Pennsylvania",
"number": 1.9593872461702886
},
{
"label": "New York",
"number": 1.7699115044247788
},
{
"label": "Alabama",
"number": 1.6734279918864097
},
{
"label": "Kentucky",
"number": 1.407954945441746
},
{
"label": "Illinois",
"number": 1.1778563015312131
},
{
"label": "Iowa",
"number": 1.1219147344801794
},
{
"label": "Kansas",
"number": 1.092896174863388
},
{
"label": "Wisconsin",
"number": 0.8661614058465895
},
{
"label": "West Virginia",
"number": 0.6961506961506961
}
],
"answer": "Nebraska",
"answerValue": 2.2900763358778624,
"question": "Which state's subreddit mentioned Obama the most in the past year?",
"rowIndex": 63,
"approved": true,
"params": {
"regex": "(?i)\\b(obama|barack)\\b",
"per": 100
},
"sql": "\nSELECT\n states.state AS state,\n SUM(CASE WHEN REGEXP_MATCH(body, r'(?i)\\b(obama|barack)\\b') THEN 1 ELSE 0 END) * 100 / COUNT(*) AS per\nFROM (\n SELECT\n *\n FROM\n TABLE_QUERY([fh-bigquery:reddit_comments], 'regexp_match(table_id, \"^2016\")')) comments\nJOIN\n [subreddits.states] states\nON\n states.subreddit = comments.subreddit\nGROUP BY\n state\nORDER BY\n per DESC IGNORE CASE\n",
"inputSQL": "\nSELECT\n states.state AS state,\n SUM(CASE WHEN REGEXP_MATCH(body, r'(?i)\\b(obama|barack)\\b') THEN 1 ELSE 0 END) * 100 / COUNT(*) AS per\nFROM (\n SELECT\n *\n FROM\n TABLE_QUERY([fh-bigquery:reddit_comments], 'regexp_match(table_id, \"^2016\")')) comments\nJOIN\n [subreddits.states] states\nON\n states.subreddit = comments.subreddit\nWHERE\n states.state = 'userInput'\nGROUP BY\n state\nORDER BY\n per DESC IGNORE CASE\n",
"databases": "Reddit",
"units": "% of Comments",
"hasPassingInputQuery": true,
"rowsSearched": 185166080,
"warnings": {}
},
{
"top10": [
{
"label": "Ohio",
"number": 3.7789427697684577
},
{
"label": "Iowa",
"number": 3.5901271503365746
},
{
"label": "South Carolina",
"number": 3.5624325296869377
},
{
"label": "Nevada",
"number": 3.559870550161812
},
{
"label": "New York",
"number": 2.4582104228121926
},
{
"label": "Florida",
"number": 2.45275432247688
},
{
"label": "Missouri",
"number": 2.2538552787663106
},
{
"label": "Minnesota",
"number": 1.8172977572479183
},
{
"label": "North Carolina",
"number": 1.7235943247503942
},
{
"label": "Pennsylvania",
"number": 1.7100106875667973
}
],
"answer": "Ohio",
"answerValue": 3.7789427697684577,
"question": "Which state's subreddit mentioned Donald Trump the most in the past year?",
"rowIndex": 67,
"approved": true,
"params": {
"regex": "(?i)\\b(trump|the donald)\\b",
"per": 100
},
"sql": "\nSELECT\n states.state AS state,\n SUM(CASE WHEN REGEXP_MATCH(body, r'(?i)\\b(trump|the donald)\\b') THEN 1 ELSE 0 END) * 100 / COUNT(*) AS per\nFROM (\n SELECT\n *\n FROM\n TABLE_QUERY([fh-bigquery:reddit_comments], 'regexp_match(table_id, \"^2016\")')) comments\nJOIN\n [subreddits.states] states\nON\n states.subreddit = comments.subreddit\nGROUP BY\n state\nORDER BY\n per DESC IGNORE CASE\n",
"inputSQL": "\nSELECT\n states.state AS state,\n SUM(CASE WHEN REGEXP_MATCH(body, r'(?i)\\b(trump|the donald)\\b') THEN 1 ELSE 0 END) * 100 / COUNT(*) AS per\nFROM (\n SELECT\n *\n FROM\n TABLE_QUERY([fh-bigquery:reddit_comments], 'regexp_match(table_id, \"^2016\")')) comments\nJOIN\n [subreddits.states] states\nON\n states.subreddit = comments.subreddit\nWHERE\n states.state = 'userInput'\nGROUP BY\n state\nORDER BY\n per DESC IGNORE CASE\n",
"databases": "Reddit",
"units": "Comments per 1,000",
"hasPassingInputQuery": true,
"rowsSearched": 185166080,
"warnings": {}
},
{
"top10": [
{
"label": "Texas",
"number": 56
},
{
"label": "New York",
"number": 52
},
{
"label": "Washington",
"number": 45
},
{
"label": "California",
"number": 43
},
{
"label": "Florida",
"number": 41
},
{
"label": "Hawaii",
"number": 35
},
{
"label": "Colorado",
"number": 32
},
{
"label": "Oregon",
"number": 27
},
{
"label": "Alaska",
"number": 27
},
{
"label": "Utah",
"number": 23
}
],
"answer": "Texas",
"answerValue": 56,
"question": "Which state was mentioned the most in /r/travel in the past month?",
"rowIndex": 100,
"approved": true,
"params": {
"freebase_tag": "states",
"subreddit": "travel",
"top_wiki": 500
},
"sql": "\nSELECT\n pop_topic.name,\n COUNT(UNIQUE(comments.id)) AS total\nFROM (\n SELECT\n topic_wiki.mid AS mid,\n topic_wiki.name AS name,\n SUM(requests) AS total\n FROM (\n SELECT\n topic.mid AS mid,\n topic.name as name,\n REPLACE(REPLACE(wiki.title, '$0028', '('), '$0029', ')') AS title\n FROM (\n SELECT\n mid,\n name\n FROM\n [freebase_tags.states]) topic\n JOIN (\n SELECT\n REGEXP_REPLACE(REGEXP_REPLACE(obj, '/wikipedia/en/', ''), '/wikipedia/id/', '') AS title,\n sub AS mid\n FROM\n [fh-bigquery:freebase20140119.triples_nolang]\n WHERE\n obj CONTAINS '/wikipedia/en'\n OR obj CONTAINS '/wikipedia/id'\n AND pred = '/type/object/key') wiki\n ON\n wiki.mid = topic.mid) topic_wiki\n JOIN\n [fh-bigquery:wikipedia.pagecounts_201605] views\n ON\n views.title = topic_wiki.title\n GROUP BY\n mid,\n name\n ORDER BY\n total DESC\n LIMIT\n 500 ) pop_topic\nCROSS JOIN (\n SELECT\n id,\n body\n FROM\n [fh-bigquery:reddit_comments.2016_05]\n WHERE\n subreddit = 'travel') comments\nWHERE\n comments.body CONTAINS CONCAT(' ', pop_topic.name, ' ')\nGROUP BY\n pop_topic.name,\nORDER BY\n total DESC\nIGNORE CASE\n",
"inputSQL": "\nSELECT\n pop_topic.name,\n COUNT(UNIQUE(comments.id)) AS total\nFROM (\n SELECT\n topic_wiki.mid AS mid,\n topic_wiki.name AS name,\n SUM(requests) AS total\n FROM (\n SELECT\n topic.mid AS mid,\n topic.name as name,\n REPLACE(REPLACE(wiki.title, '$0028', '('), '$0029', ')') AS title\n FROM (\n SELECT\n mid,\n name\n FROM\n [freebase_tags.states]\n WHERE\n name = 'userInput') topic\n JOIN (\n SELECT\n REGEXP_REPLACE(REGEXP_REPLACE(obj, '/wikipedia/en/', ''), '/wikipedia/id/', '') AS title,\n sub AS mid\n FROM\n [fh-bigquery:freebase20140119.triples_nolang]\n WHERE\n obj CONTAINS '/wikipedia/en'\n OR obj CONTAINS '/wikipedia/id'\n AND pred = '/type/object/key') wiki\n ON\n wiki.mid = topic.mid) topic_wiki\n JOIN\n [fh-bigquery:wikipedia.pagecounts_201605] views\n ON\n views.title = topic_wiki.title\n GROUP BY\n mid,\n name\n ORDER BY\n total DESC\n LIMIT\n 500 ) pop_topic\nCROSS JOIN (\n SELECT\n id,\n body\n FROM\n [fh-bigquery:reddit_comments.2016_05]\n WHERE\n subreddit = 'travel') comments\nWHERE\n comments.body CONTAINS CONCAT(' ', pop_topic.name, ' ')\nGROUP BY\n pop_topic.name,\nORDER BY\n total DESC IGNORE CASE\n",
"databases": "Reddit",
"units": "Mentions",
"hasPassingInputQuery": true,
"rowsSearched": 6347255158,
"warnings": {}
},
{
"top10": [
{
"label": "Ronaldo",
"number": 46
},
{
"label": "Jamie Vardy",
"number": 15
},
{
"label": "Usain Bolt",
"number": 15
},
{
"label": "Draymond Green",
"number": 10
},
{
"label": "Michael Jordan",
"number": 9
},
{
"label": "Steven Adams",
"number": 8
},
{
"label": "Mike Tyson",
"number": 8
},
{
"label": "Cristiano Ronaldo",
"number": 7
},
{
"label": "Paul Pierce",
"number": 7
},
{
"label": "Babe Ruth",
"number": 7
}
],
"answer": "Ronaldo",
"answerValue": 46,
"question": "Who was the most popular athlete in /r/sports in the past month?",
"rowIndex": 77,
"approved": true,
"params": {
"freebase_tag": "athletes",
"subreddit": "sports",
"num_common_words": 500,
"substring": true,
"top_wiki": 500
},
"sql": "\nSELECT\n pop_topic.name,\n COUNT(UNIQUE(comments.id)) AS total\nFROM (\n SELECT\n topic_wiki.mid AS mid,\n topic_wiki.name AS name,\n SUM(requests) AS total\n FROM (\n SELECT\n topic.mid AS mid,\n topic.name as name,\n REPLACE(REPLACE(wiki.title, '$0028', '('), '$0029', ')') AS title\n FROM (\n SELECT\n mid,\n name\n FROM\n [freebase_tags.athletes]) topic\n JOIN (\n SELECT\n REGEXP_REPLACE(REGEXP_REPLACE(obj, '/wikipedia/en/', ''), '/wikipedia/id/', '') AS title,\n sub AS mid\n FROM\n [fh-bigquery:freebase20140119.triples_nolang]\n WHERE\n obj CONTAINS '/wikipedia/en'\n OR obj CONTAINS '/wikipedia/id'\n AND pred = '/type/object/key') wiki\n ON\n wiki.mid = topic.mid) topic_wiki\n JOIN\n [fh-bigquery:wikipedia.pagecounts_201605] views\n ON\n views.title = topic_wiki.title\n GROUP BY\n mid,\n name\n ORDER BY\n total DESC\n LIMIT\n 500 ) pop_topic\nCROSS JOIN (\n SELECT\n id,\n body\n FROM\n [fh-bigquery:reddit_comments.2016_05]\n WHERE\n subreddit = 'sports') comments\nWHERE\n comments.body CONTAINS CONCAT(' ', pop_topic.name, ' ')\nGROUP BY\n pop_topic.name,\nORDER BY\n total DESC\nIGNORE CASE\n",
"inputSQL": "\nSELECT\n pop_topic.name,\n COUNT(UNIQUE(comments.id)) AS total\nFROM (\n SELECT\n topic_wiki.mid AS mid,\n topic_wiki.name AS name,\n SUM(requests) AS total\n FROM (\n SELECT\n topic.mid AS mid,\n topic.name as name,\n REPLACE(REPLACE(wiki.title, '$0028', '('), '$0029', ')') AS title\n FROM (\n SELECT\n mid,\n name\n FROM\n [freebase_tags.athletes]\n WHERE\n name = 'userInput'\n OR name CONTAINS CONCAT(' ', 'userInput')\n OR name CONTAINS CONCAT('userInput', ' ')\n) topic\n JOIN (\n SELECT\n REGEXP_REPLACE(REGEXP_REPLACE(obj, '/wikipedia/en/', ''), '/wikipedia/id/', '') AS title,\n sub AS mid\n FROM\n [fh-bigquery:freebase20140119.triples_nolang]\n WHERE\n obj CONTAINS '/wikipedia/en'\n OR obj CONTAINS '/wikipedia/id'\n AND pred = '/type/object/key') wiki\n ON\n wiki.mid = topic.mid) topic_wiki\n JOIN\n [fh-bigquery:wikipedia.pagecounts_201605] views\n ON\n views.title = topic_wiki.title\n GROUP BY\n mid,\n name\n ORDER BY\n total DESC\n LIMIT\n 500 ) pop_topic\nCROSS JOIN (\n SELECT\n id,\n body\n FROM\n [fh-bigquery:reddit_comments.2016_05]\n WHERE\n subreddit = 'sports') comments\nWHERE\n comments.body CONTAINS CONCAT(' ', pop_topic.name, ' ')\nGROUP BY\n pop_topic.name,\nORDER BY\n total DESC IGNORE CASE\n",
"databases": "Reddit",
"units": "Mentions",
"hasPassingInputQuery": true,
"rowsSearched": 7387771039,
"warnings": {}
},
{
"top10": [
{
"label": "Radiohead",
"number": 1091
},
{
"label": "The Beatles",
"number": 329
},
{
"label": "Yes",
"number": 246
},
{
"label": "Tool",
"number": 210
},
{
"label": "Prince",
"number": 204
},
{
"label": "Skrillex",
"number": 176
},
{
"label": "Queen",
"number": 169
},
{
"label": "Nirvana",
"number": 155
},
{
"label": "Metallica",
"number": 152
},
{
"label": "Search",
"number": 152
}
],
"answer": "Radiohead",
"answerValue": 1091,
"question": "What musical group was talked about in /r/music the most in the past month?",
"rowIndex": 75,
"approved": true,
"params": {
"freebase_tag": "musicians",
"subreddit": "music",
"top_wiki": 500
},
"sql": "\nSELECT\n pop_topic.name,\n COUNT(UNIQUE(comments.id)) AS total\nFROM (\n SELECT\n topic_wiki.mid AS mid,\n topic_wiki.name AS name,\n SUM(requests) AS total\n FROM (\n SELECT\n topic.mid AS mid,\n topic.name as name,\n REPLACE(REPLACE(wiki.title, '$0028', '('), '$0029', ')') AS title\n FROM (\n SELECT\n mid,\n name\n FROM\n [freebase_tags.musicians]) topic\n JOIN (\n SELECT\n REGEXP_REPLACE(REGEXP_REPLACE(obj, '/wikipedia/en/', ''), '/wikipedia/id/', '') AS title,\n sub AS mid\n FROM\n [fh-bigquery:freebase20140119.triples_nolang]\n WHERE\n obj CONTAINS '/wikipedia/en'\n OR obj CONTAINS '/wikipedia/id'\n AND pred = '/type/object/key') wiki\n ON\n wiki.mid = topic.mid) topic_wiki\n JOIN\n [fh-bigquery:wikipedia.pagecounts_201605] views\n ON\n views.title = topic_wiki.title\n GROUP BY\n mid,\n name\n ORDER BY\n total DESC\n LIMIT\n 500 ) pop_topic\nCROSS JOIN (\n SELECT\n id,\n body\n FROM\n [fh-bigquery:reddit_comments.2016_05]\n WHERE\n subreddit = 'music') comments\nWHERE\n comments.body CONTAINS CONCAT(' ', pop_topic.name, ' ')\nGROUP BY\n pop_topic.name,\nORDER BY\n total DESC\nIGNORE CASE\n",
"inputSQL": "\nSELECT\n pop_topic.name,\n COUNT(UNIQUE(comments.id)) AS total\nFROM (\n SELECT\n topic_wiki.mid AS mid,\n topic_wiki.name AS name,\n SUM(requests) AS total\n FROM (\n SELECT\n topic.mid AS mid,\n topic.name as name,\n REPLACE(REPLACE(wiki.title, '$0028', '('), '$0029', ')') AS title\n FROM (\n SELECT\n mid,\n name\n FROM\n [freebase_tags.musicians]\n WHERE\n name = 'userInput') topic\n JOIN (\n SELECT\n REGEXP_REPLACE(REGEXP_REPLACE(obj, '/wikipedia/en/', ''), '/wikipedia/id/', '') AS title,\n sub AS mid\n FROM\n [fh-bigquery:freebase20140119.triples_nolang]\n WHERE\n obj CONTAINS '/wikipedia/en'\n OR obj CONTAINS '/wikipedia/id'\n AND pred = '/type/object/key') wiki\n ON\n wiki.mid = topic.mid) topic_wiki\n JOIN\n [fh-bigquery:wikipedia.pagecounts_201605] views\n ON\n views.title = topic_wiki.title\n GROUP BY\n mid,\n name\n ORDER BY\n total DESC\n LIMIT\n 500 ) pop_topic\nCROSS JOIN (\n SELECT\n id,\n body\n FROM\n [fh-bigquery:reddit_comments.2016_05]\n WHERE\n subreddit = 'music') comments\nWHERE\n comments.body CONTAINS CONCAT(' ', pop_topic.name, ' ')\nGROUP BY\n pop_topic.name,\nORDER BY\n total DESC IGNORE CASE\n",
"databases": "Reddit",
"units": "Mentions",
"hasPassingInputQuery": true,
"rowsSearched": 6623907684,
"warnings": {}
},
{
"top10": [
{
"label": "Oxford",
"number": 6
},
{
"label": "Pittsburgh",
"number": 5
},
{
"label": "Toronto",
"number": 5
},
{
"label": "London",
"number": 4
},
{
"label": "Calgary",
"number": 4
},
{
"label": "Chicago",
"number": 4
},
{
"label": "Melbourne",
"number": 4
},
{
"label": "Seattle",
"number": 4
},
{
"label": "Vancouver",
"number": 3
},
{
"label": "Cincinnati",
"number": 3
}
],
"answer": "Oxford",
"answerValue": 6,
"question": "Which U.S. city was mentioned the most in /r/UpliftingNews in the past month?",
"rowIndex": 107,
"approved": true,
"params": {
"freebase_tag": "cities",
"subreddit": "upliftingnews",
"top_wiki": 75
},
"sql": "\nSELECT\n pop_topic.name,\n COUNT(UNIQUE(comments.id)) AS total\nFROM (\n SELECT\n topic_wiki.mid AS mid,\n topic_wiki.name AS name,\n SUM(requests) AS total\n FROM (\n SELECT\n topic.mid AS mid,\n topic.name as name,\n REPLACE(REPLACE(wiki.title, '$0028', '('), '$0029', ')') AS title\n FROM (\n SELECT\n mid,\n name\n FROM\n [freebase_tags.cities]) topic\n JOIN (\n SELECT\n REGEXP_REPLACE(REGEXP_REPLACE(obj, '/wikipedia/en/', ''), '/wikipedia/id/', '') AS title,\n sub AS mid\n FROM\n [fh-bigquery:freebase20140119.triples_nolang]\n WHERE\n obj CONTAINS '/wikipedia/en'\n OR obj CONTAINS '/wikipedia/id'\n AND pred = '/type/object/key') wiki\n ON\n wiki.mid = topic.mid) topic_wiki\n JOIN\n [fh-bigquery:wikipedia.pagecounts_201605] views\n ON\n views.title = topic_wiki.title\n GROUP BY\n mid,\n name\n ORDER BY\n total DESC\n LIMIT\n 75 ) pop_topic\nCROSS JOIN (\n SELECT\n id,\n body\n FROM\n [fh-bigquery:reddit_comments.2016_05]\n WHERE\n subreddit = 'upliftingnews') comments\nWHERE\n comments.body CONTAINS CONCAT(' ', pop_topic.name, ' ')\nGROUP BY\n pop_topic.name,\nORDER BY\n total DESC\nIGNORE CASE\n",
"inputSQL": "\nSELECT\n pop_topic.name,\n COUNT(UNIQUE(comments.id)) AS total\nFROM (\n SELECT\n topic_wiki.mid AS mid,\n topic_wiki.name AS name,\n SUM(requests) AS total\n FROM (\n SELECT\n topic.mid AS mid,\n topic.name as name,\n REPLACE(REPLACE(wiki.title, '$0028', '('), '$0029', ')') AS title\n FROM (\n SELECT\n mid,\n name\n FROM\n [freebase_tags.cities]\n WHERE\n name = 'userInput') topic\n JOIN (\n SELECT\n REGEXP_REPLACE(REGEXP_REPLACE(obj, '/wikipedia/en/', ''), '/wikipedia/id/', '') AS title,\n sub AS mid\n FROM\n [fh-bigquery:freebase20140119.triples_nolang]\n WHERE\n obj CONTAINS '/wikipedia/en'\n OR obj CONTAINS '/wikipedia/id'\n AND pred = '/type/object/key') wiki\n ON\n wiki.mid = topic.mid) topic_wiki\n JOIN\n [fh-bigquery:wikipedia.pagecounts_201605] views\n ON\n views.title = topic_wiki.title\n GROUP BY\n mid,\n name\n ORDER BY\n total DESC\n LIMIT\n 75 ) pop_topic\nCROSS JOIN (\n SELECT\n id,\n body\n FROM\n [fh-bigquery:reddit_comments.2016_05]\n WHERE\n subreddit = 'upliftingnews') comments\nWHERE\n comments.body CONTAINS CONCAT(' ', pop_topic.name, ' ')\nGROUP BY\n pop_topic.name,\nORDER BY\n total DESC IGNORE CASE\n",
"databases": "Reddit",
"units": "Mentions",
"hasPassingInputQuery": true,
"rowsSearched": 6527903831,
"warnings": {}
},
{
"top10": [
{
"label": "California",
"number": 1080
},
{
"label": "Texas",
"number": 998
},
{
"label": "Florida",
"number": 657
},
{
"label": "Washington",
"number": 534
},
{
"label": "New York",
"number": 487
},
{
"label": "Colorado",
"number": 456
},
{
"label": "North Carolina",
"number": 373
},
{
"label": "Oklahoma",
"number": 288
},
{
"label": "Illinois",
"number": 263
},
{
"label": "Oregon",
"number": 253
}
],
"answer": "California",
"answerValue": 1080,
"question": "Which state was mentioned the most in /r/news in the past month?",
"rowIndex": 98,
"approved": true,
"params": {
"freebase_tag": "states",
"subreddit": "news",
"top_wiki": 500
},
"sql": "\nSELECT\n pop_topic.name,\n COUNT(UNIQUE(comments.id)) AS total\nFROM (\n SELECT\n topic_wiki.mid AS mid,\n topic_wiki.name AS name,\n SUM(requests) AS total\n FROM (\n SELECT\n topic.mid AS mid,\n topic.name as name,\n REPLACE(REPLACE(wiki.title, '$0028', '('), '$0029', ')') AS title\n FROM (\n SELECT\n mid,\n name\n FROM\n [freebase_tags.states]) topic\n JOIN (\n SELECT\n REGEXP_REPLACE(REGEXP_REPLACE(obj, '/wikipedia/en/', ''), '/wikipedia/id/', '') AS title,\n sub AS mid\n FROM\n [fh-bigquery:freebase20140119.triples_nolang]\n WHERE\n obj CONTAINS '/wikipedia/en'\n OR obj CONTAINS '/wikipedia/id'\n AND pred = '/type/object/key') wiki\n ON\n wiki.mid = topic.mid) topic_wiki\n JOIN\n [fh-bigquery:wikipedia.pagecounts_201605] views\n ON\n views.title = topic_wiki.title\n GROUP BY\n mid,\n name\n ORDER BY\n total DESC\n LIMIT\n 500 ) pop_topic\nCROSS JOIN (\n SELECT\n id,\n body\n FROM\n [fh-bigquery:reddit_comments.2016_05]\n WHERE\n subreddit = 'news') comments\nWHERE\n comments.body CONTAINS CONCAT(' ', pop_topic.name, ' ')\nGROUP BY\n pop_topic.name,\nORDER BY\n total DESC\nIGNORE CASE\n",
"inputSQL": "\nSELECT\n pop_topic.name,\n COUNT(UNIQUE(comments.id)) AS total\nFROM (\n SELECT\n topic_wiki.mid AS mid,\n topic_wiki.name AS name,\n SUM(requests) AS total\n FROM (\n SELECT\n topic.mid AS mid,\n topic.name as name,\n REPLACE(REPLACE(wiki.title, '$0028', '('), '$0029', ')') AS title\n FROM (\n SELECT\n mid,\n name\n FROM\n [freebase_tags.states]\n WHERE\n name = 'userInput') topic\n JOIN (\n SELECT\n REGEXP_REPLACE(REGEXP_REPLACE(obj, '/wikipedia/en/', ''), '/wikipedia/id/', '') AS title,\n sub AS mid\n FROM\n [fh-bigquery:freebase20140119.triples_nolang]\n WHERE\n obj CONTAINS '/wikipedia/en'\n OR obj CONTAINS '/wikipedia/id'\n AND pred = '/type/object/key') wiki\n ON\n wiki.mid = topic.mid) topic_wiki\n JOIN\n [fh-bigquery:wikipedia.pagecounts_201605] views\n ON\n views.title = topic_wiki.title\n GROUP BY\n mid,\n name\n ORDER BY\n total DESC\n LIMIT\n 500 ) pop_topic\nCROSS JOIN (\n SELECT\n id,\n body\n FROM\n [fh-bigquery:reddit_comments.2016_05]\n WHERE\n subreddit = 'news') comments\nWHERE\n comments.body CONTAINS CONCAT(' ', pop_topic.name, ' ')\nGROUP BY\n pop_topic.name,\nORDER BY\n total DESC IGNORE CASE\n",
"databases": "Reddit",
"units": "Mentions",
"hasPassingInputQuery": true,
"rowsSearched": 6347814029,
"warnings": {}
},
{
"top10": [
{
"label": "Australia",
"number": 95
},
{
"label": "Canada",
"number": 95
},
{
"label": "China",
"number": 79
},
{
"label": "India",
"number": 36
},
{
"label": "Japan",
"number": 17
},
{
"label": "South Africa",
"number": 16
},
{
"label": "Malaysia",
"number": 16
},
{
"label": "Mexico",
"number": 16
},
{
"label": "Indonesia",
"number": 15
},
{
"label": "Russia",
"number": 14
}
],
"answer": "Australia",
"answerValue": 95,
"question": "Which country was mentioned the most in /r/UpliftingNews in the past month?",
"rowIndex": 108,
"approved": true,
"params": {
"freebase_tag": "countries",
"subreddit": "upliftingnews",
"top_wiki": 500
},
"sql": "\nSELECT\n pop_topic.name,\n COUNT(UNIQUE(comments.id)) AS total\nFROM (\n SELECT\n topic_wiki.mid AS mid,\n topic_wiki.name AS name,\n SUM(requests) AS total\n FROM (\n SELECT\n topic.mid AS mid,\n topic.name as name,\n REPLACE(REPLACE(wiki.title, '$0028', '('), '$0029', ')') AS title\n FROM (\n SELECT\n mid,\n name\n FROM\n [freebase_tags.countries]) topic\n JOIN (\n SELECT\n REGEXP_REPLACE(REGEXP_REPLACE(obj, '/wikipedia/en/', ''), '/wikipedia/id/', '') AS title,\n sub AS mid\n FROM\n [fh-bigquery:freebase20140119.triples_nolang]\n WHERE\n obj CONTAINS '/wikipedia/en'\n OR obj CONTAINS '/wikipedia/id'\n AND pred = '/type/object/key') wiki\n ON\n wiki.mid = topic.mid) topic_wiki\n JOIN\n [fh-bigquery:wikipedia.pagecounts_201605] views\n ON\n views.title = topic_wiki.title\n GROUP BY\n mid,\n name\n ORDER BY\n total DESC\n LIMIT\n 500 ) pop_topic\nCROSS JOIN (\n SELECT\n id,\n body\n FROM\n [fh-bigquery:reddit_comments.2016_05]\n WHERE\n subreddit = 'upliftingnews') comments\nWHERE\n comments.body CONTAINS CONCAT(' ', pop_topic.name, ' ')\nGROUP BY\n pop_topic.name,\nORDER BY\n total DESC\nIGNORE CASE\n",
"inputSQL": "\nSELECT\n pop_topic.name,\n COUNT(UNIQUE(comments.id)) AS total\nFROM (\n SELECT\n topic_wiki.mid AS mid,\n topic_wiki.name AS name,\n SUM(requests) AS total\n FROM (\n SELECT\n topic.mid AS mid,\n topic.name as name,\n REPLACE(REPLACE(wiki.title, '$0028', '('), '$0029', ')') AS title\n FROM (\n SELECT\n mid,\n name\n FROM\n [freebase_tags.countries]\n WHERE\n name = 'userInput') topic\n JOIN (\n SELECT\n REGEXP_REPLACE(REGEXP_REPLACE(obj, '/wikipedia/en/', ''), '/wikipedia/id/', '') AS title,\n sub AS mid\n FROM\n [fh-bigquery:freebase20140119.triples_nolang]\n WHERE\n obj CONTAINS '/wikipedia/en'\n OR obj CONTAINS '/wikipedia/id'\n AND pred = '/type/object/key') wiki\n ON\n wiki.mid = topic.mid) topic_wiki\n JOIN\n [fh-bigquery:wikipedia.pagecounts_201605] views\n ON\n views.title = topic_wiki.title\n GROUP BY\n mid,\n name\n ORDER BY\n total DESC\n LIMIT\n 500 ) pop_topic\nCROSS JOIN (\n SELECT\n id,\n body\n FROM\n [fh-bigquery:reddit_comments.2016_05]\n WHERE\n subreddit = 'upliftingnews') comments\nWHERE\n comments.body CONTAINS CONCAT(' ', pop_topic.name, ' ')\nGROUP BY\n pop_topic.name,\nORDER BY\n total DESC IGNORE CASE\n",
"databases": "Reddit",
"units": "Mentions",
"hasPassingInputQuery": true,
"rowsSearched": 6363107187,
"warnings": {}
},
{
"top10": [
{
"label": "Seattle",
"number": 2.111801242236025
},
{
"label": "Los Angeles",
"number": 2.047327217353936
},
{
"label": "Atlanta",
"number": 1.824178134389152
},
{
"label": "Pittsburgh",
"number": 1.7944059394836598
},
{
"label": "Austin",
"number": 1.7139845078141636
},
{
"label": "Denver",
"number": 1.6448027238133387
},
{
"label": "Boston",
"number": 1.5753958798171697
},
{
"label": "San Diego",
"number": 1.509454290793514
},
{
"label": "Houston",
"number": 1.4879872537110945
},
{
"label": "San Francisco",
"number": 1.403764641538672
}
],
"answer": "Seattle",
"answerValue": 2.111801242236025,
"question": "Which city's subreddit discussed traffic the most?",
"rowIndex": 153,
"approved": true,
"params": {
"regex": "(?i)\\b(traffic)\\b",
"per": 100
},
"sql": "\nSELECT\n cities.city,\n SUM(CASE WHEN REGEXP_MATCH(body, r'(?i)\\b(traffic)\\b') THEN 1 ELSE 0 END) * 100 / COUNT(*) AS per\nFROM (\n SELECT\n body,\n subreddit\n FROM\n TABLE_QUERY([fh-bigquery:reddit_comments], 'regexp_match(table_id, \"^(2016|2015_(0[7-9]|1[0-2]))\")')) comments\nJOIN\n [subreddits.cities] cities\nON\n comments.subreddit = cities.subreddit\nGROUP BY\n cities.city\nORDER BY\n per DESC IGNORE CASE\n",
"inputSQL": "\nSELECT\n cities.city,\n SUM(CASE WHEN REGEXP_MATCH(body, r'(?i)\\b(traffic)\\b') THEN 1 ELSE 0 END) * 100 / COUNT(*) AS per\nFROM (\n SELECT\n body,\n subreddit\n FROM\n TABLE_QUERY([fh-bigquery:reddit_comments], 'regexp_match(table_id, \"^(2016|2015_(0[7-9]|1[0-2]))\")')) comments\nJOIN\n [subreddits.cities] cities\nON\n comments.subreddit = cities.subreddit\nJOIN\n [fh-bigquery:freebase20140119.triples_lang_en] aliases\nON\n aliases.sub = cities.mid\nWHERE\n (aliases.pred = '/common/topic/alias'\n OR aliases.pred = '/type/object/name')\n AND aliases.obj = 'userInput'\nGROUP BY\n cities.city\nORDER BY\n per DESC IGNORE CASE\n",
"databases": "Reddit",
"units": "% of Comments",
"hasPassingInputQuery": true,
"rowsSearched": 532396361,
"warnings": {}
},
{
"top10": [
{
"label": "New York",
"number": 6.588003933136677
},
{
"label": "Nevada",
"number": 3.559870550161812
},
{
"label": "Mississippi",
"number": 2.992957746478873
},
{
"label": "South Carolina",
"number": 2.626844188557035
},
{
"label": "Missouri",
"number": 2.372479240806643
},
{
"label": "Iowa",
"number": 1.6454749439042633
},
{
"label": "Ohio",
"number": 1.6164263870685889
},
{
"label": "Tennessee",
"number": 1.4304291287386215
},
{
"label": "Pennsylvania",
"number": 1.3181332383327395
},
{
"label": "Nebraska",
"number": 1.2213740458015268
}
],
"answer": "New York",
"answerValue": 6.588003933136677,
"question": "Which state's subreddit mentioned Hillary Clinton the most in the past year?",
"rowIndex": 167,
"approved": true,
"params": {
"regex": "(?i)\\b(hillary|clinton)\\b",
"per": 100
},
"sql": "\nSELECT\n states.state AS state,\n SUM(CASE WHEN REGEXP_MATCH(body, r'(?i)\\b(hillary|clinton)\\b') THEN 1 ELSE 0 END) * 100 / COUNT(*) AS per\nFROM (\n SELECT\n *\n FROM\n TABLE_QUERY([fh-bigquery:reddit_comments], 'regexp_match(table_id, \"^2016\")')) comments\nJOIN\n [subreddits.states] states\nON\n states.subreddit = comments.subreddit\nGROUP BY\n state\nORDER BY\n per DESC IGNORE CASE\n",
"inputSQL": "\nSELECT\n states.state AS state,\n SUM(CASE WHEN REGEXP_MATCH(body, r'(?i)\\b(hillary|clinton)\\b') THEN 1 ELSE 0 END) * 100 / COUNT(*) AS per\nFROM (\n SELECT\n *\n FROM\n TABLE_QUERY([fh-bigquery:reddit_comments], 'regexp_match(table_id, \"^2016\")')) comments\nJOIN\n [subreddits.states] states\nON\n states.subreddit = comments.subreddit\nWHERE\n states.state = 'userInput'\nGROUP BY\n state\nORDER BY\n per DESC IGNORE CASE\n",
"databases": "Reddit",
"units": "% of Mentions",
"hasPassingInputQuery": true,
"rowsSearched": 185166080,
"warnings": {}
},
{
"top10": [
{
"label": "Russia",
"number": 3237
},
{
"label": "Germany",
"number": 3035
},
{
"label": "Poland",
"number": 1420
},
{
"label": "France",
"number": 1391
},
{
"label": "Turkey",
"number": 1385
},
{
"label": "Greece",
"number": 1353
},
{
"label": "Sweden",
"number": 1094
},
{
"label": "Ukraine",
"number": 916
},
{
"label": "Romania",
"number": 781
},
{
"label": "Spain",
"number": 775
}
],
"answer": "Russia",
"answerValue": 3237,
"question": "Which country was mentioned the most in /r/europe in the past month? ",
"rowIndex": 69,
"approved": true,
"params": {
"freebase_tag": "countries",
"subreddit": "europe"
},
"sql": "\nSELECT\n topic.name,\n COUNT(UNIQUE(comments.id)) AS total\nFROM\n [freebase_tags.countries] topic\nCROSS JOIN (\n SELECT\n id,\n body\n FROM\n [fh-bigquery:reddit_comments.2016_05]\n WHERE\n subreddit = 'europe') comments\nWHERE\n comments.body CONTAINS CONCAT(' ', topic.name, ' ')\nGROUP BY\n topic.name,\nORDER BY\n total DESC IGNORE CASE\n",
"inputSQL": "\nSELECT\n topic.name,\n COUNT(UNIQUE(comments.id)) AS total\nFROM (\n SELECT\n *\n FROM\n [freebase_tags.countries]\n WHERE\n name = 'userInput') topic\nCROSS JOIN (\n SELECT\n id,\n body\n FROM\n [fh-bigquery:reddit_comments.2016_05]\n WHERE\n subreddit = 'europe') comments\nWHERE\n comments.body CONTAINS CONCAT(' ', topic.name, ' ')\nGROUP BY\n topic.name,\nORDER BY\n total DESC IGNORE CASE\n",
"databases": "Reddit",
"units": "Comments",
"hasPassingInputQuery": true,
"rowsSearched": 65379093,
"warnings": {}
},
{
"top10": [
{
"label": "Kansas",
"number": 5.46448087431694
},
{
"label": "Tennessee",
"number": 5.201560468140442
},
{
"label": "Missouri",
"number": 4.744958481613286
},
{
"label": "South Dakota",
"number": 4.405286343612334
},
{
"label": "New Mexico",
"number": 3.816793893129771
},
{
"label": "Massachusetts",
"number": 3.707627118644068
},
{
"label": "West Virginia",
"number": 3.276003276003276
},
{
"label": "Nevada",
"number": 3.236245954692557
},
{
"label": "Illinois",
"number": 2.944640753828033
},
{
"label": "Michigan",
"number": 2.1515497882068177
}
],
"answer": "Kansas",
"answerValue": 5.46448087431694,
"question": "Which state's subreddit mentioned unemployment the most in the past year?",
"rowIndex": 185,
"approved": true,
"params": {
"regex": "(?i)\\b(unemployment|unemployed)\\b",
"per": 1000
},
"sql": "\nSELECT\n states.state AS state,\n SUM(CASE WHEN REGEXP_MATCH(body, r'(?i)\\b(unemployment|unemployed)\\b') THEN 1 ELSE 0 END) * 1000 / COUNT(*) AS per\nFROM (\n SELECT\n *\n FROM\n TABLE_QUERY([fh-bigquery:reddit_comments], 'regexp_match(table_id, \"^2016\")')) comments\nJOIN\n [subreddits.states] states\nON\n states.subreddit = comments.subreddit\nGROUP BY\n state\nORDER BY\n per DESC IGNORE CASE\n",
"inputSQL": "\nSELECT\n states.state AS state,\n SUM(CASE WHEN REGEXP_MATCH(body, r'(?i)\\b(unemployment|unemployed)\\b') THEN 1 ELSE 0 END) * 1000 / COUNT(*) AS per\nFROM (\n SELECT\n *\n FROM\n TABLE_QUERY([fh-bigquery:reddit_comments], 'regexp_match(table_id, \"^2016\")')) comments\nJOIN\n [subreddits.states] states\nON\n states.subreddit = comments.subreddit\nWHERE\n states.state = 'userInput'\nGROUP BY\n state\nORDER BY\n per DESC IGNORE CASE\n",
"databases": "Reddit",
"units": "Mentions per 1,000",
"hasPassingInputQuery": true,
"rowsSearched": 185166080,
"warnings": {}
},
{
"top10": [
{
"label": "San Francisco",
"number": 7.641705786731082
},
{
"label": "Vancouver",
"number": 2.0920935435093315
},
{
"label": "Montreal",
"number": 1.5772210203084653
},
{
"label": "San Diego",
"number": 1.5730216021117276
},
{
"label": "New York City",
"number": 1.4869238170209045
},
{
"label": "Austin",
"number": 1.2531984486869343
},
{
"label": "Denver",
"number": 1.0848521263101676
},
{
"label": "Los Angeles",
"number": 1.0603314586797705
},
{
"label": "Chicago",
"number": 1.0168693269395443
},
{
"label": "Washington, D.C.",
"number": 0.9992089595736708
}
],
"answer": "San Francisco",
"answerValue": 7.641705786731082,
"question": "Which city's subreddit mentioned Airbnb the most?",
"rowIndex": 190,
"approved": true,
"params": {
"regex": "(?i)\\b(airbnb)\\b",
"per": 1000
},
"sql": "\nSELECT\n cities.city,\n SUM(CASE WHEN REGEXP_MATCH(body, r'(?i)\\b(airbnb)\\b') THEN 1 ELSE 0 END) * 1000 / COUNT(*) AS per\nFROM (\n SELECT\n body,\n subreddit\n FROM\n TABLE_QUERY([fh-bigquery:reddit_comments], 'regexp_match(table_id, \"^(2016|2015_(0[7-9]|1[0-2]))\")')) comments\nJOIN\n [subreddits.cities] cities\nON\n comments.subreddit = cities.subreddit\nGROUP BY\n cities.city\nORDER BY\n per DESC IGNORE CASE\n",
"inputSQL": "\nSELECT\n cities.city,\n SUM(CASE WHEN REGEXP_MATCH(body, r'(?i)\\b(airbnb)\\b') THEN 1 ELSE 0 END) * 1000 / COUNT(*) AS per\nFROM (\n SELECT\n body,\n subreddit\n FROM\n TABLE_QUERY([fh-bigquery:reddit_comments], 'regexp_match(table_id, \"^(2016|2015_(0[7-9]|1[0-2]))\")')) comments\nJOIN\n [subreddits.cities] cities\nON\n comments.subreddit = cities.subreddit\nJOIN\n [fh-bigquery:freebase20140119.triples_lang_en] aliases\nON\n aliases.sub = cities.mid\nWHERE\n (aliases.pred = '/common/topic/alias'\n OR aliases.pred = '/type/object/name')\n AND aliases.obj = 'userInput'\nGROUP BY\n cities.city\nORDER BY\n per DESC IGNORE CASE\n",
"databases": "Reddit",
"units": "Mentions per 1,000",
"hasPassingInputQuery": true,
"rowsSearched": 532396361,
"warnings": {}
},
{
"top10": [
{
"label": "Iowa",
"number": 2.7424582398404387
},
{
"label": "Idaho",
"number": 2.4479804161566707
},
{
"label": "Texas",
"number": 2.3393180887771217
},
{
"label": "New York",
"number": 1.966568338249754
},
{
"label": "Illinois",
"number": 1.7667844522968197
},
{
"label": "Ohio",
"number": 1.747487986020096
},
{
"label": "Missouri",
"number": 1.1862396204033214
},
{
"label": "Kentucky",
"number": 1.0559662090813093
},
{
"label": "Utah",
"number": 1.0476689366160294
},
{
"label": "Oklahoma",
"number": 0.9599232061435086
}
],
"answer": "Iowa",
"answerValue": 2.7424582398404387,
"question": "Which state's subreddit mentioned Ted Cruz the most in the past year?",
"rowIndex": 215,
"approved": true,
"params": {
"regex": "(?i)\\b(ted cruz)\\b",
"per": 1000
},
"sql": "\nSELECT\n states.state AS state,\n SUM(CASE WHEN REGEXP_MATCH(body, r'(?i)\\b(ted cruz)\\b') THEN 1 ELSE 0 END) * 1000 / COUNT(*) AS per\nFROM (\n SELECT\n *\n FROM\n TABLE_QUERY([fh-bigquery:reddit_comments], 'regexp_match(table_id, \"^2016\")')) comments\nJOIN\n [subreddits.states] states\nON\n states.subreddit = comments.subreddit\nGROUP BY\n state\nORDER BY\n per DESC IGNORE CASE\n",
"inputSQL": "\nSELECT\n states.state AS state,\n SUM(CASE WHEN REGEXP_MATCH(body, r'(?i)\\b(ted cruz)\\b') THEN 1 ELSE 0 END) * 1000 / COUNT(*) AS per\nFROM (\n SELECT\n *\n FROM\n TABLE_QUERY([fh-bigquery:reddit_comments], 'regexp_match(table_id, \"^2016\")')) comments\nJOIN\n [subreddits.states] states\nON\n states.subreddit = comments.subreddit\nWHERE\n states.state = 'userInput'\nGROUP BY\n state\nORDER BY\n per DESC IGNORE CASE\n",
"databases": "Reddit",
"units": "Comments per 1,000",
"hasPassingInputQuery": true,
"rowsSearched": 185166080,
"warnings": {}
},
{
"top10": [
{
"label": "Nevada",
"number": 9.06148867313916
},
{
"label": "New York",
"number": 6.784660766961652
},
{
"label": "Nebraska",
"number": 6.717557251908397
},
{
"label": "South Carolina",
"number": 5.325656711047139
},
{
"label": "Iowa",
"number": 3.5402642732485665
},
{
"label": "Missouri",
"number": 3.0842230130486357
},
{
"label": "Tennessee",
"number": 2.6657997399219764
},
{
"label": "Ohio",
"number": 2.5557011795543905
},
{
"label": "Montana",
"number": 2.1760154738878144
},
{
"label": "Vermont",
"number": 2.1467327199811277
}
],
"answer": "Nevada",
"answerValue": 9.06148867313916,
"question": "Which state's subreddit mentioned Bernie Sanders the most in the past year?",
"rowIndex": 216,
"approved": true,
"params": {
"regex": "(?i)\\b(bernie|sanders|#?feelthebern)\\b",
"per": 100
},
"sql": "\nSELECT\n states.state AS state,\n SUM(CASE WHEN REGEXP_MATCH(body, r'(?i)\\b(bernie|sanders|#?feelthebern)\\b') THEN 1 ELSE 0 END) * 100 / COUNT(*) AS per\nFROM (\n SELECT\n *\n FROM\n TABLE_QUERY([fh-bigquery:reddit_comments], 'regexp_match(table_id, \"^2016\")')) comments\nJOIN\n [subreddits.states] states\nON\n states.subreddit = comments.subreddit\nGROUP BY\n state\nORDER BY\n per DESC IGNORE CASE\n",
"inputSQL": "\nSELECT\n states.state AS state,\n SUM(CASE WHEN REGEXP_MATCH(body, r'(?i)\\b(bernie|sanders|#?feelthebern)\\b') THEN 1 ELSE 0 END) * 100 / COUNT(*) AS per\nFROM (\n SELECT\n *\n FROM\n TABLE_QUERY([fh-bigquery:reddit_comments], 'regexp_match(table_id, \"^2016\")')) comments\nJOIN\n [subreddits.states] states\nON\n states.subreddit = comments.subreddit\nWHERE\n states.state = 'userInput'\nGROUP BY\n state\nORDER BY\n per DESC IGNORE CASE\n",
"databases": "Reddit",
"units": "% of Comments",
"hasPassingInputQuery": true,
"rowsSearched": 185166080,
"warnings": {}
},
{
"top10": [
{
"label": "Los Angeles",
"number": 6.072382803011902
},
{
"label": "San Diego",
"number": 3.663200991219092
},
{
"label": "Houston",
"number": 3.431086431827862
},
{
"label": "Vancouver",
"number": 2.4166097037236502
},
{
"label": "Boston",
"number": 1.9496133266902065
},
{
"label": "Dallas",
"number": 1.869775626924769
},
{
"label": "Montreal",
"number": 1.8145020587619514
},
{
"label": "Chicago",
"number": 1.462864645772678
},
{
"label": "Washington, D.C.",
"number": 1.3877902216300984
},
{
"label": "Denver",
"number": 1.3352026169971294
}
],
"answer": "Los Angeles",
"answerValue": 6.072382803011902,
"question": "Which city’s subreddit talked about gas prices the most?",
"rowIndex": 214,
"approved": true,
"params": {
"regex": "(?i)\\b(gas prices?|price of gas)\\b",
"per": 10000
},
"sql": "\nSELECT\n cities.city,\n SUM(CASE WHEN REGEXP_MATCH(body, r'(?i)\\b(gas prices?|price of gas)\\b') THEN 1 ELSE 0 END) * 10000 / COUNT(*) AS per\nFROM (\n SELECT\n body,\n subreddit\n FROM\n TABLE_QUERY([fh-bigquery:reddit_comments], 'regexp_match(table_id, \"^(2016|2015_(0[7-9]|1[0-2]))\")')) comments\nJOIN\n [subreddits.cities] cities\nON\n comments.subreddit = cities.subreddit\nGROUP BY\n cities.city\nORDER BY\n per DESC IGNORE CASE\n",
"inputSQL": "\nSELECT\n cities.city,\n SUM(CASE WHEN REGEXP_MATCH(body, r'(?i)\\b(gas prices?|price of gas)\\b') THEN 1 ELSE 0 END) * 10000 / COUNT(*) AS per\nFROM (\n SELECT\n body,\n subreddit\n FROM\n TABLE_QUERY([fh-bigquery:reddit_comments], 'regexp_match(table_id, \"^(2016|2015_(0[7-9]|1[0-2]))\")')) comments\nJOIN\n [subreddits.cities] cities\nON\n comments.subreddit = cities.subreddit\nJOIN\n [fh-bigquery:freebase20140119.triples_lang_en] aliases\nON\n aliases.sub = cities.mid\nWHERE\n (aliases.pred = '/common/topic/alias'\n OR aliases.pred = '/type/object/name')\n AND aliases.obj = 'userInput'\nGROUP BY\n cities.city\nORDER BY\n per DESC IGNORE CASE\n",
"databases": "Reddit",
"units": "Comments per 10,000",
"hasPassingInputQuery": true,
"rowsSearched": 532396361,
"warnings": {}
},
{
"top10": [
{
"label": "Ohio",
"number": 3.757099169943207
},
{
"label": "New York",
"number": 0.688298918387414
},
{
"label": "Wisconsin",
"number": 0.3997668026984259
},
{
"label": "Idaho",
"number": 0.3671970624235006
},
{
"label": "Arizona",
"number": 0.14527845036319612
},
{
"label": "South Carolina",
"number": 0.1439366678661389
},
{
"label": "Pennsylvania",
"number": 0.14250089063056645
},
{
"label": "Florida",
"number": 0.1407318053880177
},
{
"label": "Tennessee",
"number": 0.13003901170351106
},
{
"label": "North Dakota",
"number": 0.12562814070351758
}
],
"answer": "Ohio",
"answerValue": 3.757099169943207,
"question": "Which state's subreddit mentioned John Kasich the most in the past year?",
"rowIndex": 218,
"approved": true,
"params": {
"regex": "(?i)\\b(kasich)\\b",
"per": 100
},
"sql": "\nSELECT\n states.state AS state,\n SUM(CASE WHEN REGEXP_MATCH(body, r'(?i)\\b(kasich)\\b') THEN 1 ELSE 0 END) * 100 / COUNT(*) AS per\nFROM (\n SELECT\n *\n FROM\n TABLE_QUERY([fh-bigquery:reddit_comments], 'regexp_match(table_id, \"^2016\")')) comments\nJOIN\n [subreddits.states] states\nON\n states.subreddit = comments.subreddit\nGROUP BY\n state\nORDER BY\n per DESC IGNORE CASE\n",
"inputSQL": "\nSELECT\n states.state AS state,\n SUM(CASE WHEN REGEXP_MATCH(body, r'(?i)\\b(kasich)\\b') THEN 1 ELSE 0 END) * 100 / COUNT(*) AS per\nFROM (\n SELECT\n *\n FROM\n TABLE_QUERY([fh-bigquery:reddit_comments], 'regexp_match(table_id, \"^2016\")')) comments\nJOIN\n [subreddits.states] states\nON\n states.subreddit = comments.subreddit\nWHERE\n states.state = 'userInput'\nGROUP BY\n state\nORDER BY\n per DESC IGNORE CASE\n",
"databases": "Reddit",
"units": "% of Comments",
"hasPassingInputQuery": true,
"rowsSearched": 185166080,
"warnings": {}
},
{
"top10": [
{
"label": "United States",
"number": 129654
},
{
"label": "Japan",
"number": 50486
},
{
"label": "Russia",
"number": 48841
},
{
"label": "United Kingdom",
"number": 37946
},
{
"label": "Pakistan",
"number": 28558
},
{
"label": "Taiwan",
"number": 26521
},
{
"label": "Australia",
"number": 23346
},
{
"label": "France",
"number": 23058
},
{
"label": "South Korea",
"number": 22825
},
{
"label": "Philippines",
"number": 21712
}
],
"answer": "United States",
"answerValue": 129654,
"question": "Which country was mentioned with China the most last year?",
"rowIndex": 265,
"approved": true,
"params": {
"country": "China",
"year": 2015
},
"sql": "\nSELECT\n other.country,\n COUNT(*) AS total\nFROM (\n SELECT\n ccode,\n other\n FROM (\n SELECT\n Actor1Code AS ccode,\n Actor2Code AS other,\n Year\n FROM\n [gdelt-bq:full.events]) cc1,\n (\n SELECT\n Actor1Code AS other,\n Actor2Code AS ccode,\n Year\n FROM\n [gdelt-bq:full.events]) cc2\n WHERE\n Year = 2015) events\nJOIN\n [gdelt-bq:extra.countryinfo] country\nON\n country.iso3 = events.ccode\nJOIN\n [gdelt-bq:extra.countryinfo] other\nON\n events.other = other.iso3\nWHERE\n country.country = 'China' \nGROUP BY\n country.country,\n other.country\nORDER BY\n total DESC\nIGNORE CASE\n",
"inputSQL": "\nSELECT\n other.country,\n COUNT(UNIQUE(events.id)) AS total\nFROM (\n SELECT\n ccode,\n other,\n id\n FROM (\n SELECT\n Actor1Code AS ccode,\n Actor2Code AS other,\n Year,\n GLOBALEVENTID as id\n FROM\n [gdelt-bq:full.events]) cc1,\n (\n SELECT\n Actor1Code AS other,\n Actor2Code AS ccode,\n Year,\n GLOBALEVENTID as id\n FROM\n [gdelt-bq:full.events]) cc2\n WHERE\n Year = 2015) events\nJOIN\n [gdelt-bq:extra.countryinfo] country\nON\n country.iso3 = events.ccode\nJOIN\n [gdelt-bq:extra.countryinfo] other\nON\n events.other = other.iso3\nJOIN (\n SELECT\n *\n FROM (\n SELECT\n sub,\n obj AS alias,\n CASE WHEN obj = 'userInput' THEN 1 ELSE 0 END AS input,\n SUM(input) OVER (PARTITION BY sub) AS input_alias\n FROM\n [fh-bigquery:freebase20140119.triples_lang_en]\n WHERE\n (pred = '/common/topic/alias'\n OR pred = '/type/object/name'))\n WHERE\n input_alias > 0) aliases\nON\n aliases.alias = other.country\nWHERE\n country.country = 'China' \nGROUP BY\n country.country,\n other.country\nORDER BY\n total DESC\nIGNORE CASE\n",
"databases": "GDELT",
"units": "Events",
"hasPassingInputQuery": true,
"rowsSearched": 761334193,
"warnings": {}
},
{
"top10": [
{
"label": "South Korea",
"number": 30196
},
{
"label": "United States",
"number": 13973
},
{
"label": "China",
"number": 13124
},
{
"label": "Russia",
"number": 4929
},
{
"label": "Japan",
"number": 4468
},
{
"label": "Iran",
"number": 3459
},
{
"label": "Canada",
"number": 1661
},
{
"label": "United Kingdom",
"number": 781
},
{
"label": "Cuba",
"number": 630
},
{
"label": "Pakistan",
"number": 521
}
],
"answer": "South Korea",
"answerValue": 30196,
"question": "Which country was mentioned with North Korea the most last year?",
"rowIndex": 264,
"approved": true,
"params": {
"country": "North Korea",
"year": 2015
},
"sql": "\nSELECT\n other.country,\n COUNT(*) AS total\nFROM (\n SELECT\n ccode,\n other\n FROM (\n SELECT\n Actor1Code AS ccode,\n Actor2Code AS other,\n Year\n FROM\n [gdelt-bq:full.events]) cc1,\n (\n SELECT\n Actor1Code AS other,\n Actor2Code AS ccode,\n Year\n FROM\n [gdelt-bq:full.events]) cc2\n WHERE\n Year = 2015) events\nJOIN\n [gdelt-bq:extra.countryinfo] country\nON\n country.iso3 = events.ccode\nJOIN\n [gdelt-bq:extra.countryinfo] other\nON\n events.other = other.iso3\nWHERE\n country.country = 'North Korea' \nGROUP BY\n country.country,\n other.country\nORDER BY\n total DESC\nIGNORE CASE\n",
"inputSQL": "\nSELECT\n other.country,\n COUNT(UNIQUE(events.id)) AS total\nFROM (\n SELECT\n ccode,\n other,\n id\n FROM (\n SELECT\n Actor1Code AS ccode,\n Actor2Code AS other,\n Year,\n GLOBALEVENTID as id\n FROM\n [gdelt-bq:full.events]) cc1,\n (\n SELECT\n Actor1Code AS other,\n Actor2Code AS ccode,\n Year,\n GLOBALEVENTID as id\n FROM\n [gdelt-bq:full.events]) cc2\n WHERE\n Year = 2015) events\nJOIN\n [gdelt-bq:extra.countryinfo] country\nON\n country.iso3 = events.ccode\nJOIN\n [gdelt-bq:extra.countryinfo] other\nON\n events.other = other.iso3\nJOIN (\n SELECT\n *\n FROM (\n SELECT\n sub,\n obj AS alias,\n CASE WHEN obj = 'userInput' THEN 1 ELSE 0 END AS input,\n SUM(input) OVER (PARTITION BY sub) AS input_alias\n FROM\n [fh-bigquery:freebase20140119.triples_lang_en]\n WHERE\n (pred = '/common/topic/alias'\n OR pred = '/type/object/name'))\n WHERE\n input_alias > 0) aliases\nON\n aliases.alias = other.country\nWHERE\n country.country = 'North Korea' \nGROUP BY\n country.country,\n other.country\nORDER BY\n total DESC\nIGNORE CASE\n",
"databases": "GDELT",
"units": "Events",
"hasPassingInputQuery": true,
"rowsSearched": 761310688,
"warnings": {}
},
{
"top10": [
{
"label": "The New York Times",
"number": 647317
},
{
"label": "The Guardian",
"number": 417426
},
{
"label": "USA Today",
"number": 262968
},
{
"label": "The Washington Post",
"number": 210146
},
{
"label": "The Times",
"number": 200101
},
{
"label": "The Daily Telegraph",
"number": 183398
},
{
"label": "The Economist",
"number": 177357
},
{
"label": "The Wall Street Journal",
"number": 169922
},
{
"label": "Le Monde",
"number": 144782
},
{
"label": "The Times of India",
"number": 130490
}
],
"answer": "The New York Times",
"answerValue": 647317,
"question": "What newspaper had the most popular Wikipedia page in the past month?",
"rowIndex": 255,
"approved": true,
"params": {
"freebase_tag": "newspapers",
"substring": true
},
"sql": "\nSELECT\n topic_wiki.name AS name,\n SUM(requests) AS total\nFROM (\n SELECT\n topic.mid AS mid,\n topic.name AS name,\n REPLACE(REPLACE(wiki.title, '$0028', '('), '$0029', ')') AS title\n FROM (\n SELECT\n mid,\n name\n FROM\n [freebase_tags.newspapers]) topic\n JOIN (\n SELECT\n REGEXP_REPLACE(REGEXP_REPLACE(obj, '/wikipedia/en/', ''), '/wikipedia/id/', '') AS title,\n sub AS mid\n FROM\n [fh-bigquery:freebase20140119.triples_nolang]\n WHERE\n obj CONTAINS '/wikipedia/en'\n OR obj CONTAINS '/wikipedia/id'\n AND pred = '/type/object/key') wiki\n ON\n wiki.mid = topic.mid) topic_wiki\nJOIN\n [fh-bigquery:wikipedia.pagecounts_201605] views\nON\n views.title = topic_wiki.title\nGROUP BY\n mid,\n name\nORDER BY\n total DESC\nLIMIT\n 10\nIGNORE CASE\n",
"inputSQL": "\nSELECT\n topic_wiki.name AS name,\n SUM(requests) AS total\nFROM (\n SELECT\n topic.mid AS mid,\n topic.name AS name,\n REPLACE(REPLACE(wiki.title, '$0028', '('), '$0029', ')') AS title\n FROM (\n SELECT\n mid,\n name\n FROM\n [freebase_tags.newspapers]) topic\n JOIN (\n SELECT\n REGEXP_REPLACE(REGEXP_REPLACE(obj, '/wikipedia/en/', ''), '/wikipedia/id/', '') AS title,\n sub AS mid\n FROM\n [fh-bigquery:freebase20140119.triples_nolang]\n WHERE\n obj CONTAINS '/wikipedia/en'\n OR obj CONTAINS '/wikipedia/id'\n AND pred = '/type/object/key') wiki\n ON\n wiki.mid = topic.mid\n WHERE\n topic.name CONTAINS 'userInput') topic_wiki\nJOIN\n [fh-bigquery:wikipedia.pagecounts_201605] views\nON\n views.title = topic_wiki.title\nGROUP BY\n mid,\n name\nORDER BY\n total DESC\nLIMIT\n 10\nIGNORE CASE\n",
"databases": "Wikipedia",
"units": "Views",
"hasPassingInputQuery": true,
"rowsSearched": 6309204805,
"warnings": {}
},
{
"top10": [
{
"label": "Time",
"number": 467643
},
{
"label": "Rolling Stone",
"number": 316895
},
{
"label": "Playboy",
"number": 214295
},
{
"label": "De Stijl",
"number": 178628
},
{
"label": "The Economist",
"number": 177357
},
{
"label": "TWICE",
"number": 171716
},
{
"label": "Der Spiegel",
"number": 154315
},
{
"label": "Life Magazine",
"number": 154106
},
{
"label": "Billboard",
"number": 145264
},
{
"label": "Nature",
"number": 133762
}
],
"answer": "Time",
"answerValue": 467643,
"question": "What magazine had the most popular Wikipedia page in the past month?",
"rowIndex": 254,
"approved": true,
"params": {
"freebase_tag": "magazines",
"substring": true
},
"sql": "\nSELECT\n topic_wiki.name AS name,\n SUM(requests) AS total\nFROM (\n SELECT\n topic.mid AS mid,\n topic.name AS name,\n REPLACE(REPLACE(wiki.title, '$0028', '('), '$0029', ')') AS title\n FROM (\n SELECT\n mid,\n name\n FROM\n [freebase_tags.magazines]) topic\n JOIN (\n SELECT\n REGEXP_REPLACE(REGEXP_REPLACE(obj, '/wikipedia/en/', ''), '/wikipedia/id/', '') AS title,\n sub AS mid\n FROM\n [fh-bigquery:freebase20140119.triples_nolang]\n WHERE\n obj CONTAINS '/wikipedia/en'\n OR obj CONTAINS '/wikipedia/id'\n AND pred = '/type/object/key') wiki\n ON\n wiki.mid = topic.mid) topic_wiki\nJOIN\n [fh-bigquery:wikipedia.pagecounts_201605] views\nON\n views.title = topic_wiki.title\nGROUP BY\n mid,\n name\nORDER BY\n total DESC\nLIMIT\n 10\nIGNORE CASE\n",
"inputSQL": "\nSELECT\n topic_wiki.name AS name,\n SUM(requests) AS total\nFROM (\n SELECT\n topic.mid AS mid,\n topic.name AS name,\n REPLACE(REPLACE(wiki.title, '$0028', '('), '$0029', ')') AS title\n FROM (\n SELECT\n mid,\n name\n FROM\n [freebase_tags.magazines]) topic\n JOIN (\n SELECT\n REGEXP_REPLACE(REGEXP_REPLACE(obj, '/wikipedia/en/', ''), '/wikipedia/id/', '') AS title,\n sub AS mid\n FROM\n [fh-bigquery:freebase20140119.triples_nolang]\n WHERE\n obj CONTAINS '/wikipedia/en'\n OR obj CONTAINS '/wikipedia/id'\n AND pred = '/type/object/key') wiki\n ON\n wiki.mid = topic.mid\n WHERE\n topic.name CONTAINS 'userInput') topic_wiki\nJOIN\n [fh-bigquery:wikipedia.pagecounts_201605] views\nON\n views.title = topic_wiki.title\nGROUP BY\n mid,\n name\nORDER BY\n total DESC\nLIMIT\n 10\nIGNORE CASE\n",
"databases": "Wikipedia",
"units": "Views",
"hasPassingInputQuery": true,
"rowsSearched": 6295636324,
"warnings": {}
},
{
"top10": [
{
"label": "Nauru",
"number": 3.2859832277939414
},
{
"label": "Vanuatu",
"number": 2.6664702416028283
},
{
"label": "Botswana",
"number": 2.610099951988128
},
{
"label": "Zambia",
"number": 2.593852948629765
},
{
"label": "Burundi",
"number": 2.5023882814987792
},
{
"label": "Estonia",
"number": 2.423284604240748
},
{
"label": "Ghana",
"number": 2.378803769013764
},
{
"label": "Guinea-Bissau",
"number": 2.261221734728316
},
{
"label": "Australia",
"number": 2.257467803139979
},
{
"label": "New Zealand",
"number": 2.250073006911321
}
],
"answer": "Nauru",
"answerValue": 3.2859832277939414,
"question": "Officials of which country made the most pessimistic statements last year?",
"rowIndex": 291,
"approved": true,
"params": {
"codes": "('012')",
"relation": "Actor1CountryCode",
"year": 2015,
"per": 100,
"min_events": 1
},
"sql": "\nSELECT\n codes.country,\n SUM(CASE WHEN events.EventCode IN ('012') THEN 1 ELSE 0 END) * 100 / COUNT(*) as per,\n COUNT(*) as total\nFROM\n [gdelt-bq:full.events] events\nJOIN\n [gdelt-bq:extra.countryinfo] codes\nON\n events.Actor1CountryCode = codes.iso3\nWHERE\n Year = 2015\nGROUP BY\n codes.country\nHAVING\n total > 1\nORDER BY\n per DESC\nIGNORE CASE\n",
"inputSQL": "\nSELECT\n codes.country,\n SUM(CASE WHEN events.EventCode IN ('012') THEN 1 ELSE 0 END) * 100 / COUNT(*) as per,\n COUNT(*) as total\nFROM\n [gdelt-bq:full.events] events\nJOIN\n [gdelt-bq:extra.countryinfo] codes\nON\n events.Actor1CountryCode = codes.iso3\nJOIN (\n SELECT\n *\n FROM (\n SELECT\n sub,\n obj AS alias,\n CASE WHEN obj = 'userInput' THEN 1 ELSE 0 END AS input,\n SUM(input) OVER (PARTITION BY sub) AS input_alias\n FROM\n [fh-bigquery:freebase20140119.triples_lang_en]\n WHERE\n (pred = '/common/topic/alias'\n OR pred = '/type/object/name'))\n WHERE\n input_alias > 0) aliases\nON\n aliases.alias = codes.country\nWHERE\n Year = 2015\nGROUP BY\n codes.country\nHAVING\n total > 1\nORDER BY\n per DESC\nIGNORE CASE\n",
"databases": "GDELT",
"units": "% of Events",
"hasPassingInputQuery": true,
"rowsSearched": 380821141,
"warnings": {}
},
{
"top10": [
{
"label": "Antigua and Barbuda",
"number": 3.7137191274562826
},
{
"label": "Luxembourg",
"number": 3.6345156315715688
},
{
"label": "Georgia",
"number": 3.5460992907801416
},
{
"label": "Comoros",
"number": 3.4870641169853767
},
{
"label": "Papua New Guinea",
"number": 3.439406627587032
},
{
"label": "Anguilla",
"number": 3.4033309196234613
},
{
"label": "Madagascar",
"number": 3.1140350877192984
},
{
"label": "Cook Islands",
"number": 3.056768558951965
},
{
"label": "Trinidad and Tobago",
"number": 3.055904522613065
},
{
"label": "Burkina Faso",
"number": 2.9885057471264367
}
],
"answer": "Antigua and Barbuda",
"answerValue": 3.7137191274562826,
"question": "Officials of which country made the most optimistic statements last year?",
"rowIndex": 292,
"approved": true,
"params": {
"codes": "('013')",
"relation": "Actor1CountryCode",
"year": 2015,
"per": 100,
"min_events": 1
},
"sql": "\nSELECT\n codes.country,\n SUM(CASE WHEN events.EventCode IN ('013') THEN 1 ELSE 0 END) * 100 / COUNT(*) as per,\n COUNT(*) as total\nFROM\n [gdelt-bq:full.events] events\nJOIN\n [gdelt-bq:extra.countryinfo] codes\nON\n events.Actor1CountryCode = codes.iso3\nWHERE\n Year = 2015\nGROUP BY\n codes.country\nHAVING\n total > 1\nORDER BY\n per DESC\nIGNORE CASE\n",
"inputSQL": "\nSELECT\n codes.country,\n SUM(CASE WHEN events.EventCode IN ('013') THEN 1 ELSE 0 END) * 100 / COUNT(*) as per,\n COUNT(*) as total\nFROM\n [gdelt-bq:full.events] events\nJOIN\n [gdelt-bq:extra.countryinfo] codes\nON\n events.Actor1CountryCode = codes.iso3\nJOIN (\n SELECT\n *\n FROM (\n SELECT\n sub,\n obj AS alias,\n CASE WHEN obj = 'userInput' THEN 1 ELSE 0 END AS input,\n SUM(input) OVER (PARTITION BY sub) AS input_alias\n FROM\n [fh-bigquery:freebase20140119.triples_lang_en]\n WHERE\n (pred = '/common/topic/alias'\n OR pred = '/type/object/name'))\n WHERE\n input_alias > 0) aliases\nON\n aliases.alias = codes.country\nWHERE\n Year = 2015\nGROUP BY\n codes.country\nHAVING\n total > 1\nORDER BY\n per DESC\nIGNORE CASE\n",
"databases": "GDELT",
"units": "% of Events",
"hasPassingInputQuery": true,
"rowsSearched": 380821141,
"warnings": {}
},
{
"top10": [
{
"label": "Benin",
"number": 4.09982174688057
},
{
"label": "Moldova",
"number": 2.610948258231645
},
{
"label": "Niger",
"number": 2.578350744609913
},
{
"label": "Burkina Faso",
"number": 2.257799671592775
},
{
"label": "Cameroon",
"number": 2.1497738949262497
},
{
"label": "Guinea-Bissau",
"number": 2.0587242659466756
},
{
"label": "Georgia",
"number": 1.9250253292806485
},
{
"label": "Zambia",
"number": 1.8951598305759694
},
{
"label": "Swaziland",
"number": 1.8194380469829572
},
{
"label": "India",
"number": 1.781373556395753
}
],
"answer": "Benin",
"answerValue": 4.09982174688057,
"question": "Which country made the most demands for economic, military and/or humanitarian aid?",
"rowIndex": 305,
"approved": true,
"params": {
"codes": "('100','101','1011','1012','1013','1014','102','103','1031','1032','1033', '1034', '104', '1041', '1042', '1043', '1044', '105', '1051', '1052', '1053', '1054', '1055', '1056', '106', '107', '108')",
"relation": "Actor1CountryCode",
"year": 2015,
"per": 100,
"min_events": 1
},
"sql": "\nSELECT\n codes.country,\n SUM(CASE WHEN events.EventCode IN ('100','101','1011','1012','1013','1014','102','103','1031','1032','1033', '1034', '104', '1041', '1042', '1043', '1044', '105', '1051', '1052', '1053', '1054', '1055', '1056', '106', '107', '108') THEN 1 ELSE 0 END) * 100 / COUNT(*) as per,\n COUNT(*) as total\nFROM\n [gdelt-bq:full.events] events\nJOIN\n [gdelt-bq:extra.countryinfo] codes\nON\n events.Actor1CountryCode = codes.iso3\nWHERE\n Year = 2015\nGROUP BY\n codes.country\nHAVING\n total > 1\nORDER BY\n per DESC\nIGNORE CASE\n",
"inputSQL": "\nSELECT\n codes.country,\n SUM(CASE WHEN events.EventCode IN ('100','101','1011','1012','1013','1014','102','103','1031','1032','1033', '1034', '104', '1041', '1042', '1043', '1044', '105', '1051', '1052', '1053', '1054', '1055', '1056', '106', '107', '108') THEN 1 ELSE 0 END) * 100 / COUNT(*) as per,\n COUNT(*) as total\nFROM\n [gdelt-bq:full.events] events\nJOIN\n [gdelt-bq:extra.countryinfo] codes\nON\n events.Actor1CountryCode = codes.iso3\nJOIN (\n SELECT\n *\n FROM (\n SELECT\n sub,\n obj AS alias,\n CASE WHEN obj = 'userInput' THEN 1 ELSE 0 END AS input,\n SUM(input) OVER (PARTITION BY sub) AS input_alias\n FROM\n [fh-bigquery:freebase20140119.triples_lang_en]\n WHERE\n (pred = '/common/topic/alias'\n OR pred = '/type/object/name'))\n WHERE\n input_alias > 0) aliases\nON\n aliases.alias = codes.country\nWHERE\n Year = 2015\nGROUP BY\n codes.country\nHAVING\n total > 1\nORDER BY\n per DESC\nIGNORE CASE\n",
"databases": "GDELT",
"units": "% of Events",
"hasPassingInputQuery": true,
"rowsSearched": 380821141,
"warnings": {}
},
{
"top10": [
{
"label": "Leonardo da Vinci",
"number": 4030785
},
{
"label": "Steve Jobs",
"number": 2148210
},
{
"label": "Nikola Tesla",
"number": 1761161
},
{
"label": "Elon Musk",
"number": 1405763
},
{
"label": "Thomas Edison",
"number": 1140822
},
{
"label": "Benjamin Franklin",
"number": 1056492
},
{
"label": "Thomas Jefferson",
"number": 968988
},
{
"label": "Henry Ford",
"number": 648404
},
{
"label": "Blaise Pascal",
"number": 623118
},
{
"label": "Alexander Graham Bell",
"number": 619397
}
],
"answer": "Leonardo da Vinci",
"answerValue": 4030785,
"question": "Who was the most popular inventor in the past month?",
"rowIndex": 285,
"approved": true,
"params": {
"freebase_tag": "inventors",
"substring": true
},
"sql": "\nSELECT\n topic_wiki.name AS name,\n SUM(requests) AS total\nFROM (\n SELECT\n topic.mid AS mid,\n topic.name AS name,\n REPLACE(REPLACE(wiki.title, '$0028', '('), '$0029', ')') AS title\n FROM (\n SELECT\n mid,\n name\n FROM\n [freebase_tags.inventors]) topic\n JOIN (\n SELECT\n REGEXP_REPLACE(REGEXP_REPLACE(obj, '/wikipedia/en/', ''), '/wikipedia/id/', '') AS title,\n sub AS mid\n FROM\n [fh-bigquery:freebase20140119.triples_nolang]\n WHERE\n obj CONTAINS '/wikipedia/en'\n OR obj CONTAINS '/wikipedia/id'\n AND pred = '/type/object/key') wiki\n ON\n wiki.mid = topic.mid) topic_wiki\nJOIN\n [fh-bigquery:wikipedia.pagecounts_201605] views\nON\n views.title = topic_wiki.title\nGROUP BY\n mid,\n name\nORDER BY\n total DESC\nLIMIT\n 10\nIGNORE CASE\n",
"inputSQL": "\nSELECT\n topic_wiki.name AS name,\n SUM(requests) AS total\nFROM (\n SELECT\n topic.mid AS mid,\n topic.name AS name,\n REPLACE(REPLACE(wiki.title, '$0028', '('), '$0029', ')') AS title\n FROM (\n SELECT\n mid,\n name\n FROM\n [freebase_tags.inventors]) topic\n JOIN (\n SELECT\n REGEXP_REPLACE(REGEXP_REPLACE(obj, '/wikipedia/en/', ''), '/wikipedia/id/', '') AS title,\n sub AS mid\n FROM\n [fh-bigquery:freebase20140119.triples_nolang]\n WHERE\n obj CONTAINS '/wikipedia/en'\n OR obj CONTAINS '/wikipedia/id'\n AND pred = '/type/object/key') wiki\n ON\n wiki.mid = topic.mid\n WHERE\n topic.name CONTAINS 'userInput') topic_wiki\nJOIN\n [fh-bigquery:wikipedia.pagecounts_201605] views\nON\n views.title = topic_wiki.title\nGROUP BY\n mid,\n name\nORDER BY\n total DESC\nLIMIT\n 10\nIGNORE CASE\n",
"databases": "Wikipedia",
"units": "Views",
"hasPassingInputQuery": true,
"rowsSearched": 6290706111,
"warnings": {}
},
{
"top10": [
{
"label": "Wi-Fi",
"number": 1572500
},
{
"label": "USB",
"number": 1517236
},
{
"label": "HDMI",
"number": 808745
},
{
"label": "RAID",
"number": 722376
},
{
"label": "AK-47",
"number": 713080
},
{
"label": "Internet Protocol",
"number": 522558
},
{
"label": "ARPANET",
"number": 503345
},
{
"label": "Light-emitting diode",
"number": 430364
},
{
"label": "Periodic Table",
"number": 393780
},
{
"label": "Laser",
"number": 392024
}
],
"answer": "Wi-Fi",
"answerValue": 1572500,
"question": "What invention had the most pageviews in the past month?",
"rowIndex": 328,
"approved": true,
"params": {
"freebase_tag": "invention",
"substring": true
},
"sql": "\nSELECT\n topic_wiki.name AS name,\n SUM(requests) AS total\nFROM (\n SELECT\n topic.mid AS mid,\n topic.name AS name,\n REPLACE(REPLACE(wiki.title, '$0028', '('), '$0029', ')') AS title\n FROM (\n SELECT\n mid,\n name\n FROM\n [freebase_tags.invention]) topic\n JOIN (\n SELECT\n REGEXP_REPLACE(REGEXP_REPLACE(obj, '/wikipedia/en/', ''), '/wikipedia/id/', '') AS title,\n sub AS mid\n FROM\n [fh-bigquery:freebase20140119.triples_nolang]\n WHERE\n obj CONTAINS '/wikipedia/en'\n OR obj CONTAINS '/wikipedia/id'\n AND pred = '/type/object/key') wiki\n ON\n wiki.mid = topic.mid) topic_wiki\nJOIN\n [fh-bigquery:wikipedia.pagecounts_201605] views\nON\n views.title = topic_wiki.title\nGROUP BY\n mid,\n name\nORDER BY\n total DESC\nLIMIT\n 10\nIGNORE CASE\n",
"inputSQL": "\nSELECT\n topic_wiki.name AS name,\n SUM(requests) AS total\nFROM (\n SELECT\n topic.mid AS mid,\n topic.name AS name,\n REPLACE(REPLACE(wiki.title, '$0028', '('), '$0029', ')') AS title\n FROM (\n SELECT\n mid,\n name\n FROM\n [freebase_tags.invention]) topic\n JOIN (\n SELECT\n REGEXP_REPLACE(REGEXP_REPLACE(obj, '/wikipedia/en/', ''), '/wikipedia/id/', '') AS title,\n sub AS mid\n FROM\n [fh-bigquery:freebase20140119.triples_nolang]\n WHERE\n obj CONTAINS '/wikipedia/en'\n OR obj CONTAINS '/wikipedia/id'\n AND pred = '/type/object/key') wiki\n ON\n wiki.mid = topic.mid\n WHERE\n topic.name CONTAINS 'userInput') topic_wiki\nJOIN\n [fh-bigquery:wikipedia.pagecounts_201605] views\nON\n views.title = topic_wiki.title\nGROUP BY\n mid,\n name\nORDER BY\n total DESC\nLIMIT\n 10\nIGNORE CASE\n",
"databases": "Wikipedia",
"units": "Views",
"hasPassingInputQuery": true,
"rowsSearched": 6297667619,
"warnings": {}
},
{
"top10": [
{
"label": "China",
"number": 6222
},
{
"label": "Israel",
"number": 5482
},
{
"label": "Russia",
"number": 4970
},
{
"label": "Germany",
"number": 4354
},
{
"label": "Japan",
"number": 3533
},
{
"label": "India",
"number": 2435
},
{
"label": "Iran",
"number": 2435
},
{
"label": "Iraq",
"number": 2298
},
{
"label": "Venezuela",
"number": 2073
},
{
"label": "Canada",
"number": 1948
}
],
"answer": "China",
"answerValue": 6222,
"question": "Which country was mentioned in /r/worldnews the most in the past month?",
"rowIndex": 71,
"approved": true,
"params": {
"freebase_tag": "countries",
"subreddit": "worldnews"
},
"sql": "\nSELECT\n topic.name,\n COUNT(UNIQUE(comments.id)) AS total\nFROM\n [freebase_tags.countries] topic\nCROSS JOIN (\n SELECT\n id,\n body\n FROM\n [fh-bigquery:reddit_comments.2016_05]\n WHERE\n subreddit = 'worldnews') comments\nWHERE\n comments.body CONTAINS CONCAT(' ', topic.name, ' ')\nGROUP BY\n topic.name,\nORDER BY\n total DESC IGNORE CASE\n",
"inputSQL": "\nSELECT\n topic.name,\n COUNT(UNIQUE(comments.id)) AS total\nFROM (\n SELECT\n *\n FROM\n [freebase_tags.countries]\n WHERE\n name = 'userInput') topic\nCROSS JOIN (\n SELECT\n id,\n body\n FROM\n [fh-bigquery:reddit_comments.2016_05]\n WHERE\n subreddit = 'worldnews') comments\nWHERE\n comments.body CONTAINS CONCAT(' ', topic.name, ' ')\nGROUP BY\n topic.name,\nORDER BY\n total DESC IGNORE CASE\n",
"databases": "Reddit",
"units": "Comments",
"hasPassingInputQuery": true,
"rowsSearched": 65867932,
"warnings": {}
},
{
"top10": [
{
"label": "China",
"number": 1173
},
{
"label": "Canada",
"number": 865
},
{
"label": "Japan",
"number": 842
},
{
"label": "Germany",
"number": 729
},
{
"label": "Iraq",
"number": 633
},
{
"label": "Mexico",
"number": 589
},
{
"label": "Israel",
"number": 512
},
{
"label": "Russia",
"number": 500
},
{
"label": "India",
"number": 391
},
{
"label": "France",
"number": 373
}
],
"answer": "China",
"answerValue": 1173,
"question": "Which country was discussed in /r/news the most in the past month?",
"rowIndex": 70,
"approved": true,
"params": {
"freebase_tag": "countries",
"subreddit": "news"
},
"sql": "\nSELECT\n topic.name,\n COUNT(UNIQUE(comments.id)) AS total\nFROM\n [freebase_tags.countries] topic\nCROSS JOIN (\n SELECT\n id,\n body\n FROM\n [fh-bigquery:reddit_comments.2016_05]\n WHERE\n subreddit = 'news') comments\nWHERE\n comments.body CONTAINS CONCAT(' ', topic.name, ' ')\nGROUP BY\n topic.name,\nORDER BY\n total DESC IGNORE CASE\n",
"inputSQL": "\nSELECT\n topic.name,\n COUNT(UNIQUE(comments.id)) AS total\nFROM (\n SELECT\n *\n FROM\n [freebase_tags.countries]\n WHERE\n name = 'userInput') topic\nCROSS JOIN (\n SELECT\n id,\n body\n FROM\n [fh-bigquery:reddit_comments.2016_05]\n WHERE\n subreddit = 'news') comments\nWHERE\n comments.body CONTAINS CONCAT(' ', topic.name, ' ')\nGROUP BY\n topic.name,\nORDER BY\n total DESC IGNORE CASE\n",
"databases": "Reddit",
"units": "Mentions",
"hasPassingInputQuery": true,
"rowsSearched": 65805944,
"warnings": {}
},
{
"top10": [
{
"label": "Italy",
"number": 207
},
{
"label": "Thailand",
"number": 174
},
{
"label": "Japan",
"number": 163
},
{
"label": "Canada",
"number": 145
},
{
"label": "Vietnam",
"number": 140
},
{
"label": "Mexico",
"number": 128
},
{
"label": "China",
"number": 116
},
{
"label": "Germany",
"number": 104
},
{
"label": "Spain",
"number": 104
},
{
"label": "France",
"number": 97
}
],
"answer": "Italy",
"answerValue": 207,
"question": "Which country was discussed the most in /r/travel in the past month?",
"rowIndex": 59,
"approved": true,
"params": {
"freebase_tag": "countries",
"subreddit": "travel"
},
"sql": "\nSELECT\n topic.name,\n COUNT(UNIQUE(comments.id)) AS total\nFROM\n [freebase_tags.countries] topic\nCROSS JOIN (\n SELECT\n id,\n body\n FROM\n [fh-bigquery:reddit_comments.2016_05]\n WHERE\n subreddit = 'travel') comments\nWHERE\n comments.body CONTAINS CONCAT(' ', topic.name, ' ')\nGROUP BY\n topic.name,\nORDER BY\n total DESC IGNORE CASE\n",
"inputSQL": "\nSELECT\n topic.name,\n COUNT(UNIQUE(comments.id)) AS total\nFROM (\n SELECT\n *\n FROM\n [freebase_tags.countries]\n WHERE\n name = 'userInput') topic\nCROSS JOIN (\n SELECT\n id,\n body\n FROM\n [fh-bigquery:reddit_comments.2016_05]\n WHERE\n subreddit = 'travel') comments\nWHERE\n comments.body CONTAINS CONCAT(' ', topic.name, ' ')\nGROUP BY\n topic.name,\nORDER BY\n total DESC IGNORE CASE\n",
"databases": "Reddit",
"units": "Mentions",
"hasPassingInputQuery": true,
"rowsSearched": 65246482,
"warnings": {}
},
{
"top10": [
{
"label": "San Francisco",
"number": 1565
},
{
"label": "New York City",
"number": 837
},
{
"label": "Detroit",
"number": 346
},
{
"label": "Austin",
"number": 282
},
{
"label": "Berlin",
"number": 250
},
{
"label": "Buffalo",
"number": 198
},
{
"label": "Washington, D.C.",
"number": 184
},
{
"label": "Charlotte",
"number": 173
},
{
"label": "Seattle",
"number": 161
},
{
"label": "Atlanta",
"number": 160
}
],
"answer": "San Francisco",
"answerValue": 1565,
"question": "Which city was mentioned the most in /r/news in the past month?",
"rowIndex": 99,
"approved": true,
"params": {
"freebase_tag": "cities",
"subreddit": "news",
"top_wiki": 75,
"num_common_words": 2000
},
"sql": "\nSELECT\n pop_topic.name,\n COUNT(UNIQUE(comments.id)) AS total\nFROM (\n SELECT\n topic_wiki.mid AS mid,\n topic_wiki.name AS name,\n topic_wiki.title AS title,\n SUM(requests) AS total\n FROM (\n SELECT\n topic.mid AS mid,\n topic.name AS name,\n REPLACE(REPLACE(wiki.title, '$0028', '('), '$0029', ')') AS title\n FROM (\n SELECT\n mid,\n name\n FROM\n [freebase_tags.cities]) topic\n JOIN (\n SELECT\n REGEXP_REPLACE(REGEXP_REPLACE(obj, '/wikipedia/en/', ''), '/wikipedia/id/', '') AS title,\n sub AS mid\n FROM\n [fh-bigquery:freebase20140119.triples_nolang]\n WHERE\n obj CONTAINS '/wikipedia/en'\n OR obj CONTAINS '/wikipedia/id'\n AND pred = '/type/object/key') wiki\n ON\n wiki.mid = topic.mid) topic_wiki\n JOIN\n [fh-bigquery:wikipedia.pagecounts_201605] views\n ON\n views.title = topic_wiki.title\n GROUP BY\n mid,\n name,\n title\n ORDER BY\n total DESC\n LIMIT\n 500 ) pop_topic\nJOIN (\n SELECT\n sub,\n obj AS alias\n FROM\n [fh-bigquery:freebase20140119.triples_lang_en]\n WHERE\n (pred = '/common/topic/alias'\n OR pred = '/type/object/name')\n AND obj NOT IN (\n SELECT\n word\n FROM\n [words.top10000]\n LIMIT\n 2000)) aliases\nON\n aliases.sub = pop_topic.mid\nCROSS JOIN (\n SELECT\n id,\n body\n FROM\n [fh-bigquery:reddit_comments.2016_05]\n WHERE\n subreddit = 'news') comments\nWHERE\n comments.body CONTAINS CONCAT(' ', aliases.alias, ' ')\nGROUP BY\n pop_topic.name,\nORDER BY\n total DESC IGNORE CASE\n",
"inputSQL": "\nSELECT\n pop_topic.name,\n COUNT(UNIQUE(comments.id)) AS total\nFROM (\n SELECT\n topic_wiki.mid AS mid,\n topic_wiki.name AS name,\n topic_wiki.title AS title,\n SUM(requests) AS total\n FROM (\n SELECT\n topic.mid AS mid,\n topic.name AS name,\n REPLACE(REPLACE(wiki.title, '$0028', '('), '$0029', ')') AS title\n FROM (\n SELECT\n mid,\n name\n FROM\n [freebase_tags.cities]) topic\n JOIN (\n SELECT\n REGEXP_REPLACE(REGEXP_REPLACE(obj, '/wikipedia/en/', ''), '/wikipedia/id/', '') AS title,\n sub AS mid\n FROM\n [fh-bigquery:freebase20140119.triples_nolang]\n WHERE\n obj CONTAINS '/wikipedia/en'\n OR obj CONTAINS '/wikipedia/id'\n AND pred = '/type/object/key') wiki\n ON\n wiki.mid = topic.mid) topic_wiki\n JOIN\n [fh-bigquery:wikipedia.pagecounts_201605] views\n ON\n views.title = topic_wiki.title\n GROUP BY\n mid,\n name,\n title\n ORDER BY\n total DESC\n LIMIT\n 500 ) pop_topic\nJOIN (\n SELECT\n sub,\n obj AS alias,\n CASE WHEN obj = 'userInput' THEN 1 ELSE 0 END AS input,\n SUM(input) OVER (PARTITION BY sub) AS input_alias\n FROM\n [fh-bigquery:freebase20140119.triples_lang_en]\n WHERE\n (pred = '/common/topic/alias'\n OR pred = '/type/object/name')\n AND obj NOT IN (\n SELECT\n word\n FROM\n [words.top10000]\n LIMIT\n 2000)) aliases\nON\n aliases.sub = pop_topic.mid\nCROSS JOIN (\n SELECT\n id,\n body\n FROM\n [fh-bigquery:reddit_comments.2016_05]\n WHERE\n subreddit = 'news') comments\nWHERE\n aliases.input_alias > 0\n AND comments.body CONTAINS CONCAT(' ', aliases.alias, ' ')\nGROUP BY\n pop_topic.name,\nORDER BY\n total DESC IGNORE CASE\n",
"databases": "Reddit",
"units": "Mentions",
"hasPassingInputQuery": true,
"rowsSearched": 6668641394,
"warnings": {}
},
{
"top10": [
{
"label": "Barack Obama",
"number": 10662825
},
{
"label": "Abraham Lincoln",
"number": 1811982
},
{
"label": "Bill Clinton",
"number": 1684604
},
{
"label": "Ronald Reagan",
"number": 1612824
},
{
"label": "George Washington",
"number": 1254974
},
{
"label": "Theodore Roosevelt",
"number": 1227924
},
{
"label": "Richard Nixon",
"number": 1220512
},
{
"label": "Jimmy Carter",
"number": 1025198
},
{
"label": "Thomas Jefferson",
"number": 968988
},
{
"label": "Gerald Ford",
"number": 692814
}
],
"answer": "Barack Obama",
"answerValue": 10662825,
"question": "Which U.S. President had the most popular page in the past month?",
"rowIndex": 141,
"approved": true,
"params": {
"freebase_tag": "presidents",
"substring": true
},
"sql": "\nSELECT\n topic_wiki.name AS name,\n SUM(requests) AS total\nFROM (\n SELECT\n topic.mid AS mid,\n topic.name AS name,\n REPLACE(REPLACE(wiki.title, '$0028', '('), '$0029', ')') AS title\n FROM (\n SELECT\n mid,\n name\n FROM\n [freebase_tags.presidents]) topic\n JOIN (\n SELECT\n REGEXP_REPLACE(REGEXP_REPLACE(obj, '/wikipedia/en/', ''), '/wikipedia/id/', '') AS title,\n sub AS mid\n FROM\n [fh-bigquery:freebase20140119.triples_nolang]\n WHERE\n obj CONTAINS '/wikipedia/en'\n OR obj CONTAINS '/wikipedia/id'\n AND pred = '/type/object/key') wiki\n ON\n wiki.mid = topic.mid) topic_wiki\nJOIN\n [fh-bigquery:wikipedia.pagecounts_201605] views\nON\n views.title = topic_wiki.title\nGROUP BY\n mid,\n name\nORDER BY\n total DESC\nLIMIT\n 10\nIGNORE CASE\n",
"inputSQL": "\nSELECT\n topic_wiki.name AS name,\n SUM(requests) AS total\nFROM (\n SELECT\n topic.mid AS mid,\n topic.name AS name,\n REPLACE(REPLACE(wiki.title, '$0028', '('), '$0029', ')') AS title\n FROM (\n SELECT\n mid,\n name\n FROM\n [freebase_tags.presidents]) topic\n JOIN (\n SELECT\n REGEXP_REPLACE(REGEXP_REPLACE(obj, '/wikipedia/en/', ''), '/wikipedia/id/', '') AS title,\n sub AS mid\n FROM\n [fh-bigquery:freebase20140119.triples_nolang]\n WHERE\n obj CONTAINS '/wikipedia/en'\n OR obj CONTAINS '/wikipedia/id'\n AND pred = '/type/object/key') wiki\n ON\n wiki.mid = topic.mid\n WHERE\n topic.name CONTAINS 'userInput') topic_wiki\nJOIN\n [fh-bigquery:wikipedia.pagecounts_201605] views\nON\n views.title = topic_wiki.title\nGROUP BY\n mid,\n name\nORDER BY\n total DESC\nLIMIT\n 10\nIGNORE CASE\n",
"databases": "Wikipedia",
"units": "Views",
"hasPassingInputQuery": true,
"rowsSearched": 6281508755,
"warnings": {}
},
{
"top10": [
{
"label": "Indian Institutes of Management",
"number": 20584
},
{
"label": "Harvard Business School",
"number": 18661
},
{
"label": "INSEAD",
"number": 16579
},
{
"label": "London Business School",
"number": 10035
},
{
"label": "MIT Sloan School of Management",
"number": 8177
},
{
"label": "Hult International Business School",
"number": 7610
},
{
"label": "Pentacle",
"number": 5812
},
{
"label": "Columbia Business School",
"number": 5770
},
{
"label": "Stanford Graduate School of Business",
"number": 4767
},
{
"label": "EDHEC Business School",
"number": 4616
}
],
"answer": "Indian Institutes of Management",
"answerValue": 20584,
"question": "Which business school had the most popular page on Wikipedia in the past month?",
"rowIndex": 351,
"approved": true,
"params": {
"wikidata_tag": "business_schools_clean",
"substring": true
},
"sql": "\n SELECT\n replace(school.name, \"_\", \" \") AS name,\n SUM(requests) AS total\n FROM\n [wikidata_tags.business_schools_clean] school\n JOIN EACH (\n SELECT\n title,\n requests\n FROM\n [fh-bigquery:wikipedia.pagecounts_201605]) views\n ON\n views.title = name\n GROUP BY\n name\n ORDER BY\n total DESC IGNORE case\n",
"inputSQL": "\n SELECT\n replace(school.name, \"_\", \" \") AS name,\n SUM(requests) AS total\n FROM\n [wikidata_tags.business_schools_clean] school\n JOIN EACH (\n SELECT\n title,\n requests\n FROM\n [fh-bigquery:wikipedia.pagecounts_201605]\n WHERE\n title = 'userInput'\nOR title CONTAINS CONCAT('_', replace('userInput', ' ', '_' ))\nOR title CONTAINS CONCAT(replace('userInput', ' ', '_' ), '_')\n ) views\n ON\n views.title = name\n GROUP BY\n name\n ORDER BY\n total DESC IGNORE case\n",
"databases": "Wikipedia",
"units": "Views",
"hasPassingInputQuery": true,
"rowsSearched": 10351631552,
"warnings": {}
},
{
"top10": [
{
"label": "Volbeat",
"number": 70678
},
{
"label": "Golem",
"number": 48119
},
{
"label": "Pikachu",
"number": 33360
},
{
"label": "Electrode",
"number": 13404
},
{
"label": "Dragonair",
"number": 10840
},
{
"label": "Mewtwo",
"number": 10498
},
{
"label": "MissingNo.",
"number": 10429
},
{
"label": "Kabuto",
"number": 7253
},
{
"label": "Klang",
"number": 4542
},
{
"label": "Jynx",
"number": 4359
}
],
"answer": "Volbeat",
"answerValue": 70678,
"question": "What was the most popular pokemon species in the past month?",
"rowIndex": 355,
"approved": true,
"params": {
"wikidata_tag": "pokemon_species",
"substring": true
},
"sql": "\n SELECT\n replace(school.name, \"_\", \" \") AS name,\n SUM(requests) AS total\n FROM\n [wikidata_tags.pokemon_species] school\n JOIN EACH (\n SELECT\n title,\n requests\n FROM\n [fh-bigquery:wikipedia.pagecounts_201605]) views\n ON\n views.title = name\n GROUP BY\n name\n ORDER BY\n total DESC IGNORE case\n",
"inputSQL": "\n SELECT\n replace(school.name, \"_\", \" \") AS name,\n SUM(requests) AS total\n FROM\n [wikidata_tags.pokemon_species] school\n JOIN EACH (\n SELECT\n title,\n requests\n FROM\n [fh-bigquery:wikipedia.pagecounts_201605]\n WHERE\n title = 'userInput'\nOR title CONTAINS CONCAT('_', replace('userInput', ' ', '_' ))\nOR title CONTAINS CONCAT(replace('userInput', ' ', '_' ), '_')\n ) views\n ON\n views.title = name\n GROUP BY\n name\n ORDER BY\n total DESC IGNORE case\n",
"databases": "Wikipedia",
"units": "Views",
"hasPassingInputQuery": true,
"rowsSearched": 10351631958,
"warnings": {}
},
{
"top10": [
{
"label": "Northern Ireland",
"number": 0.452088962793598
},
{
"label": "Ireland",
"number": 0.12568716816238312
},
{
"label": "Cyprus",
"number": 0.08488964346349745
},
{
"label": "Malta",
"number": 0.07745933384972889
},
{
"label": "Ukraine",
"number": 0.06533812479581835
},
{
"label": "Netherlands",
"number": 0.062421972534332085
},
{
"label": "Germany",
"number": 0.04165757473567277
},
{
"label": "Dublin",
"number": 0.03363605785401951
},
{
"label": "Greece",
"number": 0.03166287671627184
},
{
"label": "Iceland",
"number": 0.0312565117732861
}
],
"answer": "Northern Ireland",
"answerValue": 0.452088962793598,
"question": "Other than the United Kingdom, which country's subreddit mentions “Brexit” the most?",
"rowIndex": 357,
"approved": true,
"params": {
"term": "brexit",
"per": 100,
"filter_country": "United Kingdom"
},
"sql": "\n SELECT\n countries.country AS country,\n SUM(CASE WHEN comments.body CONTAINS 'brexit' THEN 1 ELSE 0 END) * 100 / COUNT(*) AS per\n FROM (\n SELECT\n *\n FROM\n TABLE_QUERY([fh-bigquery:reddit_comments], 'regexp_match(table_id, \"2016\")')) comments\n JOIN\n [subreddits.european_countries] countries\n ON\n countries.subreddit = comments.subreddit\n \n WHERE country != 'United Kingdom'\n GROUP BY\n country\n ORDER BY\n per DESC IGNORE CASE\n",
"inputSQL": "\n SELECT\n countries.country AS country,\n SUM(CASE WHEN comments.body CONTAINS 'brexit' THEN 1 ELSE 0 END) * 100 / COUNT(*) AS per\n FROM (\n SELECT\n *\n FROM\n TABLE_QUERY([fh-bigquery:reddit_comments], 'regexp_match(table_id, \"2016\")')) comments\n JOIN\n [subreddits.european_countries] countries\n ON\n countries.subreddit = comments.subreddit\n \n WHERE country != 'United Kingdom' AND country = 'userInput'\n GROUP BY\n country\n ORDER BY\n per DESC IGNORE CASE\n",
"databases": "Reddit",
"units": "Mentions",
"hasPassingInputQuery": true,
"rowsSearched": 314669636,
"warnings": {}
},
{
"top10": [
{
"label": "China",
"number": 49
},
{
"label": "Greece",
"number": 26
},
{
"label": "Canada",
"number": 13
},
{
"label": "Germany",
"number": 13
},
{
"label": "Venezuela",
"number": 13
},
{
"label": "Russia",
"number": 12
},
{
"label": "Japan",
"number": 8
},
{
"label": "Mexico",
"number": 6
},
{
"label": "France",
"number": 6
},
{
"label": "Australia",
"number": 5
}
],
"answer": "China",
"answerValue": 49,
"question": "What country was mentioned the most in /r/economy since Brexit?\n",
"rowIndex": 356,
"approved": true,
"params": {
"freebase_tag": "countries",
"subreddit": "economy"
},
"sql": "\nSELECT\n topic.name,\n COUNT(UNIQUE(comments.id)) AS total\nFROM\n [freebase_tags.countries] topic\nCROSS JOIN (\n SELECT\n id,\n body\n FROM\n [fh-bigquery:reddit_comments.2016_05]\n WHERE\n subreddit = 'economy') comments\nWHERE\n comments.body CONTAINS CONCAT(' ', topic.name, ' ')\nGROUP BY\n topic.name,\nORDER BY\n total DESC IGNORE CASE\n",
"inputSQL": "\nSELECT\n topic.name,\n COUNT(UNIQUE(comments.id)) AS total\nFROM (\n SELECT\n *\n FROM\n [freebase_tags.countries]\n WHERE\n name = 'userInput') topic\nCROSS JOIN (\n SELECT\n id,\n body\n FROM\n [fh-bigquery:reddit_comments.2016_05]\n WHERE\n subreddit = 'economy') comments\nWHERE\n comments.body CONTAINS CONCAT(' ', topic.name, ' ')\nGROUP BY\n topic.name,\nORDER BY\n total DESC IGNORE CASE\n",
"databases": "Reddit",
"units": "Mentions",
"hasPassingInputQuery": true,
"rowsSearched": 65216562,
"warnings": {}
},
{
"top10": [
{
"label": "Rattata",
"number": 31
},
{
"label": "Pikachu",
"number": 26
},
{
"label": "Eevee",
"number": 22
},
{
"label": "Magikarp",
"number": 16
},
{
"label": "Mewtwo",
"number": 14
},
{
"label": "Pidgey",
"number": 11
},
{
"label": "Abra",
"number": 9
},
{
"label": "Jolteon",
"number": 9
},
{
"label": "Haunter",
"number": 8
},
{
"label": "Tauros",
"number": 8
}
],
"answer": "Rattata",
"answerValue": 31,
"question": "Which pokemon species was mentioned the most in /r/pokemongo in the past month?",
"rowIndex": 358,
"approved": true,
"params": {
"wikidata_tag": "pokemon_species",
"subreddit": "pokemongo",
"num_common_words": 0,
"top_wiki": 500
},
"sql": "\nSELECT\n topic.name,\n COUNT(UNIQUE(comments.id)) AS total\nFROM\n [wikidata_tags.pokemon_species] topic\nCROSS JOIN (\n SELECT\n id,\n body\n FROM\n [fh-bigquery:reddit_comments.2016_05]\n WHERE\n subreddit = 'pokemongo') comments\nWHERE\n topic.name NOT IN (\n SELECT\n word\n FROM\n [words.top10000]\n LIMIT\n 0) AND\n comments.body CONTAINS CONCAT(' ', topic.name, ' ')\nGROUP BY\n topic.name,\nORDER BY\n total DESC IGNORE CASE\n",
"inputSQL": "\nSELECT\n topic.name,\n COUNT(UNIQUE(comments.id)) AS total\nFROM (\n SELECT\n *\n FROM\n [wikidata_tags.pokemon_species]\n WHERE\n name = 'userInput') topic\nCROSS JOIN (\n SELECT\n id,\n body\n FROM\n [fh-bigquery:reddit_comments.2016_05]\n WHERE\n subreddit = 'pokemongo') comments\nWHERE\n comments.body CONTAINS CONCAT(' ', topic.name, ' ')\nGROUP BY\n topic.name,\nORDER BY\n total DESC IGNORE CASE\n",
"databases": "Reddit",
"units": "Mentions",
"hasPassingInputQuery": true,
"rowsSearched": 65224549,
"warnings": {}
},
{
"top10": [
{
"label": "Go",
"number": 613
},
{
"label": "LESS",
"number": 583
},
{
"label": "JavaScript",
"number": 319
},
{
"label": "PHP",
"number": 306
},
{
"label": "HTML",
"number": 293
},
{
"label": "SMALL",
"number": 270
},
{
"label": "BASIC",
"number": 193
},
{
"label": "Cool",
"number": 118
},
{
"label": "FACT",
"number": 114
},
{
"label": "Python",
"number": 93
}
],
"answer": "Go",
"answerValue": 613,
"question": "Which programming language was mentioned the most in /r/webdev in the past month?",
"rowIndex": 364,
"approved": true,
"params": {
"wikidata_tag": "programming_languages",
"subreddit": "webdev",
"num_common_words": 0,
"top_wiki": 500
},
"sql": "\nSELECT\n topic.name,\n COUNT(UNIQUE(comments.id)) AS total\nFROM\n [wikidata_tags.programming_languages] topic\nCROSS JOIN (\n SELECT\n id,\n body\n FROM\n [fh-bigquery:reddit_comments.2016_05]\n WHERE\n subreddit = 'webdev') comments\nWHERE\n topic.name NOT IN (\n SELECT\n word\n FROM\n [words.top10000]\n LIMIT\n 0) AND\n comments.body CONTAINS CONCAT(' ', topic.name, ' ')\nGROUP BY\n topic.name,\nORDER BY\n total DESC IGNORE CASE\n",
"inputSQL": "\nSELECT\n topic.name,\n COUNT(UNIQUE(comments.id)) AS total\nFROM (\n SELECT\n *\n FROM\n [wikidata_tags.programming_languages]\n WHERE\n name = 'userInput') topic\nCROSS JOIN (\n SELECT\n id,\n body\n FROM\n [fh-bigquery:reddit_comments.2016_05]\n WHERE\n subreddit = 'webdev') comments\nWHERE\n comments.body CONTAINS CONCAT(' ', topic.name, ' ')\nGROUP BY\n topic.name,\nORDER BY\n total DESC IGNORE CASE\n",
"databases": "Reddit",
"units": "Mentions",
"hasPassingInputQuery": true,
"rowsSearched": 65232949,
"warnings": {}
},
{
"top10": [
{
"label": "New Mexico",
"number": 0.34843205574912894
},
{
"label": "New York",
"number": 0.30543677458766033
},
{
"label": "Hawaii",
"number": 0.21934888016624335
},
{
"label": "South Dakota",
"number": 0.1457725947521866
},
{
"label": "Montana",
"number": 0.12828736369467608
},
{
"label": "Florida",
"number": 0.10657193605683836
},
{
"label": "California",
"number": 0.1019863046962265
},
{
"label": "Alaska",
"number": 0.09867771857114664
},
{
"label": "Utah",
"number": 0.0942507068803016
},
{
"label": "Arizona",
"number": 0.09218005838070364
}
],
"answer": "New Mexico",
"answerValue": 0.34843205574912894,
"question": "Which state's subreddit talked about Airbnb the most in the past year?\n",
"rowIndex": 366,
"approved": true,
"params": {
"regex": "(?i)\\b(airbnb)\\b",
"per": 100
},
"sql": "\nSELECT\n states.state AS state,\n SUM(CASE WHEN REGEXP_MATCH(body, r'(?i)\\b(airbnb)\\b') THEN 1 ELSE 0 END) * 100 / COUNT(*) AS per\nFROM (\n SELECT\n *\n FROM\n TABLE_QUERY([fh-bigquery:reddit_comments], 'regexp_match(table_id, \"^2016\")')) comments\nJOIN\n [subreddits.states] states\nON\n states.subreddit = comments.subreddit\nGROUP BY\n state\nORDER BY\n per DESC IGNORE CASE\n",
"inputSQL": "\nSELECT\n states.state AS state,\n SUM(CASE WHEN REGEXP_MATCH(body, r'(?i)\\b(airbnb)\\b') THEN 1 ELSE 0 END) * 100 / COUNT(*) AS per\nFROM (\n SELECT\n *\n FROM\n TABLE_QUERY([fh-bigquery:reddit_comments], 'regexp_match(table_id, \"^2016\")')) comments\nJOIN\n [subreddits.states] states\nON\n states.subreddit = comments.subreddit\nWHERE\n states.state = 'userInput'\nGROUP BY\n state\nORDER BY\n per DESC IGNORE CASE\n",
"databases": "Reddit",
"units": "% of Comments",
"hasPassingInputQuery": true,
"rowsSearched": 314656225,
"warnings": {}
},
{
"top10": [
{
"label": "Google",
"number": 69
},
{
"label": "Target",
"number": 68
},
{
"label": "Apple",
"number": 49
},
{
"label": "Monsanto",
"number": 31
},
{
"label": "Facebook",
"number": 26
},
{
"label": "Microsoft",
"number": 18
},
{
"label": "Visa",
"number": 18
},
{
"label": "Walmart",
"number": 12
},
{
"label": "Gap",
"number": 12
},
{
"label": "Nordstrom",
"number": 9
}
],
"answer": "Google",
"answerValue": 69,
"question": "Which Fortune 500 company was mentioned the most in /r/business in the past month?",
"rowIndex": 365,
"approved": true,
"params": {
"wikidata_tag": "fortune_500",
"subreddit": "business",
"num_common_words": 0,
"top_wiki": 500
},
"sql": "\nSELECT\n topic.name,\n COUNT(UNIQUE(comments.id)) AS total\nFROM\n [wikidata_tags.fortune_500] topic\nCROSS JOIN (\n SELECT\n id,\n body\n FROM\n [fh-bigquery:reddit_comments.2016_05]\n WHERE\n subreddit = 'business') comments\nWHERE\n topic.name NOT IN (\n SELECT\n word\n FROM\n [words.top10000]\n LIMIT\n 0) AND\n comments.body CONTAINS CONCAT(' ', topic.name, ' ')\nGROUP BY\n topic.name,\nORDER BY\n total DESC IGNORE CASE\n",
"inputSQL": "\nSELECT\n topic.name,\n COUNT(UNIQUE(comments.id)) AS total\nFROM (\n SELECT\n *\n FROM\n [wikidata_tags.fortune_500]\n WHERE\n name = 'userInput') topic\nCROSS JOIN (\n SELECT\n id,\n body\n FROM\n [fh-bigquery:reddit_comments.2016_05]\n WHERE\n subreddit = 'business') comments\nWHERE\n comments.body CONTAINS CONCAT(' ', topic.name, ' ')\nGROUP BY\n topic.name,\nORDER BY\n total DESC IGNORE CASE\n",
"databases": "Reddit",
"units": "Mentions",
"hasPassingInputQuery": true,
"rowsSearched": 65223292,
"warnings": {}
},
{
"top10": [
{
"label": "New Jersey",
"number": 0.727669102050436
},
{
"label": "Delaware",
"number": 0.3743509238014733
},
{
"label": "Texas",
"number": 0.32676256437577694
},
{
"label": "Florida",
"number": 0.3197158081705151
},
{
"label": "New York",
"number": 0.30543677458766033
},
{
"label": "Connecticut",
"number": 0.2799882110226938
},
{
"label": "New Mexico",
"number": 0.2787456445993031
},
{
"label": "Alabama",
"number": 0.2556727388942154
},
{
"label": "Michigan",
"number": 0.25395033860045146
},
{
"label": "Hawaii",
"number": 0.2482105749249596
}
],
"answer": "New Jersey",
"answerValue": 0.727669102050436,
"question": "Which state's subreddit talked about Lyft and Uber the most in the past year?",
"rowIndex": 367,
"approved": true,
"params": {
"regex": "(?i)\\b(lyft|uber)\\b",
"per": 100
},
"sql": "\nSELECT\n states.state AS state,\n SUM(CASE WHEN REGEXP_MATCH(body, r'(?i)\\b(lyft|uber)\\b') THEN 1 ELSE 0 END) * 100 / COUNT(*) AS per\nFROM (\n SELECT\n *\n FROM\n TABLE_QUERY([fh-bigquery:reddit_comments], 'regexp_match(table_id, \"^2016\")')) comments\nJOIN\n [subreddits.states] states\nON\n states.subreddit = comments.subreddit\nGROUP BY\n state\nORDER BY\n per DESC IGNORE CASE\n",
"inputSQL": "\nSELECT\n states.state AS state,\n SUM(CASE WHEN REGEXP_MATCH(body, r'(?i)\\b(lyft|uber)\\b') THEN 1 ELSE 0 END) * 100 / COUNT(*) AS per\nFROM (\n SELECT\n *\n FROM\n TABLE_QUERY([fh-bigquery:reddit_comments], 'regexp_match(table_id, \"^2016\")')) comments\nJOIN\n [subreddits.states] states\nON\n states.subreddit = comments.subreddit\nWHERE\n states.state = 'userInput'\nGROUP BY\n state\nORDER BY\n per DESC IGNORE CASE\n",
"databases": "Reddit",
"units": "% of Comments",
"hasPassingInputQuery": true,
"rowsSearched": 314656225,
"warnings": {}
},
{
"top10": [
{
"label": "Hawaii",
"number": 0.33479565920110826
},
{
"label": "Nevada",
"number": 0.20366598778004075
},
{
"label": "Mississippi",
"number": 0.1600640256102441
},
{
"label": "Rhode Island",
"number": 0.14784151389710232
},
{
"label": "South Dakota",
"number": 0.1457725947521866
},
{
"label": "California",
"number": 0.12626875819532807
},
{
"label": "New York",
"number": 0.12217470983506414
},
{
"label": "Vermont",
"number": 0.09827319949459497
},
{
"label": "New Jersey",
"number": 0.08543483384397832
},
{
"label": "Maine",
"number": 0.08405127127547804
}
],
"answer": "Hawaii",
"answerValue": 0.33479565920110826,
"question": "Which state’s subreddit mentioned Yelp the most in the past year?",
"rowIndex": 368,
"approved": true,
"params": {
"regex": "(?i)\\b(yelp)\\b",
"per": 100
},
"sql": "\nSELECT\n states.state AS state,\n SUM(CASE WHEN REGEXP_MATCH(body, r'(?i)\\b(yelp)\\b') THEN 1 ELSE 0 END) * 100 / COUNT(*) AS per\nFROM (\n SELECT\n *\n FROM\n TABLE_QUERY([fh-bigquery:reddit_comments], 'regexp_match(table_id, \"^2016\")')) comments\nJOIN\n [subreddits.states] states\nON\n states.subreddit = comments.subreddit\nGROUP BY\n state\nORDER BY\n per DESC IGNORE CASE\n",
"inputSQL": "\nSELECT\n states.state AS state,\n SUM(CASE WHEN REGEXP_MATCH(body, r'(?i)\\b(yelp)\\b') THEN 1 ELSE 0 END) * 100 / COUNT(*) AS per\nFROM (\n SELECT\n *\n FROM\n TABLE_QUERY([fh-bigquery:reddit_comments], 'regexp_match(table_id, \"^2016\")')) comments\nJOIN\n [subreddits.states] states\nON\n states.subreddit = comments.subreddit\nWHERE\n states.state = 'userInput'\nGROUP BY\n state\nORDER BY\n per DESC IGNORE CASE\n",
"databases": "Reddit",
"units": "% of Comments",
"hasPassingInputQuery": true,
"rowsSearched": 314656225,
"warnings": {}
},
{
"top10": [
{
"label": "Kansas",
"number": 4.024539877300613
},
{
"label": "Nevada",
"number": 3.4623217922606924
},
{
"label": "Pennsylvania",
"number": 3.379762392462106
},
{
"label": "Wisconsin",
"number": 2.978414279784143
},
{
"label": "North Carolina",
"number": 2.5261638397690365
},
{
"label": "Kentucky",
"number": 2.489905787348587
},
{
"label": "Missouri",
"number": 2.4663677130044843
},
{
"label": "Illinois",
"number": 2.3110066588327456
},
{
"label": "New York",
"number": 2.2602321319486864
},
{
"label": "Indiana",
"number": 2.1591765001255334
}
],
"answer": "Kansas",
"answerValue": 4.024539877300613,
"question": "Which state’s subreddit mentioned \"Republicans\" the most in the past year?",
"rowIndex": 369,
"approved": true,
"params": {
"regex": "(?i)\\b(republican|republicans)\\b",
"per": 100
},
"sql": "\nSELECT\n states.state AS state,\n SUM(CASE WHEN REGEXP_MATCH(body, r'(?i)\\b(republican|republicans)\\b') THEN 1 ELSE 0 END) * 100 / COUNT(*) AS per\nFROM (\n SELECT\n *\n FROM\n TABLE_QUERY([fh-bigquery:reddit_comments], 'regexp_match(table_id, \"^2016\")')) comments\nJOIN\n [subreddits.states] states\nON\n states.subreddit = comments.subreddit\nGROUP BY\n state\nORDER BY\n per DESC IGNORE CASE\n",
"inputSQL": "\nSELECT\n states.state AS state,\n SUM(CASE WHEN REGEXP_MATCH(body, r'(?i)\\b(republican|republicans)\\b') THEN 1 ELSE 0 END) * 100 / COUNT(*) AS per\nFROM (\n SELECT\n *\n FROM\n TABLE_QUERY([fh-bigquery:reddit_comments], 'regexp_match(table_id, \"^2016\")')) comments\nJOIN\n [subreddits.states] states\nON\n states.subreddit = comments.subreddit\nWHERE\n states.state = 'userInput'\nGROUP BY\n state\nORDER BY\n per DESC IGNORE CASE\n",
"databases": "Reddit",
"units": "% of Comments",
"hasPassingInputQuery": true,
"rowsSearched": 314656225,
"warnings": {}
},
{
"top10": [
{
"label": "Illinois",
"number": 2.7418723070896984
},
{
"label": "Pennsylvania",
"number": 2.0688242523555918
},
{
"label": "New York",
"number": 1.8937080024434942
},
{
"label": "Maryland",
"number": 1.6825164594001463
},
{
"label": "Kansas",
"number": 1.6687116564417177
},
{
"label": "North Carolina",
"number": 1.4119451461566221
},
{
"label": "Wisconsin",
"number": 1.3594852635948527
},
{
"label": "Iowa",
"number": 1.3301749271137027
},
{
"label": "Kentucky",
"number": 1.3010318528488112
},
{
"label": "Indiana",
"number": 1.2427818227466734
}
],
"answer": "Illinois",
"answerValue": 2.7418723070896984,
"question": "Which state’s subreddit mentioned \"Democrats\" the most in the past year?",
"rowIndex": 370,
"approved": true,
"params": {
"regex": "(?i)\\b(democrat|democrats)\\b",
"per": 100
},
"sql": "\nSELECT\n states.state AS state,\n SUM(CASE WHEN REGEXP_MATCH(body, r'(?i)\\b(democrat|democrats)\\b') THEN 1 ELSE 0 END) * 100 / COUNT(*) AS per\nFROM (\n SELECT\n *\n FROM\n TABLE_QUERY([fh-bigquery:reddit_comments], 'regexp_match(table_id, \"^2016\")')) comments\nJOIN\n [subreddits.states] states\nON\n states.subreddit = comments.subreddit\nGROUP BY\n state\nORDER BY\n per DESC IGNORE CASE\n",
"inputSQL": "\nSELECT\n states.state AS state,\n SUM(CASE WHEN REGEXP_MATCH(body, r'(?i)\\b(democrat|democrats)\\b') THEN 1 ELSE 0 END) * 100 / COUNT(*) AS per\nFROM (\n SELECT\n *\n FROM\n TABLE_QUERY([fh-bigquery:reddit_comments], 'regexp_match(table_id, \"^2016\")')) comments\nJOIN\n [subreddits.states] states\nON\n states.subreddit = comments.subreddit\nWHERE\n states.state = 'userInput'\nGROUP BY\n state\nORDER BY\n per DESC IGNORE CASE\n",
"databases": "Reddit",
"units": "% of Comments",
"hasPassingInputQuery": true,
"rowsSearched": 314656225,
"warnings": {}
},
{
"top10": [
{
"label": "Vermont",
"number": 0.07019514249613927
},
{
"label": "Wyoming",
"number": 0.06578947368421052
},
{
"label": "Maryland",
"number": 0.0487685930260912
},
{
"label": "Delaware",
"number": 0.04830334500664171
},
{
"label": "Washington",
"number": 0.048134777376654635
},
{
"label": "New Jersey",
"number": 0.04124440254536884
},
{
"label": "Ohio",
"number": 0.027427317608337904
},
{
"label": "Kansas",
"number": 0.024539877300613498
},
{
"label": "Maine",
"number": 0.02101281781886951
},
{
"label": "Oregon",
"number": 0.014832393948383269
}
],
"answer": "Vermont",
"answerValue": 0.07019514249613927,
"question": "Which state’s reddit mentioned “telecommuting” the most in the past year?",
"rowIndex": 376,
"approved": true,
"params": {
"regex": "(?i)\\b(telecommuting|telecommute|work+from+home|WFH)\\b",
"per": 100
},
"sql": "\nSELECT\n states.state AS state,\n SUM(CASE WHEN REGEXP_MATCH(body, r'(?i)\\b(telecommuting|telecommute|work+from+home|WFH)\\b') THEN 1 ELSE 0 END) * 100 / COUNT(*) AS per\nFROM (\n SELECT\n *\n FROM\n TABLE_QUERY([fh-bigquery:reddit_comments], 'regexp_match(table_id, \"^2016\")')) comments\nJOIN\n [subreddits.states] states\nON\n states.subreddit = comments.subreddit\nGROUP BY\n state\nORDER BY\n per DESC IGNORE CASE\n",
"inputSQL": "\nSELECT\n states.state AS state,\n SUM(CASE WHEN REGEXP_MATCH(body, r'(?i)\\b(telecommuting|telecommute|work+from+home|WFH)\\b') THEN 1 ELSE 0 END) * 100 / COUNT(*) AS per\nFROM (\n SELECT\n *\n FROM\n TABLE_QUERY([fh-bigquery:reddit_comments], 'regexp_match(table_id, \"^2016\")')) comments\nJOIN\n [subreddits.states] states\nON\n states.subreddit = comments.subreddit\nWHERE\n states.state = 'userInput'\nGROUP BY\n state\nORDER BY\n per DESC IGNORE CASE\n",
"databases": "Reddit",
"units": "% of Comments",
"hasPassingInputQuery": true,
"rowsSearched": 314656225,
"warnings": {}
},
{
"top10": [
{
"label": "Costco",
"number": 160
},
{
"label": "Wells Fargo",
"number": 150
},
{
"label": "Netflix",
"number": 128
},
{
"label": "USAA",
"number": 121
},
{
"label": "Walmart",
"number": 117
},
{
"label": "Facebook",
"number": 95
},
{
"label": "Starbucks",
"number": 70
},
{
"label": "CarMax",
"number": 47
},
{
"label": "Comcast",
"number": 46
},
{
"label": "PPL",
"number": 46
}
],
"answer": "Costco",
"answerValue": 160,
"question": "Which Fortune 500 company was discussed the most in /r/personalfinance in the past month?",
"rowIndex": 361,
"approved": true,
"params": {
"wikidata_tag": "fortune_500",
"subreddit": "personalfinance",
"num_common_words": 10000,
"top_wiki": 500
},
"sql": "\nSELECT\n topic.name,\n COUNT(UNIQUE(comments.id)) AS total\nFROM\n [wikidata_tags.fortune_500] topic\nCROSS JOIN (\n SELECT\n id,\n body\n FROM\n [fh-bigquery:reddit_comments.2016_05]\n WHERE\n subreddit = 'personalfinance') comments\nWHERE\n topic.name NOT IN (\n SELECT\n word\n FROM\n [words.top10000]\n LIMIT\n 10000) AND\n comments.body CONTAINS CONCAT(' ', topic.name, ' ')\nGROUP BY\n topic.name,\nORDER BY\n total DESC IGNORE CASE\n",
"inputSQL": "\nSELECT\n topic.name,\n COUNT(UNIQUE(comments.id)) AS total\nFROM (\n SELECT\n *\n FROM\n [wikidata_tags.fortune_500]\n WHERE\n name = 'userInput') topic\nCROSS JOIN (\n SELECT\n id,\n body\n FROM\n [fh-bigquery:reddit_comments.2016_05]\n WHERE\n subreddit = 'personalfinance') comments\nWHERE\n comments.body CONTAINS CONCAT(' ', topic.name, ' ')\nGROUP BY\n topic.name,\nORDER BY\n total DESC IGNORE CASE\n",
"databases": "Reddit",
"units": "Mentions",
"hasPassingInputQuery": true,
"rowsSearched": 65397308,
"warnings": {}
},
{
"top10": [
{
"label": "New York",
"number": 0.12217470983506414
},
{
"label": "Louisiana",
"number": 0.07674597083653108
},
{
"label": "Missouri",
"number": 0.07473841554559044
},
{
"label": "Georgia",
"number": 0.07407407407407407
},
{
"label": "Mississippi",
"number": 0.040016006402561026
},
{
"label": "Illinois",
"number": 0.03916960438699569
},
{
"label": "Alabama",
"number": 0.03195909236177692
},
{
"label": "Arizona",
"number": 0.030726686126901215
},
{
"label": "West Virginia",
"number": 0.02858776443682104
},
{
"label": "Vermont",
"number": 0.028078056998455708
}
],
"answer": "New York",
"answerValue": 0.12217470983506414,
"question": "Which state’s reddit mentioned “freelancing” the most in the past year?",
"rowIndex": 377,
"approved": true,
"params": {
"regex": "(?i)\\b(freelancing|freelance|freelancer)\\b",
"per": 100
},
"sql": "\nSELECT\n states.state AS state,\n SUM(CASE WHEN REGEXP_MATCH(body, r'(?i)\\b(freelancing|freelance|freelancer)\\b') THEN 1 ELSE 0 END) * 100 / COUNT(*) AS per\nFROM (\n SELECT\n *\n FROM\n TABLE_QUERY([fh-bigquery:reddit_comments], 'regexp_match(table_id, \"^2016\")')) comments\nJOIN\n [subreddits.states] states\nON\n states.subreddit = comments.subreddit\nGROUP BY\n state\nORDER BY\n per DESC IGNORE CASE\n",
"inputSQL": "\nSELECT\n states.state AS state,\n SUM(CASE WHEN REGEXP_MATCH(body, r'(?i)\\b(freelancing|freelance|freelancer)\\b') THEN 1 ELSE 0 END) * 100 / COUNT(*) AS per\nFROM (\n SELECT\n *\n FROM\n TABLE_QUERY([fh-bigquery:reddit_comments], 'regexp_match(table_id, \"^2016\")')) comments\nJOIN\n [subreddits.states] states\nON\n states.subreddit = comments.subreddit\nWHERE\n states.state = 'userInput'\nGROUP BY\n state\nORDER BY\n per DESC IGNORE CASE\n",
"databases": "Reddit",
"units": "% of Comments",
"hasPassingInputQuery": true,
"rowsSearched": 314656225,
"warnings": {}
},
{
"top10": [
{
"label": "Cayman Islands",
"number": 46.526761021636574
},
{
"label": "Luxembourg",
"number": 31.69447866502408
},
{
"label": "Macao",
"number": 31.096563011456627
},
{
"label": "Norway",
"number": 30.823356437723167
},
{
"label": "Liechtenstein",
"number": 27.580772261623327
},
{
"label": "Mauritius",
"number": 26.077619266758706
},
{
"label": "Cook Islands",
"number": 23.898781630740395
},
{
"label": "Malawi",
"number": 23.060001517105363
},
{
"label": "Qatar",
"number": 23.056817474640614
},
{
"label": "New Zealand",
"number": 22.674912089244575
}
],
"answer": "Cayman Islands",
"answerValue": 46.526761021636574,
"question": "Which country provided the most propotional economic aid so far this year?",
"rowIndex": 379,
"approved": true,
"params": {
"codes": "('071')",
"relation": "Actor1CountryCode",
"year": 2016,
"per": 1000,
"min_events": 1
},
"sql": "\nSELECT\n codes.country,\n SUM(CASE WHEN events.EventCode IN ('071') THEN 1 ELSE 0 END) * 1000 / COUNT(*) as per,\n COUNT(*) as total\nFROM\n [gdelt-bq:full.events] events\nJOIN\n [gdelt-bq:extra.countryinfo] codes\nON\n events.Actor1CountryCode = codes.iso3\nWHERE\n Year = 2016\nGROUP BY\n codes.country\nHAVING\n total > 1\nORDER BY\n per DESC\nIGNORE CASE\n",
"inputSQL": "\nSELECT\n codes.country,\n SUM(CASE WHEN events.EventCode IN ('071') THEN 1 ELSE 0 END) * 1000 / COUNT(*) as per,\n COUNT(*) as total\nFROM\n [gdelt-bq:full.events] events\nJOIN\n [gdelt-bq:extra.countryinfo] codes\nON\n events.Actor1CountryCode = codes.iso3\nJOIN (\n SELECT\n *\n FROM (\n SELECT\n sub,\n obj AS alias,\n CASE WHEN obj = 'userInput' THEN 1 ELSE 0 END AS input,\n SUM(input) OVER (PARTITION BY sub) AS input_alias\n FROM\n [fh-bigquery:freebase20140119.triples_lang_en]\n WHERE\n (pred = '/common/topic/alias'\n OR pred = '/type/object/name'))\n WHERE\n input_alias > 0) aliases\nON\n aliases.alias = codes.country\nWHERE\n Year = 2016\nGROUP BY\n codes.country\nHAVING\n total > 1\nORDER BY\n per DESC\nIGNORE CASE\n",
"databases": "GDELT",
"units": "Events per 1,000",
"hasPassingInputQuery": true,
"rowsSearched": 399980372,
"warnings": {}
},
{
"top10": [
{
"label": "Chad",
"number": 10.666666666666666
},
{
"label": "Benin",
"number": 9.867683337071092
},
{
"label": "Iraq",
"number": 9.34275122733193
},
{
"label": "Niger",
"number": 9.324324324324325
},
{
"label": "Lithuania",
"number": 8.619620488651867
},
{
"label": "Saudi Arabia",
"number": 8.341757614518647
},
{
"label": "Cameroon",
"number": 7.059578564552359
},
{
"label": "Czech Republic",
"number": 6.831743103398576
},
{
"label": "Afghanistan",
"number": 6.350130452595036
},
{
"label": "Mali",
"number": 6.285943560251438
}
],
"answer": "Chad",
"answerValue": 10.666666666666666,
"question": "Which country provided the most proportional military aid so far this year?",
"rowIndex": 380,
"approved": true,
"params": {
"codes": "('072')",
"relation": "Actor1CountryCode",
"year": 2016,
"per": 1000,
"min_events": 1
},
"sql": "\nSELECT\n codes.country,\n SUM(CASE WHEN events.EventCode IN ('072') THEN 1 ELSE 0 END) * 1000 / COUNT(*) as per,\n COUNT(*) as total\nFROM\n [gdelt-bq:full.events] events\nJOIN\n [gdelt-bq:extra.countryinfo] codes\nON\n events.Actor1CountryCode = codes.iso3\nWHERE\n Year = 2016\nGROUP BY\n codes.country\nHAVING\n total > 1\nORDER BY\n per DESC\nIGNORE CASE\n",
"inputSQL": "\nSELECT\n codes.country,\n SUM(CASE WHEN events.EventCode IN ('072') THEN 1 ELSE 0 END) * 1000 / COUNT(*) as per,\n COUNT(*) as total\nFROM\n [gdelt-bq:full.events] events\nJOIN\n [gdelt-bq:extra.countryinfo] codes\nON\n events.Actor1CountryCode = codes.iso3\nJOIN (\n SELECT\n *\n FROM (\n SELECT\n sub,\n obj AS alias,\n CASE WHEN obj = 'userInput' THEN 1 ELSE 0 END AS input,\n SUM(input) OVER (PARTITION BY sub) AS input_alias\n FROM\n [fh-bigquery:freebase20140119.triples_lang_en]\n WHERE\n (pred = '/common/topic/alias'\n OR pred = '/type/object/name'))\n WHERE\n input_alias > 0) aliases\nON\n aliases.alias = codes.country\nWHERE\n Year = 2016\nGROUP BY\n codes.country\nHAVING\n total > 1\nORDER BY\n per DESC\nIGNORE CASE\n",
"databases": "GDELT",
"units": "Events per 1,000",
"hasPassingInputQuery": true,
"rowsSearched": 399980372,
"warnings": {}
},
{
"top10": [
{
"label": "HBO",
"number": 707
},
{
"label": "Fox",
"number": 442
},
{
"label": "ESPN",
"number": 420
},
{
"label": "CBS",
"number": 403
},
{
"label": "BET",
"number": 304
},
{
"label": "ABC",
"number": 221
},
{
"label": "NBC",
"number": 196
},
{
"label": "The CW",
"number": 192
},
{
"label": "Comedy Central",
"number": 186
},
{
"label": "Pop",
"number": 162
}
],
"answer": "HBO",
"answerValue": 707,
"question": "Which TV station was mentioned the most in /r/television in the past month?",
"rowIndex": 374,
"approved": true,
"params": {
"wikidata_tag": "tv_stations",
"subreddit": "television",
"num_common_words": 1000,
"top_wiki": 500
},
"sql": "\nSELECT\n topic.name,\n COUNT(UNIQUE(comments.id)) AS total\nFROM\n [wikidata_tags.tv_stations] topic\nCROSS JOIN (\n SELECT\n id,\n body\n FROM\n [fh-bigquery:reddit_comments.2016_05]\n WHERE\n subreddit = 'television') comments\nWHERE\n topic.name NOT IN (\n SELECT\n word\n FROM\n [words.top10000]\n LIMIT\n 1000) AND\n comments.body CONTAINS CONCAT(' ', topic.name, ' ')\nGROUP BY\n topic.name,\nORDER BY\n total DESC IGNORE CASE\n",
"inputSQL": "\nSELECT\n topic.name,\n COUNT(UNIQUE(comments.id)) AS total\nFROM (\n SELECT\n *\n FROM\n [wikidata_tags.tv_stations]\n WHERE\n name = 'userInput') topic\nCROSS JOIN (\n SELECT\n id,\n body\n FROM\n [fh-bigquery:reddit_comments.2016_05]\n WHERE\n subreddit = 'television') comments\nWHERE\n comments.body CONTAINS CONCAT(' ', topic.name, ' ')\nGROUP BY\n topic.name,\nORDER BY\n total DESC IGNORE CASE\n",
"databases": "Reddit",
"units": "Mentions",
"hasPassingInputQuery": true,
"rowsSearched": 65351771,
"warnings": {}
},
{
"top10": [
{
"label": "Apple",
"number": 476
},
{
"label": "Google",
"number": 284
},
{
"label": "Target",
"number": 144
},
{
"label": "Facebook",
"number": 90
},
{
"label": "Walmart",
"number": 47
},
{
"label": "Microsoft",
"number": 40
},
{
"label": "Disney",
"number": 37
},
{
"label": "Intel",
"number": 33
},
{
"label": "Visa",
"number": 32
},
{
"label": "Gap",
"number": 31
}
],
"answer": "Apple",
"answerValue": 476,
"question": "Which Fortune 500 was mentioned the most in /r/investing in the past year?",
"rowIndex": 378,
"approved": true,
"params": {
"wikidata_tag": "fortune_500",
"subreddit": "investing",
"num_common_words": 500,
"top_wiki": 500
},
"sql": "\nSELECT\n topic.name,\n COUNT(UNIQUE(comments.id)) AS total\nFROM\n [wikidata_tags.fortune_500] topic\nCROSS JOIN (\n SELECT\n id,\n body\n FROM\n [fh-bigquery:reddit_comments.2016_05]\n WHERE\n subreddit = 'investing') comments\nWHERE\n topic.name NOT IN (\n SELECT\n word\n FROM\n [words.top10000]\n LIMIT\n 500) AND\n comments.body CONTAINS CONCAT(' ', topic.name, ' ')\nGROUP BY\n topic.name,\nORDER BY\n total DESC IGNORE CASE\n",
"inputSQL": "\nSELECT\n topic.name,\n COUNT(UNIQUE(comments.id)) AS total\nFROM (\n SELECT\n *\n FROM\n [wikidata_tags.fortune_500]\n WHERE\n name = 'userInput') topic\nCROSS JOIN (\n SELECT\n id,\n body\n FROM\n [fh-bigquery:reddit_comments.2016_05]\n WHERE\n subreddit = 'investing') comments\nWHERE\n comments.body CONTAINS CONCAT(' ', topic.name, ' ')\nGROUP BY\n topic.name,\nORDER BY\n total DESC IGNORE CASE\n",
"databases": "Reddit",
"units": "Mentions",
"hasPassingInputQuery": true,
"rowsSearched": 65252054,
"warnings": {}
},
{
"top10": [
{
"label": "Israel",
"number": 265
},
{
"label": "Russia",
"number": 92
},
{
"label": "Syria",
"number": 71
},
{
"label": "Iraq",
"number": 69
},
{
"label": "Iran",
"number": 49
},
{
"label": "China",
"number": 40
},
{
"label": "Saudi Arabia",
"number": 40
},
{
"label": "Germany",
"number": 33
},
{
"label": "Japan",
"number": 31
},
{
"label": "Libya",
"number": 25
}
],
"answer": "Israel",
"answerValue": 265,
"question": "Which country is mentioned the most in /r/worldpolitics in the past month?",
"rowIndex": 384,
"approved": true,
"params": {
"freebase_tag": "countries",
"subreddit": "worldpolitics"
},
"sql": "\nSELECT\n topic.name,\n COUNT(UNIQUE(comments.id)) AS total\nFROM\n [freebase_tags.countries] topic\nCROSS JOIN (\n SELECT\n id,\n body\n FROM\n [fh-bigquery:reddit_comments.2016_05]\n WHERE\n subreddit = 'worldpolitics') comments\nWHERE\n comments.body CONTAINS CONCAT(' ', topic.name, ' ')\nGROUP BY\n topic.name,\nORDER BY\n total DESC IGNORE CASE\n",
"inputSQL": "\nSELECT\n topic.name,\n COUNT(UNIQUE(comments.id)) AS total\nFROM (\n SELECT\n *\n FROM\n [freebase_tags.countries]\n WHERE\n name = 'userInput') topic\nCROSS JOIN (\n SELECT\n id,\n body\n FROM\n [fh-bigquery:reddit_comments.2016_05]\n WHERE\n subreddit = 'worldpolitics') comments\nWHERE\n comments.body CONTAINS CONCAT(' ', topic.name, ' ')\nGROUP BY\n topic.name,\nORDER BY\n total DESC IGNORE CASE\n",
"databases": "Reddit",
"units": "Mentions",
"hasPassingInputQuery": true,
"rowsSearched": 65223962,
"warnings": {}
},
{
"top10": [
{
"label": "Netflix",
"number": 276900
},
{
"label": "Opera",
"number": 263609
},
{
"label": "BitTorrent",
"number": 201440
},
{
"label": "Dolphin",
"number": 136388
},
{
"label": "Haiku",
"number": 103643
},
{
"label": "Osiris",
"number": 99176
},
{
"label": "MATLAB",
"number": 97479
},
{
"label": "Dropbox",
"number": 79922
},
{
"label": "Hydrogen",
"number": 72984
},
{
"label": "Hyper-V",
"number": 69715
}
],
"answer": "Netflix",
"answerValue": 276900,
"question": "Which software application had the most popular page in the last month?",
"rowIndex": 385,
"approved": true,
"params": {
"wikidata_tag": "software_applications",
"substring": true
},
"sql": "\n SELECT\n replace(school.name, \"_\", \" \") AS name,\n SUM(requests) AS total\n FROM\n [wikidata_tags.software_applications] school\n JOIN EACH (\n SELECT\n title,\n requests\n FROM\n [fh-bigquery:wikipedia.pagecounts_201605]) views\n ON\n views.title = name\n GROUP BY\n name\n ORDER BY\n total DESC IGNORE case\n",
"inputSQL": "\n SELECT\n replace(school.name, \"_\", \" \") AS name,\n SUM(requests) AS total\n FROM\n [wikidata_tags.software_applications] school\n JOIN EACH (\n SELECT\n title,\n requests\n FROM\n [fh-bigquery:wikipedia.pagecounts_201605]\n WHERE\n title = 'userInput'\nOR title CONTAINS CONCAT('_', replace('userInput', ' ', '_' ))\nOR title CONTAINS CONCAT(replace('userInput', ' ', '_' ), '_')\n ) views\n ON\n views.title = name\n GROUP BY\n name\n ORDER BY\n total DESC IGNORE case\n",
"databases": "Wikipedia",
"units": "Views",
"hasPassingInputQuery": true,
"rowsSearched": 10351631908,
"warnings": {}
},
{
"top10": [
{
"label": "Ford",
"number": 1073
},
{
"label": "BMW",
"number": 972
},
{
"label": "Honda",
"number": 892
},
{
"label": "Toyota",
"number": 753
},
{
"label": "Subaru",
"number": 657
},
{
"label": "Mazda",
"number": 555
},
{
"label": "Nissan",
"number": 526
},
{
"label": "Porsche",
"number": 514
},
{
"label": "Audi",
"number": 490
},
{
"label": "Seat",
"number": 397
}
],
"answer": "Ford",
"answerValue": 1073,
"question": "Which car brand was mentioned the most in /r/cars in the past month?",
"rowIndex": 400,
"approved": true,
"params": {
"wikidata_tag": "cars",
"subreddit": "cars",
"substring": true,
"num_common_words": 0,
"top_wiki": 500
},
"sql": "\nSELECT\n topic.name,\n COUNT(UNIQUE(comments.id)) AS total\nFROM\n [wikidata_tags.cars] topic\nCROSS JOIN (\n SELECT\n id,\n body\n FROM\n [fh-bigquery:reddit_comments.2016_05]\n WHERE\n subreddit = 'cars') comments\nWHERE\n topic.name NOT IN (\n SELECT\n word\n FROM\n [words.top10000]\n LIMIT\n 0) AND\n comments.body CONTAINS CONCAT(' ', topic.name, ' ')\nGROUP BY\n topic.name,\nORDER BY\n total DESC IGNORE CASE\n",
"inputSQL": "\nSELECT\n topic.name,\n COUNT(UNIQUE(comments.id)) AS total\nFROM (\n SELECT\n *\n FROM\n [wikidata_tags.cars]\n WHERE\n name = 'userInput'\n OR name CONTAINS CONCAT(' ', 'userInput')\n OR name CONTAINS CONCAT('userInput', ' ')\n) topic\nCROSS JOIN (\n SELECT\n id,\n body\n FROM\n [fh-bigquery:reddit_comments.2016_05]\n WHERE\n subreddit = 'cars') comments\nWHERE\n comments.body CONTAINS CONCAT(' ', topic.name, ' ')\nGROUP BY\n topic.name,\nORDER BY\n total DESC IGNORE CASE\n",
"databases": "Reddit",
"units": "Mentions",
"hasPassingInputQuery": true,
"rowsSearched": 65319322,
"warnings": {}
},
{
"top10": [
{
"label": "Google",
"number": 102
},
{
"label": "Facebook",
"number": 82
},
{
"label": "Netflix",
"number": 50
},
{
"label": "Target",
"number": 32
},
{
"label": "Disney",
"number": 27
},
{
"label": "Gap",
"number": 25
},
{
"label": "UPS",
"number": 20
},
{
"label": "Ball",
"number": 19
},
{
"label": "Walmart",
"number": 15
},
{
"label": "Progressive",
"number": 14
}
],
"answer": "Google",
"answerValue": 102,
"question": "Which Fortune 500 is mentioned the most in /r/bestof in the past month?",
"rowIndex": 402,
"approved": true,
"params": {
"wikidata_tag": "fortune_500",
"subreddit": "bestof",
"num_common_words": 500,
"top_wiki": 500
},
"sql": "\nSELECT\n topic.name,\n COUNT(UNIQUE(comments.id)) AS total\nFROM\n [wikidata_tags.fortune_500] topic\nCROSS JOIN (\n SELECT\n id,\n body\n FROM\n [fh-bigquery:reddit_comments.2016_05]\n WHERE\n subreddit = 'bestof') comments\nWHERE\n topic.name NOT IN (\n SELECT\n word\n FROM\n [words.top10000]\n LIMIT\n 500) AND\n comments.body CONTAINS CONCAT(' ', topic.name, ' ')\nGROUP BY\n topic.name,\nORDER BY\n total DESC IGNORE CASE\n",
"inputSQL": "\nSELECT\n topic.name,\n COUNT(UNIQUE(comments.id)) AS total\nFROM (\n SELECT\n *\n FROM\n [wikidata_tags.fortune_500]\n WHERE\n name = 'userInput') topic\nCROSS JOIN (\n SELECT\n id,\n body\n FROM\n [fh-bigquery:reddit_comments.2016_05]\n WHERE\n subreddit = 'bestof') comments\nWHERE\n comments.body CONTAINS CONCAT(' ', topic.name, ' ')\nGROUP BY\n topic.name,\nORDER BY\n total DESC IGNORE CASE\n",
"databases": "Reddit",
"units": "Mentions",
"hasPassingInputQuery": true,
"rowsSearched": 65252275,
"warnings": {}
},
{
"top10": [
{
"label": "Uber",
"number": 17
},
{
"label": "Tesla",
"number": 14
},
{
"label": "Facebook",
"number": 13
},
{
"label": "At Home",
"number": 11
},
{
"label": "Twitter",
"number": 7
},
{
"label": "Outright",
"number": 6
},
{
"label": "Kickstarter",
"number": 6
},
{
"label": "SOMEDAY",
"number": 5
},
{
"label": "Slack",
"number": 5
},
{
"label": "Interface,",
"number": 5
}
],
"answer": "Uber",
"answerValue": 17,
"question": "Which business was mentioned the most in /r/tech in the past month?",
"rowIndex": 350,
"approved": true,
"params": {
"wikidata_tag": "businesses_clean",
"subreddit": "tech",
"num_common_words": 10000,
"top_wiki": 500
},
"sql": "\nSELECT\n topic.name,\n COUNT(UNIQUE(comments.id)) AS total\nFROM\n [wikidata_tags.businesses_clean] topic\nCROSS JOIN (\n SELECT\n id,\n body\n FROM\n [fh-bigquery:reddit_comments.2016_05]\n WHERE\n subreddit = 'tech') comments\nWHERE\n topic.name NOT IN (\n SELECT\n word\n FROM\n [words.top10000]\n LIMIT\n 10000) AND\n comments.body CONTAINS CONCAT(' ', topic.name, ' ')\nGROUP BY\n topic.name,\nORDER BY\n total DESC IGNORE CASE\n",
"inputSQL": "\nSELECT\n topic.name,\n COUNT(UNIQUE(comments.id)) AS total\nFROM (\n SELECT\n *\n FROM\n [wikidata_tags.businesses_clean]\n WHERE\n name = 'userInput') topic\nCROSS JOIN (\n SELECT\n id,\n body\n FROM\n [fh-bigquery:reddit_comments.2016_05]\n WHERE\n subreddit = 'tech') comments\nWHERE\n comments.body CONTAINS CONCAT(' ', topic.name, ' ')\nGROUP BY\n topic.name,\nORDER BY\n total DESC IGNORE CASE\n",
"databases": "Reddit",
"units": "Mentions",
"hasPassingInputQuery": true,
"rowsSearched": 65314761,
"warnings": {}
},
{
"top10": [
{
"label": "Manager",
"number": 433
},
{
"label": "Boss",
"number": 394
},
{
"label": "employee",
"number": 186
},
{
"label": "Sales",
"number": 169
},
{
"label": "Major",
"number": 169
},
{
"label": "Internship",
"number": 145
},
{
"label": "Marketing",
"number": 140
},
{
"label": "General",
"number": 112
},
{
"label": "Support",
"number": 99
},
{
"label": "Private",
"number": 90
}
],
"answer": "Manager",
"answerValue": 433,
"question": "Which job title was discussed the most in /r/jobs in the past month?",
"rowIndex": 362,
"approved": true,
"params": {
"freebase_tag": "job_titles",
"subreddit": "jobs"
},
"sql": "\nSELECT\n topic.name,\n COUNT(UNIQUE(comments.id)) AS total\nFROM\n [freebase_tags.job_titles] topic\nCROSS JOIN (\n SELECT\n id,\n body\n FROM\n [fh-bigquery:reddit_comments.2016_05]\n WHERE\n subreddit = 'jobs') comments\nWHERE\n comments.body CONTAINS CONCAT(' ', topic.name, ' ')\nGROUP BY\n topic.name,\nORDER BY\n total DESC IGNORE CASE\n",
"inputSQL": "\nSELECT\n topic.name,\n COUNT(UNIQUE(comments.id)) AS total\nFROM (\n SELECT\n *\n FROM\n [freebase_tags.job_titles]\n WHERE\n name = 'userInput') topic\nCROSS JOIN (\n SELECT\n id,\n body\n FROM\n [fh-bigquery:reddit_comments.2016_05]\n WHERE\n subreddit = 'jobs') comments\nWHERE\n comments.body CONTAINS CONCAT(' ', topic.name, ' ')\nGROUP BY\n topic.name,\nORDER BY\n total DESC IGNORE CASE\n",
"databases": "Reddit",
"units": "Mentions",
"hasPassingInputQuery": true,
"rowsSearched": 65251273,
"warnings": {}
},
{
"top10": [
{
"label": "Uber",
"number": 79
},
{
"label": "Facebook",
"number": 26
},
{
"label": "Lyft",
"number": 22
},
{
"label": "Bayer",
"number": 15
},
{
"label": "Sports Authority",
"number": 13
},
{
"label": "Tesla",
"number": 13
},
{
"label": "Twitter",
"number": 11
},
{
"label": "Nordstrom",
"number": 9
},
{
"label": "Outright",
"number": 8
},
{
"label": "Sears",
"number": 8
}
],
"answer": "Uber",
"answerValue": 79,
"question": "Which business was mentioned the most in /r/Business in the past month?",
"rowIndex": 349,
"approved": true,
"params": {
"wikidata_tag": "businesses_clean",
"subreddit": "business",
"num_common_words": 10000,
"top_wiki": 500
},
"sql": "\nSELECT\n topic.name,\n COUNT(UNIQUE(comments.id)) AS total\nFROM\n [wikidata_tags.businesses_clean] topic\nCROSS JOIN (\n SELECT\n id,\n body\n FROM\n [fh-bigquery:reddit_comments.2016_05]\n WHERE\n subreddit = 'business') comments\nWHERE\n topic.name NOT IN (\n SELECT\n word\n FROM\n [words.top10000]\n LIMIT\n 10000) AND\n comments.body CONTAINS CONCAT(' ', topic.name, ' ')\nGROUP BY\n topic.name,\nORDER BY\n total DESC IGNORE CASE\n",
"inputSQL": "\nSELECT\n topic.name,\n COUNT(UNIQUE(comments.id)) AS total\nFROM (\n SELECT\n *\n FROM\n [wikidata_tags.businesses_clean]\n WHERE\n name = 'userInput') topic\nCROSS JOIN (\n SELECT\n id,\n body\n FROM\n [fh-bigquery:reddit_comments.2016_05]\n WHERE\n subreddit = 'business') comments\nWHERE\n comments.body CONTAINS CONCAT(' ', topic.name, ' ')\nGROUP BY\n topic.name,\nORDER BY\n total DESC IGNORE CASE\n",
"databases": "Reddit",
"units": "Mentions",
"hasPassingInputQuery": true,
"rowsSearched": 65320696,
"warnings": {}
}
],
"total": 65
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment