with squid as (select

from axelar.core.ez_squid

where source_chain = 'avalanche'

),

price as (select

HOUR::date as day,

SYMBOL,

TOKEN_ADDRESS,

avg(price) as token_price

from avalanche.core.fact_hourly_token_prices

group by 1,2,3

date_trunc('week',s.BLOCK_TIMESTAMP) as date,

p.symbol as assets,

count(distinct s.tx_hash) as no_swaps,

count(distinct FROM_ADDRESS) as no_swappers,

sum(RAW_AMOUNT/pow(10,a.DECIMALS)*token_price) as volume,

avg(RAW_AMOUNT/pow(10,a.DECIMALS)*token_price) as avg_volume,

median(RAW_AMOUNT/pow(10,a.DECIMALS)*token_price) as median_volume

FROM avalanche.core.fact_token_transfers s

LEFT JOIN avalanche.core.dim_labels l ON s.TO_ADDRESS = l.address

JOIN price p ON s.block_timestamp::date = p.day and p.token_address=s.CONTRACT_ADDRESS

left join avalanche.core.dim_contracts a on s.CONTRACT_ADDRESS=a.ADDRESS

left join squid b on s.FROM_ADDRESS=b.sender and s.BLOCK_TIMESTAMP::date=b.BLOCK_TIMESTAMP::date

where label_type='dex'

and s.tx_hash in (select tx_hash from squid)

group by 1,2 having ASSETS is not null