with squid as (select

from axelar.core.ez_squid

where source_chain = 'binance'

),

price as (select

HOUR::date as day,

SYMBOL,

TOKEN_ADDRESS,

DECIMALS,

avg(price) as token_price

from bsc.core.fact_hourly_token_prices

group by 1,2,3,4

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

project_name as dex,

count(distinct s.tx_hash) as no_swaps,

count(distinct FROM_ADDRESS) as no_swappers,

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

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

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

FROM bsc.core.fact_token_transfers s

LEFT JOIN bsc.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 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