select

date_trunc('day',hour) as time,

symbol,

avg(price) as avg_price

from bsc.core.fact_hourly_token_prices

where symbol in ('WBNB','USDT')

and HOUR::date >= '2023-08-04'

group by 1 , 2)

(select

'Sawp For TS' as action,

date_trunc('day',BLOCK_TIMESTAMP::DATE) as date,

SYMBOL_IN as From_token,

AMOUNT_IN as From_Amount,

(From_Amount * avg_price) AS From_Amount_USD,

'TS' as For_token,

case

when PLATFORM = 'pancakeswap-v2' then (AMOUNT_OUT/pow(10,18))

when PLATFORM = 'pancakeswap-v3' then AMOUNT_OUT

end as For_Amount,

null AS For_Amount_USD,

coalesce(From_Amount_USD,For_Amount_USD) as swap_value_usd,

ORIGIN_FROM_ADDRESS,

tx_hash,

PLATFORM

from bsc.core.ez_dex_swaps

inner join hourly_price on date = time and symbol = From_token

where TOKEN_OUT = '0x9879406c2ef6578ceb59009d64151ef3f225830b'

having TOKEN_IN != '0x9879406c2ef6578ceb59009d64151ef3f225830b')

UNION ALL

(select

'Swap From TS' as actoin,

date_trunc('day',BLOCK_TIMESTAMP::DATE) as date,

'TS' as From_token,

case