select *

from $query('ad651c06-e8bf-46b0-8491-3db499ad0b81')

),

participants_contracts_list as (

select *

from $query('75f58bb3-b2cd-4208-85e9-e3d75d901a1c')

),

all_contracts_list as (

select *

from $query('22c3bdd1-3720-4594-bcd6-e14a9c76d781')

),

polygon_dexs_swaps as (

select BLOCK_TIMESTAMP, TX_HASH, ORIGIN_FUNCTION_SIGNATURE, ORIGIN_FROM_ADDRESS, ORIGIN_TO_ADDRESS,

AMOUNT_IN_UNADJ, AMOUNT_IN, AMOUNT_IN_USD, AMOUNT_OUT_UNADJ, AMOUNT_OUT, AMOUNT_OUT_USD, PLATFORM,

TOKEN_IN, TOKEN_OUT, SYMBOL_IN, SYMBOL_OUT

from polygon.defi.ez_dex_swaps

where (TOKEN_IN in (select contract from all_contracts_list) or TOKEN_OUT in (select contract from all_contracts_list))

-- and BLOCK_TIMESTAMP::date>='2024-09-01'

),

swaps_with_one_price AS (

select

DATE_TRUNC('hour', block_timestamp) as hour,

token_in as token_address,

symbol_in as symbol,

coalesce(amount_out_usd,amount_out*PRICE) as value,

amount_in as token_amount,

value/token_amount as token_price

from polygon_dexs_swaps a

left join polygon.price.ez_prices_hourly c

on hour=date_trunc(hour,BLOCK_TIMESTAMP) and c.TOKEN_ADDRESS=token_out

join all_contracts_list b