when contract_address = '0x22b1cbb8d98a01a3b71d034bb899775a76eb1cc2' then 'MATIC'

when contract_address = '0x3e4a3a4796d16c0cd582c382691998f7c06420b6' then 'USDT'

when contract_address = '0xb8901acb165ed027e32754e0ffe830802919727f' then 'WETH'

when contract_address = '0x3d4cc8a61c7528fd86c55cfe061a78dcba48edd1' then 'DAI' end as currency,

sum(event_inputs:amount/1e18) as volume,

count(distinct event_inputs:recipient::string) as sender_count, count(distinct tx_hash) as tx_count

from ethereum_core.fact_event_logs

where contract_address in

('0x22b1cbb8d98a01a3b71d034bb899775a76eb1cc2', --matic

'0xb8901acb165ed027e32754e0ffe830802919727f', --eth

'0x3d4cc8a61c7528fd86c55cfe061a78dcba48edd1') --dai

and event_name = 'TransferSentToL2' and year(block_timestamp) = '2022'

group by 1,2),

usdc as (select date(block_timestamp) as dt, contract_address, case

when contract_address = '0x3666f603cc164936c1b87e207f36beba4ac5f18a' then 'USDC'

when contract_address = '0x3e4a3a4796d16c0cd582c382691998f7c06420b6' then 'USDT'

end as currency,

sum(event_inputs:amount/1e6) as volume,

count(distinct event_inputs:recipient::string) as sender_count, count(distinct tx_hash) as tx_count

from ethereum_core.fact_event_logs

where contract_address in ('0x3666f603cc164936c1b87e207f36beba4ac5f18a', '0x3e4a3a4796d16c0cd582c382691998f7c06420b6')

and event_name = 'TransferSentToL2' and year(block_timestamp) = '2022'

group by 1,2),

price as (select date_trunc('day', hour) as dt, avg(price) as price, symbol, token_address

from ethereum.core.fact_hourly_token_prices

where symbol in ('WETH', 'MATIC') and year(hour) = '2022'

group by 1,3,4),

lasts as (select a.dt, a.currency, a.volume, b.price as avg_daily_price, case

when avg_daily_price is null then 1

else avg_daily_price end as prices,

(a.volume*prices) as vol_usd,

a.sender_count, a.tx_count