The GDELT Project

TFIDF Using BigQuery + Cloud Video AI To Chronicle Visual Trends In CNN Coverage In Covid-19

How has CNN's visual narrative changed over the course of the Covid-19 pandemic? One of the metrics we track in the Visual Global Entity Graph (VGEG) 2.0 using Google's Cloud Video API to analyze CNN's television news programming in collaboration with the Internet Archive's Television News Archive is the list of distinct visual entities (objects and activities) depicted onscreen throughout the day. Using a single SQL query in BigQuery, we can have BigQuery compute a TFIDF ranking of the most "significant" entities by day since we started analyzing CNN on Jan. 25th of this year, creating a daily chronology of the visual metaphors that have defined the past quarter-year!

Looking at all onscreen entities, we have the following ranking:

Entities
1/25/2020 investor, public relations, professional, expert, entrepreneur, diplomat, energy, profession, party leader, politician
1/26/2020 public relations, professional, investor, expert, entrepreneur, energy, diplomat, profession, party leader, computer wallpaper
1/27/2020 public relations, professional, investor, expert, entrepreneur, diplomat, profession, energy, communication, new media
1/28/2020 investor, public relations, professional, expert, entrepreneur, diplomat, energy, profession, party leader, politician
1/29/2020 public relations, professional, investor, expert, entrepreneur, diplomat, energy, profession, communication, party leader
1/30/2020 judge, parliament, woodpecker, boeing 747 400, gopher tortoise, piciformes, boeing 747 8, concorde, galápagos tortoise, boeing 747
1/31/2020 parliament, judge, tosa, charadriiformes, borador, shorebird, gull, central asian shepherd dog, crow, indian spitz
2/1/2020 six man football, football helmet, parliament, face mask, canadian football, football gear, defensive tackle, football equipment, tosa, bombay mix
2/2/2020 gopher tortoise, fattoush, analog television, galápagos tortoise, desert tortoise, box turtle, toyota 4runner, pond turtle, chelonoidis, artist
2/3/2020 barbed wire, touchdown, canadian football, newsprint, bmw 3 series e30, roman temple, wire fencing, wreath, compact cassette, sloop
2/4/2020 gopher tortoise, galápagos tortoise, desert tortoise, box turtle, chelonoidis, pond turtle, curling, tortoise, end table, tabloid
2/5/2020 parliament, self help book, gopher tortoise, librarian, trial, galápagos tortoise, box turtle, desert tortoise, chelonoidis, pond turtle
2/6/2020 darts, gopher tortoise, judge, intrusion, galápagos tortoise, box turtle, desert tortoise, chelonoidis, pond turtle, tortoise
2/7/2020 ms island escape, gopher tortoise, cruiseferry, matador, galápagos tortoise, desert tortoise, red carpet, box turtle, chelonoidis, pond turtle
2/8/2020 special agent, landslide, naval officer, colonel, gopher tortoise, bailey bridge, cruiseferry, aircraft carrier, red carpet, toyota camry
2/9/2020 self help book, fattoush, librarian, military helicopter, historian, melting, darth vader, helicopter rotor, toyota avensis, boeing ch 47 chinook
2/10/2020 premiere, red carpet, cruiseferry, saxophonist, gopher tortoise, self propelled artillery, fantastic four, analog television, ms island escape, galápagos tortoise
2/11/2020 gopher tortoise, naval officer, calendar, desert tortoise, galápagos tortoise, box turtle, sugar house, chelonoidis, pond turtle, aqueduct
2/12/2020 calendar, naval officer, gopher tortoise, galápagos tortoise, box turtle, chelonoidis, pond turtle, desert tortoise, tortoise, natural rubber
2/13/2020 stock trader, s curl, gopher tortoise, natural rubber, chain, playing in the snow, coaxial cable, boeing 747 400, ac cobra, locket
2/14/2020 gopher tortoise, manager, galápagos tortoise, box turtle, desert tortoise, chelonoidis, pond turtle, tortoise, natural rubber, end table
2/15/2020 gopher tortoise, box turtle, galápagos tortoise, desert tortoise, interview, chelonoidis, pond turtle, corgi chihuahua, russkiy toy, cruiseferry
2/16/2020 self propelled artillery, power station, fattoush, gopher tortoise, locust, cargo aircraft, armored car, nuclear power plant, grasshopper, galápagos tortoise
2/17/2020 boeing e 4, boeing 747 8, cargo aircraft, boeing 747 400, elapidae, cadillac srx, fan convention, cadillac xts, boeing 747, port wine
2/18/2020 boeing e 4, walt disney world, natural rubber, gopher tortoise, boeing 747 8, cargo aircraft, box turtle, galápagos tortoise, chelonoidis, desert tortoise
2/19/2020 gopher tortoise, natural rubber, box turtle, galápagos tortoise, desert tortoise, chelonoidis, pond turtle, chess boxing, tortoise, end table
2/20/2020 gopher tortoise, galápagos tortoise, box turtle, chelonoidis, pond turtle, desert tortoise, chess boxing, tortoise, roystonea, end table
2/21/2020 tornado, gopher tortoise, mazda bt 50, flag day usa, wildfire, explosive material, galápagos tortoise, desert tortoise, chelonoidis, box turtle
2/22/2020 gopher tortoise, galápagos tortoise, box turtle, chelonoidis, desert tortoise, pond turtle, tortoise, mercedes benz sprinter, end table, earthquake
2/23/2020 curry puff, gopher tortoise, ice hockey position, chicken nugget, pastel, empanada, fattoush, college ice hockey, hockey, panzerotti
2/24/2020 curry puff, chicken nugget, empanada, panzerotti, pastel, skink, hognose snake, comb over, colubridae, rattlesnake
2/25/2020 curry puff, empanada, pastel, chicken nugget, panzerotti, puff pastry, gopher tortoise, galápagos tortoise, box turtle, chelonoidis
2/26/2020 curry puff, historian, self help book, chicken nugget, empanada, pastel, panzerotti, gopher tortoise, vestment, walt disney world
2/27/2020 curry puff, chicken nugget, empanada, pastel, panzerotti, gopher tortoise, collegiate wrestling, greco roman wrestling, picnic boat, wrestling singlet
2/28/2020 curry puff, gopher tortoise, chicken nugget, galápagos tortoise, empanada, box turtle, weightlifter, desert tortoise, pastel, panzerotti
2/29/2020 gopher tortoise, galápagos tortoise, box turtle, tiltrotor, hero, chelonoidis, desert tortoise, pond turtle, curry puff, tortoise
3/1/2020 gopher tortoise, hero, box turtle, galápagos tortoise, desert tortoise, pond turtle, angry birds, monjayaki, chelonoidis, curling
3/2/2020 dastar, cope, traditional japanese musical instruments, tiara, analog television, geomungo, retro style, newsprint, vestment, coachman
3/3/2020 earthquake, curling, baseball umpire, netball, tsunami, baseball uniform, goal, baseball protective gear, bowling pin, baseball park
3/4/2020 earthquake, cable television, curling, demolition, flag day usa, gentoo penguin, king penguin, bridal veil, stalagmite, chevrolet captiva
3/5/2020 cruiseferry, boeing 747 8, picnic boat, roman temple, manager, elektroboot, launch, ocean liner, boeing 747 400, igneous rock
3/6/2020 award ceremony, fan convention, military helicopter, master of ceremonies, picnic boat, satellite, cruiseferry, ice hockey position, bridal veil, trader
3/7/2020 cruiseferry, black hawk, military helicopter, helicopter rotor, grand prix motorcycle racing, harbor seal, superbike racing, mushing, king penguin, postage stamp
3/8/2020 garden gnome, cruiseferry, cannabis, oyster, retro style, hemp family, newsprint, bivalve, seafood boil, grand prix motorcycle racing
3/9/2020 aircraft carrier, coachman, destroyer, frigate, naval ship, cruiseferry, newsprint, garden gnome, retro style, battleship
3/10/2020 cruiseferry, buffalo wing, general tso's chicken, sloppy joe, stock trader, grand prix motorcycle racing, orange chicken, kofta, awning, ocean liner
3/11/2020 boeing 727, curling, coaxial cable, ballroom dance, ballroom, buffalo wing, general tso's chicken, netball, boeing c 32, red carpet
3/12/2020 red carpet, ford escape hybrid, grand prix motorcycle racing, superbike racing, motorcycle racing, darts, buffalo wing, mahjong, awning, motorcycle fairing
3/13/2020 ice hockey position, comedy club, bandy, defenseman, basketball hoop, hockey, college ice hockey, grand prix motorcycle racing, goaltender, original chicken sandwich
3/14/2020 poker table, macadamia, ford escape hybrid, sloppy joe, boeing 717, mcdonnell douglas dc 9, volkswagen transporter t5, grand prix motorcycle racing, breakfast roll, geranium
3/15/2020 garden gnome, slum, grand prix motorcycle racing, jet bridge, dice game, netball, dice, airport apron, morning glory, earthenware
3/16/2020 picnic table, netball, drone, goal, forward, international rules football, sidecar, twinjet, boeing c 40 clipper, boeing 727
3/17/2020 jockey, check in, ac cobra, brug, chevrolet captiva, extradosed bridge, pint, chevrolet cruze, european herring gull, school bus
3/18/2020 police van, fare, hockey pants, ice hockey position, bandy, college ice hockey, defenseman, rink bandy, hockey, ice hockey equipment
3/19/2020 morning glory, puppet, door mat, beach volleyball, riding mower, housewarming party, mower, awning, sidecar, sewing machine needle
3/20/2020 oil tanker, heavy lift ship, surf fishing, ballroom dance, handymax, casting fishing, dancesport, mercury mountaineer, window screen, panamax
3/21/2020 stock pot, librarian, housewarming party, dancesport, food steamer, stupa, deadlift, red eye effect, ballroom dance, time attack
3/22/2020 ac cobra, librarian, stock pot, slum, dancesport, bat, food steamer, sugar cake, peking opera, dragon boat
3/23/2020 judge, darts, jeep liberty, stock pot, trial, rock dove, tiple, geranium, submarine, jeep compass
3/24/2020 creeking, russkiy toy, dog crate, corgi chihuahua, panini, kennel, chihuahua, self help book, plastic wrap, quesadilla
3/25/2020 stupa, judge, panini, trial, dog crate, kennel, dome, patty melt, quesadilla, stock pot
3/26/2020 panini, patty melt, quesadilla, rou jia mo, melt sandwich, gordita, florentine biscuit, supercarrier, dog crate, toy poodle
3/27/2020 panini, quesadilla, money changer, patty melt, staple food, tang soo do, melt sandwich, rou jia mo, florentine biscuit, gordita
3/28/2020 author, dog crate, kennel, staple food, panini, baby activity, aspin, green bean, pit bull, cargo ship
3/29/2020 panini, self help book, staple food, kennel, patty melt, dog crate, hardware, quesadilla, ultralight aviation, rou jia mo
3/30/2020 kennel, dog crate, red eye effect, baby activity, motel, staple food, aspin, panini, author, hardware
3/31/2020 motel, author, dog crate, kennel, panini, staple food, stable, barn, baby activity, aspin
4/1/2020 motel, dog crate, stable, kennel, staple food, panini, barn, author, pink family, aspin
4/2/2020 author, pink family, motel, dog crate, kennel, staple food, stable, barn, aspin, ham and cheese sandwich
4/3/2020 nadaswaram, lantana, geranium, motel, verbena, beacon, invoice, alyssum, staple food, antonov an 124 ruslan
4/4/2020 author, baby activity, dog crate, kennel, mushing, motel, staple food, dog sled, stable, aspin
4/5/2020 author, historian, self help book, librarian, motel, staple food, massively multiplayer online role playing game, baby activity, quarter, hardware
4/6/2020 author, historian, jinrikisha, librarian, stretcher, lantana, motel, geranium, staple food, baby activity
4/7/2020 medical radiography, x ray, author, motel, radiography, staple food, great grey owl, screech owl, pink family, western screech owl
4/8/2020 author, pink family, motel, staple food, submarine sandwich, dehumidifier, ham and cheese sandwich, dog crate, hardware, barn
4/9/2020 pink family, polar bear, staple food, dog crate, ham and cheese sandwich, submarine sandwich, motel, hemp family, kennel, stable
4/10/2020 staple food, lantana, geranium, hemp family, author, ham and cheese sandwich, submarine sandwich, alyssum, cannabis, adhesive
4/11/2020 baby activity, quarter, staple food, dog crate, nickel, water tower, kennel, ham and cheese sandwich, submarine sandwich, silver medal
4/12/2020 author, historian, non fiction, librarian, self help book, water filter, quarter, staple food, candle holder, pepperoni
4/13/2020 author, non fiction, historian, self help book, librarian, tornado, tropical cyclone, arcade, temperature, atlas
4/14/2020 dog crate, kennel, author, pink family, staple food, dog clothes, dandie dinmont terrier, maltepoo, bolonka, pet supply
4/15/2020 author, pink family, submarine sandwich, baby activity, staple food, ham and cheese sandwich, self help book, dog crate, kennel, non fiction
4/16/2020 author, pink family, historian, staple food, dog crate, frigate, kennel, librarian, non fiction, supercarrier
4/17/2020 author, lantana, geranium, motel, hardware, dog crate, kennel, staple food, baby activity, water filter
4/18/2020 staple food, light aircraft carrier, hardware, supercarrier, garlic, stimulant, stock dove, amphibious assault ship, dog clothes, tusk
4/19/2020 author, non fiction, historian, self help book, librarian, scrubs, polar bear, staple food, pepperoni, computer cooling

To create the list above the following query was used:

WITH nested AS (

WITH data AS (
SELECT TIMESTAMP_TRUNC(date, DAY) date, station, entities.name word, count(1) count FROM `gdelt-bq.gdeltv2.vgegv2_iatv`, unnest(entities) entities where DATE(date) >= "2020-01-25" and station='CNN' group by date,station, entities.name
)
, word_day_type AS (
# how many times a word is mentioned in each "document"
SELECT word, SUM(count) counts, date, station
FROM data
GROUP BY 1, 3, 4
)
, day_type AS (
# total # of words in each "document"
SELECT SUM(count) counts, date, station
FROM data
GROUP BY 2, 3
)
, tf AS (
# TF for a word in a "document"
SELECT word, date, station, a.counts/b.counts tf
FROM word_day_type a
JOIN day_type b
USING(date, station)
)
, word_in_docs AS (
# how many "documents" have a word
SELECT word, COUNT(DISTINCT FORMAT('%s %s', FORMAT_TIMESTAMP( "%m/%d/%E4Y", date), station)) indocs
FROM word_day_type
GROUP BY 1
)
, total_docs AS (
# total # of docs
SELECT COUNT(DISTINCT FORMAT('%s %s', FORMAT_TIMESTAMP( "%m/%d/%E4Y", date), station)) total_docs
FROM data
)
, idf AS (
# IDF for a word
SELECT word, LOG(total_docs.total_docs/indocs) idf
FROM word_in_docs
CROSS JOIN total_docs
)

SELECT date, station,
ARRAY_AGG(STRUCT(ARRAY_TO_STRING(words, ', ') AS wordlist)) top_words
FROM (
SELECT date, station, ARRAY_AGG(word ORDER BY tfidf DESC LIMIT 10) words
FROM (
SELECT word, date, station, tf.tf * idf.idf tfidf
FROM tf
JOIN idf
USING(word)
)
GROUP BY date, station
)
GROUP BY date, station
ORDER BY date DESC

) select FORMAT_TIMESTAMP( "%m/%d/%E4Y", date) day, station, wordlist from nested, UNNEST(top_words) order by date asc, station asc

Many of these are undoubtedly bursts from advertising campaigns. What if we restrict to just entities that were onscreen for at least 15 minutes total in a given day? We get the following:

Entities
1/25/2020 investor, public relations, professional, expert, entrepreneur, diplomat, energy, profession, management, party leader
1/26/2020 public relations, professional, investor, expert, entrepreneur, energy, diplomat, profession, management, competition
1/27/2020 public relations, professional, investor, expert, entrepreneur, diplomat, profession, energy, management, player
1/28/2020 investor, public relations, professional, expert, entrepreneur, diplomat, energy, profession, management, company
1/29/2020 public relations, professional, investor, expert, entrepreneur, diplomat, energy, profession, management, company
1/30/2020 court, parliament, government, soil, adaptation, metropolis, tuxedo, chair, asphalt, jacket
1/31/2020 court, parliament, government, gesture, chair, metropolitan area, auto part, electric blue, architecture, billboard
2/1/2020 government, debate, team sport, ball game, sports, player, parliament, convention, court, fan
2/2/2020 performing arts, singer, singing, music artist, performance art, music venue, rock concert, song, concert, microphone
2/3/2020 light fixture, still life photography, iron, wrinkle, vegetation, player, cool, texture, cloud, number
2/4/2020 television set, system, screen, online advertising, fan, championship, government, competition event, team, sport venue
2/5/2020 jury, government, parliament, court, convention, academic conference, traffic, fan, television set, flag of the united states
2/6/2020 flag of the united states, academic conference, convention, flag, sign language, stage, employment, snow, government, uniform
2/7/2020 debate, snow, sign language, living room, employment, stage, team, competition event, uniform, company
2/8/2020 geological phenomenon, cloud, atmospheric phenomenon, snow, employment, skyscraper, ceremony, living room, ecoregion, communication device
2/9/2020 microphone, singer, fiction, singing, scene, model, action film, pc game, sign language, audio equipment
2/10/2020 winter storm, winter, singer, microphone, singing, performing arts, wrinkle, horizon, ceremony, snow
2/11/2020 protest, winter, snow, fan, championship, competition event, sport venue, flag, uniform, illustration
2/12/2020 screen, multimedia software, television set, employment, competition event, travel, gesture, hardwood, sign, circle
2/13/2020 wilderness, employment, living room, monochrome photography, monochrome, flag, outdoor recreation, macro photography, travel, diagram
2/14/2020 couch, living room, driving, outdoor recreation, competition event, luxury vehicle, diagram, automotive wheel system, paper product, adaptation
2/15/2020 horizon, body of water, wilderness, skyscraper, pet, animal, ecoregion, atmospheric phenomenon, living room, monochrome
2/16/2020 academic conference, ceremony, cloud, monochrome, singer, microphone, monochrome photography, geological phenomenon, horizon, wrinkle
2/17/2020 air travel, airline, aerospace engineering, airliner, passenger, blazer, sports, ceremony, couch, monochrome
2/18/2020 communication device, team, wilderness, living room, driving, adaptation, luxury vehicle, automotive wheel system, diagram, paper product
2/19/2020 employment, stage, living room, travel, luxury vehicle, automotive wheel system, drink, black and white, illustration, compact car
2/20/2020 employment, wilderness, living room, company, flag, luxury vehicle, automotive wheel system, adaptation, diagram, travel
2/21/2020 protest, flag, travel, driving, competition event, luxury vehicle, diagram, automotive wheel system, paper product, illustration
2/22/2020 protest, skyscraper, team, travel, luxury vehicle, automotive wheel system, compact car, metropolis, food, automotive design
2/23/2020 singer, performing arts, musical instrument, singing, music artist, fan, song, protest, string instrument, monochrome
2/24/2020 stadium, arena, fan, pope, music artist, basketball, sport venue, performing arts, music venue, history
2/25/2020 debate, stage, convention, led display, couch, flag of the united states, living room, flag, lawn, adaptation
2/26/2020 employment, traffic sign, team, company, luxury vehicle, automotive wheel system, diagram, lane, black and white, tourist attraction
2/27/2020 debate, scoreboard, led display, employment, stage, traffic sign, snow, travel, company, competition event
2/28/2020 scoreboard, traffic sign, ecoregion, snow, wilderness, travel, abdomen, spring, luxury vehicle, competition event
2/29/2020 skyscraper, protest, competition event, luxury vehicle, lane, street, compact car, black and white, metropolis, paper
3/1/2020 fan, protest, championship, player, stadium, restaurant, blazer, cuisine, body of water, monochrome
3/2/2020 monochrome, flag of the united states, monochrome photography, veterans day, tradition, ceremony, winter, black and white, protest, style
3/3/2020 fan, protest, screen, television set, championship, geological phenomenon, traffic sign, competition event, sport venue, ceremony
3/4/2020 television set, lcd tv, screen, multimedia software, sport venue, flag, competition event, spring, compact car, residential area
3/5/2020 cruise ship, watercraft, ship, wilderness, flower, spring, soil, rural area, outdoor recreation, macro photography
3/6/2020 academic conference, auditorium, convention, stadium, stage, couch, flag of the united states, sport venue, employment, ceremony
3/7/2020 tower block, downtown, skyscraper, body of water, stadium, travel, ceiling, sport venue, employment, atmospheric phenomenon
3/8/2020 meal, dish, sea, monochrome, monochrome photography, restaurant, ingredient, body of water, watercraft, cloud
3/9/2020 scoreboard, monochrome, boat, monochrome photography, ship, watercraft, tradition, history, blazer, ceremony
3/10/2020 traffic sign, ecoregion, lawn, spring, company, competition event, travel, sign, lane, street
3/11/2020 television set, lcd tv, flag of the united states, traffic sign, sport venue, competition event, lane, flag, street, compact car
3/12/2020 flag of the united states, scoreboard, flag, player, sports, traffic sign, sport venue, spring, flower, competition event
3/13/2020 television studio, led display, flat panel display, government, stage, sport venue, traffic sign, lawn, driving, spring
3/14/2020 led display, skyscraper, traffic sign, ceremony, government, living room, scene, macro photography, lawn, lane
3/15/2020 passenger, flat panel display, multimedia software, led display, team, retail, stage, monochrome, monochrome photography, sport venue
3/16/2020 debate, traffic sign, snow, flag, lane, automotive wheel system, uniform, street, compact car, residential area
3/17/2020 personal luxury car, executive car, traffic, retail, traffic sign, downtown, lane, driving, lawn, luxury vehicle
3/18/2020 television set, executive car, screen, traffic sign, communication device, driving, machine, luxury vehicle, automotive wheel system, flag
3/19/2020 communication device, living room, flower, machine, driving, lawn, spring, automotive wheel system, luxury vehicle, diagram
3/20/2020 led display, flat panel display, stage, flower, selfie, automotive wheel system, luxury vehicle, diagram, lane, illustration
3/21/2020 cool, living room, scene, shoe, lane, abdomen, selfie, automotive wheel system, travel, luxury vehicle
3/22/2020 hair care, flat panel display, roof, atmospheric phenomenon, skyscraper, ecoregion, hospital, land lot, scene, monochrome
3/23/2020 government, skyscraper, atmospheric phenomenon, machine, hospital, driving, service, selfie, automotive wheel system, lane
3/24/2020 animal, machine, lawn, spring, automotive wheel system, lane, paper product, illustration, compact car, tie
3/25/2020 government, animal, driving, selfie, spring, company, drink, automotive wheel system, luxury vehicle, lane
3/26/2020 photomontage, flower, selfie, spring, lane, luxury vehicle, automotive wheel system, illustration, drink, tie
3/27/2020 photomontage, uniform, rectangle, machine, shoe, trunk, style, abdomen, selfie, competition event
3/28/2020 restaurant, carnivore, rectangle, canidae, dog breed, pet, skyscraper, shoe, artwork, machine
3/29/2020 nonbuilding structure, sidewalk, condominium, suburb, public transport, estate, traffic, windshield, downtown, urban design
3/30/2020 flag of the united states, medical equipment, personal protective equipment, medical, organization, dog, carnivore, machine, canidae, dog breed
3/31/2020 dog, carnivore, dog breed, canidae, sign language, rectangle, organization, pet, machine, hospital
4/1/2020 buzz cut, tooth, selfie, wrinkle, trunk, sign language, thumb, mobile phone, grass family, smartphone
4/2/2020 buzz cut, trunk, love, selfie, tooth, thumb, organization, sign language, wrinkle, artwork
4/3/2020 photomontage, team, organization, animation, artwork, roof, machine, shoe, flag of the united states, land lot
4/4/2020 organization, human leg, trousers, dog, couch, carnivore, canidae, management, dog breed, shoe
4/5/2020 bookcase, grassland, shelving, farm, library, classroom, organization, grass family, learning, book
4/6/2020 medical equipment, medical, hospital, health care, clinic, service, organization, personal protective equipment, learning, machine
4/7/2020 buzz cut, tooth, wrinkle, photomontage, trunk, selfie, medical, ceiling, animation, parallel
4/8/2020 photomontage, tooth, mobile phone, sign language, smartphone, trunk, selfie, animation, wrinkle, thumb
4/9/2020 tooth, photomontage, love, animation, trunk, thumb, selfie, physical fitness, downtown, abdomen
4/10/2020 kitchen, photomontage, grass family, play, physical fitness, artwork, shoe, trunk, hospital, organization
4/11/2020 photomontage, portable communications device, baby, science, mobile phone, windshield, human leg, smartphone, trousers, cuisine
4/12/2020 publication, library, shelving, public library, book, bookselling, bookcase, librarian, author, historian
4/13/2020 map, geology, water resources, vegetation, computer program, publication, shelving, ecoregion, book, urban design
4/14/2020 buzz cut, lighting accessory, tooth, plaster, garden, wrinkle, selfie, ceiling, dog, carnivore
4/15/2020 veterans day, flag of the united states, photomontage, flag, learning, love, shelving, library, play, organization
4/16/2020 flag of the united states, garden, veterans day, love, learning, play, texture, dog, carnivore, canidae
4/17/2020 web page, multimedia software, team, number, photomontage, organization, animation, texture, artwork, pet
4/18/2020 hair care, web page, plaster, tooth, wrinkle, multimedia software, sunglasses, selfie, trunk, mobile phone
4/19/2020 medical equipment, organization, hospital, team, machine, artwork, land lot, service, shoe, trunk

Creating this table involves the exact same query as above with a single change – replace the root "WITH data AS ()" with the following that adds "having count>900" to the end. That's all there is to it!

SELECT TIMESTAMP_TRUNC(date, DAY) date, station, entities.name word, count(1) count FROM `gdelt-bq.gdeltv2.vgegv2_iatv`, unnest(entities) entities where DATE(date) >= "2020-01-25" and station='CNN' group by date,station, entities.name having count>900