Example Queries

SELECT from_geo, to_geo, SUM(usd) AS usd, SUM(n_transfers) AS transfers
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 = 'PH'
  AND counterparty_geo NOT IN ('unattributed', 'not_attributable', 'cex_no_multiplier')
  AND activity_date >= DATEADD(day, -90, CURRENT_DATE())
GROUP BY 1
ORDER BY ABS(net_usd) DESC
LIMIT 20;

SELECT subcategory, from_geo, to_geo, SUM(usd) AS usd
FROM allium_identity.geo.corridors_tiered_daily
WHERE classification = 'payments'
  AND subcategory IN ('c2c_payment', 'b2b_payment', 'b2c_payment', 'c2b_payment')
  AND measurability != 'not_measurable'
  AND activity_date >= DATEADD(day, -90, CURRENT_DATE())
GROUP BY 1, 2, 3
ORDER BY usd DESC
LIMIT 50;

SELECT from_geo,
       SUM(CASE WHEN subcategory = 'c2x' THEN usd END) AS consumer_deposits,
       SUM(CASE WHEN subcategory = 'i2x' THEN usd END) AS institutional_deposits
FROM allium_identity.geo.corridors_tiered_daily
WHERE classification = 'cex_flow'
  AND subcategory IN ('c2x', 'i2x')
  AND from_geo NOT IN ('unattributed', 'not_attributable', 'EXCLUDED')
  AND activity_date >= DATEADD(day, -90, CURRENT_DATE())
GROUP BY 1
ORDER BY consumer_deposits DESC NULLS LAST
LIMIT 20;

SELECT chain,
       SUM(usd) AS total_usd,
       SUM(CASE WHEN measurability != 'not_measurable' THEN usd END) / SUM(usd) AS geo_signal_share,
       SUM(CASE WHEN tier = 'x_intra_cex' THEN usd END) / SUM(usd)              AS intra_exchange_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 DATE_TRUNC('month', activity_date) AS month,
       SUM(CASE WHEN from_geo = 'PH' AND to_geo = 'SG' THEN usd END) AS ph_to_sg,
       SUM(CASE WHEN from_geo = 'SG' AND to_geo = 'PH' THEN usd END) AS sg_to_ph
FROM allium_identity.geo.corridor_matrix_daily
WHERE (from_geo, to_geo) IN (('PH','SG'), ('SG','PH'))
GROUP BY 1
ORDER BY 1;

SELECT g.address, g.primary_country, g.primary_region, g.confidence, g.reasoning
FROM allium_identity.geo.addresses_geography g
WHERE g.chain = 'evm'
  AND g.address IN ('0x...', '0x...')
  AND g.confidence IN ('Very High Conviction', 'High Conviction');

SELECT measurability, SUM(usd) AS usd,
       SUM(usd) / SUM(SUM(usd)) OVER () AS share
FROM allium_identity.geo.corridors_tiered_daily
WHERE chain = 'ethereum'
  AND activity_date >= DATEADD(day, -30, CURRENT_DATE())
GROUP BY 1;