SELECT STRFTIME_UTC_USEC(a.ending_at, "%Y-%m-%d") ending_at1, STRFTIME_UTC_USEC(b.ending_at-60*86400000000, "%Y-%m-%d") starting_at2, STRFTIME_UTC_USEC(b.ending_at, "%Y-%m-%d") ending_at2, a.country, b.country, CORR(a.c, b.c) corr, COUNT(*) c FROM ( SELECT country, date+i*86400000000 ending_at, c, i FROM [gdelt-bq:sample_views.country_date_matconf_numarts] a CROSS JOIN (SELECT i FROM [fh-bigquery:public_dump.numbers_255] WHERE i < 60) b ) b JOIN ( SELECT country, date+i*86400000000 ending_at, c, i FROM [gdelt-bq:sample_views.country_date_matconf_numarts] a CROSS JOIN (SELECT i FROM [fh-bigquery:public_dump.numbers_255] WHERE i < 60) b WHERE country='Egypt' AND date+i*86400000000 = PARSE_UTC_USEC('2011-01-27') ) a ON a.i=b.i WHERE a.ending_at != b.ending_at GROUP EACH BY ending_at1, ending_at2, starting_at2, a.country, b.country HAVING (c = 60 AND ABS(corr) > 0.254) ORDER BY corr DESC
Demonstrate your proficiency to design, build and manage solutions on Google Cloud Platform.
Follow