Dim Country

1-- Country dimension derived from events and enriched with an uploaded country->region lookup.
2-- One row per ISO-2 country_code (the natural key). Every column aliased (required by view-create).
3-- Replace <country_region_lookup> with the uploaded lookup table (columns: country_code, region, continent).
4SELECT
5    seen.country_code                              AS country_code,
6    any(lk.region)                                 AS region,
7    any(lk.continent)                              AS continent,
8    count()                                        AS events_seen   -- optional: popularity, for QA
9FROM (
10    SELECT
11        upper(properties.$geoip_country_code)      AS country_code
12    FROM events
13    WHERE properties.$geoip_country_code != ''
14      AND timestamp >= now() - INTERVAL 90 DAY
15) AS seen
16LEFT JOIN <country_region_lookup> AS lk ON seen.country_code = lk.country_code
17GROUP BY country_code
18-- Materialize on a slow schedule (7day/30day): country attributes barely change but are read constantly.