with monthly_transfers AS (

select date_trunc('day',block_timestamp) as day_,

symbol, sum(amount) as total_transfer_amount,

count(*) as n_transfers,

count(distinct from_address) as "# 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_timestamp < date_trunc('day', current_date)

AND block_number > 4634700 -- first stable created

group by day_, symbol

),

BTC_price as (

select

date_trunc(day, hour) as date,

avg(price) as "$BTC Price"

from

bitcoin.price.ez_prices_hourly

group by

1),

table1 as (select *,

case when symbol = 'USDC' then 1

when symbol = 'DAI' then 2 else 3

end as decided_coin_order,

from monthly_transfers

order by day_, decided_coin_order ASC)

select *