SELECT

date_trunc('day', block_timestamp) as date1,

sum(SWAP_TO_AMOUNT) * avg(CLOSE) as Daily_Volume,

avg(CLOSE) as WETH_Price,

lag(avg(CLOSE)) over (order by date_trunc('day', block_timestamp)) as prev_price

FROM solana.core.fact_swaps

LEFT OUTER JOIN solana.core.fact_token_prices_hourly

ON date_trunc('hour', block_timestamp) = RECORDED_HOUR

where symbol like 'SOL'

and SWAP_FROM_AMOUNT <1e7

and SWAP_TO_AMOUNT <1e7

AND ( SWAP_FROM_MINT like 'So11111111111111111111111111111111111111112'

OR SWAP_TO_MINT like 'So11111111111111111111111111111111111111112' )

GROUP BY 1

SELECT

date1,

Daily_Volume,

WETH_Price,

SUM(CASE WHEN WETH_Price > prev_price THEN Daily_Volume ELSE 0 END) OVER (ORDER BY date1) -

SUM(CASE WHEN WETH_Price < prev_price THEN Daily_Volume ELSE 0 END) OVER (ORDER BY date1) AS OBV

FROM data

WHERE date1 > current_date - 90