> ## Documentation Index
> Fetch the complete documentation index at: https://docs.allium.so/llms.txt
> Use this file to discover all available pages before exploring further.

# Example Queries

> Compliance, regional, exchange-gravity, and cross-border flow recipes for the allium_identity.geo schema

These recipes run against the `allium_identity.geo` schema. Filter on the **conviction tier**
(`confidence` / `conviction_level`), not on a numeric `score`, and mind the
[chain-value caveat](/historical-data/identity/geo/reference#chain-values--joining) when joining.

## 1. Compliance & KYC monitoring

Addresses attributed to sanctioned countries, at high conviction:

```sql theme={null}
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;
```

## 2. Country distribution

```sql theme={null}
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;
```

## 3. Measurability & wallet-type mix by chain

```sql theme={null}
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;
```

## 4. Exchange operating countries (local/regional)

```sql theme={null}
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;
```

## 5. Global-exchange geography (Binance/Coinbase) via gravity

Where are a global exchange's deposits coming from, latest week:

```sql theme={null}
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;
```

Check attribution coverage before trusting a distribution:

```sql theme={null}
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;
```

## 6. Entity-to-entity USDC/USDT flows

CEX → DeFi USDC flows (last 8 weeks):

```sql theme={null}
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;
```

## 7. Cross-border remittance corridors (exact)

```sql theme={null}
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;
```

## 8. Country flow balance (distributed, incl. global exchanges)

Net USDT inflow/outflow per country, with global-CEX legs redistributed by gravity:

```sql theme={null}
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;
```

## 9. Join wallet type to country (mind the chain values)

`master_list` uses per-chain values while `addresses_geography` uses `evm` / `solana` / `tron`:

```sql theme={null}
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;
```

<Warning>
  Preserve address case for Solana/Tron joins — lowercasing breaks them. See
  [Case sensitivity](/historical-data/identity/geo/reference#6-case-sensitivity).
</Warning>

## Next Steps

<CardGroup cols={2}>
  <Card title="Table Reference" icon="table" href="/historical-data/identity/geo/tables">
    Full column-level schema for every table
  </Card>

  <Card title="Field Values Reference" icon="list" href="/historical-data/identity/geo/reference">
    Data dictionary, data quality, and best practices
  </Card>
</CardGroup>