Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
GDELT Event File - some BigQuery SQL that smooths the goldstein scale over the last 28 days.
select
end_SQLDATE as SQLDATE
, AVG_GoldsteinScale
from
(
SELECT
ending_at_day AS end_SQLDATE
, ROUND(AVG(SUMG),5) AS AVG_GoldsteinScale
, COUNT(DISTINCT SQLDATE) as C
FROM
(
SELECT
GoldsteinScale AS SUMG
, SQLDATE-i AS ending_at_day
, SQLDATE
FROM [gdelt-bq:full.events] g
CROSS JOIN
( SELECT i
FROM [fh-bigquery:public_dump.numbers_255]
WHERE i<28
) b
WHERE (Actor1CountryCode='UKR' OR Actor2CountryCode='RUS' OR ActionGeo_CountryCode='UKR') AND
(Actor1CountryCode='RUS' OR Actor2CountryCode='UKR' or ActionGeo_CountryCode='UKR')
) q
GROUP BY end_SQLDATE
HAVING C=28
) b
ORDER BY SQLDATE
;;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment