Table Reference

SELECT chain, measurability, not_measurable_reason, COUNT(*) AS address_count
FROM allium_identity.geo.master_list
GROUP BY 1, 2, 3
ORDER BY 1, address_count DESC;

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 top_country, 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
ORDER BY num_exchanges DESC;

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

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;