Example Queries

SELECT chain, address, primary_country, primary_region, confidence, reasoning
FROM allium_identity.geo.addresses_geography
WHERE primary_country IN ('Iran', 'North Korea', 'Syria', 'Cuba')
  AND confidence IN ('Very High Conviction', 'High Conviction')
ORDER BY primary_country;

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

SELECT chain, wallet_type, SUM(wallet_count) AS wallets, ROUND(SUM(pct_of_total), 2) AS pct_of_chain
FROM allium_identity.geo.coverage_summary
WHERE measurability = 'potentially_measurable'
GROUP BY 1, 2
ORDER BY 1, wallets DESC;

SELECT top_country, top_region, COUNT(*) AS num_exchanges, ROUND(AVG(likelihood_score), 3) AS avg_confidence
FROM allium_identity.geo.exchange_country_attribution
WHERE status = 'active'
GROUP BY 1, 2
ORDER BY num_exchanges DESC
LIMIT 25;

SELECT global_exchange, country, region,
       ROUND(gravity_share, 4) AS share, wallet_count_country
FROM allium_identity.geo.exchange_gravity_weekly
WHERE global_exchange IN ('binance', 'coinbase')
  AND flow_direction = 'inbound_deposit'
  AND week_start = (SELECT MAX(week_start) FROM allium_identity.geo.exchange_gravity_weekly)
ORDER BY global_exchange, gravity_share DESC;

SELECT global_exchange, flow_direction, week_start, ROUND(coverage_pct, 3) AS coverage_pct
FROM allium_identity.geo.exchange_gravity_weekly
QUALIFY ROW_NUMBER() OVER (PARTITION BY global_exchange, flow_direction ORDER BY week_start DESC) = 1
ORDER BY coverage_pct DESC;

SELECT from_entity, to_entity, SUM(volume_usd) AS volume_usd, SUM(transfer_count) AS transfers
FROM allium_identity.geo.entity_flows_weekly
WHERE token = 'USDC'
  AND from_entity_category = 'cex'
  AND to_entity_category IN ('dex', 'protocol')
  AND week_start >= DATEADD(week, -8, CURRENT_DATE())
GROUP BY 1, 2
ORDER BY volume_usd DESC
LIMIT 20;

SELECT from_country, to_country, SUM(volume_usd) AS volume_usd, SUM(transfer_count) AS transfers
FROM allium_identity.geo.country_corridors_weekly
WHERE token = 'USDT'
  AND is_cross_border = TRUE
  AND week_start >= DATEADD(week, -12, CURRENT_DATE())
GROUP BY 1, 2
ORDER BY volume_usd DESC
LIMIT 30;

WITH f AS (
  SELECT to_country AS country, SUM(distributed_volume_usd) AS inflow, 0 AS outflow
  FROM allium_identity.geo.country_corridors_distributed_weekly
  WHERE token = 'USDT' AND week_start >= DATEADD(week, -8, CURRENT_DATE())
    AND to_country NOT IN ('unattributed', 'global_cex')
  GROUP BY 1
  UNION ALL
  SELECT from_country, 0, SUM(distributed_volume_usd)
  FROM allium_identity.geo.country_corridors_distributed_weekly
  WHERE token = 'USDT' AND week_start >= DATEADD(week, -8, CURRENT_DATE())
    AND from_country NOT IN ('unattributed', 'global_cex')
  GROUP BY 1
)
SELECT country, SUM(inflow) AS inflow_usd, SUM(outflow) AS outflow_usd,
       SUM(inflow) - SUM(outflow) AS net_usd
FROM f GROUP BY 1
ORDER BY ABS(SUM(inflow) - SUM(outflow)) DESC
LIMIT 30;

WITH ml AS (
    SELECT CASE WHEN chain IN ('solana', 'tron') THEN chain ELSE 'evm' END AS chain_family,
           address, wallet_type
    FROM allium_identity.geo.master_list
)
SELECT g.primary_country, ml.wallet_type, COUNT(*) AS addresses
FROM allium_identity.geo.addresses_geography g
JOIN ml ON ml.chain_family = g.chain AND ml.address = g.address
WHERE g.confidence IN ('Very High Conviction', 'High Conviction')
GROUP BY 1, 2
ORDER BY addresses DESC
LIMIT 30;