Table Reference

SELECT primary_country, primary_region, COUNT(*) AS addresses
FROM allium_identity.geo.addresses_geography
WHERE confidence IN ('Very High Conviction', 'High Conviction')
  AND primary_country IS NOT NULL
GROUP BY 1, 2
ORDER BY addresses DESC
LIMIT 20;

SELECT classification, measurability, SUM(usd) AS usd
FROM allium_identity.geo.corridors_tiered_daily
WHERE chain = 'tron'
  AND activity_date >= DATEADD(day, -30, CURRENT_DATE())
GROUP BY 1, 2
ORDER BY usd DESC;

SELECT from_geo, to_geo, SUM(usd) AS usd
FROM allium_identity.geo.corridor_matrix_daily
WHERE tier = '1_exact'
  AND from_geo != to_geo
  AND activity_date >= DATEADD(day, -90, CURRENT_DATE())
GROUP BY 1, 2
ORDER BY usd DESC
LIMIT 30;

SELECT counterparty_geo,
       SUM(CASE WHEN direction = 'inbound'  THEN usd END) AS in_usd,
       SUM(CASE WHEN direction = 'outbound' THEN usd END) AS out_usd,
       SUM(CASE WHEN direction = 'inbound' THEN usd ELSE -usd END) AS net_usd
FROM allium_identity.geo.country_flows_daily
WHERE geo = 'SG'
  AND counterparty_geo NOT IN ('unattributed', 'not_attributable', 'cex_no_multiplier')
GROUP BY 1
ORDER BY ABS(net_usd) DESC
LIMIT 20;

SELECT chain,
       SUM(usd) AS total_usd,
       SUM(CASE WHEN measurability != 'not_measurable' THEN usd END) / SUM(usd) AS geo_signal_share
FROM allium_identity.geo.corridors_coverage_daily
WHERE activity_date >= DATEADD(day, -30, CURRENT_DATE())
GROUP BY 1
ORDER BY total_usd DESC;

SELECT f.key AS country, SUM(t.usd_amount * f.value) AS usd
FROM allium_identity.geo.transfers_geo_resolved t,
     LATERAL FLATTEN(input => t.to_geo_distribution) f
WHERE t.chain = 'ethereum'
  AND t.activity_date = CURRENT_DATE - 7
  AND t.to_exchange_slug = 'bitget'
GROUP BY 1
ORDER BY usd DESC;