How to add a particular country for specified words counts search using google bigquery for GDELT gfg

Question

I am using GDELT with Google Bigquery. I want to search for a specific word such as "economy" and "unemployment" for a particular country such as Canada. The sample codes are given below. I add ToLinkURL like '%canada%' to WHERE. However, it does not bring me any data. Is there any better way to do it.?

SELECT
  SUBSTR(CAST(DATE AS STRING), 0, 8) DAY,
  COUNT(1) COUNT
FROM
  `gdelt-bq.gdeltv2.gfg_partitioned`
WHERE
  
  LOWER(LinkText) LIKE '%economy%'
   
  AND (LOWER(LinkText) LIKE '%uncertainty%' AND _PARTITIONTIME >= "2019-09-04" AND _PARTITIONTIME < "2019-09-07") AND ToLinkURL like '%canada%'
    
GROUP BY
  DAY 
ORDER BY
 DAY DESC

score 1 · Answer 1 · answered May 22 '20 at 22:15

Below is for BigQuery Standard SQL

#standardSQL
SELECT
  SUBSTR(CAST(DATE AS STRING), 0, 8) DAY,
  COUNT(1) COUNT
FROM `gdelt-bq.gdeltv2.gfg_partitioned`
WHERE LOWER(LinkText) LIKE '%economy%'
  AND LOWER(LinkText) LIKE '%unemployment%' 
  AND _PARTITIONTIME >= "2019-09-04" AND _PARTITIONTIME < "2019-09-07"
  AND ToLinkURL LIKE '%canada%'
GROUP BY DAY 
ORDER BY DAY DESC

and returns

Row DAY         COUNT   
1   20190906    12

Meantime, I feel below is more relevant to what you want (this depends on what you really want to look for existence of those words in the same entry or any of them)

#standardSQL
SELECT
  SUBSTR(CAST(DATE AS STRING), 0, 8) DAY,
  COUNT(1) COUNT
FROM `gdelt-bq.gdeltv2.gfg_partitioned`
WHERE (LOWER(LinkText) LIKE '%economy%'
  OR LOWER(LinkText) LIKE '%unemployment%')
  AND _PARTITIONTIME >= "2019-09-04" AND _PARTITIONTIME < "2019-09-07"
  AND ToLinkURL LIKE '%canada%'
GROUP BY DAY 
ORDER BY DAY DESC

In this case, output is

Row DAY         COUNT    
1   20190906    831  
2   20190905    718  
3   20190904    815

How to add a particular country for specified words counts search using google bigquery for GDELT gfg

1 Answers1