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: " SELECT codes.country, SUM(CASE WHEN events.EventCode IN ('031', '030') THEN 1 ELSE 0 END) * 100 / COUNT(*) as per, COUNT(*) as total FROM [gdelt-bq:full.events] events JOIN [gdelt-bq:extra.countryinfo] codes ON events.Actor1CountryCode = codes.iso3 WHERE Year = 2015 GROUP BY codes.country HAVING total > 1000 ORDER BY per DESC IGNORE CASE ",
inputSQL: " SELECT codes.country, SUM(CASE WHEN events.EventCode IN ('031', '030') THEN 1 ELSE 0 END) * 100 / COUNT(*) as per, COUNT(*) as total FROM [gdelt-bq:full.events] events JOIN [gdelt-bq:extra.countryinfo] codes ON events.Actor1CountryCode = codes.iso3 JOIN ( SELECT * FROM ( SELECT sub, obj AS alias, CASE WHEN obj = 'userInput' THEN 1 ELSE 0 END AS input, SUM(input) OVER (PARTITION BY sub) AS input_alias FROM [fh-bigquery:freebase20140119.triples_lang_en] WHERE (pred = '/common/topic/alias' OR pred = '/type/object/name')) WHERE input_alias > 0) aliases ON aliases.alias = codes.country WHERE Year = 2015 GROUP BY codes.country HAVING total > 1000 ORDER BY per DESC IGNORE CASE ",
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: " SELECT codes.country, SUM(CASE WHEN events.EventCode IN ('0312') THEN 1 ELSE 0 END) * 1000 / COUNT(*) as per, COUNT(*) as total FROM [gdelt-bq:full.events] events JOIN [gdelt-bq:extra.countryinfo] codes ON events.Actor1CountryCode = codes.iso3 WHERE Year = 2015 GROUP BY codes.country HAVING total > 1 ORDER BY per DESC IGNORE CASE ",
inputSQL: " SELECT codes.country, SUM(CASE WHEN events.EventCode IN ('0312') THEN 1 ELSE 0 END) * 1000 / COUNT(*) as per, COUNT(*) as total FROM [gdelt-bq:full.events] events JOIN [gdelt-bq:extra.countryinfo] codes ON events.Actor1CountryCode = codes.iso3 JOIN ( SELECT * FROM ( SELECT sub, obj AS alias, CASE WHEN obj = 'userInput' THEN 1 ELSE 0 END AS input, SUM(input) OVER (PARTITION BY sub) AS input_alias FROM [fh-bigquery:freebase20140119.triples_lang_en] WHERE (pred = '/common/topic/alias' OR pred = '/type/object/name')) WHERE input_alias > 0) aliases ON aliases.alias = codes.country WHERE Year = 2015 GROUP BY codes.country HAVING total > 1 ORDER BY per DESC IGNORE CASE ",
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: " SELECT codes.country, SUM(CASE WHEN events.EventCode IN ('0241') THEN 1 ELSE 0 END) * 1000 / COUNT(*) as per, COUNT(*) as total FROM [gdelt-bq:full.events] events JOIN [gdelt-bq:extra.countryinfo] codes ON events.Actor1CountryCode = codes.iso3 WHERE Year = 2015 GROUP BY codes.country HAVING total > 1 ORDER BY per DESC IGNORE CASE ",
inputSQL: " SELECT codes.country, SUM(CASE WHEN events.EventCode IN ('0241') THEN 1 ELSE 0 END) * 1000 / COUNT(*) as per, COUNT(*) as total FROM [gdelt-bq:full.events] events JOIN [gdelt-bq:extra.countryinfo] codes ON events.Actor1CountryCode = codes.iso3 JOIN ( SELECT * FROM ( SELECT sub, obj AS alias, CASE WHEN obj = 'userInput' THEN 1 ELSE 0 END AS input, SUM(input) OVER (PARTITION BY sub) AS input_alias FROM [fh-bigquery:freebase20140119.triples_lang_en] WHERE (pred = '/common/topic/alias' OR pred = '/type/object/name')) WHERE input_alias > 0) aliases ON aliases.alias = codes.country WHERE Year = 2015 GROUP BY codes.country HAVING total > 1 ORDER BY per DESC IGNORE CASE ",
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: " SELECT codes.country, SUM(CASE WHEN events.EventCode IN ('0314') THEN 1 ELSE 0 END) * 10000 / COUNT(*) as per, COUNT(*) as total FROM [gdelt-bq:full.events] events JOIN [gdelt-bq:extra.countryinfo] codes ON events.Actor1CountryCode = codes.iso3 WHERE Year = 2015 GROUP BY codes.country HAVING total > 1 ORDER BY per DESC IGNORE CASE ",
inputSQL: " SELECT codes.country, SUM(CASE WHEN events.EventCode IN ('0314') THEN 1 ELSE 0 END) * 10000 / COUNT(*) as per, COUNT(*) as total FROM [gdelt-bq:full.events] events JOIN [gdelt-bq:extra.countryinfo] codes ON events.Actor1CountryCode = codes.iso3 JOIN ( SELECT * FROM ( SELECT sub, obj AS alias, CASE WHEN obj = 'userInput' THEN 1 ELSE 0 END AS input, SUM(input) OVER (PARTITION BY sub) AS input_alias FROM [fh-bigquery:freebase20140119.triples_lang_en] WHERE (pred = '/common/topic/alias' OR pred = '/type/object/name')) WHERE input_alias > 0) aliases ON aliases.alias = codes.country WHERE Year = 2015 GROUP BY codes.country HAVING total > 1 ORDER BY per DESC IGNORE CASE ",
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: " SELECT codes.country, SUM(CASE WHEN events.EventCode IN ('061') THEN 1 ELSE 0 END) total FROM [gdelt-bq:full.events] events JOIN [gdelt-bq:extra.countryinfo] codes ON events.Actor1CountryCode = codes.iso3 WHERE Year = 2015 AND codes.country != 'United States' GROUP BY codes.country ORDER BY total DESC IGNORE CASE ",
inputSQL: " SELECT 'userInput', COUNT(*) FROM ( SELECT events.GLOBALEVENTID, FROM [gdelt-bq:full.events] events JOIN [gdelt-bq:extra.countryinfo] codes ON events.Actor1CountryCode = codes.iso3 JOIN ( SELECT * FROM ( SELECT sub, obj AS alias, CASE WHEN obj = 'userInput' THEN 1 ELSE 0 END AS input, SUM(input) OVER (PARTITION BY sub) AS input_alias FROM [fh-bigquery:freebase20140119.triples_lang_en] WHERE (pred = '/common/topic/alias' OR pred = '/type/object/name')) WHERE input_alias > 0) aliases ON aliases.alias = codes.country WHERE Year = 2015 AND events.EventCode IN ('061') AND codes.country != 'United States' GROUP BY events.GLOBALEVENTID) IGNORE CASE ",
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: " SELECT other.country, COUNT(*) AS total FROM ( SELECT ccode, other FROM ( SELECT Actor1Code AS ccode, Actor2Code AS other, Year FROM [gdelt-bq:full.events]) cc1, ( SELECT Actor1Code AS other, Actor2Code AS ccode, Year FROM [gdelt-bq:full.events]) cc2 WHERE Year = 2015) events JOIN [gdelt-bq:extra.countryinfo] country ON country.iso3 = events.ccode JOIN [gdelt-bq:extra.countryinfo] other ON events.other = other.iso3 WHERE country.country = 'United States' GROUP BY country.country, other.country ORDER BY total DESC IGNORE CASE ",
inputSQL: " SELECT other.country, COUNT(UNIQUE(events.id)) AS total FROM ( SELECT ccode, other, id FROM ( SELECT Actor1Code AS ccode, Actor2Code AS other, Year, GLOBALEVENTID as id FROM [gdelt-bq:full.events]) cc1, ( SELECT Actor1Code AS other, Actor2Code AS ccode, Year, GLOBALEVENTID as id FROM [gdelt-bq:full.events]) cc2 WHERE Year = 2015) events JOIN [gdelt-bq:extra.countryinfo] country ON country.iso3 = events.ccode JOIN [gdelt-bq:extra.countryinfo] other ON events.other = other.iso3 JOIN ( SELECT * FROM ( SELECT sub, obj AS alias, CASE WHEN obj = 'userInput' THEN 1 ELSE 0 END AS input, SUM(input) OVER (PARTITION BY sub) AS input_alias FROM [fh-bigquery:freebase20140119.triples_lang_en] WHERE (pred = '/common/topic/alias' OR pred = '/type/object/name')) WHERE input_alias > 0) aliases ON aliases.alias = other.country WHERE country.country = 'United States' GROUP BY country.country, other.country ORDER BY total DESC IGNORE CASE ",
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: " SELECT codes.country, SUM(CASE WHEN events.EventCode IN ('0231') THEN 1 ELSE 0 END) * 1000 / COUNT(*) as per, COUNT(*) as total FROM [gdelt-bq:full.events] events JOIN [gdelt-bq:extra.countryinfo] codes ON events.Actor1CountryCode = codes.iso3 WHERE Year = 2015 GROUP BY codes.country HAVING total > 1 ORDER BY per DESC IGNORE CASE ",
inputSQL: " SELECT codes.country, SUM(CASE WHEN events.EventCode IN ('0231') THEN 1 ELSE 0 END) * 1000 / COUNT(*) as per, COUNT(*) as total FROM [gdelt-bq:full.events] events JOIN [gdelt-bq:extra.countryinfo] codes ON events.Actor1CountryCode = codes.iso3 JOIN ( SELECT * FROM ( SELECT sub, obj AS alias, CASE WHEN obj = 'userInput' THEN 1 ELSE 0 END AS input, SUM(input) OVER (PARTITION BY sub) AS input_alias FROM [fh-bigquery:freebase20140119.triples_lang_en] WHERE (pred = '/common/topic/alias' OR pred = '/type/object/name')) WHERE input_alias > 0) aliases ON aliases.alias = codes.country WHERE Year = 2015 GROUP BY codes.country HAVING total > 1 ORDER BY per DESC IGNORE CASE ",
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: " SELECT other.country, COUNT(*) AS total FROM ( SELECT ccode, other FROM ( SELECT Actor1Code AS ccode, Actor2Code AS other, Year FROM [gdelt-bq:full.events]) cc1, ( SELECT Actor1Code AS other, Actor2Code AS ccode, Year FROM [gdelt-bq:full.events]) cc2 WHERE Year = 2015) events JOIN [gdelt-bq:extra.countryinfo] country ON country.iso3 = events.ccode JOIN [gdelt-bq:extra.countryinfo] other ON events.other = other.iso3 WHERE country.country = 'India' GROUP BY country.country, other.country ORDER BY total DESC IGNORE CASE ",
inputSQL: " SELECT other.country, COUNT(UNIQUE(events.id)) AS total FROM ( SELECT ccode, other, id FROM ( SELECT Actor1Code AS ccode, Actor2Code AS other, Year, GLOBALEVENTID as id FROM [gdelt-bq:full.events]) cc1, ( SELECT Actor1Code AS other, Actor2Code AS ccode, Year, GLOBALEVENTID as id FROM [gdelt-bq:full.events]) cc2 WHERE Year = 2015) events JOIN [gdelt-bq:extra.countryinfo] country ON country.iso3 = events.ccode JOIN [gdelt-bq:extra.countryinfo] other ON events.other = other.iso3 JOIN ( SELECT * FROM ( SELECT sub, obj AS alias, CASE WHEN obj = 'userInput' THEN 1 ELSE 0 END AS input, SUM(input) OVER (PARTITION BY sub) AS input_alias FROM [fh-bigquery:freebase20140119.triples_lang_en] WHERE (pred = '/common/topic/alias' OR pred = '/type/object/name')) WHERE input_alias > 0) aliases ON aliases.alias = other.country WHERE country.country = 'India' GROUP BY country.country, other.country ORDER BY total DESC IGNORE CASE ",
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: " SELECT states.state AS state, SUM(CASE WHEN REGEXP_MATCH(body, r'(?i)\b(obama|barack)\b') THEN 1 ELSE 0 END) * 100 / COUNT(*) AS per FROM ( SELECT * FROM TABLE_QUERY([fh-bigquery:reddit_comments], 'regexp_match(table_id, "^2016")')) comments JOIN [subreddits.states] states ON states.subreddit = comments.subreddit GROUP BY state ORDER BY per DESC IGNORE CASE ",
inputSQL: " SELECT states.state AS state, SUM(CASE WHEN REGEXP_MATCH(body, r'(?i)\b(obama|barack)\b') THEN 1 ELSE 0 END) * 100 / COUNT(*) AS per FROM ( SELECT * FROM TABLE_QUERY([fh-bigquery:reddit_comments], 'regexp_match(table_id, "^2016")')) comments JOIN [subreddits.states] states ON states.subreddit = comments.subreddit WHERE states.state = 'userInput' GROUP BY state ORDER BY per DESC IGNORE CASE ",
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: " SELECT states.state AS state, SUM(CASE WHEN REGEXP_MATCH(body, r'(?i)\b(trump|the donald)\b') THEN 1 ELSE 0 END) * 100 / COUNT(*) AS per FROM ( SELECT * FROM TABLE_QUERY([fh-bigquery:reddit_comments], 'regexp_match(table_id, "^2016")')) comments JOIN [subreddits.states] states ON states.subreddit = comments.subreddit GROUP BY state ORDER BY per DESC IGNORE CASE ",
inputSQL: " SELECT states.state AS state, SUM(CASE WHEN REGEXP_MATCH(body, r'(?i)\b(trump|the donald)\b') THEN 1 ELSE 0 END) * 100 / COUNT(*) AS per FROM ( SELECT * FROM TABLE_QUERY([fh-bigquery:reddit_comments], 'regexp_match(table_id, "^2016")')) comments JOIN [subreddits.states] states ON states.subreddit = comments.subreddit WHERE states.state = 'userInput' GROUP BY state ORDER BY per DESC IGNORE CASE ",
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: " SELECT pop_topic.name, COUNT(UNIQUE(comments.id)) AS total FROM ( SELECT topic_wiki.mid AS mid, topic_wiki.name AS name, SUM(requests) AS total FROM ( SELECT topic.mid AS mid, topic.name as name, REPLACE(REPLACE(wiki.title, '$0028', '('), '$0029', ')') AS title FROM ( SELECT mid, name FROM [freebase_tags.states]) topic JOIN ( SELECT REGEXP_REPLACE(REGEXP_REPLACE(obj, '/wikipedia/en/', ''), '/wikipedia/id/', '') AS title, sub AS mid FROM [fh-bigquery:freebase20140119.triples_nolang] WHERE obj CONTAINS '/wikipedia/en' OR obj CONTAINS '/wikipedia/id' AND pred = '/type/object/key') wiki ON wiki.mid = topic.mid) topic_wiki JOIN [fh-bigquery:wikipedia.pagecounts_201605] views ON views.title = topic_wiki.title GROUP BY mid, name ORDER BY total DESC LIMIT 500 ) pop_topic CROSS JOIN ( SELECT id, body FROM [fh-bigquery:reddit_comments.2016_05] WHERE subreddit = 'travel') comments WHERE comments.body CONTAINS CONCAT(' ', pop_topic.name, ' ') GROUP BY pop_topic.name, ORDER BY total DESC IGNORE CASE ",
inputSQL: " SELECT pop_topic.name, COUNT(UNIQUE(comments.id)) AS total FROM ( SELECT topic_wiki.mid AS mid, topic_wiki.name AS name, SUM(requests) AS total FROM ( SELECT topic.mid AS mid, topic.name as name, REPLACE(REPLACE(wiki.title, '$0028', '('), '$0029', ')') AS title FROM ( SELECT mid, name FROM [freebase_tags.states] WHERE name = 'userInput') topic JOIN ( SELECT REGEXP_REPLACE(REGEXP_REPLACE(obj, '/wikipedia/en/', ''), '/wikipedia/id/', '') AS title, sub AS mid FROM [fh-bigquery:freebase20140119.triples_nolang] WHERE obj CONTAINS '/wikipedia/en' OR obj CONTAINS '/wikipedia/id' AND pred = '/type/object/key') wiki ON wiki.mid = topic.mid) topic_wiki JOIN [fh-bigquery:wikipedia.pagecounts_201605] views ON views.title = topic_wiki.title GROUP BY mid, name ORDER BY total DESC LIMIT 500 ) pop_topic CROSS JOIN ( SELECT id, body FROM [fh-bigquery:reddit_comments.2016_05] WHERE subreddit = 'travel') comments WHERE comments.body CONTAINS CONCAT(' ', pop_topic.name, ' ') GROUP BY pop_topic.name, ORDER BY total DESC IGNORE CASE ",
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: " SELECT pop_topic.name, COUNT(UNIQUE(comments.id)) AS total FROM ( SELECT topic_wiki.mid AS mid, topic_wiki.name AS name, SUM(requests) AS total FROM ( SELECT topic.mid AS mid, topic.name as name, REPLACE(REPLACE(wiki.title, '$0028', '('), '$0029', ')') AS title FROM ( SELECT mid, name FROM [freebase_tags.athletes]) topic JOIN ( SELECT REGEXP_REPLACE(REGEXP_REPLACE(obj, '/wikipedia/en/', ''), '/wikipedia/id/', '') AS title, sub AS mid FROM [fh-bigquery:freebase20140119.triples_nolang] WHERE obj CONTAINS '/wikipedia/en' OR obj CONTAINS '/wikipedia/id' AND pred = '/type/object/key') wiki ON wiki.mid = topic.mid) topic_wiki JOIN [fh-bigquery:wikipedia.pagecounts_201605] views ON views.title = topic_wiki.title GROUP BY mid, name ORDER BY total DESC LIMIT 500 ) pop_topic CROSS JOIN ( SELECT id, body FROM [fh-bigquery:reddit_comments.2016_05] WHERE subreddit = 'sports') comments WHERE comments.body CONTAINS CONCAT(' ', pop_topic.name, ' ') GROUP BY pop_topic.name, ORDER BY total DESC IGNORE CASE ",
inputSQL: " SELECT pop_topic.name, COUNT(UNIQUE(comments.id)) AS total FROM ( SELECT topic_wiki.mid AS mid, topic_wiki.name AS name, SUM(requests) AS total FROM ( SELECT topic.mid AS mid, topic.name as name, REPLACE(REPLACE(wiki.title, '$0028', '('), '$0029', ')') AS title FROM ( SELECT mid, name FROM [freebase_tags.athletes] WHERE name = 'userInput' OR name CONTAINS CONCAT(' ', 'userInput') OR name CONTAINS CONCAT('userInput', ' ') ) topic JOIN ( SELECT REGEXP_REPLACE(REGEXP_REPLACE(obj, '/wikipedia/en/', ''), '/wikipedia/id/', '') AS title, sub AS mid FROM [fh-bigquery:freebase20140119.triples_nolang] WHERE obj CONTAINS '/wikipedia/en' OR obj CONTAINS '/wikipedia/id' AND pred = '/type/object/key') wiki ON wiki.mid = topic.mid) topic_wiki JOIN [fh-bigquery:wikipedia.pagecounts_201605] views ON views.title = topic_wiki.title GROUP BY mid, name ORDER BY total DESC LIMIT 500 ) pop_topic CROSS JOIN ( SELECT id, body FROM [fh-bigquery:reddit_comments.2016_05] WHERE subreddit = 'sports') comments WHERE comments.body CONTAINS CONCAT(' ', pop_topic.name, ' ') GROUP BY pop_topic.name, ORDER BY total DESC IGNORE CASE ",
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: " SELECT pop_topic.name, COUNT(UNIQUE(comments.id)) AS total FROM ( SELECT topic_wiki.mid AS mid, topic_wiki.name AS name, SUM(requests) AS total FROM ( SELECT topic.mid AS mid, topic.name as name, REPLACE(REPLACE(wiki.title, '$0028', '('), '$0029', ')') AS title FROM ( SELECT mid, name FROM [freebase_tags.musicians]) topic JOIN ( SELECT REGEXP_REPLACE(REGEXP_REPLACE(obj, '/wikipedia/en/', ''), '/wikipedia/id/', '') AS title, sub AS mid FROM [fh-bigquery:freebase20140119.triples_nolang] WHERE obj CONTAINS '/wikipedia/en' OR obj CONTAINS '/wikipedia/id' AND pred = '/type/object/key') wiki ON wiki.mid = topic.mid) topic_wiki JOIN [fh-bigquery:wikipedia.pagecounts_201605] views ON views.title = topic_wiki.title GROUP BY mid, name ORDER BY total DESC LIMIT 500 ) pop_topic CROSS JOIN ( SELECT id, body FROM [fh-bigquery:reddit_comments.2016_05] WHERE subreddit = 'music') comments WHERE comments.body CONTAINS CONCAT(' ', pop_topic.name, ' ') GROUP BY pop_topic.name, ORDER BY total DESC IGNORE CASE ",
inputSQL: " SELECT pop_topic.name, COUNT(UNIQUE(comments.id)) AS total FROM ( SELECT topic_wiki.mid AS mid, topic_wiki.name AS name, SUM(requests) AS total FROM ( SELECT topic.mid AS mid, topic.name as name, REPLACE(REPLACE(wiki.title, '$0028', '('), '$0029', ')') AS title FROM ( SELECT mid, name FROM [freebase_tags.musicians] WHERE name = 'userInput') topic JOIN ( SELECT REGEXP_REPLACE(REGEXP_REPLACE(obj, '/wikipedia/en/', ''), '/wikipedia/id/', '') AS title, sub AS mid FROM [fh-bigquery:freebase20140119.triples_nolang] WHERE obj CONTAINS '/wikipedia/en' OR obj CONTAINS '/wikipedia/id' AND pred = '/type/object/key') wiki ON wiki.mid = topic.mid) topic_wiki JOIN [fh-bigquery:wikipedia.pagecounts_201605] views ON views.title = topic_wiki.title GROUP BY mid, name ORDER BY total DESC LIMIT 500 ) pop_topic CROSS JOIN ( SELECT id, body FROM [fh-bigquery:reddit_comments.2016_05] WHERE subreddit = 'music') comments WHERE comments.body CONTAINS CONCAT(' ', pop_topic.name, ' ') GROUP BY pop_topic.name, ORDER BY total DESC IGNORE CASE ",
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: " SELECT pop_topic.name, COUNT(UNIQUE(comments.id)) AS total FROM ( SELECT topic_wiki.mid AS mid, topic_wiki.name AS name, SUM(requests) AS total FROM ( SELECT topic.mid AS mid, topic.name as name, REPLACE(REPLACE(wiki.title, '$0028', '('), '$0029', ')') AS title FROM ( SELECT mid, name FROM [freebase_tags.cities]) topic JOIN ( SELECT REGEXP_REPLACE(REGEXP_REPLACE(obj, '/wikipedia/en/', ''), '/wikipedia/id/', '') AS title, sub AS mid FROM [fh-bigquery:freebase20140119.triples_nolang] WHERE obj CONTAINS '/wikipedia/en' OR obj CONTAINS '/wikipedia/id' AND pred = '/type/object/key') wiki ON wiki.mid = topic.mid) topic_wiki JOIN [fh-bigquery:wikipedia.pagecounts_201605] views ON views.title = topic_wiki.title GROUP BY mid, name ORDER BY total DESC LIMIT 75 ) pop_topic CROSS JOIN ( SELECT id, body FROM [fh-bigquery:reddit_comments.2016_05] WHERE subreddit = 'upliftingnews') comments WHERE comments.body CONTAINS CONCAT(' ', pop_topic.name, ' ') GROUP BY pop_topic.name, ORDER BY total DESC IGNORE CASE ",
inputSQL: " SELECT pop_topic.name, COUNT(UNIQUE(comments.id)) AS total FROM ( SELECT topic_wiki.mid AS mid, topic_wiki.name AS name, SUM(requests) AS total FROM ( SELECT topic.mid AS mid, topic.name as name, REPLACE(REPLACE(wiki.title, '$0028', '('), '$0029', ')') AS title FROM ( SELECT mid, name FROM [freebase_tags.cities] WHERE name = 'userInput') topic JOIN ( SELECT REGEXP_REPLACE(REGEXP_REPLACE(obj, '/wikipedia/en/', ''), '/wikipedia/id/', '') AS title, sub AS mid FROM [fh-bigquery:freebase20140119.triples_nolang] WHERE obj CONTAINS '/wikipedia/en' OR obj CONTAINS '/wikipedia/id' AND pred = '/type/object/key') wiki ON wiki.mid = topic.mid) topic_wiki JOIN [fh-bigquery:wikipedia.pagecounts_201605] views ON views.title = topic_wiki.title GROUP BY mid, name ORDER BY total DESC LIMIT 75 ) pop_topic CROSS JOIN ( SELECT id, body FROM [fh-bigquery:reddit_comments.2016_05] WHERE subreddit = 'upliftingnews') comments WHERE comments.body CONTAINS CONCAT(' ', pop_topic.name, ' ') GROUP BY pop_topic.name, ORDER BY total DESC IGNORE CASE ",
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: " SELECT pop_topic.name, COUNT(UNIQUE(comments.id)) AS total FROM ( SELECT topic_wiki.mid AS mid, topic_wiki.name AS name, SUM(requests) AS total FROM ( SELECT topic.mid AS mid, topic.name as name, REPLACE(REPLACE(wiki.title, '$0028', '('), '$0029', ')') AS title FROM ( SELECT mid, name FROM [freebase_tags.states]) topic JOIN ( SELECT REGEXP_REPLACE(REGEXP_REPLACE(obj, '/wikipedia/en/', ''), '/wikipedia/id/', '') AS title, sub AS mid FROM [fh-bigquery:freebase20140119.triples_nolang] WHERE obj CONTAINS '/wikipedia/en' OR obj CONTAINS '/wikipedia/id' AND pred = '/type/object/key') wiki ON wiki.mid = topic.mid) topic_wiki JOIN [fh-bigquery:wikipedia.pagecounts_201605] views ON views.title = topic_wiki.title GROUP BY mid, name ORDER BY total DESC LIMIT 500 ) pop_topic CROSS JOIN ( SELECT id, body FROM [fh-bigquery:reddit_comments.2016_05] WHERE subreddit = 'news') comments WHERE comments.body CONTAINS CONCAT(' ', pop_topic.name, ' ') GROUP BY pop_topic.name, ORDER BY total DESC IGNORE CASE ",
inputSQL: " SELECT pop_topic.name, COUNT(UNIQUE(comments.id)) AS total FROM ( SELECT topic_wiki.mid AS mid, topic_wiki.name AS name, SUM(requests) AS total FROM ( SELECT topic.mid AS mid, topic.name as name, REPLACE(REPLACE(wiki.title, '$0028', '('), '$0029', ')') AS title FROM ( SELECT mid, name FROM [freebase_tags.states] WHERE name = 'userInput') topic JOIN ( SELECT REGEXP_REPLACE(REGEXP_REPLACE(obj, '/wikipedia/en/', ''), '/wikipedia/id/', '') AS title, sub AS mid FROM [fh-bigquery:freebase20140119.triples_nolang] WHERE obj CONTAINS '/wikipedia/en' OR obj CONTAINS '/wikipedia/id' AND pred = '/type/object/key') wiki ON wiki.mid = topic.mid) topic_wiki JOIN [fh-bigquery:wikipedia.pagecounts_201605] views ON views.title = topic_wiki.title GROUP BY mid, name ORDER BY total DESC LIMIT 500 ) pop_topic CROSS JOIN ( SELECT id, body FROM [fh-bigquery:reddit_comments.2016_05] WHERE subreddit = 'news') comments WHERE comments.body CONTAINS CONCAT(' ', pop_topic.name, ' ') GROUP BY pop_topic.name, ORDER BY total DESC IGNORE CASE ",
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: " SELECT pop_topic.name, COUNT(UNIQUE(comments.id)) AS total FROM ( SELECT topic_wiki.mid AS mid, topic_wiki.name AS name, SUM(requests) AS total FROM ( SELECT topic.mid AS mid, topic.name as name, REPLACE(REPLACE(wiki.title, '$0028', '('), '$0029', ')') AS title FROM ( SELECT mid, name FROM [freebase_tags.countries]) topic JOIN ( SELECT REGEXP_REPLACE(REGEXP_REPLACE(obj, '/wikipedia/en/', ''), '/wikipedia/id/', '') AS title, sub AS mid FROM [fh-bigquery:freebase20140119.triples_nolang] WHERE obj CONTAINS '/wikipedia/en' OR obj CONTAINS '/wikipedia/id' AND pred = '/type/object/key') wiki ON wiki.mid = topic.mid) topic_wiki JOIN [fh-bigquery:wikipedia.pagecounts_201605] views ON views.title = topic_wiki.title GROUP BY mid, name ORDER BY total DESC LIMIT 500 ) pop_topic CROSS JOIN ( SELECT id, body FROM [fh-bigquery:reddit_comments.2016_05] WHERE subreddit = 'upliftingnews') comments WHERE comments.body CONTAINS CONCAT(' ', pop_topic.name, ' ') GROUP BY pop_topic.name, ORDER BY total DESC IGNORE CASE ",
inputSQL: " SELECT pop_topic.name, COUNT(UNIQUE(comments.id)) AS total FROM ( SELECT topic_wiki.mid AS mid, topic_wiki.name AS name, SUM(requests) AS total FROM ( SELECT topic.mid AS mid, topic.name as name, REPLACE(REPLACE(wiki.title, '$0028', '('), '$0029', ')') AS title FROM ( SELECT mid, name FROM [freebase_tags.countries] WHERE name = 'userInput') topic JOIN ( SELECT REGEXP_REPLACE(REGEXP_REPLACE(obj, '/wikipedia/en/', ''), '/wikipedia/id/', '') AS title, sub AS mid FROM [fh-bigquery:freebase20140119.triples_nolang] WHERE obj CONTAINS '/wikipedia/en' OR obj CONTAINS '/wikipedia/id' AND pred = '/type/object/key') wiki ON wiki.mid = topic.mid) topic_wiki JOIN [fh-bigquery:wikipedia.pagecounts_201605] views ON views.title = topic_wiki.title GROUP BY mid, name ORDER BY total DESC LIMIT 500 ) pop_topic CROSS JOIN ( SELECT id, body FROM [fh-bigquery:reddit_comments.2016_05] WHERE subreddit = 'upliftingnews') comments WHERE comments.body CONTAINS CONCAT(' ', pop_topic.name, ' ') GROUP BY pop_topic.name, ORDER BY total DESC IGNORE CASE ",
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: " SELECT cities.city, SUM(CASE WHEN REGEXP_MATCH(body, r'(?i)\b(traffic)\b') THEN 1 ELSE 0 END) * 100 / COUNT(*) AS per FROM ( SELECT body, subreddit FROM TABLE_QUERY([fh-bigquery:reddit_comments], 'regexp_match(table_id, "^(2016|2015_(0[7-9]|1[0-2]))")')) comments JOIN [subreddits.cities] cities ON comments.subreddit = cities.subreddit GROUP BY cities.city ORDER BY per DESC IGNORE CASE ",
inputSQL: " SELECT cities.city, SUM(CASE WHEN REGEXP_MATCH(body, r'(?i)\b(traffic)\b') THEN 1 ELSE 0 END) * 100 / COUNT(*) AS per FROM ( SELECT body, subreddit FROM TABLE_QUERY([fh-bigquery:reddit_comments], 'regexp_match(table_id, "^(2016|2015_(0[7-9]|1[0-2]))")')) comments JOIN [subreddits.cities] cities ON comments.subreddit = cities.subreddit JOIN [fh-bigquery:freebase20140119.triples_lang_en] aliases ON aliases.sub = cities.mid WHERE (aliases.pred = '/common/topic/alias' OR aliases.pred = '/type/object/name') AND aliases.obj = 'userInput' GROUP BY cities.city ORDER BY per DESC IGNORE CASE ",
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: " SELECT states.state AS state, SUM(CASE WHEN REGEXP_MATCH(body, r'(?i)\b(hillary|clinton)\b') THEN 1 ELSE 0 END) * 100 / COUNT(*) AS per FROM ( SELECT * FROM TABLE_QUERY([fh-bigquery:reddit_comments], 'regexp_match(table_id, "^2016")')) comments JOIN [subreddits.states] states ON states.subreddit = comments.subreddit GROUP BY state ORDER BY per DESC IGNORE CASE ",
inputSQL: " SELECT states.state AS state, SUM(CASE WHEN REGEXP_MATCH(body, r'(?i)\b(hillary|clinton)\b') THEN 1 ELSE 0 END) * 100 / COUNT(*) AS per FROM ( SELECT * FROM TABLE_QUERY([fh-bigquery:reddit_comments], 'regexp_match(table_id, "^2016")')) comments JOIN [subreddits.states] states ON states.subreddit = comments.subreddit WHERE states.state = 'userInput' GROUP BY state ORDER BY per DESC IGNORE CASE ",
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: " SELECT topic.name, COUNT(UNIQUE(comments.id)) AS total FROM [freebase_tags.countries] topic CROSS JOIN ( SELECT id, body FROM [fh-bigquery:reddit_comments.2016_05] WHERE subreddit = 'europe') comments WHERE comments.body CONTAINS CONCAT(' ', topic.name, ' ') GROUP BY topic.name, ORDER BY total DESC IGNORE CASE ",
inputSQL: " SELECT topic.name, COUNT(UNIQUE(comments.id)) AS total FROM ( SELECT * FROM [freebase_tags.countries] WHERE name = 'userInput') topic CROSS JOIN ( SELECT id, body FROM [fh-bigquery:reddit_comments.2016_05] WHERE subreddit = 'europe') comments WHERE comments.body CONTAINS CONCAT(' ', topic.name, ' ') GROUP BY topic.name, ORDER BY total DESC IGNORE CASE ",
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: " SELECT states.state AS state, SUM(CASE WHEN REGEXP_MATCH(body, r'(?i)\b(unemployment|unemployed)\b') THEN 1 ELSE 0 END) * 1000 / COUNT(*) AS per FROM ( SELECT * FROM TABLE_QUERY([fh-bigquery:reddit_comments], 'regexp_match(table_id, "^2016")')) comments JOIN [subreddits.states] states ON states.subreddit = comments.subreddit GROUP BY state ORDER BY per DESC IGNORE CASE ",
inputSQL: " SELECT states.state AS state, SUM(CASE WHEN REGEXP_MATCH(body, r'(?i)\b(unemployment|unemployed)\b') THEN 1 ELSE 0 END) * 1000 / COUNT(*) AS per FROM ( SELECT * FROM TABLE_QUERY([fh-bigquery:reddit_comments], 'regexp_match(table_id, "^2016")')) comments JOIN [subreddits.states] states ON states.subreddit = comments.subreddit WHERE states.state = 'userInput' GROUP BY state ORDER BY per DESC IGNORE CASE ",
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: " SELECT cities.city, SUM(CASE WHEN REGEXP_MATCH(body, r'(?i)\b(airbnb)\b') THEN 1 ELSE 0 END) * 1000 / COUNT(*) AS per FROM ( SELECT body, subreddit FROM TABLE_QUERY([fh-bigquery:reddit_comments], 'regexp_match(table_id, "^(2016|2015_(0[7-9]|1[0-2]))")')) comments JOIN [subreddits.cities] cities ON comments.subreddit = cities.subreddit GROUP BY cities.city ORDER BY per DESC IGNORE CASE ",
inputSQL: " SELECT cities.city, SUM(CASE WHEN REGEXP_MATCH(body, r'(?i)\b(airbnb)\b') THEN 1 ELSE 0 END) * 1000 / COUNT(*) AS per FROM ( SELECT body, subreddit FROM TABLE_QUERY([fh-bigquery:reddit_comments], 'regexp_match(table_id, "^(2016|2015_(0[7-9]|1[0-2]))")')) comments JOIN [subreddits.cities] cities ON comments.subreddit = cities.subreddit JOIN [fh-bigquery:freebase20140119.triples_lang_en] aliases ON aliases.sub = cities.mid WHERE (aliases.pred = '/common/topic/alias' OR aliases.pred = '/type/object/name') AND aliases.obj = 'userInput' GROUP BY cities.city ORDER BY per DESC IGNORE CASE ",
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: " SELECT states.state AS state, SUM(CASE WHEN REGEXP_MATCH(body, r'(?i)\b(ted cruz)\b') THEN 1 ELSE 0 END) * 1000 / COUNT(*) AS per FROM ( SELECT * FROM TABLE_QUERY([fh-bigquery:reddit_comments], 'regexp_match(table_id, "^2016")')) comments JOIN [subreddits.states] states ON states.subreddit = comments.subreddit GROUP BY state ORDER BY per DESC IGNORE CASE ",
inputSQL: " SELECT states.state AS state, SUM(CASE WHEN REGEXP_MATCH(body, r'(?i)\b(ted cruz)\b') THEN 1 ELSE 0 END) * 1000 / COUNT(*) AS per FROM ( SELECT * FROM TABLE_QUERY([fh-bigquery:reddit_comments], 'regexp_match(table_id, "^2016")')) comments JOIN [subreddits.states] states ON states.subreddit = comments.subreddit WHERE states.state = 'userInput' GROUP BY state ORDER BY per DESC IGNORE CASE ",
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: " SELECT states.state AS state, SUM(CASE WHEN REGEXP_MATCH(body, r'(?i)\b(bernie|sanders|#?feelthebern)\b') THEN 1 ELSE 0 END) * 100 / COUNT(*) AS per FROM ( SELECT * FROM TABLE_QUERY([fh-bigquery:reddit_comments], 'regexp_match(table_id, "^2016")')) comments JOIN [subreddits.states] states ON states.subreddit = comments.subreddit GROUP BY state ORDER BY per DESC IGNORE CASE ",
inputSQL: " SELECT states.state AS state, SUM(CASE WHEN REGEXP_MATCH(body, r'(?i)\b(bernie|sanders|#?feelthebern)\b') THEN 1 ELSE 0 END) * 100 / COUNT(*) AS per FROM ( SELECT * FROM TABLE_QUERY([fh-bigquery:reddit_comments], 'regexp_match(table_id, "^2016")')) comments JOIN [subreddits.states] states ON states.subreddit = comments.subreddit WHERE states.state = 'userInput' GROUP BY state ORDER BY per DESC IGNORE CASE ",
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: " SELECT cities.city, SUM(CASE WHEN REGEXP_MATCH(body, r'(?i)\b(gas prices?|price of gas)\b') THEN 1 ELSE 0 END) * 10000 / COUNT(*) AS per FROM ( SELECT body, subreddit FROM TABLE_QUERY([fh-bigquery:reddit_comments], 'regexp_match(table_id, "^(2016|2015_(0[7-9]|1[0-2]))")')) comments JOIN [subreddits.cities] cities ON comments.subreddit = cities.subreddit GROUP BY cities.city ORDER BY per DESC IGNORE CASE ",
inputSQL: " SELECT cities.city, SUM(CASE WHEN REGEXP_MATCH(body, r'(?i)\b(gas prices?|price of gas)\b') THEN 1 ELSE 0 END) * 10000 / COUNT(*) AS per FROM ( SELECT body, subreddit FROM TABLE_QUERY([fh-bigquery:reddit_comments], 'regexp_match(table_id, "^(2016|2015_(0[7-9]|1[0-2]))")')) comments JOIN [subreddits.cities] cities ON comments.subreddit = cities.subreddit JOIN [fh-bigquery:freebase20140119.triples_lang_en] aliases ON aliases.sub = cities.mid WHERE (aliases.pred = '/common/topic/alias' OR aliases.pred = '/type/object/name') AND aliases.obj = 'userInput' GROUP BY cities.city ORDER BY per DESC IGNORE CASE ",
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: " SELECT states.state AS state, SUM(CASE WHEN REGEXP_MATCH(body, r'(?i)\b(kasich)\b') THEN 1 ELSE 0 END) * 100 / COUNT(*) AS per FROM ( SELECT * FROM TABLE_QUERY([fh-bigquery:reddit_comments], 'regexp_match(table_id, "^2016")')) comments JOIN [subreddits.states] states ON states.subreddit = comments.subreddit GROUP BY state ORDER BY per DESC IGNORE CASE ",
inputSQL: " SELECT states.state AS state, SUM(CASE WHEN REGEXP_MATCH(body, r'(?i)\b(kasich)\b') THEN 1 ELSE 0 END) * 100 / COUNT(*) AS per FROM ( SELECT * FROM TABLE_QUERY([fh-bigquery:reddit_comments], 'regexp_match(table_id, "^2016")')) comments JOIN [subreddits.states] states ON states.subreddit = comments.subreddit WHERE states.state = 'userInput' GROUP BY state ORDER BY per DESC IGNORE CASE ",
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: " SELECT other.country, COUNT(*) AS total FROM ( SELECT ccode, other FROM ( SELECT Actor1Code AS ccode, Actor2Code AS other, Year FROM [gdelt-bq:full.events]) cc1, ( SELECT Actor1Code AS other, Actor2Code AS ccode, Year FROM [gdelt-bq:full.events]) cc2 WHERE Year = 2015) events JOIN [gdelt-bq:extra.countryinfo] country ON country.iso3 = events.ccode JOIN [gdelt-bq:extra.countryinfo] other ON events.other = other.iso3 WHERE country.country = 'China' GROUP BY country.country, other.country ORDER BY total DESC IGNORE CASE ",
inputSQL: " SELECT other.country, COUNT(UNIQUE(events.id)) AS total FROM ( SELECT ccode, other, id FROM ( SELECT Actor1Code AS ccode, Actor2Code AS other, Year, GLOBALEVENTID as id FROM [gdelt-bq:full.events]) cc1, ( SELECT Actor1Code AS other, Actor2Code AS ccode, Year, GLOBALEVENTID as id FROM [gdelt-bq:full.events]) cc2 WHERE Year = 2015) events JOIN [gdelt-bq:extra.countryinfo] country ON country.iso3 = events.ccode JOIN [gdelt-bq:extra.countryinfo] other ON events.other = other.iso3 JOIN ( SELECT * FROM ( SELECT sub, obj AS alias, CASE WHEN obj = 'userInput' THEN 1 ELSE 0 END AS input, SUM(input) OVER (PARTITION BY sub) AS input_alias FROM [fh-bigquery:freebase20140119.triples_lang_en] WHERE (pred = '/common/topic/alias' OR pred = '/type/object/name')) WHERE input_alias > 0) aliases ON aliases.alias = other.country WHERE country.country = 'China' GROUP BY country.country, other.country ORDER BY total DESC IGNORE CASE ",
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: " SELECT other.country, COUNT(*) AS total FROM ( SELECT ccode, other FROM ( SELECT Actor1Code AS ccode, Actor2Code AS other, Year FROM [gdelt-bq:full.events]) cc1, ( SELECT Actor1Code AS other, Actor2Code AS ccode, Year FROM [gdelt-bq:full.events]) cc2 WHERE Year = 2015) events JOIN [gdelt-bq:extra.countryinfo] country ON country.iso3 = events.ccode JOIN [gdelt-bq:extra.countryinfo] other ON events.other = other.iso3 WHERE country.country = 'North Korea' GROUP BY country.country, other.country ORDER BY total DESC IGNORE CASE ",
inputSQL: " SELECT other.country, COUNT(UNIQUE(events.id)) AS total FROM ( SELECT ccode, other, id FROM ( SELECT Actor1Code AS ccode, Actor2Code AS other, Year, GLOBALEVENTID as id FROM [gdelt-bq:full.events]) cc1, ( SELECT Actor1Code AS other, Actor2Code AS ccode, Year, GLOBALEVENTID as id FROM [gdelt-bq:full.events]) cc2 WHERE Year = 2015) events JOIN [gdelt-bq:extra.countryinfo] country ON country.iso3 = events.ccode JOIN [gdelt-bq:extra.countryinfo] other ON events.other = other.iso3 JOIN ( SELECT * FROM ( SELECT sub, obj AS alias, CASE WHEN obj = 'userInput' THEN 1 ELSE 0 END AS input, SUM(input) OVER (PARTITION BY sub) AS input_alias FROM [fh-bigquery:freebase20140119.triples_lang_en] WHERE (pred = '/common/topic/alias' OR pred = '/type/object/name')) WHERE input_alias > 0) aliases ON aliases.alias = other.country WHERE country.country = 'North Korea' GROUP BY country.country, other.country ORDER BY total DESC IGNORE CASE ",
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: " SELECT topic_wiki.name AS name, SUM(requests) AS total FROM ( SELECT topic.mid AS mid, topic.name AS name, REPLACE(REPLACE(wiki.title, '$0028', '('), '$0029', ')') AS title FROM ( SELECT mid, name FROM [freebase_tags.newspapers]) topic JOIN ( SELECT REGEXP_REPLACE(REGEXP_REPLACE(obj, '/wikipedia/en/', ''), '/wikipedia/id/', '') AS title, sub AS mid FROM [fh-bigquery:freebase20140119.triples_nolang] WHERE obj CONTAINS '/wikipedia/en' OR obj CONTAINS '/wikipedia/id' AND pred = '/type/object/key') wiki ON wiki.mid = topic.mid) topic_wiki JOIN [fh-bigquery:wikipedia.pagecounts_201605] views ON views.title = topic_wiki.title GROUP BY mid, name ORDER BY total DESC LIMIT 10 IGNORE CASE ",
inputSQL: " SELECT topic_wiki.name AS name, SUM(requests) AS total FROM ( SELECT topic.mid AS mid, topic.name AS name, REPLACE(REPLACE(wiki.title, '$0028', '('), '$0029', ')') AS title FROM ( SELECT mid, name FROM [freebase_tags.newspapers]) topic JOIN ( SELECT REGEXP_REPLACE(REGEXP_REPLACE(obj, '/wikipedia/en/', ''), '/wikipedia/id/', '') AS title, sub AS mid FROM [fh-bigquery:freebase20140119.triples_nolang] WHERE obj CONTAINS '/wikipedia/en' OR obj CONTAINS '/wikipedia/id' AND pred = '/type/object/key') wiki ON wiki.mid = topic.mid WHERE topic.name CONTAINS 'userInput') topic_wiki JOIN [fh-bigquery:wikipedia.pagecounts_201605] views ON views.title = topic_wiki.title GROUP BY mid, name ORDER BY total DESC LIMIT 10 IGNORE CASE ",
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: " SELECT topic_wiki.name AS name, SUM(requests) AS total FROM ( SELECT topic.mid AS mid, topic.name AS name, REPLACE(REPLACE(wiki.title, '$0028', '('), '$0029', ')') AS title FROM ( SELECT mid, name FROM [freebase_tags.magazines]) topic JOIN ( SELECT REGEXP_REPLACE(REGEXP_REPLACE(obj, '/wikipedia/en/', ''), '/wikipedia/id/', '') AS title, sub AS mid FROM [fh-bigquery:freebase20140119.triples_nolang] WHERE obj CONTAINS '/wikipedia/en' OR obj CONTAINS '/wikipedia/id' AND pred = '/type/object/key') wiki ON wiki.mid = topic.mid) topic_wiki JOIN [fh-bigquery:wikipedia.pagecounts_201605] views ON views.title = topic_wiki.title GROUP BY mid, name ORDER BY total DESC LIMIT 10 IGNORE CASE ",
inputSQL: " SELECT topic_wiki.name AS name, SUM(requests) AS total FROM ( SELECT topic.mid AS mid, topic.name AS name, REPLACE(REPLACE(wiki.title, '$0028', '('), '$0029', ')') AS title FROM ( SELECT mid, name FROM [freebase_tags.magazines]) topic JOIN ( SELECT REGEXP_REPLACE(REGEXP_REPLACE(obj, '/wikipedia/en/', ''), '/wikipedia/id/', '') AS title, sub AS mid FROM [fh-bigquery:freebase20140119.triples_nolang] WHERE obj CONTAINS '/wikipedia/en' OR obj CONTAINS '/wikipedia/id' AND pred = '/type/object/key') wiki ON wiki.mid = topic.mid WHERE topic.name CONTAINS 'userInput') topic_wiki JOIN [fh-bigquery:wikipedia.pagecounts_201605] views ON views.title = topic_wiki.title GROUP BY mid, name ORDER BY total DESC LIMIT 10 IGNORE CASE ",
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: " SELECT codes.country, SUM(CASE WHEN events.EventCode IN ('012') THEN 1 ELSE 0 END) * 100 / COUNT(*) as per, COUNT(*) as total FROM [gdelt-bq:full.events] events JOIN [gdelt-bq:extra.countryinfo] codes ON events.Actor1CountryCode = codes.iso3 WHERE Year = 2015 GROUP BY codes.country HAVING total > 1 ORDER BY per DESC IGNORE CASE ",
inputSQL: " SELECT codes.country, SUM(CASE WHEN events.EventCode IN ('012') THEN 1 ELSE 0 END) * 100 / COUNT(*) as per, COUNT(*) as total FROM [gdelt-bq:full.events] events JOIN [gdelt-bq:extra.countryinfo] codes ON events.Actor1CountryCode = codes.iso3 JOIN ( SELECT * FROM ( SELECT sub, obj AS alias, CASE WHEN obj = 'userInput' THEN 1 ELSE 0 END AS input, SUM(input) OVER (PARTITION BY sub) AS input_alias FROM [fh-bigquery:freebase20140119.triples_lang_en] WHERE (pred = '/common/topic/alias' OR pred = '/type/object/name')) WHERE input_alias > 0) aliases ON aliases.alias = codes.country WHERE Year = 2015 GROUP BY codes.country HAVING total > 1 ORDER BY per DESC IGNORE CASE ",
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: " SELECT codes.country, SUM(CASE WHEN events.EventCode IN ('013') THEN 1 ELSE 0 END) * 100 / COUNT(*) as per, COUNT(*) as total FROM [gdelt-bq:full.events] events JOIN [gdelt-bq:extra.countryinfo] codes ON events.Actor1CountryCode = codes.iso3 WHERE Year = 2015 GROUP BY codes.country HAVING total > 1 ORDER BY per DESC IGNORE CASE ",
inputSQL: " SELECT codes.country, SUM(CASE WHEN events.EventCode IN ('013') THEN 1 ELSE 0 END) * 100 / COUNT(*) as per, COUNT(*) as total FROM [gdelt-bq:full.events] events JOIN [gdelt-bq:extra.countryinfo] codes ON events.Actor1CountryCode = codes.iso3 JOIN ( SELECT * FROM ( SELECT sub, obj AS alias, CASE WHEN obj = 'userInput' THEN 1 ELSE 0 END AS input, SUM(input) OVER (PARTITION BY sub) AS input_alias FROM [fh-bigquery:freebase20140119.triples_lang_en] WHERE (pred = '/common/topic/alias' OR pred = '/type/object/name')) WHERE input_alias > 0) aliases ON aliases.alias = codes.country WHERE Year = 2015 GROUP BY codes.country HAVING total > 1 ORDER BY per DESC IGNORE CASE ",
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: " SELECT codes.country, 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, COUNT(*) as total FROM [gdelt-bq:full.events] events JOIN [gdelt-bq:extra.countryinfo] codes ON events.Actor1CountryCode = codes.iso3 WHERE Year = 2015 GROUP BY codes.country HAVING total > 1 ORDER BY per DESC IGNORE CASE ",
inputSQL: " SELECT codes.country, 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, COUNT(*) as total FROM [gdelt-bq:full.events] events JOIN [gdelt-bq:extra.countryinfo] codes ON events.Actor1CountryCode = codes.iso3 JOIN ( SELECT * FROM ( SELECT sub, obj AS alias, CASE WHEN obj = 'userInput' THEN 1 ELSE 0 END AS input, SUM(input) OVER (PARTITION BY sub) AS input_alias FROM [fh-bigquery:freebase20140119.triples_lang_en] WHERE (pred = '/common/topic/alias' OR pred = '/type/object/name')) WHERE input_alias > 0) aliases ON aliases.alias = codes.country WHERE Year = 2015 GROUP BY codes.country HAVING total > 1 ORDER BY per DESC IGNORE CASE ",
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: " SELECT topic_wiki.name AS name, SUM(requests) AS total FROM ( SELECT topic.mid AS mid, topic.name AS name, REPLACE(REPLACE(wiki.title, '$0028', '('), '$0029', ')') AS title FROM ( SELECT mid, name FROM [freebase_tags.inventors]) topic JOIN ( SELECT REGEXP_REPLACE(REGEXP_REPLACE(obj, '/wikipedia/en/', ''), '/wikipedia/id/', '') AS title, sub AS mid FROM [fh-bigquery:freebase20140119.triples_nolang] WHERE obj CONTAINS '/wikipedia/en' OR obj CONTAINS '/wikipedia/id' AND pred = '/type/object/key') wiki ON wiki.mid = topic.mid) topic_wiki JOIN [fh-bigquery:wikipedia.pagecounts_201605] views ON views.title = topic_wiki.title GROUP BY mid, name ORDER BY total DESC LIMIT 10 IGNORE CASE ",
inputSQL: " SELECT topic_wiki.name AS name, SUM(requests) AS total FROM ( SELECT topic.mid AS mid, topic.name AS name, REPLACE(REPLACE(wiki.title, '$0028', '('), '$0029', ')') AS title FROM ( SELECT mid, name FROM [freebase_tags.inventors]) topic JOIN ( SELECT REGEXP_REPLACE(REGEXP_REPLACE(obj, '/wikipedia/en/', ''), '/wikipedia/id/', '') AS title, sub AS mid FROM [fh-bigquery:freebase20140119.triples_nolang] WHERE obj CONTAINS '/wikipedia/en' OR obj CONTAINS '/wikipedia/id' AND pred = '/type/object/key') wiki ON wiki.mid = topic.mid WHERE topic.name CONTAINS 'userInput') topic_wiki JOIN [fh-bigquery:wikipedia.pagecounts_201605] views ON views.title = topic_wiki.title GROUP BY mid, name ORDER BY total DESC LIMIT 10 IGNORE CASE ",
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: " SELECT topic_wiki.name AS name, SUM(requests) AS total FROM ( SELECT topic.mid AS mid, topic.name AS name, REPLACE(REPLACE(wiki.title, '$0028', '('), '$0029', ')') AS title FROM ( SELECT mid, name FROM [freebase_tags.invention]) topic JOIN ( SELECT REGEXP_REPLACE(REGEXP_REPLACE(obj, '/wikipedia/en/', ''), '/wikipedia/id/', '') AS title, sub AS mid FROM [fh-bigquery:freebase20140119.triples_nolang] WHERE obj CONTAINS '/wikipedia/en' OR obj CONTAINS '/wikipedia/id' AND pred = '/type/object/key') wiki ON wiki.mid = topic.mid) topic_wiki JOIN [fh-bigquery:wikipedia.pagecounts_201605] views ON views.title = topic_wiki.title GROUP BY mid, name ORDER BY total DESC LIMIT 10 IGNORE CASE ",
inputSQL: " SELECT topic_wiki.name AS name, SUM(requests) AS total FROM ( SELECT topic.mid AS mid, topic.name AS name, REPLACE(REPLACE(wiki.title, '$0028', '('), '$0029', ')') AS title FROM ( SELECT mid, name FROM [freebase_tags.invention]) topic JOIN ( SELECT REGEXP_REPLACE(REGEXP_REPLACE(obj, '/wikipedia/en/', ''), '/wikipedia/id/', '') AS title, sub AS mid FROM [fh-bigquery:freebase20140119.triples_nolang] WHERE obj CONTAINS '/wikipedia/en' OR obj CONTAINS '/wikipedia/id' AND pred = '/type/object/key') wiki ON wiki.mid = topic.mid WHERE topic.name CONTAINS 'userInput') topic_wiki JOIN [fh-bigquery:wikipedia.pagecounts_201605] views ON views.title = topic_wiki.title GROUP BY mid, name ORDER BY total DESC LIMIT 10 IGNORE CASE ",
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: " SELECT topic.name, COUNT(UNIQUE(comments.id)) AS total FROM [freebase_tags.countries] topic CROSS JOIN ( SELECT id, body FROM [fh-bigquery:reddit_comments.2016_05] WHERE subreddit = 'worldnews') comments WHERE comments.body CONTAINS CONCAT(' ', topic.name, ' ') GROUP BY topic.name, ORDER BY total DESC IGNORE CASE ",
inputSQL: " SELECT topic.name, COUNT(UNIQUE(comments.id)) AS total FROM ( SELECT * FROM [freebase_tags.countries] WHERE name = 'userInput') topic CROSS JOIN ( SELECT id, body FROM [fh-bigquery:reddit_comments.2016_05] WHERE subreddit = 'worldnews') comments WHERE comments.body CONTAINS CONCAT(' ', topic.name, ' ') GROUP BY topic.name, ORDER BY total DESC IGNORE CASE ",
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: " SELECT topic.name, COUNT(UNIQUE(comments.id)) AS total FROM [freebase_tags.countries] topic CROSS JOIN ( SELECT id, body FROM [fh-bigquery:reddit_comments.2016_05] WHERE subreddit = 'news') comments WHERE comments.body CONTAINS CONCAT(' ', topic.name, ' ') GROUP BY topic.name, ORDER BY total DESC IGNORE CASE ",
inputSQL: " SELECT topic.name, COUNT(UNIQUE(comments.id)) AS total FROM ( SELECT * FROM [freebase_tags.countries] WHERE name = 'userInput') topic CROSS JOIN ( SELECT id, body FROM [fh-bigquery:reddit_comments.2016_05] WHERE subreddit = 'news') comments WHERE comments.body CONTAINS CONCAT(' ', topic.name, ' ') GROUP BY topic.name, ORDER BY total DESC IGNORE CASE ",
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: " SELECT topic.name, COUNT(UNIQUE(comments.id)) AS total FROM [freebase_tags.countries] topic CROSS JOIN ( SELECT id, body FROM [fh-bigquery:reddit_comments.2016_05] WHERE subreddit = 'travel') comments WHERE comments.body CONTAINS CONCAT(' ', topic.name, ' ') GROUP BY topic.name, ORDER BY total DESC IGNORE CASE ",
inputSQL: " SELECT topic.name, COUNT(UNIQUE(comments.id)) AS total FROM ( SELECT * FROM [freebase_tags.countries] WHERE name = 'userInput') topic CROSS JOIN ( SELECT id, body FROM [fh-bigquery:reddit_comments.2016_05] WHERE subreddit = 'travel') comments WHERE comments.body CONTAINS CONCAT(' ', topic.name, ' ') GROUP BY topic.name, ORDER BY total DESC IGNORE CASE ",
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: " SELECT pop_topic.name, COUNT(UNIQUE(comments.id)) AS total FROM ( SELECT topic_wiki.mid AS mid, topic_wiki.name AS name, topic_wiki.title AS title, SUM(requests) AS total FROM ( SELECT topic.mid AS mid, topic.name AS name, REPLACE(REPLACE(wiki.title, '$0028', '('), '$0029', ')') AS title FROM ( SELECT mid, name FROM [freebase_tags.cities]) topic JOIN ( SELECT REGEXP_REPLACE(REGEXP_REPLACE(obj, '/wikipedia/en/', ''), '/wikipedia/id/', '') AS title, sub AS mid FROM [fh-bigquery:freebase20140119.triples_nolang] WHERE obj CONTAINS '/wikipedia/en' OR obj CONTAINS '/wikipedia/id' AND pred = '/type/object/key') wiki ON wiki.mid = topic.mid) topic_wiki JOIN [fh-bigquery:wikipedia.pagecounts_201605] views ON views.title = topic_wiki.title GROUP BY mid, name, title ORDER BY total DESC LIMIT 500 ) pop_topic JOIN ( SELECT sub, obj AS alias FROM [fh-bigquery:freebase20140119.triples_lang_en] WHERE (pred = '/common/topic/alias' OR pred = '/type/object/name') AND obj NOT IN ( SELECT word FROM [words.top10000] LIMIT 2000)) aliases ON aliases.sub = pop_topic.mid CROSS JOIN ( SELECT id, body FROM [fh-bigquery:reddit_comments.2016_05] WHERE subreddit = 'news') comments WHERE comments.body CONTAINS CONCAT(' ', aliases.alias, ' ') GROUP BY pop_topic.name, ORDER BY total DESC IGNORE CASE ",
inputSQL: " SELECT pop_topic.name, COUNT(UNIQUE(comments.id)) AS total FROM ( SELECT topic_wiki.mid AS mid, topic_wiki.name AS name, topic_wiki.title AS title, SUM(requests) AS total FROM ( SELECT topic.mid AS mid, topic.name AS name, REPLACE(REPLACE(wiki.title, '$0028', '('), '$0029', ')') AS title FROM ( SELECT mid, name FROM [freebase_tags.cities]) topic JOIN ( SELECT REGEXP_REPLACE(REGEXP_REPLACE(obj, '/wikipedia/en/', ''), '/wikipedia/id/', '') AS title, sub AS mid FROM [fh-bigquery:freebase20140119.triples_nolang] WHERE obj CONTAINS '/wikipedia/en' OR obj CONTAINS '/wikipedia/id' AND pred = '/type/object/key') wiki ON wiki.mid = topic.mid) topic_wiki JOIN [fh-bigquery:wikipedia.pagecounts_201605] views ON views.title = topic_wiki.title GROUP BY mid, name, title ORDER BY total DESC LIMIT 500 ) pop_topic JOIN ( SELECT sub, obj AS alias, CASE WHEN obj = 'userInput' THEN 1 ELSE 0 END AS input, SUM(input) OVER (PARTITION BY sub) AS input_alias FROM [fh-bigquery:freebase20140119.triples_lang_en] WHERE (pred = '/common/topic/alias' OR pred = '/type/object/name') AND obj NOT IN ( SELECT word FROM [words.top10000] LIMIT 2000)) aliases ON aliases.sub = pop_topic.mid CROSS JOIN ( SELECT id, body FROM [fh-bigquery:reddit_comments.2016_05] WHERE subreddit = 'news') comments WHERE aliases.input_alias > 0 AND comments.body CONTAINS CONCAT(' ', aliases.alias, ' ') GROUP BY pop_topic.name, ORDER BY total DESC IGNORE CASE ",
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: " SELECT topic_wiki.name AS name, SUM(requests) AS total FROM ( SELECT topic.mid AS mid, topic.name AS name, REPLACE(REPLACE(wiki.title, '$0028', '('), '$0029', ')') AS title FROM ( SELECT mid, name FROM [freebase_tags.presidents]) topic JOIN ( SELECT REGEXP_REPLACE(REGEXP_REPLACE(obj, '/wikipedia/en/', ''), '/wikipedia/id/', '') AS title, sub AS mid FROM [fh-bigquery:freebase20140119.triples_nolang] WHERE obj CONTAINS '/wikipedia/en' OR obj CONTAINS '/wikipedia/id' AND pred = '/type/object/key') wiki ON wiki.mid = topic.mid) topic_wiki JOIN [fh-bigquery:wikipedia.pagecounts_201605] views ON views.title = topic_wiki.title GROUP BY mid, name ORDER BY total DESC LIMIT 10 IGNORE CASE ",
inputSQL: " SELECT topic_wiki.name AS name, SUM(requests) AS total FROM ( SELECT topic.mid AS mid, topic.name AS name, REPLACE(REPLACE(wiki.title, '$0028', '('), '$0029', ')') AS title FROM ( SELECT mid, name FROM [freebase_tags.presidents]) topic JOIN ( SELECT REGEXP_REPLACE(REGEXP_REPLACE(obj, '/wikipedia/en/', ''), '/wikipedia/id/', '') AS title, sub AS mid FROM [fh-bigquery:freebase20140119.triples_nolang] WHERE obj CONTAINS '/wikipedia/en' OR obj CONTAINS '/wikipedia/id' AND pred = '/type/object/key') wiki ON wiki.mid = topic.mid WHERE topic.name CONTAINS 'userInput') topic_wiki JOIN [fh-bigquery:wikipedia.pagecounts_201605] views ON views.title = topic_wiki.title GROUP BY mid, name ORDER BY total DESC LIMIT 10 IGNORE CASE ",
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: " SELECT replace(school.name, "_", " ") AS name, SUM(requests) AS total FROM [wikidata_tags.business_schools_clean] school JOIN EACH ( SELECT title, requests FROM [fh-bigquery:wikipedia.pagecounts_201605]) views ON views.title = name GROUP BY name ORDER BY total DESC IGNORE case ",
inputSQL: " SELECT replace(school.name, "_", " ") AS name, SUM(requests) AS total FROM [wikidata_tags.business_schools_clean] school JOIN EACH ( SELECT title, requests FROM [fh-bigquery:wikipedia.pagecounts_201605] WHERE title = 'userInput' OR title CONTAINS CONCAT('_', replace('userInput', ' ', '_' )) OR title CONTAINS CONCAT(replace('userInput', ' ', '_' ), '_') ) views ON views.title = name GROUP BY name ORDER BY total DESC IGNORE case ",
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: " SELECT replace(school.name, "_", " ") AS name, SUM(requests) AS total FROM [wikidata_tags.pokemon_species] school JOIN EACH ( SELECT title, requests FROM [fh-bigquery:wikipedia.pagecounts_201605]) views ON views.title = name GROUP BY name ORDER BY total DESC IGNORE case ",
inputSQL: " SELECT replace(school.name, "_", " ") AS name, SUM(requests) AS total FROM [wikidata_tags.pokemon_species] school JOIN EACH ( SELECT title, requests FROM [fh-bigquery:wikipedia.pagecounts_201605] WHERE title = 'userInput' OR title CONTAINS CONCAT('_', replace('userInput', ' ', '_' )) OR title CONTAINS CONCAT(replace('userInput', ' ', '_' ), '_') ) views ON views.title = name GROUP BY name ORDER BY total DESC IGNORE case ",
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: " SELECT countries.country AS country, SUM(CASE WHEN comments.body CONTAINS 'brexit' THEN 1 ELSE 0 END) * 100 / COUNT(*) AS per FROM ( SELECT * FROM TABLE_QUERY([fh-bigquery:reddit_comments], 'regexp_match(table_id, "2016")')) comments JOIN [subreddits.european_countries] countries ON countries.subreddit = comments.subreddit WHERE country != 'United Kingdom' GROUP BY country ORDER BY per DESC IGNORE CASE ",
inputSQL: " SELECT countries.country AS country, SUM(CASE WHEN comments.body CONTAINS 'brexit' THEN 1 ELSE 0 END) * 100 / COUNT(*) AS per FROM ( SELECT * FROM TABLE_QUERY([fh-bigquery:reddit_comments], 'regexp_match(table_id, "2016")')) comments JOIN [subreddits.european_countries] countries ON countries.subreddit = comments.subreddit WHERE country != 'United Kingdom' AND country = 'userInput' GROUP BY country ORDER BY per DESC IGNORE CASE ",
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? ",
rowIndex: 356,
approved: true,
params: {
freebase_tag: "countries",
subreddit: "economy"
},
sql: " SELECT topic.name, COUNT(UNIQUE(comments.id)) AS total FROM [freebase_tags.countries] topic CROSS JOIN ( SELECT id, body FROM [fh-bigquery:reddit_comments.2016_05] WHERE subreddit = 'economy') comments WHERE comments.body CONTAINS CONCAT(' ', topic.name, ' ') GROUP BY topic.name, ORDER BY total DESC IGNORE CASE ",
inputSQL: " SELECT topic.name, COUNT(UNIQUE(comments.id)) AS total FROM ( SELECT * FROM [freebase_tags.countries] WHERE name = 'userInput') topic CROSS JOIN ( SELECT id, body FROM [fh-bigquery:reddit_comments.2016_05] WHERE subreddit = 'economy') comments WHERE comments.body CONTAINS CONCAT(' ', topic.name, ' ') GROUP BY topic.name, ORDER BY total DESC IGNORE CASE ",
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: " SELECT topic.name, COUNT(UNIQUE(comments.id)) AS total FROM [wikidata_tags.pokemon_species] topic CROSS JOIN ( SELECT id, body FROM [fh-bigquery:reddit_comments.2016_05] WHERE subreddit = 'pokemongo') comments WHERE topic.name NOT IN ( SELECT word FROM [words.top10000] LIMIT 0) AND comments.body CONTAINS CONCAT(' ', topic.name, ' ') GROUP BY topic.name, ORDER BY total DESC IGNORE CASE ",
inputSQL: " SELECT topic.name, COUNT(UNIQUE(comments.id)) AS total FROM ( SELECT * FROM [wikidata_tags.pokemon_species] WHERE name = 'userInput') topic CROSS JOIN ( SELECT id, body FROM [fh-bigquery:reddit_comments.2016_05] WHERE subreddit = 'pokemongo') comments WHERE comments.body CONTAINS CONCAT(' ', topic.name, ' ') GROUP BY topic.name, ORDER BY total DESC IGNORE CASE ",
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: " SELECT topic.name, COUNT(UNIQUE(comments.id)) AS total FROM [wikidata_tags.programming_languages] topic CROSS JOIN ( SELECT id, body FROM [fh-bigquery:reddit_comments.2016_05] WHERE subreddit = 'webdev') comments WHERE topic.name NOT IN ( SELECT word FROM [words.top10000] LIMIT 0) AND comments.body CONTAINS CONCAT(' ', topic.name, ' ') GROUP BY topic.name, ORDER BY total DESC IGNORE CASE ",
inputSQL: " SELECT topic.name, COUNT(UNIQUE(comments.id)) AS total FROM ( SELECT * FROM [wikidata_tags.programming_languages] WHERE name = 'userInput') topic CROSS JOIN ( SELECT id, body FROM [fh-bigquery:reddit_comments.2016_05] WHERE subreddit = 'webdev') comments WHERE comments.body CONTAINS CONCAT(' ', topic.name, ' ') GROUP BY topic.name, ORDER BY total DESC IGNORE CASE ",
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? ",
rowIndex: 366,
approved: true,
params: {
regex: "(?i)\b(airbnb)\b",
per: 100
},
sql: " SELECT states.state AS state, SUM(CASE WHEN REGEXP_MATCH(body, r'(?i)\b(airbnb)\b') THEN 1 ELSE 0 END) * 100 / COUNT(*) AS per FROM ( SELECT * FROM TABLE_QUERY([fh-bigquery:reddit_comments], 'regexp_match(table_id, "^2016")')) comments JOIN [subreddits.states] states ON states.subreddit = comments.subreddit GROUP BY state ORDER BY per DESC IGNORE CASE ",
inputSQL: " SELECT states.state AS state, SUM(CASE WHEN REGEXP_MATCH(body, r'(?i)\b(airbnb)\b') THEN 1 ELSE 0 END) * 100 / COUNT(*) AS per FROM ( SELECT * FROM TABLE_QUERY([fh-bigquery:reddit_comments], 'regexp_match(table_id, "^2016")')) comments JOIN [subreddits.states] states ON states.subreddit = comments.subreddit WHERE states.state = 'userInput' GROUP BY state ORDER BY per DESC IGNORE CASE ",
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: " SELECT topic.name, COUNT(UNIQUE(comments.id)) AS total FROM [wikidata_tags.fortune_500] topic CROSS JOIN ( SELECT id, body FROM [fh-bigquery:reddit_comments.2016_05] WHERE subreddit = 'business') comments WHERE topic.name NOT IN ( SELECT word FROM [words.top10000] LIMIT 0) AND comments.body CONTAINS CONCAT(' ', topic.name, ' ') GROUP BY topic.name, ORDER BY total DESC IGNORE CASE ",
inputSQL: " SELECT topic.name, COUNT(UNIQUE(comments.id)) AS total FROM ( SELECT * FROM [wikidata_tags.fortune_500] WHERE name = 'userInput') topic CROSS JOIN ( SELECT id, body FROM [fh-bigquery:reddit_comments.2016_05] WHERE subreddit = 'business') comments WHERE comments.body CONTAINS CONCAT(' ', topic.name, ' ') GROUP BY topic.name, ORDER BY total DESC IGNORE CASE ",
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: " SELECT states.state AS state, SUM(CASE WHEN REGEXP_MATCH(body, r'(?i)\b(lyft|uber)\b') THEN 1 ELSE 0 END) * 100 / COUNT(*) AS per FROM ( SELECT * FROM TABLE_QUERY([fh-bigquery:reddit_comments], 'regexp_match(table_id, "^2016")')) comments JOIN [subreddits.states] states ON states.subreddit = comments.subreddit GROUP BY state ORDER BY per DESC IGNORE CASE ",
inputSQL: " SELECT states.state AS state, SUM(CASE WHEN REGEXP_MATCH(body, r'(?i)\b(lyft|uber)\b') THEN 1 ELSE 0 END) * 100 / COUNT(*) AS per FROM ( SELECT * FROM TABLE_QUERY([fh-bigquery:reddit_comments], 'regexp_match(table_id, "^2016")')) comments JOIN [subreddits.states] states ON states.subreddit = comments.subreddit WHERE states.state = 'userInput' GROUP BY state ORDER BY per DESC IGNORE CASE ",
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: " SELECT states.state AS state, SUM(CASE WHEN REGEXP_MATCH(body, r'(?i)\b(yelp)\b') THEN 1 ELSE 0 END) * 100 / COUNT(*) AS per FROM ( SELECT * FROM TABLE_QUERY([fh-bigquery:reddit_comments], 'regexp_match(table_id, "^2016")')) comments JOIN [subreddits.states] states ON states.subreddit = comments.subreddit GROUP BY state ORDER BY per DESC IGNORE CASE ",
inputSQL: " SELECT states.state AS state, SUM(CASE WHEN REGEXP_MATCH(body, r'(?i)\b(yelp)\b') THEN 1 ELSE 0 END) * 100 / COUNT(*) AS per FROM ( SELECT * FROM TABLE_QUERY([fh-bigquery:reddit_comments], 'regexp_match(table_id, "^2016")')) comments JOIN [subreddits.states] states ON states.subreddit = comments.subreddit WHERE states.state = 'userInput' GROUP BY state ORDER BY per DESC IGNORE CASE ",
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: " SELECT states.state AS state, SUM(CASE WHEN REGEXP_MATCH(body, r'(?i)\b(republican|republicans)\b') THEN 1 ELSE 0 END) * 100 / COUNT(*) AS per FROM ( SELECT * FROM TABLE_QUERY([fh-bigquery:reddit_comments], 'regexp_match(table_id, "^2016")')) comments JOIN [subreddits.states] states ON states.subreddit = comments.subreddit GROUP BY state ORDER BY per DESC IGNORE CASE ",
inputSQL: " SELECT states.state AS state, SUM(CASE WHEN REGEXP_MATCH(body, r'(?i)\b(republican|republicans)\b') THEN 1 ELSE 0 END) * 100 / COUNT(*) AS per FROM ( SELECT * FROM TABLE_QUERY([fh-bigquery:reddit_comments], 'regexp_match(table_id, "^2016")')) comments JOIN [subreddits.states] states ON states.subreddit = comments.subreddit WHERE states.state = 'userInput' GROUP BY state ORDER BY per DESC IGNORE CASE ",
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: " SELECT states.state AS state, SUM(CASE WHEN REGEXP_MATCH(body, r'(?i)\b(democrat|democrats)\b') THEN 1 ELSE 0 END) * 100 / COUNT(*) AS per FROM ( SELECT * FROM TABLE_QUERY([fh-bigquery:reddit_comments], 'regexp_match(table_id, "^2016")')) comments JOIN [subreddits.states] states ON states.subreddit = comments.subreddit GROUP BY state ORDER BY per DESC IGNORE CASE ",
inputSQL: " SELECT states.state AS state, SUM(CASE WHEN REGEXP_MATCH(body, r'(?i)\b(democrat|democrats)\b') THEN 1 ELSE 0 END) * 100 / COUNT(*) AS per FROM ( SELECT * FROM TABLE_QUERY([fh-bigquery:reddit_comments], 'regexp_match(table_id, "^2016")')) comments JOIN [subreddits.states] states ON states.subreddit = comments.subreddit WHERE states.state = 'userInput' GROUP BY state ORDER BY per DESC IGNORE CASE ",
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: " SELECT states.state AS state, 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 FROM ( SELECT * FROM TABLE_QUERY([fh-bigquery:reddit_comments], 'regexp_match(table_id, "^2016")')) comments JOIN [subreddits.states] states ON states.subreddit = comments.subreddit GROUP BY state ORDER BY per DESC IGNORE CASE ",
inputSQL: " SELECT states.state AS state, 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 FROM ( SELECT * FROM TABLE_QUERY([fh-bigquery:reddit_comments], 'regexp_match(table_id, "^2016")')) comments JOIN [subreddits.states] states ON states.subreddit = comments.subreddit WHERE states.state = 'userInput' GROUP BY state ORDER BY per DESC IGNORE CASE ",
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: " SELECT topic.name, COUNT(UNIQUE(comments.id)) AS total FROM [wikidata_tags.fortune_500] topic CROSS JOIN ( SELECT id, body FROM [fh-bigquery:reddit_comments.2016_05] WHERE subreddit = 'personalfinance') comments WHERE topic.name NOT IN ( SELECT word FROM [words.top10000] LIMIT 10000) AND comments.body CONTAINS CONCAT(' ', topic.name, ' ') GROUP BY topic.name, ORDER BY total DESC IGNORE CASE ",
inputSQL: " SELECT topic.name, COUNT(UNIQUE(comments.id)) AS total FROM ( SELECT * FROM [wikidata_tags.fortune_500] WHERE name = 'userInput') topic CROSS JOIN ( SELECT id, body FROM [fh-bigquery:reddit_comments.2016_05] WHERE subreddit = 'personalfinance') comments WHERE comments.body CONTAINS CONCAT(' ', topic.name, ' ') GROUP BY topic.name, ORDER BY total DESC IGNORE CASE ",
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: " SELECT states.state AS state, SUM(CASE WHEN REGEXP_MATCH(body, r'(?i)\b(freelancing|freelance|freelancer)\b') THEN 1 ELSE 0 END) * 100 / COUNT(*) AS per FROM ( SELECT * FROM TABLE_QUERY([fh-bigquery:reddit_comments], 'regexp_match(table_id, "^2016")')) comments JOIN [subreddits.states] states ON states.subreddit = comments.subreddit GROUP BY state ORDER BY per DESC IGNORE CASE ",
inputSQL: " SELECT states.state AS state, SUM(CASE WHEN REGEXP_MATCH(body, r'(?i)\b(freelancing|freelance|freelancer)\b') THEN 1 ELSE 0 END) * 100 / COUNT(*) AS per FROM ( SELECT * FROM TABLE_QUERY([fh-bigquery:reddit_comments], 'regexp_match(table_id, "^2016")')) comments JOIN [subreddits.states] states ON states.subreddit = comments.subreddit WHERE states.state = 'userInput' GROUP BY state ORDER BY per DESC IGNORE CASE ",
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: " SELECT codes.country, SUM(CASE WHEN events.EventCode IN ('071') THEN 1 ELSE 0 END) * 1000 / COUNT(*) as per, COUNT(*) as total FROM [gdelt-bq:full.events] events JOIN [gdelt-bq:extra.countryinfo] codes ON events.Actor1CountryCode = codes.iso3 WHERE Year = 2016 GROUP BY codes.country HAVING total > 1 ORDER BY per DESC IGNORE CASE ",
inputSQL: " SELECT codes.country, SUM(CASE WHEN events.EventCode IN ('071') THEN 1 ELSE 0 END) * 1000 / COUNT(*) as per, COUNT(*) as total FROM [gdelt-bq:full.events] events JOIN [gdelt-bq:extra.countryinfo] codes ON events.Actor1CountryCode = codes.iso3 JOIN ( SELECT * FROM ( SELECT sub, obj AS alias, CASE WHEN obj = 'userInput' THEN 1 ELSE 0 END AS input, SUM(input) OVER (PARTITION BY sub) AS input_alias FROM [fh-bigquery:freebase20140119.triples_lang_en] WHERE (pred = '/common/topic/alias' OR pred = '/type/object/name')) WHERE input_alias > 0) aliases ON aliases.alias = codes.country WHERE Year = 2016 GROUP BY codes.country HAVING total > 1 ORDER BY per DESC IGNORE CASE ",
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: " SELECT codes.country, SUM(CASE WHEN events.EventCode IN ('072') THEN 1 ELSE 0 END) * 1000 / COUNT(*) as per, COUNT(*) as total FROM [gdelt-bq:full.events] events JOIN [gdelt-bq:extra.countryinfo] codes ON events.Actor1CountryCode = codes.iso3 WHERE Year = 2016 GROUP BY codes.country HAVING total > 1 ORDER BY per DESC IGNORE CASE ",
inputSQL: " SELECT codes.country, SUM(CASE WHEN events.EventCode IN ('072') THEN 1 ELSE 0 END) * 1000 / COUNT(*) as per, COUNT(*) as total FROM [gdelt-bq:full.events] events JOIN [gdelt-bq:extra.countryinfo] codes ON events.Actor1CountryCode = codes.iso3 JOIN ( SELECT * FROM ( SELECT sub, obj AS alias, CASE WHEN obj = 'userInput' THEN 1 ELSE 0 END AS input, SUM(input) OVER (PARTITION BY sub) AS input_alias FROM [fh-bigquery:freebase20140119.triples_lang_en] WHERE (pred = '/common/topic/alias' OR pred = '/type/object/name')) WHERE input_alias > 0) aliases ON aliases.alias = codes.country WHERE Year = 2016 GROUP BY codes.country HAVING total > 1 ORDER BY per DESC IGNORE CASE ",
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: " SELECT topic.name, COUNT(UNIQUE(comments.id)) AS total FROM [wikidata_tags.tv_stations] topic CROSS JOIN ( SELECT id, body FROM [fh-bigquery:reddit_comments.2016_05] WHERE subreddit = 'television') comments WHERE topic.name NOT IN ( SELECT word FROM [words.top10000] LIMIT 1000) AND comments.body CONTAINS CONCAT(' ', topic.name, ' ') GROUP BY topic.name, ORDER BY total DESC IGNORE CASE ",
inputSQL: " SELECT topic.name, COUNT(UNIQUE(comments.id)) AS total FROM ( SELECT * FROM [wikidata_tags.tv_stations] WHERE name = 'userInput') topic CROSS JOIN ( SELECT id, body FROM [fh-bigquery:reddit_comments.2016_05] WHERE subreddit = 'television') comments WHERE comments.body CONTAINS CONCAT(' ', topic.name, ' ') GROUP BY topic.name, ORDER BY total DESC IGNORE CASE ",
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: " SELECT topic.name, COUNT(UNIQUE(comments.id)) AS total FROM [wikidata_tags.fortune_500] topic CROSS JOIN ( SELECT id, body FROM [fh-bigquery:reddit_comments.2016_05] WHERE subreddit = 'investing') comments WHERE topic.name NOT IN ( SELECT word FROM [words.top10000] LIMIT 500) AND comments.body CONTAINS CONCAT(' ', topic.name, ' ') GROUP BY topic.name, ORDER BY total DESC IGNORE CASE ",
inputSQL: " SELECT topic.name, COUNT(UNIQUE(comments.id)) AS total FROM ( SELECT * FROM [wikidata_tags.fortune_500] WHERE name = 'userInput') topic CROSS JOIN ( SELECT id, body FROM [fh-bigquery:reddit_comments.2016_05] WHERE subreddit = 'investing') comments WHERE comments.body CONTAINS CONCAT(' ', topic.name, ' ') GROUP BY topic.name, ORDER BY total DESC IGNORE CASE ",
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: " SELECT topic.name, COUNT(UNIQUE(comments.id)) AS total FROM [freebase_tags.countries] topic CROSS JOIN ( SELECT id, body FROM [fh-bigquery:reddit_comments.2016_05] WHERE subreddit = 'worldpolitics') comments WHERE comments.body CONTAINS CONCAT(' ', topic.name, ' ') GROUP BY topic.name, ORDER BY total DESC IGNORE CASE ",
inputSQL: " SELECT topic.name, COUNT(UNIQUE(comments.id)) AS total FROM ( SELECT * FROM [freebase_tags.countries] WHERE name = 'userInput') topic CROSS JOIN ( SELECT id, body FROM [fh-bigquery:reddit_comments.2016_05] WHERE subreddit = 'worldpolitics') comments WHERE comments.body CONTAINS CONCAT(' ', topic.name, ' ') GROUP BY topic.name, ORDER BY total DESC IGNORE CASE ",
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: " SELECT replace(school.name, "_", " ") AS name, SUM(requests) AS total FROM [wikidata_tags.software_applications] school JOIN EACH ( SELECT title, requests FROM [fh-bigquery:wikipedia.pagecounts_201605]) views ON views.title = name GROUP BY name ORDER BY total DESC IGNORE case ",
inputSQL: " SELECT replace(school.name, "_", " ") AS name, SUM(requests) AS total FROM [wikidata_tags.software_applications] school JOIN EACH ( SELECT title, requests FROM [fh-bigquery:wikipedia.pagecounts_201605] WHERE title = 'userInput' OR title CONTAINS CONCAT('_', replace('userInput', ' ', '_' )) OR title CONTAINS CONCAT(replace('userInput', ' ', '_' ), '_') ) views ON views.title = name GROUP BY name ORDER BY total DESC IGNORE case ",
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: " SELECT topic.name, COUNT(UNIQUE(comments.id)) AS total FROM [wikidata_tags.cars] topic CROSS JOIN ( SELECT id, body FROM [fh-bigquery:reddit_comments.2016_05] WHERE subreddit = 'cars') comments WHERE topic.name NOT IN ( SELECT word FROM [words.top10000] LIMIT 0) AND comments.body CONTAINS CONCAT(' ', topic.name, ' ') GROUP BY topic.name, ORDER BY total DESC IGNORE CASE ",
inputSQL: " SELECT topic.name, COUNT(UNIQUE(comments.id)) AS total FROM ( SELECT * FROM [wikidata_tags.cars] WHERE name = 'userInput' OR name CONTAINS CONCAT(' ', 'userInput') OR name CONTAINS CONCAT('userInput', ' ') ) topic CROSS JOIN ( SELECT id, body FROM [fh-bigquery:reddit_comments.2016_05] WHERE subreddit = 'cars') comments WHERE comments.body CONTAINS CONCAT(' ', topic.name, ' ') GROUP BY topic.name, ORDER BY total DESC IGNORE CASE ",
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: " SELECT topic.name, COUNT(UNIQUE(comments.id)) AS total FROM [wikidata_tags.fortune_500] topic CROSS JOIN ( SELECT id, body FROM [fh-bigquery:reddit_comments.2016_05] WHERE subreddit = 'bestof') comments WHERE topic.name NOT IN ( SELECT word FROM [words.top10000] LIMIT 500) AND comments.body CONTAINS CONCAT(' ', topic.name, ' ') GROUP BY topic.name, ORDER BY total DESC IGNORE CASE ",
inputSQL: " SELECT topic.name, COUNT(UNIQUE(comments.id)) AS total FROM ( SELECT * FROM [wikidata_tags.fortune_500] WHERE name = 'userInput') topic CROSS JOIN ( SELECT id, body FROM [fh-bigquery:reddit_comments.2016_05] WHERE subreddit = 'bestof') comments WHERE comments.body CONTAINS CONCAT(' ', topic.name, ' ') GROUP BY topic.name, ORDER BY total DESC IGNORE CASE ",
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: " SELECT topic.name, COUNT(UNIQUE(comments.id)) AS total FROM [wikidata_tags.businesses_clean] topic CROSS JOIN ( SELECT id, body FROM [fh-bigquery:reddit_comments.2016_05] WHERE subreddit = 'tech') comments WHERE topic.name NOT IN ( SELECT word FROM [words.top10000] LIMIT 10000) AND comments.body CONTAINS CONCAT(' ', topic.name, ' ') GROUP BY topic.name, ORDER BY total DESC IGNORE CASE ",
inputSQL: " SELECT topic.name, COUNT(UNIQUE(comments.id)) AS total FROM ( SELECT * FROM [wikidata_tags.businesses_clean] WHERE name = 'userInput') topic CROSS JOIN ( SELECT id, body FROM [fh-bigquery:reddit_comments.2016_05] WHERE subreddit = 'tech') comments WHERE comments.body CONTAINS CONCAT(' ', topic.name, ' ') GROUP BY topic.name, ORDER BY total DESC IGNORE CASE ",
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: " SELECT topic.name, COUNT(UNIQUE(comments.id)) AS total FROM [freebase_tags.job_titles] topic CROSS JOIN ( SELECT id, body FROM [fh-bigquery:reddit_comments.2016_05] WHERE subreddit = 'jobs') comments WHERE comments.body CONTAINS CONCAT(' ', topic.name, ' ') GROUP BY topic.name, ORDER BY total DESC IGNORE CASE ",
inputSQL: " SELECT topic.name, COUNT(UNIQUE(comments.id)) AS total FROM ( SELECT * FROM [freebase_tags.job_titles] WHERE name = 'userInput') topic CROSS JOIN ( SELECT id, body FROM [fh-bigquery:reddit_comments.2016_05] WHERE subreddit = 'jobs') comments WHERE comments.body CONTAINS CONCAT(' ', topic.name, ' ') GROUP BY topic.name, ORDER BY total DESC IGNORE CASE ",
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: " SELECT topic.name, COUNT(UNIQUE(comments.id)) AS total FROM [wikidata_tags.businesses_clean] topic CROSS JOIN ( SELECT id, body FROM [fh-bigquery:reddit_comments.2016_05] WHERE subreddit = 'business') comments WHERE topic.name NOT IN ( SELECT word FROM [words.top10000] LIMIT 10000) AND comments.body CONTAINS CONCAT(' ', topic.name, ' ') GROUP BY topic.name, ORDER BY total DESC IGNORE CASE ",
inputSQL: " SELECT topic.name, COUNT(UNIQUE(comments.id)) AS total FROM ( SELECT * FROM [wikidata_tags.businesses_clean] WHERE name = 'userInput') topic CROSS JOIN ( SELECT id, body FROM [fh-bigquery:reddit_comments.2016_05] WHERE subreddit = 'business') comments WHERE comments.body CONTAINS CONCAT(' ', topic.name, ' ') GROUP BY topic.name, ORDER BY total DESC IGNORE CASE ",
databases: "Reddit",
units: "Mentions",
hasPassingInputQuery: true,
rowsSearched: 65320696,
warnings: { }
}
],
total: 65
}