with day_hour AS (

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

symbol, sum(amount) as total_transfer_amount,

count(*) as n_transfers,

count(distinct from_address) as n_unique_senders,

count(distinct to_address) as n_unique_recipients

from ethereum.core.ez_token_transfers

where

contract_address IN (

'0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48', -- USDC 6082465

'0xdac17f958d2ee523a2206206994597c13d831ec7', -- USDT 4634748

'0x6b175474e89094c44da98b954eedeac495271d0f' -- DAI 8928158

AND block_timestamp >= '2024-01-01'

AND block_number > 4634700 -- first stable created

AND block_timestamp < date_trunc('month', current_date)

group by hr_, symbol

select HOUR(hr_) AS hour_component,

symbol,

case when symbol = 'USDC' then 3

when symbol = 'DAI' then 2 else 1

end as decided_coin_order,

AVG(total_transfer_amount) as average_volume_in_hr,

AVG(n_transfers) as average_transfers_in_hr,

AVG(n_unique_senders) as average_unique_senders,

AVG(n_unique_recipients) as average_unique_recipients

from day_hour

group by hour_component, symbol, decided_coin_order

order by hour_component ASC, decided_coin_order ASC