with act1 as (

with tab1 as (

select ADDRESS

,PROJECT_NAME

from arbitrum.core.dim_labels

where LABEL_TYPE='dex')

,tab2 as (

select date_trunc('month',BLOCK_TIMESTAMP)as month

,AMOUNT

,FROM_ADDRESS

,ORIGIN_FROM_ADDRESS

,ORIGIN_TO_ADDRESS

,TO_ADDRESS

,TX_HASH

from arbitrum.core.ez_token_transfers

where CONTRACT_ADDRESS in (lower('0xaf88d065e77c8cC2239327C5EDb3A432268e5831'),lower('0xFF970A61A04b1cA14834A43f5dE4533eBDDB5CC8'))

select month

,'ARBITRUM' as chain

,sum(case when ORIGIN_FROM_ADDRESS=FROM_ADDRESS then AMOUNT end) as swap_from

,sum(case when ORIGIN_FROM_ADDRESS=TO_ADDRESS then AMOUNT end) as swap_to

,avg(case when ORIGIN_FROM_ADDRESS=FROM_ADDRESS then AMOUNT end) as avg_swap_from

,avg(case when ORIGIN_FROM_ADDRESS=TO_ADDRESS then AMOUNT end) as avg_swap_to

,count(distinct case when ORIGIN_FROM_ADDRESS=FROM_ADDRESS then ORIGIN_FROM_ADDRESS end) as swappers_from

,count(distinct case when ORIGIN_FROM_ADDRESS=TO_ADDRESS then ORIGIN_FROM_ADDRESS end) as swappers_to

,count(distinct case when ORIGIN_FROM_ADDRESS=FROM_ADDRESS then TX_HASH end) as swaps_from

,count(distinct case when ORIGIN_FROM_ADDRESS=TO_ADDRESS then TX_HASH end) as swaps_to

from tab1

left join tab2

on tab1.ADDRESS=tab2.ORIGIN_TO_ADDRESS

where month is not null

group by 1,2),

act2 as (

with tab1 as (