WITH daily_stats__ethereum AS ( WITH top_tokens AS ( SELECT contract_address AS token_address , symbol , count(*) AS transfers , sum(amount_usd) AS volume_usd , row_number() OVER (order by transfers desc) AS _rank_transfers , row_number() OVER (order by volume_usd desc) AS _rank_volume FROM ethereum.core.ez_token_transfers AS tr WHERE tr.block_timestamp >= CURRENT_DATE - interval '30 days' AND tr.block_timestamp < CURRENT_DATE AND amount_usd IS NOT NULL AND contract_address NOT IN ( '0x8b3192f5eebd8579568a2ed41e6feb402f93f73f', '0x24e89bdf2f65326b94e36978a7edeac63623dafa', '0x666d875c600aa06ac1cf15641361dec3b00432ef', '0x5b4e9a810321e168989802474f689269ec442681', '0x3f7aff0ef20aa2e646290dfa4e67611b2220c597', '0xd26114cd6ee289accf82350c8d8487fedb8a0c07' ) GROUP BY 1,2 QUALIFY _rank_transfers <= 500 AND _rank_volume <= 500 ), token_transfer_value_per_tx AS ( SELECT tx_hash , block_timestamp , sum(amount_usd) AS token_value_usd FROM ethereum.core.ez_token_transfers AS tr WHERE tr.block_timestamp >= CURRENT_DATE - interval '30 days' AND tr.block_timestamp < CURRENT_DATE AND amount_usd IS NOT NULL AND contract_address IN (SELECT token_address FROM top_tokens) GROUP BY 1,2 HAVING token_value_usd IS NOT NULL AND token_value_usd < power(10,12) ), transactions AS ( SELECT tx.tx_hash , tx.block_timestamp , tx.from_address , tx.to_address , tx.eth_value , tx.gas_used , tx.gas_price , ethp.price AS eth_price_usd , tx.eth_value * ethp.price AS eth_value_usd , tr.token_value_usd , tx.gas_used * tx.gas_price * ethp.price / power(10, 9) AS tx_fee_usd FROM ethereum.core.fact_transactions AS tx LEFT JOIN ethereum.core.fact_hourly_token_prices AS ethp ON ethp.hour = date_trunc('hour', tx.block_timestamp) AND ethp.token_address = '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2' LEFT JOIN token_transfer_value_per_tx AS tr ON tr.tx_hash = tx.tx_hash WHERE tx.block_timestamp >= CURRENT_DATE - interval '30 days' AND tx.block_timestamp < CURRENT_DATE ), daily_stats AS ( SELECT 'Ethereum' AS blockchain , block_timestamp::date AS utc_date , count(*) AS transactions -- , sum(gas_used) AS gas_used , avg(tx_fee_usd) AS avg_tx_fee_usd , sum(tx_fee_usd) AS tx_fees_usd , sum(eth_value_usd + token_value_usd) AS liquidity_transferred_usd FROM transactions GROUP BY 1,2 ) SELECT * FROM daily_stats ), daily_stats__optimism AS ( WITH token_transfer_value_per_tx AS ( SELECT tx_hash , block_timestamp , sum(tr.raw_amount / power(10, c.decimals) * p.price) AS token_value_usd FROM optimism.core.fact_token_transfers AS tr LEFT JOIN optimism.core.dim_contracts AS c ON c.address = tr.contract_address LEFT JOIN optimism.core.fact_hourly_token_prices AS p ON p.hour = date_trunc('hour', block_timestamp) AND p.token_address = tr.contract_address WHERE tr.block_timestamp >= CURRENT_DATE - interval '30 days' AND tr.block_timestamp < CURRENT_DATE GROUP BY 1,2 HAVING token_value_usd IS NOT NULL ), transactions AS ( SELECT tx.tx_hash , tx.block_timestamp , tx.from_address , tx.to_address , tx.eth_value , tx.gas_used , tx.gas_price , ethp.price AS eth_price_usd , tx.eth_value * ethp.price AS eth_value_usd , tr.token_value_usd , tx.gas_used * tx.gas_price * ethp.price / power(10, 9) AS tx_fee_usd FROM optimism.core.fact_transactions AS tx LEFT JOIN ethereum.core.fact_hourly_token_prices AS ethp ON ethp.hour = date_trunc('hour', tx.block_timestamp) AND ethp.token_address = '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2' LEFT JOIN token_transfer_value_per_tx AS tr ON tr.tx_hash = tx.tx_hash WHERE tx.block_timestamp >= CURRENT_DATE - interval '30 days' AND tx.block_timestamp < CURRENT_DATE ), daily_stats AS ( SELECT 'Optimism' AS blockchain , block_timestamp::date AS utc_date , count(*) AS transactions , avg(tx_fee_usd) AS avg_tx_fee_usd , sum(tx_fee_usd) AS tx_fees_usd , sum(eth_value_usd + token_value_usd) AS liquidity_transferred_usd FROM transactions GROUP BY 1,2 ) SELECT * FROM daily_stats ), daily_stats__algorand AS ( WITH asset_prices AS ( SELECT * FROM ( SELECT asset_id, asset_name, block_hour, price_usd FROM algorand.core.ez_price_pool_balances WHERE block_hour >= CURRENT_DATE - interval '30 days' AND block_hour < CURRENT_DATE UNION ALL SELECT asset_id, asset_name, block_hour, price_usd FROM algorand.core.ez_price_swap WHERE block_hour >= CURRENT_DATE - interval '30 days' AND block_hour < CURRENT_DATE ) QUALIFY row_number() OVER (partition by asset_id, block_hour order by block_hour desc) = 1 ), top_tokens AS ( SELECT asset_id , count(*) AS swaps , avg(amount_usd) AS avg_swap_amount_usd , sum(amount_usd) AS volume_usd , row_number() OVER (order by swaps desc) AS _rank_swaps , row_number() OVER (order by volume_usd desc) AS _rank_volume FROM ( SELECT sw.fact_swap_id , sw.block_timestamp , sw.swap_from_asset_id AS asset_id , sw.swap_from_amount / power(10, da.decimals) * p.price_usd AS amount_usd FROM algorand.defi.fact_swap AS sw INNER JOIN algorand.core.dim_asset AS da ON da.dim_asset_id = sw.dim_asset_id__swap_from INNER JOIN asset_prices AS p ON p.asset_id = sw.swap_from_asset_id AND p.block_hour = date_trunc('hour', sw.block_timestamp) WHERE sw.block_timestamp >= CURRENT_DATE - interval '30 days' AND sw.block_timestamp < CURRENT_DATE AND amount_usd < 1e9 UNION ALL SELECT sw.fact_swap_id , sw.block_timestamp , sw.swap_to_asset_id AS asset_id , sw.swap_to_amount / power(10, da.decimals) * p.price_usd AS amount_usd FROM algorand.defi.fact_swap AS sw INNER JOIN algorand.core.dim_asset AS da ON da.dim_asset_id = sw.dim_asset_id__swap_to INNER JOIN asset_prices AS p ON p.asset_id = sw.swap_from_asset_id AND p.block_hour = date_trunc('hour', sw.block_timestamp) WHERE sw.block_timestamp >= CURRENT_DATE - interval '30 days' AND sw.block_timestamp < CURRENT_DATE AND amount_usd < 1e9 ) GROUP BY 1 HAVING avg_swap_amount_usd < 1000 QUALIFY _rank_swaps <= 100 AND _rank_volume <= 100 ), algo_transfer_value_per_tx AS ( SELECT tx_id , sum(tx.amount * p.price_usd) AS transfer_value_usd FROM algorand.core.ez_transaction_payment AS tx INNER JOIN asset_prices AS p ON p.block_hour = date_trunc('hour', tx.block_timestamp) AND p.asset_id = tx.asset_id WHERE tx.block_timestamp >= CURRENT_DATE - interval '30 days' AND tx.block_timestamp < CURRENT_DATE GROUP BY 1 HAVING transfer_value_usd > 0 AND transfer_value_usd < 1e12 ), asset_transfer_value_per_tx AS ( SELECT tx_id -- , tx.asset_id -- , tx.asset_name -- , da.decimals -- , tx.asset_amount / power(10, da.decimals) AS amount -- , p.price_usd -- , tx.asset_amount / power(10, da.decimals) * p.price_usd AS amount_usd , sum(tx.asset_amount / power(10, da.decimals) * p.price_usd) AS transfer_value_usd FROM algorand.core.ez_transaction_asset_transfer AS tx INNER JOIN algorand.core.dim_asset AS da ON da.asset_id = tx.asset_id LEFT JOIN asset_prices AS p ON p.block_hour = date_trunc('hour', tx.block_timestamp) AND p.asset_id = tx.asset_id WHERE tx.block_timestamp >= CURRENT_DATE - interval '30 days' AND tx.block_timestamp < CURRENT_DATE AND da.is_nft = False AND tx.asset_amount > 0 AND tx.asset_id IN (SELECT asset_id FROM top_tokens) GROUP BY 1 HAVING transfer_value_usd > 0 AND transfer_value_usd < 1e12 ), transactions AS ( SELECT tx.tx_id , tx.block_timestamp , tx.tx_sender , tx.app_id , tx.fee AS tx_fee , algop.price_usd AS algo_price_usd , coalesce(tr_algo.transfer_value_usd, 0) AS algo_value_usd , coalesce(tr_asa.transfer_value_usd, 0) AS token_value_usd , tx_fee * algo_price_usd AS tx_fee_usd FROM algorand.core.fact_transaction AS tx LEFT JOIN asset_prices AS algop ON algop.block_hour = date_trunc('hour', block_timestamp) AND algop.asset_id = 0 LEFT JOIN algo_transfer_value_per_tx AS tr_algo ON tr_algo.tx_id = tx.tx_id LEFT JOIN asset_transfer_value_per_tx AS tr_asa ON tr_asa.tx_id = tx.tx_id WHERE tx.block_timestamp >= CURRENT_DATE - interval '30 days' AND tx.block_timestamp < CURRENT_DATE ), daily_stats AS ( SELECT 'Algorand' AS blockchain , block_timestamp::date AS utc_date , count(*) AS transactions , avg(tx_fee_usd) AS avg_tx_fee_usd , sum(tx_fee_usd) AS tx_fees_usd , sum(algo_value_usd + token_value_usd) AS liquidity_transferred_usd FROM transactions GROUP BY 1,2 HAVING sum(algo_value_usd) > 0 ) SELECT * FROM daily_stats ), daily_stats__solana AS ( WITH token_prices AS ( SELECT symbol , (CASE symbol WHEN 'COPE' THEN '8HGyAAB1yoM1ttS7pXjHMa3dukTFGQggnFFH3hJZgzQh' WHEN 'FIDA' THEN 'EchesyfXePKdLtoiZSL8pBe8Myagyy8ZRqsACNCFGnvp' WHEN 'JET' THEN 'JET6zMJWkCN9tpRT2v2jfAmm5VnQFDpUBCyaKojmGtz' WHEN 'MER' THEN 'MERt85fc5boKw3BW1eYdxonEuJNvXbiMbs6hvheau5K' WHEN 'MNGO' THEN 'MangoCzJ36AjZyKwVj3VnYU4GTonjfVEnJmvvWaxLac' WHEN 'MSOL' THEN 'mSoLzYCxHdYgdzU16g5QSh3i5K3z3KZK7ytfqcJm7So' WHEN 'RAY' THEN '4k3Dyjzvzp8eMZWUXbBCjEvwSkkk59S5iCNLY3QrkX6R' WHEN 'SBR' THEN 'Saber2gLauYim4Mvftnrasomsv6NvAuncvMEZwcLpD1' WHEN 'SLND' THEN 'SLNDpmoWTVADgEdndyvWzroNL7zSi1dF9PC3xHGtPwp' WHEN 'SOL' THEN 'So11111111111111111111111111111111111111112' WHEN 'SRM' THEN 'SRMuApVNdxXokk5GT7XD5cUUgXMBCoAz2LHeuAoKWRt' WHEN 'STSOL' THEN '7dHbWXmci3dT8UFYWYZweBLXgycu7Y3iL6trKn1Y7ARj' WHEN 'USDC' THEN 'EPjFWdd5AufqSSqeM2qN1xzybapC8G4wEGGkZwyTDt1v' WHEN 'USDT' THEN 'Es9vMFrzaCERmJfrF4H2FYD4KCoNkY11McCe8BenwNYB' WHEN 'USTC' THEN '9vMJfxuKxXBoEa7rM12mYLMwTacLMLDJqHozw96WQL8i' ELSE NULL END) AS token_address , recorded_hour AS hour , avg(close) AS price_usd FROM solana.core.fact_token_prices_hourly WHERE recorded_hour >= CURRENT_DATE - interval '30 days' AND recorded_hour >= CURRENT_DATE - interval '30 days' AND recorded_hour < CURRENT_DATE AND token_address IS NOT NULL GROUP BY 1,2,3 UNION ALL SELECT (CASE token_address WHEN '0x2260fac5e5542a773aa44fbcfedf7c193bc2c599' THEN 'BTC' ELSE NULL END) AS symbol , (CASE token_address WHEN '0x2260fac5e5542a773aa44fbcfedf7c193bc2c599' THEN '9n4nbM75f5Ui33ZbPYXn59EwSgE8CGsHtAeTH5YFeJ9E' ELSE NULL END) AS token_address , hour , price AS price_usd FROM ethereum.core.fact_hourly_token_prices WHERE hour >= CURRENT_DATE - interval '30 days' AND hour < CURRENT_DATE AND token_address IN ('0x2260fac5e5542a773aa44fbcfedf7c193bc2c599') QUALIFY row_number() OVER (partition by token_address, hour order by hour desc) = 1 ), transfer_value_per_tx AS ( SELECT tx.tx_id , tx.block_timestamp -- , tx.mint -- , tx.amount -- , p.price_usd , sum(tx.amount * p.price_usd) AS transfer_value_usd FROM solana.core.fact_transfers AS tx INNER JOIN token_prices AS p ON p.hour = date_trunc('hour', tx.block_timestamp) AND p.token_address = tx.mint WHERE tx.block_timestamp >= CURRENT_DATE - interval '30 days' AND tx.block_timestamp >= CURRENT_DATE - interval '30 days' AND tx.block_timestamp < CURRENT_DATE GROUP BY 1,2 ), transactions AS ( SELECT tx.tx_id , tx.block_timestamp , tx.signers[0]::string AS from_address , tx.fee / 1e9 AS tx_fee , solp.price_usd AS sol_price_usd , tr.transfer_value_usd , tx.fee / 1e9 * solp.price_usd AS tx_fee_usd FROM solana.core.fact_transactions AS tx LEFT JOIN token_prices AS solp ON solp.hour = date_trunc('hour', tx.block_timestamp) AND solp.symbol = 'SOL' LEFT JOIN transfer_value_per_tx AS tr ON tr.tx_id = tx.tx_id WHERE tx.block_timestamp >= CURRENT_DATE - interval '30 days' AND tx.block_timestamp >= CURRENT_DATE - interval '30 days' AND tx.block_timestamp < CURRENT_DATE ), daily_stats AS ( SELECT 'Solana' AS blockchain , block_timestamp::date AS utc_date , count(*) AS transactions -- , sum(tx_fee) AS tx_fees , avg(tx_fee_usd) AS avg_tx_fee_usd , sum(tx_fee_usd) AS tx_fees_usd , sum(transfer_value_usd) AS liquidity_transferred_usd FROM transactions GROUP BY 1,2 ) SELECT * FROM daily_stats ), daily_stats__flow AS ( WITH daily_token_prices AS ( SELECT timestamp::date AS utc_date , token_contract , symbol , price_usd FROM flow.core.fact_prices AS p WHERE p.timestamp >= CURRENT_DATE - interval '30 days' AND p.timestamp < CURRENT_DATE QUALIFY row_number() OVER (partition by token_contract, timestamp::date order by timestamp desc) = 1 ), token_transfer_value_per_tx AS ( SELECT tr.tx_id -- , tr.amount -- , p.price_usd -- , tr.amount * p.price_usd AS amount_usd , sum(tr.amount * p.price_usd) AS token_value_usd FROM flow.core.ez_token_transfers AS tr LEFT JOIN daily_token_prices AS p ON p.token_contract = tr.token_contract AND p.utc_date = tr.block_timestamp::date WHERE tr.block_timestamp >= CURRENT_DATE - interval '30 days' AND tr.block_timestamp < CURRENT_DATE AND tr.recipient != '0xf919ee77447b7497' -- FlowFees contract GROUP BY 1 ), transaction_fees AS ( SELECT tx.tx_id , tx.block_timestamp , tx.event_contract , tx.event_data['amount']::float AS tx_fee , flowp.price_usd AS flow_price_usd , tx_fee * flowp.price_usd AS tx_fee_usd FROM flow.core.fact_events AS tx LEFT JOIN daily_token_prices AS flowp ON flowp.utc_date = tx.block_timestamp::date AND flowp.token_contract = 'A.1654653399040a61.FlowToken' LEFT JOIN token_transfer_value_per_tx AS tr ON tr.tx_id = tx.tx_id WHERE tx.block_timestamp >= CURRENT_DATE - interval '30 days' AND tx.block_timestamp < CURRENT_DATE AND tx.event_type = 'FeesDeducted' AND tx.event_contract = 'A.f919ee77447b7497.FlowFees' ), transactions AS ( SELECT tx.tx_id , tx.block_timestamp , tx.proposer AS from_address , tf.flow_price_usd , coalesce(tr.token_value_usd, 0) AS token_value_usd , coalesce(tf.tx_fee_usd, 0) AS tx_fee_usd FROM flow.core.fact_transactions AS tx LEFT JOIN token_transfer_value_per_tx AS tr ON tr.tx_id = tx.tx_id LEFT JOIN transaction_fees AS tf ON tf.tx_id = tx.tx_id WHERE tx.block_timestamp >= CURRENT_DATE - interval '30 days' AND tx.block_timestamp < CURRENT_DATE ), daily_stats AS ( SELECT 'Flow' AS blockchain , block_timestamp::date AS utc_date , count(*) AS transactions , avg(tx_fee_usd) AS avg_tx_fee_usd , sum(tx_fee_usd) AS tx_fees_usd , sum(token_value_usd) AS liquidity_transferred_usd FROM transactions GROUP BY 1,2 ) SELECT * FROM daily_stats ), daily_stats__near AS ( WITH daily_token_prices AS ( SELECT timestamp::date AS utc_date , token_contract , symbol , price_usd FROM near.core.fact_prices AS p WHERE p.timestamp >= CURRENT_DATE - interval '30 days' AND p.timestamp < CURRENT_DATE QUALIFY row_number() OVER (partition by token_contract, timestamp::date order by timestamp desc) = 1 ), transactions AS ( SELECT tx.tx_hash , tx.block_timestamp , tx.gas_used , tx.gas_used / 1e12 AS tgas_used , tx.transaction_fee / 1e24 AS tx_fee_near , p.price_usd AS near_price_usd , tx_fee_near * near_price_usd AS tx_fee_usd FROM near.core.fact_transactions AS tx LEFT JOIN daily_token_prices AS p ON p.utc_date = tx.block_timestamp::DATE AND p.symbol = 'wNEAR' WHERE tx.block_timestamp >= CURRENT_DATE - interval '30 days' AND tx.block_timestamp < CURRENT_DATE ), daily_stats AS ( SELECT 'NEAR' AS blockchain , block_timestamp::date AS utc_date , count(*) AS transactions , avg(tx_fee_usd) AS avg_tx_fee_usd , sum(tx_fee_usd) AS tx_fees_usd , sum(NULL) AS liquidity_transferred_usd FROM transactions GROUP BY 1,2 ) SELECT * FROM daily_stats ), daily_stats__osmosis AS ( WITH token_symbols AS ( SELECT * FROM ( SELECT address AS currency , project_name AS symbol FROM osmosis.core.dim_labels WHERE label_type = 'token' ) AS s QUALIFY row_number() OVER (partition by currency order by symbol) = 1 ), daily_token_prices AS ( SELECT dp.recorded_at::date AS utc_date , ts.currency , dp.symbol , dp.price AS price_usd FROM osmosis.core.dim_prices AS dp LEFT JOIN token_symbols AS ts ON ts.symbol = dp.symbol QUALIFY row_number() OVER (partition by dp.symbol, utc_date order by dp.recorded_at desc) = 1 ), token_transfer_value_per_tx AS ( SELECT tr.tx_id -- , tr.amount / power(10, tr.decimal) AS amount -- , p.price_usd -- , tr.amount / power(10, tr.decimal) * p.price_usd AS amount_usd , sum(tr.amount / power(10, tr.decimal) * p.price_usd) AS token_value_usd FROM osmosis.core.fact_transfers AS tr INNER JOIN daily_token_prices AS p ON p.currency = tr.currency AND p.utc_date = tr.block_timestamp::date WHERE tr.block_timestamp >= CURRENT_DATE - interval '30 days' AND tr.block_timestamp < CURRENT_DATE GROUP BY 1 ), transactions AS ( SELECT tx.tx_id , tx.block_timestamp , tx.gas_used , tx.fee , (CASE WHEN right(tx.fee, 5) = 'uosmo' THEN replace(tx.fee, 'uosmo', '')::integer / 1e6 ELSE NULL END) AS tx_fee_osmo , p.price_usd AS osmo_price_usd , coalesce(tr.token_value_usd, 0) AS token_value_usd , tx_fee_osmo * osmo_price_usd AS tx_fee_usd FROM osmosis.core.fact_transactions AS tx LEFT JOIN daily_token_prices AS p ON p.utc_date = tx.block_timestamp::DATE AND p.symbol = 'OSMO' LEFT JOIN token_transfer_value_per_tx AS tr ON tr.tx_id = tx.tx_id WHERE tx.block_timestamp >= CURRENT_DATE - interval '30 days' AND tx.block_timestamp < CURRENT_DATE ), daily_stats AS ( SELECT 'Osmosis' AS blockchain , block_timestamp::date AS utc_date , count(*) AS transactions , avg(tx_fee_usd) AS avg_tx_fee_usd , sum(tx_fee_usd) AS tx_fees_usd , sum(token_value_usd) AS liquidity_transferred_usd FROM transactions GROUP BY 1,2 ) SELECT * FROM daily_stats ), daily_stats__all_chains AS ( SELECT * FROM daily_stats__ethereum UNION ALL SELECT * FROM daily_stats__optimism UNION ALL SELECT * FROM daily_stats__algorand UNION ALL SELECT * FROM daily_stats__solana UNION ALL SELECT * FROM daily_stats__flow UNION ALL SELECT * FROM daily_stats__near UNION ALL SELECT * FROM daily_stats__osmosis ) SELECT * FROM daily_stats__all_chains LIMIT 10000

Query	Creator	Created
P30D Daily Gas Spend - All Chains - Cross-chain gas guzzlers flow near osmosis prices swaps	rmas	Nov 15, 2022
EDA - Superfluid Staking osmosis prices staking ibc defi	rmas	May 4, 2023
Sequence Conformity: A votes ➔ B votes osmosis staking ibc governance	rmas	May 5, 2023

rmas