Beerthon

{CODEME}

Sponsor by Google, NARLabs 

BigQuery 

BeerQuery

Large Scale

Easy to use

Fast & Affordable

BigQuery

SELECT Year, Actor1Name, Actor2Name, Count FROM (
SELECT Actor1Name, Actor2Name, Year, COUNT(*) Count, 
RANK() OVER(PARTITION BY YEAR ORDER BY Count DESC) rank
FROM 
(
    SELECT Actor1Name, Actor2Name,  Year 
    FROM [gdelt-bq:full.events] 
    WHERE Actor1Name < Actor2Name 
        and Actor1CountryCode != '' and Actor2CountryCode != '' 
        and Actor1CountryCode!=Actor2CountryCode
),  
(
    SELECT Actor2Name Actor1Name, Actor1Name Actor2Name, Year 
    FROM [gdelt-bq:full.events] WHERE Actor1Name > Actor2Name  
    and Actor1CountryCode != '' and Actor2CountryCode != '' 
    and Actor1CountryCode!=Actor2CountryCode),
WHERE Actor1Name IS NOT null
AND Actor2Name IS NOT null
GROUP EACH BY 1, 2, 3
HAVING Count > 100
)
WHERE rank=1
ORDER BY Year

Process the last 30 years and discovered the top defining relationship between counterfor each year

Interest Big Data

Samples from US weather stations since 1929                         115M

Measurement data of broadband connection performance  240B

Birth information for the United States from 1969 to 2008    68M

Word index for works of Shakespeare                                        164K

Revision information for Wikipedia articles                                314M

NY Taxis Log   173M

Human Gene data, Github Log, Event Data
 

Challenge Time

Easy Round

1. Average Tips %

of New York Taxis

2. Average speed at each hour of the day.

3. Average tip per month

4. The most common trip for each hour of the day:

From union square to grand central

6. Breakdown of trips by number of passengers

Challenge Time

Crazy Round

worldwide events from the last 30 years and discovered the top defining relationship for each year.