SELECT

trunc(timestamp,'day') as day,

TOKEN_CONTRACT,

avg(price_usd) as price_token

from near.core.fact_prices

group by 1,2

)

PROJECT_NAME as PLATFORMs,

count(distinct TX_HASH) as no_swaps,

count(distinct TRADER) as no_trader,

count(distinct POOL_ID) as no_pools,

sum(AMOUNT_OUT*price_token) as volume_usd,

avg(AMOUNT_OUT*price_token) as average_volume,

median(AMOUNT_OUT*price_token) as median_volume,

no_swaps/no_trader as average_swapper,

no_trader/count(distinct date_trunc(day, block_timestamp)) as average_buyer_day,

volume_USD/count(distinct date_trunc(day, block_timestamp)) as average_volume_day,

volume_usd/count(distinct BLOCK_ID) as average_volume_block,

row_number() over (order by volume_usd desc) as rank

FROM near.core.ez_dex_swaps s

left join tb5 a on s.TOKEN_OUT_CONTRACT=a.TOKEN_CONTRACT and s.BLOCK_TIMESTAMP::date=a.day

left join near.core.dim_address_labels b on s.platform=b.address

where AMOUNT_OUT <1e6

and block_timestamp>='2023-01-01'

and label_type not in ('token')

group by 1 having PLATFORMs is not null

order by 2 desc limit 10