from arbitrum.core.fact_token_transfers

where origin_to_address = lower('0x67a24ce4321ab3af51c2d0a4801c3e111d88c9d9')

and contract_address = lower('0x912ce59144191c1204e64559fe8253a0e49e6548')

and from_address = origin_to_address)

,

arbi_cex as ( select date(block_timestamp) as date, 'Arbitrum' as chain, project_name, count(DISTINCT(a.origin_from_address)) as user, sum((raw_amount/pow(10,18)*1.3)) as volume,

avg((raw_amount/pow(10,18))*1.3) as avg_volume

from arbitrum.core.fact_token_transfers a join arbi b on a.origin_from_address = b.origin_from_address

join crosschain.core.address_labels c on a.to_address = c.address

where contract_address = lower('0x912ce59144191c1204e64559fe8253a0e49e6548')

and block_timestamp > b.date

and raw_amount/pow(10,18) <= amount

and label_type in ('defi','dex')

group by 1,2,3)

,

op as ( select 'Optimism' as chain, block_timestamp as date, origin_from_address

from optimism.core.fact_token_transfers

where contract_address = '0x4200000000000000000000000000000000000042' and

from_address = lower('0xfedfaf1a10335448b7fa0268f56d2b44dbd357de')

and origin_function_signature = '0x2e7ba6ef'

and block_timestamp::date <= '2022-06-06'

,

op_cex as ( select date(block_timestamp) as date, 'Optimism' as chain, project_name, count(DISTINCT(a.origin_from_address)) as user, sum((raw_amount/pow(10,18)*1.3)) as volume,

avg((raw_amount/pow(10,18))*1.3) as avg_volume

from optimism.core.fact_token_transfers a join op b on a.origin_from_address = b.origin_from_address

join crosschain.core.address_labels c on a.to_address = c.address

where contract_address = lower('0x4200000000000000000000000000000000000042')

and block_timestamp > b.date

and label_type in ('defi','dex')

group by 1,2,3)

,

arbi_final as ( select date, chain, project_name, sum(user) as total_user, sum(volume) as total_volume, avg(avg_volume) as avg_volume,

rank() over (order by date asc) as day

from arbi_cex