-- Analyst Review Pack sample queries
-- Replace table names with your local DuckDB / BigQuery / Postgres table names.

-- 1. High-severity events by country
SELECT country_code, severity, COUNT(*) AS events
FROM events
WHERE severity IN ('critical', 'high')
GROUP BY country_code, severity
ORDER BY events DESC;

-- 2. Events with commodity exposure mappings
SELECT event_id, occurred_at, country_code, severity, title, commodity_ids_json
FROM events
WHERE commodity_ids_json <> '[]'
ORDER BY occurred_at DESC
LIMIT 50;

-- 3. Chokepoint-adjacent mapped events
SELECT event_id, occurred_at, country_code, severity, title, chokepoint_ids_json
FROM events
WHERE chokepoint_ids_json <> '[]'
ORDER BY occurred_at DESC
LIMIT 50;

-- 4. APAC high-severity public risk sample
SELECT event_id, occurred_at, country_code, event_type, severity, title
FROM events
WHERE country_code IN ('CN', 'JP', 'KR', 'TW', 'VN', 'TH', 'MY', 'PH', 'ID', 'SG', 'IN')
  AND severity IN ('critical', 'high')
ORDER BY occurred_at DESC
LIMIT 100;

-- 5. Source mix by event type
SELECT source, event_type, COUNT(*) AS events
FROM events
GROUP BY source, event_type
ORDER BY events DESC
LIMIT 50;
