with tb1 as (select

RECORDED_HOUR::date as day,

TOKEN_ADDRESS,

avg (close) as price_token

from solana.core.ez_token_prices_hourly

where day>= '2023-08-01'

group by 1,2)

select

BLOCK_TIMESTAMP::date as day,

'Sell $GP' as swap_type,

count(distinct tx_id) as no_swaps,

count(distinct swapper) as no_traders,

sum(price_token*SWAP_FROM_AMOUNT) as amount_usd,

avg(price_token*SWAP_FROM_AMOUNT) as avg_usd,

median(price_token*SWAP_FROM_AMOUNT) as median_usd

from solana.core.fact_swaps s

left join tb1 b on s.BLOCK_TIMESTAMP::date=b.day and s.SWAP_from_MINT=b.TOKEN_ADDRESS

where block_timestamp>='2023-08-01'

and SUCCEEDED = 'TRUE'

and (s.SWAP_FROM_MINT = '31k88G5Mq7ptbRDf3AM13HAq6wRQHXHikR8hik7wPygk')

group by 1

union all

select

BLOCK_TIMESTAMP::date as day,

'Buy $GP' as swap_type,

count(distinct tx_id) as no_swaps,

count(distinct swapper) as no_traders,

sum(price_token*SWAP_TO_AMOUNT) as amount_usd,

avg(price_token*SWAP_TO_AMOUNT) as avg_usd,

median(price_token*SWAP_TO_AMOUNT) as median_usd

from solana.core.fact_swaps s

left join tb1 b on s.BLOCK_TIMESTAMP::date=b.day and s.SWAP_TO_MINT=b.TOKEN_ADDRESS

where block_timestamp>='2023-08-01'