select

date_trunc('day', hour) as d,

avg(price) as avg_price

from crosschain.price.ez_prices_hourly

where symbol = 'OLAS'

and blockchain = 'ethereum'

group by d

),

transactions_with_prices as (

select

date_trunc('week', block_timestamp) as date,

count(distinct t.tx_hash) as transaction_count,

count(distinct t.origin_from_address) as unique_user,

sum(t.olas_amount * p.avg_price) as volume_in_usd

from crosschain.olas.ez_olas_locking t

join hourly_prices p on block_timestamp::date = p.d

group by date

select

date,

transaction_count,

unique_user,

volume_in_usd,

AVG(transaction_count) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS MA_7_transactions,

AVG(transaction_count) OVER (ORDER BY date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) AS MA_30_transactions,

AVG(volume_in_usd) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS MA_7_volume,

AVG(volume_in_usd) OVER (ORDER BY date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) AS MA_30_volume,

SUM(transaction_count) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cum_tx_count,

SUM(volume_in_usd) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cum_tx_volume

from transactions_with_prices

order by date;