Top flows, latest full month
Monthly user-side volume grouped by the origin chain of the asset sold and of the asset bought. Shows which chain pairs dominate settlement.
Top flows, latest full month
Origin chain of asset sold → asset bought, USD of bought leg.
Source · pipeline/sql/80_chain_pairs.sql.
How it is computed
- Take user-side entries of two-account swaps.
- Origin chain comes from the asset id (omft prefix or bridge), see pipeline/assets.py.
- Sum the bought leg's USD value per month and pair.
Read this before quoting it
- HOT Omni assets are mapped only for the EVM chain ids we can be sure of.
- Swaps whose bought leg is unpriced are counted separately (unvalued_swaps).
The query
Download chain_pairs.csvRuns in DuckDB over the local cache of BigQuery logs. Re-run with make metrics or python -m pipeline.metrics chain_pairs. Metric queries read the base tables built by 00_setup.sql.
1-- Monthly user flow by (origin chain of asset sold -> origin chain of asset bought), USD of the bought leg.2-- Only user-side entries of two-account swaps; entries with an unpriced bought leg are excluded and counted.3WITH u AS (4 SELECT l.date, l.entry_id,5 arg_max(l.origin_chain, -l.amount_raw) FILTER (WHERE l.amount_raw < 0) AS sold_chain,6 arg_max(l.origin_chain, l.amount_raw) FILTER (WHERE l.amount_raw > 0) AS bought_chain,7 max(l.usd_best) FILTER (WHERE l.amount_raw > 0) AS usd8 FROM legs_usd l JOIN pair_receipts USING (receipt_id) JOIN account_roles r ON r.account_id = l.account_id9 WHERE NOT l.failed AND r.role = 'user' GROUP BY l.date, l.entry_id)10SELECT CAST(date_trunc('month', date) AS DATE) AS month, count(DISTINCT date) AS days_covered, sold_chain, bought_chain,11 sum(usd) AS usd, count(*) AS swaps, count(*) FILTER (WHERE usd IS NULL) AS unvalued_swaps12FROM u GROUP BY ALL ORDER BY 1, 6 DESC NULLS LAST;pipeline/sql/00_setup.sql (base tables: legs, prices, roles)
1-- Base tables. Inputs: raw parquet cache ({RAW}) plus the `assets` and `prices` tables registered by metrics.py2-- from data/reference/. Nothing here is aggregated; every metric file below builds on these.34-- forward-filled daily price per key (calendar x key), so a missing day reuses the last known price5CREATE OR REPLACE TABLE prices_filled AS6WITH cal AS (SELECT CAST(d AS DATE) AS date FROM generate_series(DATE '2024-12-31', DATE '2026-10-30', INTERVAL 1 DAY) t(d)),7 keys AS (SELECT DISTINCT price_key FROM prices)8SELECT k.price_key, c.date,9 last_value(p.price IGNORE NULLS) OVER (PARTITION BY k.price_key ORDER BY c.date) AS price10FROM keys k CROSS JOIN cal c LEFT JOIN prices p ON p.price_key = k.price_key AND p.date = c.date;1112-- one row per (token_diff entry, asset); entry_id identifies one token_diff entry (one account's side of a swap)13CREATE OR REPLACE TABLE legs AS14SELECT entry_id, CAST(ts AS DATE) AS date, ts, failed, intent_hash, account_id, receipt_id,15 e.key AS asset, TRY_CAST(e.value AS DOUBLE) AS amount_raw16FROM (SELECT row_number() OVER () AS entry_id, *,17 unnest(map_entries(CAST(diff::JSON AS MAP(VARCHAR, VARCHAR)))) AS e18 FROM read_parquet('{RAW}/swaps/*.parquet'));1920-- usd: priced from the price table. usd_imputed: for an entry with exactly ONE unpriced asset, the value implied21-- by value conservation (minus the sum of the priced legs of the same entry), only if its sign matches the leg.22-- usd_best = usd, else usd_imputed. Imputed values are always reported separately, never silently mixed in.23CREATE OR REPLACE TABLE legs_usd AS24WITH base AS (25 SELECT l.*, a.symbol, a.origin_chain,26 l.amount_raw / pow(10, a.decimals) AS qty,27 CASE WHEN a.price_key = 'USD' THEN 1.0 ELSE p.price END AS price,28 l.amount_raw / pow(10, a.decimals) * CASE WHEN a.price_key = 'USD' THEN 1.0 ELSE p.price END AS usd29 FROM legs l30 LEFT JOIN assets a ON a.asset = l.asset31 LEFT JOIN prices_filled p ON p.price_key = a.price_key AND p.date = l.date),32e AS (33 SELECT *, sum(usd) OVER (PARTITION BY entry_id) AS priced_sum,34 count(*) FILTER (WHERE usd IS NULL) OVER (PARTITION BY entry_id) AS n_unpriced35 FROM base)36SELECT * EXCLUDE (priced_sum, n_unpriced),37 CASE WHEN usd IS NULL AND n_unpriced = 1 AND priced_sum IS NOT NULL38 AND sign(-priced_sum) = sign(amount_raw) THEN -priced_sum END AS usd_imputed,39 coalesce(usd, CASE WHEN usd IS NULL AND n_unpriced = 1 AND priced_sum IS NOT NULL40 AND sign(-priced_sum) = sign(amount_raw) THEN -priced_sum END) AS usd_best41FROM e;4243-- fees_collected per entry, priced the same way44CREATE OR REPLACE TABLE fees_usd AS45SELECT CAST(f.ts AS DATE) AS date, f.failed, f.e.key AS asset,46 TRY_CAST(f.e.value AS DOUBLE) / pow(10, a.decimals) *47 CASE WHEN a.price_key = 'USD' THEN 1.0 ELSE p.price END AS usd48FROM (SELECT ts, failed, unnest(map_entries(CAST(fees::JSON AS MAP(VARCHAR, VARCHAR)))) AS e49 FROM read_parquet('{RAW}/swaps/*.parquet') WHERE fees <> '{}') f50LEFT JOIN assets a ON a.asset = f.e.key51LEFT JOIN prices_filled p ON p.price_key = a.price_key AND p.date = CAST(f.ts AS DATE);5253-- NEP-245 mint/burn (deposits, withdrawals, refunds); token_ids and amounts are parallel arrays54CREATE OR REPLACE TABLE mt_usd AS55SELECT CAST(m.ts AS DATE) AS date, m.failed, m.event, m.memo, m.owner_id, m.asset,56 TRY_CAST(m.amt AS DOUBLE) / pow(10, a.decimals) *57 CASE WHEN a.price_key = 'USD' THEN 1.0 ELSE p.price END AS usd,58 a.origin_chain, a.symbol, a.asset IS NULL AS unknown_asset59FROM (SELECT ts, failed, event, memo, owner_id,60 unnest(from_json(token_ids::JSON, '["VARCHAR"]')) AS asset,61 unnest(from_json(amounts::JSON, '["VARCHAR"]')) AS amt62 FROM read_parquet('{RAW}/mt/*.parquet')) m63LEFT JOIN assets a ON a.asset = m.asset64LEFT JOIN prices_filled p ON p.price_key = a.price_key AND p.date = CAST(m.ts AS DATE)65WHERE m.asset LIKE 'nep141:%' OR m.asset LIKE 'nep245:%';666768-- Swap pairing: a receipt whose successful entries belong to exactly two accounts is one user<->solver swap.69CREATE OR REPLACE TABLE pair_receipts AS70SELECT receipt_id, min(account_id) AS a, max(account_id) AS b71FROM (SELECT DISTINCT receipt_id, account_id FROM legs WHERE NOT failed)72GROUP BY receipt_id HAVING count(*) = 2;7374-- Solver heuristic (documented in METHODOLOGY): an account is a solver if it faced >= 100 distinct counterparties75-- across the window. Users face a handful of solvers; solvers face thousands of users. Not an on-chain label.76CREATE OR REPLACE TABLE account_roles AS77WITH sides AS (SELECT a AS account_id, b AS cp FROM pair_receipts UNION ALL SELECT b, a FROM pair_receipts)78SELECT account_id, count(DISTINCT cp) AS n_counterparties, count(*) AS n_swaps,79 CASE WHEN count(DISTINCT cp) >= 100 THEN 'solver' ELSE 'user' END AS role80FROM sides GROUP BY account_id;