WITH all_blocks AS (
SELECT
block_timestamp::date AS date,
block_number,
count(tx_hash) AS number_of_tx
FROM
arbitrum.core.fact_transactions
WHERE
block_timestamp::date <= '2022-07-25'
GROUP BY date, block_number
)
SELECT
date,
count(block_number) AS number_of_blocks,
avg(number_of_tx) AS avg_number_of_tx
FROM
all_blocks
GROUP BY date
ORDER BY date ASC