Query editor
-- Goal:-- Build DAILY sports-only metrics from Polymarket trades:-- 1) classify each market (condition_id) into a single category using market_details.tags-- 2) filter trades to Sports markets and a given year-- 3) explode maker + taker into a single "trader" column-- 4) aggregate into trader-day stats-- 5) aggregate trader-day stats into day-level averages (per day)WITH tagged AS (-- Step 1: Convert raw tag strings in market_details into a normalized category label-- Output: one row per market_details row with (condition_id, category)SELECTmd.condition_id, -- unique identifier for the Polymarket market conditionCASE-- If tags are missing/empty, bucket as 'Other'WHEN md.tags IS NULL OR md.tags = '' THEN 'Other'-- Politics buckets (use keyword checks on tags)WHEN LOWER(md.tags) LIKE '%politics%' THEN 'Politics'WHEN LOWER(md.tags) LIKE '%trump%' THEN 'Politics'WHEN LOWER(md.tags) LIKE '%election%' THEN 'Politics'-- Sports buckets (keyword checks on tags)WHEN LOWER(md.tags) LIKE '%sports%' THEN 'Sports'WHEN LOWER(md.tags) LIKE '%nhl%' THEN 'Sports'WHEN LOWER(md.tags) LIKE '%boxing%' THEN 'Sports'WHEN LOWER(md.tags) LIKE '%football%' THEN 'Sports'WHEN LOWER(md.tags) LIKE '%soccer%' THEN 'Sports'-- Other topic bucketsWHEN LOWER(md.tags) LIKE '%economy%' THEN 'Economy'WHEN LOWER(md.tags) LIKE '%crypto%' THEN 'Crypto'WHEN LOWER(md.tags) LIKE '%entertainment%' THEN 'Entertainment'WHEN LOWER(md.tags) LIKE '%celebrities%' THEN 'Entertainment'WHEN LOWER(md.tags) LIKE '%movies%' THEN 'Entertainment'-- Fallback: if nothing matched, take the first comma-separated tag and title-case it-- e.g. tags="science, space" -> category="Science"ELSE INITCAP(TRIM(element_at(split(md.tags, ','), 1)))END AS categoryFROM polymarket_polygon.market_details mdWHERE md.condition_id IS NOT NULL -- ensure join key exists),cat_per_condition AS (-- Step 2: Ensure exactly ONE category per condition_id-- Why: market_details can (in some datasets) have multiple rows per condition_id or multiple tag variants.-- Approach: choose the "most frequent" category for each condition_id.SELECT condition_id, categoryFROM (SELECTcondition_id,category,-- For each condition_id, rank categories by how many rows they appear in (COUNT(*) DESC),-- with a deterministic tie-breaker on category name.ROW_NUMBER() OVER (PARTITION BY condition_idORDER BY COUNT(*) DESC, category) AS rn