select

BLOCK_TIMESTAMP, DECODED_LOG['primaryAccount'] as account,

case

when EVENT_NAME = 'LogIncreasePosition' then abs(DECODED_LOG['increasedSize'])

when EVENT_NAME = 'LogDecreasePosition' then abs(DECODED_LOG['decreasedSize'])

end as vol

from blast.core.fact_decoded_event_logs

where CONTRACT_ADDRESS = lower('0x0a8D9c0A4a039dDe3Cb825fF4c2f063f8B54313A')

and EVENT_NAME in ('LogIncreasePosition', 'LogDecreasePosition')

),

liquidate as (

select

BLOCK_TIMESTAMP, DECODED_LOG['account'] as account,

abs(DECODED_LOG['size']) as vol

from blast.core.fact_decoded_event_logs

where CONTRACT_ADDRESS = lower('0x963Cbe4cFcDC58795869be74b80A328b022DE00C')

and EVENT_NAME = 'LogLiquidationPosition'

select time, total_volume as "Daily trading volume",

sum(total_volume) over (order by time) * 1.0 as "Cummulative trading volume",

sum(total_volume) over (order by time) * 1.0 / 1e9 as Cumulative_Volume_B,

AVG(total_volume) OVER (ORDER BY time ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS avg_7_days,

AVG(total_volume) OVER (ORDER BY time ROWS BETWEEN 13 PRECEDING AND CURRENT ROW) AS avg_14_days,

AVG(total_volume) OVER (ORDER BY time ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) AS avg_30_days

from (

select date_trunc('day', block_timestamp) as time, sum(vol) / 1e30 as total_volume

from (

select * from trades

union all

select * from liquidate

)

group by date_trunc('day', block_timestamp)

order by time desc