SELECT

date_trunc('day', block_timestamp) as date,

sum(gas_used) as gas_used,

count(*) as num_tx

FROM terra.transactions

WHERE block_timestamp > CURRENT_DATE - 90

AND block_timestamp < CURRENT_DATE

AND tx_status = 'SUCCEEDED'

GROUP BY 1

SELECT

*,

avg(gas_used) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) as weekly_ma_tot_gas,

gas_used / num_tx as gas_per_tx,

avg(gas_per_tx) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) as weekly_ma_tx_gas

FROM gas