GDELT Event File - some BigQuery SQL that smooths the goldstein scale over the last 28 days.

gistfile1.txt

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
;;