Table Schemas

SELECT 
    day, 
    actor1_name, 
    actor2_name, 
    event_code,
    goldstein_scale,
    source_url
FROM gdelt_events
WHERE day >= today() - INTERVAL 7 DAY
    AND actor1_country_code = 'USA'
    AND event_root_code = '14'
ORDER BY day DESC
LIMIT 100

SELECT 
    e.event_id,
    e.day,
    e.actor1_name,
    COUNT(m.mention_identifier) as mention_count,
    COUNT(DISTINCT m.mention_source_name) as source_count
FROM gdelt_events e
LEFT JOIN gdelt_mentions m 
    ON e.event_id = m.event_id
WHERE e.day >= today() - INTERVAL 7 DAY
    AND e.event_root_code = '14'
GROUP BY e.event_id, e.day, e.actor1_name
ORDER BY mention_count DESC
LIMIT 100

SELECT 
    toDate(date) AS day,
    source_common_name,
    splitByChar(',', v1_5_tone)[1] AS tone_score,
    splitByChar(',', v1_5_tone)[7] AS word_count,
    document_identifier
FROM gdelt_gkg
WHERE date >= today() - INTERVAL 7 DAY
    AND source_common_name IN ('bbc.com', 'cnn.com')
ORDER BY day DESC
LIMIT 100

SELECT 
    source_common_name,
    COUNT() AS article_count,
    AVG(toFloat64(splitByChar(',', v1_5_tone)[1])) AS avg_tone
FROM gdelt_gkg
WHERE date >= today() - INTERVAL 7 DAY
    AND source_common_name IN ('bbc.com', 'aljazeera.com')
GROUP BY source_common_name
ORDER BY article_count DESC

SELECT 
    event_id,
    event_date,
    mention_count,
    source_count,
    avg_tone
FROM mv_event_mention_stats
WHERE day = today() - 1
    AND mention_count >= 50
ORDER BY mention_count DESC
LIMIT 100

SELECT 
    day,
    theme,
    COUNT(*) as article_count,
    AVG(tone) as avg_tone
FROM gdelt_gkg_themes_extracted
WHERE day >= today() - INTERVAL 30 DAY
    AND theme LIKE 'ENV_CLIMATE%'
GROUP BY day, theme
ORDER BY day DESC, article_count DESC
LIMIT 500

SELECT 
    day,
    person_name,
    COUNT(*) as mention_count,
    AVG(tone) as avg_tone
FROM gdelt_gkg_persons_extracted
WHERE day >= today() - INTERVAL 7 DAY
    AND person_name IN ('Joe Biden', 'Xi Jinping', 'Vladimir Putin')
GROUP BY day, person_name
ORDER BY day DESC, mention_count DESC
LIMIT 200

SELECT 
    day,
    organization_name,
    COUNT(*) as mention_count,
    AVG(tone) as avg_tone
FROM gdelt_gkg_organizations_extracted
WHERE day >= today() - INTERVAL 7 DAY
    AND organization_name LIKE '%Tesla%'
GROUP BY day, organization_name
ORDER BY day DESC, mention_count DESC
LIMIT 100

SELECT 
    day,
    location_name,
    country_code,
    latitude,
    longitude,
    COUNT(*) as mention_count
FROM gdelt_gkg_locations_extracted
WHERE day >= today() - INTERVAL 7 DAY
    AND country_code = 'UK'
GROUP BY day, location_name, country_code, latitude, longitude
ORDER BY day DESC, mention_count DESC
LIMIT 200

SELECT 
    person_name,
    first_seen,
    last_seen,
    mention_count,
    avg_tone,
    source_count
FROM gdelt_persons_master
WHERE mention_count >= 1000
ORDER BY mention_count DESC
LIMIT 100

SELECT 
    organization_name,
    first_seen,
    last_seen,
    mention_count,
    avg_tone,
    source_count
FROM gdelt_organizations_master
WHERE organization_name LIKE '%Bank%'
    AND mention_count >= 100
ORDER BY mention_count DESC
LIMIT 100

SELECT 
    e.event_id,
    e.day,
    e.actor1_name,
    m.mention_identifier,
    t.theme,
    p.person_name
FROM gdelt_events e
JOIN gdelt_mentions m 
    ON e.event_id = m.event_id
JOIN gdelt_gkg_themes_extracted t
    ON m.mention_identifier = t.document_identifier
    AND m.day = t.day
LEFT JOIN gdelt_gkg_persons_extracted p
    ON t.document_identifier = p.document_identifier
    AND t.day = p.day
WHERE e.day >= today() - INTERVAL 7 DAY
LIMIT 500

SELECT 
    e.event_id,
    e.day,
    e.actor1_name,
    e.event_code,
    s.mention_count,
    s.source_count,
    s.avg_tone
FROM gdelt_events e
JOIN mv_event_mention_stats s
    ON e.event_id = s.event_id
WHERE e.day >= today() - INTERVAL 1 DAY
    AND s.mention_count >= 50
ORDER BY s.mention_count DESC
LIMIT 100