*,

sum(net_buy_volume) over (order by day) as cume_buy_volume

FROM (

select

date_trunc('day', block_timestamp) as day,

sum(CASE WHEN event_inputs['to'] LIKE ORIGIN_FROM_ADDRESS THEN price2 * event_inputs:value/pow(10,18) END) as buy_volume_usd,

sum(CASE WHEN event_inputs['from'] LIKE ORIGIN_FROM_ADDRESS THEN price2 * event_inputs:value/pow(10,18) END) as sell_volume_usd,

sum(CASE WHEN event_inputs['to'] LIKE ORIGIN_FROM_ADDRESS THEN price2 * event_inputs:value/pow(10,18) END) - sum(CASE WHEN event_inputs['from'] LIKE ORIGIN_FROM_ADDRESS THEN price2 * event_inputs:value/pow(10,18) END) as net_buy_volume

from avalanche.core.fact_event_logs

LEFT outer JOIN (

SELECT

date_trunc('day',RECORDED_HOUR) as day2,

avg(open) as price2

FROM crosschain.core.fact_hourly_prices

where id LIKE 'avalanche-2'

GROUP BY 1

) ON date_trunc('day', block_timestamp) = day2

where contract_address = lower('0xB31f66AA3C1e785363F0875A1B74E27b85FD66c7')

and event_name = 'Transfer'

and tx_hash in (

select tx_hash

from avalanche.core.fact_event_logs

where event_name = 'Swap' and block_timestamp::date >= '2023-01-22'

) --

GROUP BY 1