SELECT

date_trunc({{period_type}}, block_timestamp) AS "Date",

AVG(GAS_USED / NULLIF(GAS_LIMIT, 0)) * 100 AS "Average Block Utilization (%)"

FROM

berachain.testnet.fact_blocks

WHERE

BLOCK_TIMESTAMP::date >= '{{start_day}}'

AND date_trunc({{period_type}}, BLOCK_TIMESTAMP) <= '{{target_day}}'

GROUP BY 1

SELECT

"Date",

AVG("Average Block Utilization (%)") AS "Average Block Utilization (%)",

LAG(AVG("Average Block Utilization (%)")) OVER (ORDER BY "Date") AS lag_utilization,

ROUND(100 * (AVG("Average Block Utilization (%)") - lag_utilization) / NULLIF(lag_utilization, 0), 2) AS "Block Utilization Change %"

FROM block_data

GROUP BY 1

ORDER BY 1 DESC