select

timestamp as p_date,

TOKEN_CONTRACT,

symbol,

avg(price_usd) as "PRICE($)"

from flow.core.fact_prices

WHERE token != 'Blocto'

group by 1, 2, 3

order by 1

),

inflow as (

SELECT

BLOCK_TIMESTAMP,

tx_id,

TRADER,

SWAP_CONTRACT,

SYMBOL as SYMBOL0,

TOKEN_IN_AMOUNT*"PRICE($)" as amt_in

FROM flow.core.ez_swaps a JOIN rpiceTb b on a.TOKEN_IN_CONTRACT = b.TOKEN_CONTRACT and a.BLOCK_TIMESTAMP::date = b.p_date

)

, outflow as (

SELECT

BLOCK_TIMESTAMP,

tx_id,

TRADER,

SWAP_CONTRACT,

SYMBOL as SYMBOL1,

TOKEN_OUT_AMOUNT*"PRICE($)" as amt_out

FROM flow.core.ez_swaps a JOIN rpiceTb b on a.TOKEN_OUT_CONTRACT = b.TOKEN_CONTRACT and a.BLOCK_TIMESTAMP::date = b.p_date

)

date_trunc('{{Frequency}}', i.block_timestamp) as date,

SYMBOL0 || ' => ' || SYMBOL1 as swap_pair,

COUNT(DISTINCT i.tx_id) as swap_tx_cnt_from,