with eth as ( select date_trunc('day', hour) as day, avg(price) as price from ethereum.core.fact_hourly_token_prices where token_address is null group by 1 ), sol as ( select date_trunc('day', recorded_hour) as day, avg(close) as price from solana.core.fact_token_prices_hourly where symbol = 'SOL' group by 1 ), poly as ( select date_trunc('day', hour) as day, avg(price) as price from ethereum.core.fact_hourly_token_prices where symbol = 'MATIC' group by 1 ), algo as ( select date_trunc('day', block_hour) as day, avg(price_usd) as price from algorand.core.ez_price_pool_balances where asset_name = 'ALGO' group by 1 ), near as ( select date_trunc('day', timestamp) as day, avg(price_usd) as price from near.core.fact_prices where symbol = 'wNEAR' group by 1 ), flow as ( select date_trunc('day', timestamp) as day, avg(price_usd) as price from flow.core.fact_prices where symbol = 'FLOW' group by 1 ), osmo as ( select date_trunc('day', recorded_at) as day, avg(price) as price from osmosis.core.dim_prices where symbol = 'OSMO' group by 1 ), eth_transfer as ( select sum(amount_usd) as transfer from ethereum.core.ez_eth_transfers where block_timestamp::date >= current_date - 30 and block_timestamp::date < current_date and amount_usd is not null ), op_transfer as ( select sum(amount_usd) as transfer from optimism.core.ez_eth_transfers where block_timestamp::date >= current_date - 30 and block_timestamp::date < current_date and amount_usd is not null ), arbi_transfer as ( select sum(amount_usd) as transfer from arbitrum.core.ez_eth_transfers where block_timestamp::date >= current_date - 30 and block_timestamp::date < current_date and amount_usd is not null ), sol_transfer as ( select sum(amount * price) as transfer from solana.core.fact_transfers join sol on block_timestamp::date = day where block_timestamp::date >= current_date - 30 and block_timestamp::date < current_date and amount is not null and mint = 'So11111111111111111111111111111111111111112' ), poly_transfer as ( select sum(amount_usd) as transfer from polygon.core.ez_matic_transfers where block_timestamp::date >= current_date - 30 and block_timestamp::date < current_date and amount_usd is not null ), algo_transfer as ( select sum(amount * price) as transfer from algorand.core.ez_transfer join algo on block_timestamp::date = day where block_timestamp::date >= current_date - 30 and block_timestamp::date < current_date and amount is not null and asset_id = '0' ), near_transfer as ( select sum(deposit/1e24 * price) as transfer from near.core.fact_transfers join near on block_timestamp::date = day where block_timestamp::date >= current_date - 30 and block_timestamp::date < current_date and deposit is not null and status = 'TRUE' ), flow_transfer as ( select sum(amount * price) as transfer from flow.core.ez_token_transfers join flow on block_timestamp::date = day where block_timestamp::date >= current_date - 30 and block_timestamp::date < current_date and amount is not null and tx_succeeded = 'TRUE' and token_contract = 'A.1654653399040a61.FlowToken' ), osmo_transfer as ( select sum(amount/1e6 * price) as transfer from osmosis.core.fact_transfers join osmo on block_timestamp::date = day where block_timestamp::date >= current_date - 30 and block_timestamp::date < current_date and amount is not null and tx_status = 'SUCCEEDED' and currency = 'uosmo' ) select 'Ethereum' as blockchain, sum(tx_fee * price) as gas_fee, avg(tx_fee * price) as avg_gas_fee, (1000000 * gas_fee) / avg(transfer) as fee_per_milion from ethereum.core.fact_transactions join eth on block_timestamp::date = day join eth_transfer where block_timestamp::date >= current_date - 30 and block_timestamp::date < current_date union all select 'Optimism' as blockchain, sum(tx_fee * price) as gas_fee, avg(tx_fee * price) as avg_gas_fee, (1000000 * gas_fee) / avg(transfer) as fee_per_milion from optimism.core.fact_transactions join eth on block_timestamp::date = day join op_transfer where block_timestamp::date >= current_date - 30 and block_timestamp::date < current_date group by 1 union all select 'Arbitrum' as blockchain, sum(tx_fee * price) as gas_fee, avg(tx_fee * price) as avg_gas_fee, (1000000 * gas_fee) / avg(transfer) as fee_per_milion from arbitrum.core.fact_transactions join eth on block_timestamp::date = day join arbi_transfer where block_timestamp::date >= current_date - 30 and block_timestamp::date < current_date group by 1 union all select 'Solana' as blockchain, sum((fee/1e9) * price) as gas_fee, avg((fee/1e9) * price) as avg_gas_fee, (1000000 * gas_fee) / avg(transfer) as fee_per_milion from solana.core.fact_transactions join sol on block_timestamp::date = day join sol_transfer where block_timestamp::date >= current_date - 30 and block_timestamp::date < current_date group by 1 union all select 'Polygon' as blockchain, sum(tx_fee * price) as gas_fee, avg(tx_fee * price) as avg_gas_fee, (1000000 * gas_fee) / avg(transfer) as fee_per_milion from polygon.core.fact_transactions join poly on block_timestamp::date = day join arbi_transfer where block_timestamp::date >= current_date - 30 and block_timestamp::date < current_date group by 1 union all select 'Algorand' as blockchain, sum(fee * price) as gas_fee, avg(fee * price) as avg_gas_fee, (1000000 * gas_fee) / avg(transfer) as fee_per_milion from algorand.core.fact_transaction join algo on block_timestamp::date = day join algo_transfer where block_timestamp::date >= current_date - 30 and block_timestamp::date < current_date group by 1 union all select 'Near' as blockchain, sum((transaction_fee/1e24) * price) as gas_fee, avg((transaction_fee/1e24) * price) as avg_gas_fee, (1000000 * gas_fee) / avg(transfer) as fee_per_milion from near.core.fact_transactions join near on block_timestamp::date = day join near_transfer where block_timestamp::date >= current_date - 30 and block_timestamp::date < current_date group by 1 union all select 'Flow' as blockchain, sum(event_data:amount * price) as gas_fee, avg(event_data:amount * price) as avg_gas_fee, (1000000 * gas_fee) / avg(transfer) as fee_per_milion from flow.core.fact_events join flow on block_timestamp::date = day join flow_transfer where block_timestamp::date >= current_date - 30 and block_timestamp::date < current_date and event_type='FeesDeducted' group by 1 union all select 'Osmosis' as blockchain, sum(((split(fee,'uosmo')[0]::numeric)/1e6) * price) as gas_fee, avg(((split(fee,'uosmo')[0]::numeric)/1e6) * price) as avg_gas_fee, (1000000 * gas_fee) / avg(transfer) as fee_per_milion from osmosis.core.fact_transactions join osmo on block_timestamp::date = day join osmo_transfer where block_timestamp::date >= current_date - 30 and block_timestamp::date < current_date and fee ilike '%uosmo%' group by 1

Open Analytics Bounty: Solana (December 3)