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