'CEX to DEX' as status,

c.project_name,

count(DISTINCT tx_hash) as count_transactions,

count(DISTINCT to_address) as count_dex_address,

sum(raw_amount/pow(10,18)) as uni_amount,

sum(count_transactions) over (partition by c.project_name order by monthly asc) as cumulative_transactions,

sum(uni_amount) over (partition by c.project_name order by monthly asc) as cumulative_volume

from ethereum.core.fact_token_transfers a join crosschain.core.dim_labels b on a.from_address = b.address

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

where b.label_type = 'cex'

and c.label_type = 'dex'

group by 1,2,3)

,

tb2 as ( select trunc(block_timestamp,'month') as monthly,

'DEX to CEX' as status,

c.project_name,

count(DISTINCT tx_hash) as count_transactions,

count(DISTINCT from_address) as count_dex_address,

sum(raw_amount/pow(10,18)) as uni_amount,

avg(raw_amount/pow(10,18)) as avg_uni_amount,

max(raw_amount/pow(10,18)) as max_uni_amount,

sum(count_transactions) over (order by monthly asc) as cumulative_transactions,

sum(uni_amount) over (order by monthly asc) as cumulative_volume

from ethereum.core.fact_token_transfers a join crosschain.core.dim_labels b on a.to_address = b.address

join crosschain.core.dim_labels c on a.from_address = c.address

where b.label_type = 'cex'

and c.label_type = 'dex'

group by 1,2,3)

select *

from tb2