select

date_trunc('day', 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,

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

WHERE BLOCK_TIMESTAMP::date >= CURRENT_DATE - INTERVAL '{{Past_Weeks}} WEEK'

)

, outflow as (

SELECT

BLOCK_TIMESTAMP,

tx_id,

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

WHERE BLOCK_TIMESTAMP::date >= CURRENT_DATE - INTERVAL '{{Past_Weeks}} WEEK'

)

SELECT

SYMBOL0 || '/' || SYMBOL1 as pair,

sum(amt_in - amt_out) as volume

FROM inflow i join outflow o on i.tx_id = o.tx_id and i.BLOCK_TIMESTAMP = o.BLOCK_TIMESTAMP and i.SYMBOL0 != o.SYMBOL1

GROUP by 1