WITH kalshi_fixed AS (
SELECT
day,
CASE
-- Map Kalshi categories to Polymarket equivalents
WHEN LOWER(category) IN ('elections', 'politics') THEN 'politics'
WHEN LOWER(category) IN ('science and technology', 'technology') THEN 'technology'
WHEN LOWER(category) IN ('climate and weather', 'weather') THEN 'weather'
WHEN LOWER(category) IN ('world', 'international') THEN 'international'
WHEN LOWER(category) IN ('financials', 'economics', 'companies') THEN 'business'
WHEN LOWER(category) IN ('entertainment', 'mentions', 'social') THEN 'culture'
WHEN LOWER(category) = 'crypto' THEN 'crypto'
WHEN LOWER(category) = 'sports' THEN 'sports'
WHEN LOWER(category) IN ('education', 'health', 'transportation', 'covid-19') THEN 'other'
WHEN category IS NULL THEN 'other'
ELSE 'other'
END AS fixed_category,
open_interest AS oi_usd
FROM
common.predictions.kalshi_open_interest_daily
WHERE
day >= CURRENT_DATE - INTERVAL '30 days'
),
kalshi_oi AS (
SELECT
day,
fixed_category AS category,
SUM(oi_usd) AS oi_usd
FROM
kalshi_fixed
GROUP BY day, fixed_category
),
poly_oi AS (
SELECT
day,
LOWER(category) AS category,
SUM(open_interest_usd) AS oi_usd
FROM
polygon.predictions.open_interest_daily
WHERE
day >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY day, LOWER(category)
)
SELECT
COALESCE(k.day, p.day) AS day,
COALESCE(k.category, p.category) AS category,
COALESCE(k.oi_usd, 0) AS kalshi_open_interest_usd,
COALESCE(p.oi_usd, 0) AS polymarket_open_interest_usd,
COALESCE(k.oi_usd, 0) + COALESCE(p.oi_usd, 0) AS combined_open_interest_usd
FROM
kalshi_oi k
FULL OUTER JOIN
poly_oi p
ON
k.day = p.day
AND k.category = p.category
ORDER BY
day DESC,
combined_open_interest_usd DESC
