GDELT queries

queries.sql

-- Count total events
SELECT COUNT(*) as nb_events FROM gdelt.events;

nb_events
440374991

-- Find the number of events per year
SELECT year,
       COUNT(globaleventid) AS nb_events
FROM gdelt.events
GROUP BY year
ORDER BY year ASC;

year	nb_events
1979	430941
1980	561445
1981	678457
1982	764325
1983	848806
1984	873229
1985	987942
1986	1077047
1987	1091590
1988	1183220
1989	1077466
1990	1126957
1991	1471086
1992	1097338
1993	1310741
1994	1774666
1995	1780524
1996	2793232
1997	3652082
1998	4267872
1999	4754487
2000	4540506
2001	4995943
2002	4415923
2003	5532338
2004	4652355
2005	3547766
2006	6358828
2007	11245510
2008	14331021
2009	23464598
2010	22502301
2011	31501556
2012	34583089
2013	35363597
2014	48381955
2015	66282411
2016	72777417
2017	12294422

-- Show top 10 event categories
SELECT eventcode,
       gdelt.eventcodes.description,
       nb_events
FROM (SELECT gdelt.events.eventcode,
             COUNT(gdelt.events.globaleventid) AS nb_events
      FROM gdelt.events
      GROUP BY gdelt.events.eventcode
      ORDER BY nb_events DESC LIMIT 10)
  JOIN gdelt.eventcodes ON eventcode = gdelt.eventcodes.code
ORDER BY nb_events DESC;

eventcode	description	                                          nb_events
010	      Make statement, not specified below	                  34612839
042	      Make a visit	                                          33958747
043	      Host a visit	                                          31448285
040	      Consult, not specified below	                          27575118
020	      Appeal, not specified below	                          25035395
051	      Praise or endorse	                                          22101164
036	      Express intent to meet or negotiate	                  19948868
190	      Use conventional military force, not specified below        19396778
046	      Engage in negotiation	                                  16520390
173	      Arrest, detain, or charge with legal action 	          14538259

-- Count Obama events per year
SELECT year,
       COUNT(globaleventid) AS nb_events
FROM gdelt.events
WHERE actor1name='BARACK OBAMA'
GROUP BY year
ORDER BY year ASC;

year	nb_events
2003	3
2004	25
2005	60
2006	246
2007	2765
2008	27552
2009	67039
2010	46059
2011	55303
2012	56631
2013	57018
2014	83115
2015	108943
2016	112106
2017	15629

-- Count Obama/Putin events per category
SELECT eventcode,
       gdelt.eventcodes.description,
       nb_events
FROM (SELECT gdelt.events.eventcode,
             COUNT(gdelt.events.globaleventid) AS nb_events
      FROM gdelt.events
      WHERE actor1name='BARACK OBAMA'and actor2name='VLADIMIR PUTIN'
      GROUP BY gdelt.events.eventcode
      ORDER BY nb_events DESC)
  JOIN gdelt.eventcodes ON eventcode = gdelt.eventcodes.code
  WHERE nb_events >= 50
ORDER BY nb_events DESC;

eventcode	description                                      nb_events
040	      Consult, not specified below	                     381
046	      Engage in negotiation	                             214
036	      Express intent to meet or negotiate	             155
041	      Discuss by telephone	                             117
042	      Make a visit	                                     90
030	      Express intent to cooperate, not specified below	     86
020	      Appeal, not specified below	                     80
044	      Meet at a third location	                             68
111	      Criticize or denounce 	                             63