Building cross-chain dashboards in SQL is harder than it looks. The data is fragmented — every chain has its own tables, every bridge has its own event schema, and prices are denominated in different tokens. This tutorial walks through the SQL patterns that work: union-then-aggregate for chain comparisons, stitching for cross-chain transfers, price normalization, and the common pitfalls (double-counting, schema drift, time-zone bugs). Examples use Dune Analytics syntax, but the patterns translate to Allium, BigQuery, or Snowflake.
​

For background on the platforms, see our Dune Analytics tutorial and our pillar guide on onchain analytics platforms.
​

Most onchain warehouses follow the same pattern: one table per chain, identical schema. On Dune:

The schema for EVM chains is consistent: hash, block_time, from, to, value, gas_used, gas_price. Solana, Bitcoin, and other non-EVM chains have their own schemas — cross-chain dashboards that include them need separate handling.
​

To compare metrics across chains, UNION the per-chain results.
​

SELECT 'ethereum' AS chain, COUNT(*) AS tx_24h
FROM ethereum.transactions
WHERE block_time >= NOW() - INTERVAL '1' DAY
UNION ALL
SELECT 'arbitrum', COUNT(*)
FROM arbitrum.transactions
WHERE block_time >= NOW() - INTERVAL '1' DAY
UNION ALL
SELECT 'optimism', COUNT(*)
FROM optimism.transactions
WHERE block_time >= NOW() - INTERVAL '1' DAY
ORDER BY tx_24h DESC
​

The pattern is verbose but readable. For dashboards, parameterize the chain list so users can toggle which chains to include.
​

For Dune-curated cross-chain tables (dex.trades, tokens.transfers, nft.trades), the union is already done — the table includes a blockchain column. Filter with WHERE blockchain IN ('ethereum', 'arbitrum', 'optimism').
​

A common question: how is USDC distributed across chains?
​

SELECT contract_chain, SUM(amount) AS net_supply
FROM (
SELECT 'ethereum' AS contract_chain, SUM(value/1e6) AS amount
FROM erc20_ethereum.evt_Transfer
WHERE contract_address = LOWER('0xA0b86991c6218b36c1D19D4a2e9Eb0cE3606eB48')
AND "from" = 0x0000000000000000000000000000000000000000
UNION ALL
SELECT 'ethereum', -SUM(value/1e6)
FROM erc20_ethereum.evt_Transfer
WHERE contract_address = LOWER('0xA0b86991c6218b36c1D19D4a2e9Eb0cE3606eB48')
AND "to" = 0x0000000000000000000000000000000000000000
) GROUP BY contract_chain
​

Net supply = mints (transfers from 0x0) minus burns (transfers to 0x0). Repeat the pattern for each chain where USDC is natively issued. For wrapped variants (USDC.e on Avalanche), substitute the wrapped contract address.
​

For the production version, see our stablecoin onchain analytics guide.
​

The hardest pattern. A CCTP burn-mint shows as two events on two chains. To represent it as one transfer, join the burn (origin chain) to the mint (destination chain) using the message hash.
​

WITH burns AS (
SELECT block_time AS burn_time, evt_tx_hash AS burn_tx,
burnToken, amount, mintRecipient, destinationDomain, nonce
FROM cctp_ethereum.TokenMessenger_evt_DepositForBurn
WHERE block_time >= NOW() - INTERVAL '7' DAY
),
mints AS (
SELECT block_time AS mint_time, evt_tx_hash AS mint_tx,
recipient, amount, sourceDomain, nonce
FROM cctp_ethereum.MessageTransmitter_evt_MessageReceived
WHERE block_time >= NOW() - INTERVAL '7' DAY
)
SELECT b.burn_time, b.amount, b.burn_tx, m.mint_tx,
m.mint_time - b.burn_time AS settle_latency
FROM burns b
LEFT JOIN mints m ON b.nonce = m.nonce AND b.destinationDomain = m.sourceDomain
​

The join key is the message nonce + source/destination domain. CCTP guarantees the nonce is unique per source chain. The query above joins the Ethereum side; the production version unions across all CCTP source chains.
​

For non-Circle bridges (Stargate, Hop, Across, Wormhole, LayerZero), each has its own join schema. Most production dashboards stitch one bridge at a time and union the results. Allium publishes pre-stitched cross_chain_transfers tables that handle this for you.
​

Cross-chain dashboards usually report values in USD. Token amounts are in native units; conversion needs a price feed.
​

Dune provides prices.usd, a curated table with token prices indexed by minute or hour. The standard pattern:
​

SELECT t.block_time, t.amount * p.price AS amount_usd
FROM erc20_ethereum.evt_Transfer t
LEFT JOIN prices.usd p
ON p.contract_address = t.contract_address
AND p.minute = DATE_TRUNC('minute', t.block_time)
AND p.blockchain = 'ethereum'
​

The minute-level join is exact for the price-feed cadence. For thinly traded tokens or tokens not in the price feed, the LEFT JOIN returns NULL — handle that case explicitly (skip the row, or fall back to a different price source).
​

For chain-native tokens (ETH, MATIC, AVAX), Dune's prices.usd includes them under the chain's wrapped-token contract address. ETH price is at the WETH contract.
​

Block times in onchain warehouses are UTC. Aggregating to "daily" totals needs explicit truncation.
​

SELECT DATE_TRUNC('day', block_time) AS day, COUNT(*) AS tx_count
FROM ethereum.transactions
WHERE block_time >= NOW() - INTERVAL '30' DAY
GROUP BY 1
ORDER BY day
​

Three pitfalls. (1) DATE_TRUNC on Trino expects single-quoted unit strings. (2) Some chains have slow blocks late in the UTC day, so the latest day's count looks low until midnight UTC; cap at day < CURRENT_DATE if that matters. (3) For user-facing dashboards, consider letting users pick their timezone with a parameter.
​

The most common bug. Three sources of double-counting:
​

Wrapped tokens. USDC + USDC.e on the same chain, both counted as "USDC supply." Fix: include only one in any given query, or distinguish them explicitly with a wrapped flag.
​

Liquid staking. ETH locked in Lido + stETH used as collateral on Aave both counted as "ETH TVL." Fix: count ETH at the lowest level (Lido contracts) and exclude derivatives (stETH) from secondary protocol TVL.
​

Internal transfers. A user transfers USDC from their EOA to their own smart wallet — counted as a transfer event but not economically meaningful. Fix: filter WHERE "from" != "to" and exclude transfers between the same beneficial owner (hard without wallet labels).
​

Cross-chain queries are expensive — they scan multiple per-chain tables. Five rules:

A practical example. Build a dashboard that shows daily stablecoin transfer volume across Ethereum, Arbitrum, Base, Polygon, and Solana for the past 30 days.
​

Step 1. Identify the stablecoin contracts per chain. USDC and USDT addresses are different on each chain. Dune's tokens.erc20 table or the issuer's docs are the references.
​

Step 2. Write per-chain queries summing transfer values, USD-normalized.
​

Step 3. UNION the per-chain results into a single time-series. Solana uses solana.transfers with a different schema — handle separately and union at the daily level.
​

Step 4. Build the visualizations. Stacked area chart for chain breakdown, headline counter for total, bar chart for chain ranking.
​

Step 5. Parameterize. Let users pick the time range (7/30/90 day) and the stablecoin (USDC/USDT/all).
​

Step 6. Publish. Public dashboards on Dune get a permanent URL and rank in the discovery feed.
​

Dune's syntax is the most common in cross-chain analytics work, but other tools solve the same patterns with different trade-offs.
​

Allium SQL. Same SQL surface, customer's warehouse delivery. The schema differs slightly from Dune (table names, decoded column names) but the patterns are identical. Allium's cross_chain_transfers table is the easiest cross-chain stitching available — much less work than the CCTP join above.
​

BigQuery public datasets. Google publishes Bitcoin and Ethereum public datasets in BigQuery. The schema is pre-decoded and free to query (you pay for compute). Coverage is narrower than Dune (mostly EVM mainnet) but the price-per-query model is attractive for ad-hoc analysis.
​

Self-hosted indexers. Goldsky, Subsquid, The Graph, and Ponder let teams index their own subset of chains. Cheaper at scale than commercial warehouses but requires real engineering investment. Used by protocol teams that need bespoke schemas not available elsewhere.
​

Notebook environments. Hex, Mode, and Jupyter notebooks query any SQL warehouse and produce charts inline. Many analyst teams use Hex on top of Dune or Allium — the SQL stays in the warehouse, the analysis lives in the notebook.
​

Most teams pick one primary tool and supplement with one or two others. Dune for community work, Allium for production, BigQuery for ad-hoc. The patterns translate.
​

Solana's data model differs from EVM. Instead of contracts emitting logs, Solana programs produce instructions that get parsed into different table shapes.
​

Dune's Solana tables include solana.transactions, solana.account_activity, solana.transfers, and program-specific tables (Jupiter swaps, Raydium pools, etc.). The query for "Solana DEX volume in the last 24h" looks like:
​

SELECT SUM(amount_usd) AS volume_24h
FROM dex_solana.trades
WHERE block_time >= NOW() - INTERVAL '1' DAY
​

Note the table name dex_solana.trades — Dune namespaces Solana DEX data under dex_solana, not the Ethereum-style dex.trades. Always check the namespace when porting EVM patterns to Solana.
​

For background on Solana's DeFi ecosystem, see our Solana DeFi apps guide.
​

Comparing bridge volumes is a common cross-chain analytics question. Bridge events have different schemas, but volume is the unifying metric.
​

Three approaches:
​

Per-bridge. Query each bridge's events directly — Stargate's swap events, Hop's TransferSent, Across's FundsDeposited, etc. Sum the USD-normalized amounts. Verbose but precise.
​

Curated bridge tables.bridge.transactions on Dune is a Dune-curated cross-bridge view. It includes most major bridges and normalizes the schema. Faster to query but inherits any methodology gaps from the spellbook.
​

DeFiLlama bridges API. The free DeFiLlama bridges endpoint returns daily bridge volume. Less granular than Dune (no per-transaction detail) but free and well-maintained.
​

Most production bridge dashboards combine the second and third — Dune for analyst use, DeFiLlama API for embedded widgets.
​

How to know your dashboard is correct.
​

Cross-check headline numbers against multiple sources. If your "USDC supply on Base" number matches DeFiLlama's stablecoin page, that's a strong signal. If it's off by 10%, something is off — usually wrapped vs native confusion.
​

Spot-check large transactions on the explorer. Pick the largest transfer in your dashboard and look it up on Etherscan or the chain's explorer. Does the USD value match? Is the to/from address sensible? This catches decoding bugs.
​

Compare against the protocol's own dashboard. If you're tracking Uniswap, compare against info.uniswap.org. If you're tracking Aave, compare against the Aave UI. The numbers won't match exactly (methodology differs) but should be in the same ballpark.
​

Test edge cases. What does your dashboard show during a chain reorg? When a price feed drops out? When a new chain is added to the data warehouse? Brittle dashboards fall over on edge cases that more robust ones handle gracefully.
​

Eco moves stablecoins across 15 chains. Cross-chain dashboards built in SQL show stablecoin flows that settle through Eco within minutes — Dune's tokens.transfers and chain-specific tables include them. Teams that integrate Eco often build a custom dashboard tracking their own routes — settlement latency, chain distribution, success rate. The same SQL patterns above work for that. For more on Eco's stablecoin execution, see our chain abstraction guide.
​

Dune's tokens.erc20 table has token contract addresses by chain. Issuer documentation is the authoritative source — Circle's docs list all USDC deployments, Tether's list all USDT deployments. Etherscan and chain explorers verify on the chain side.
​

Dune's Solana tables (solana.transactions, solana.transfers) have a different schema from EVM. The patterns above need adaptation. Most cross-chain dashboards either treat Solana as a separate query and union at the daily level, or use a Dune-curated cross-chain table that already normalizes.
​

Dune covers 100+ chains as of 2026. For chains not on Dune, options include Allium (broader chain coverage), the chain's own block explorer API, or running your own indexer. Allium covers some chains Dune doesn't.
​

Three options. (1) Compute a synthetic price from a DEX pool — sum of inverse-weighted swap rates over a window. (2) Use CoinGecko's API as a fallback. (3) Skip those tokens with an explicit filter, and disclose the gap in the dashboard.
​

On Dune, all free-tier dashboards are public by default. Paid tiers can make them private. Public dashboards rank in Dune's discovery feed and pick up organic views. Most successful crypto dashboards started as public Dune queries.
​