select date_trunc('hour', block_timestamp) as hora,

count(distinct tx_id) as txs,

round(sum(tx_message:txn:aamt/pow(10,6)),2) as daily_vol,

round(daily_vol / txs, 2) as avg_tx,

round(sum(fee), 3) as daily_fees,

round(daily_fees / txs, 5) as avg_fee

from algorand.transactions

where asset_id = '31566704'

and tx_type_name = 'asset transfer'

and block_timestamp::DATE = '2022-01-21'

group by 1

order by 1

),

Solana as (

select date_trunc('hour', block_timestamp) as hora,

count(distinct tx_id) as txs,

round(sum(amount),2) as daily_vol,

round(daily_vol / txs, 2) as avg_tx

from solana.fact_transfers

where mint = 'EPjFWdd5AufqSSqeM2qN1xzybapC8G4wEGGkZwyTDt1v'

and block_timestamp::DATE = '2022-01-21'

group by 1

order by 1

),

Ethereum as (

select date_trunc('hour', block_timestamp) as hora,

count(distinct tx_id) as txs,

round(sum(amount),2) as daily_vol,

round(sum(amount_usd),2) as daily_vol_usd,

round(daily_vol / txs, 2) as avg_tx

from ethereum.udm_events

where contract_address = '0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48'