WITH main as (
SELECT
DATE_TRUNC ('day' ,BLOCK_TIMESTAMP ) as daily,
count(DISTINCT tx_hash) as daily_transaction
FROM polygon.core.fact_transactions
WHERE BLOCK_TIMESTAMP::DATE >= '2022-07-01'
GROUP BY daily),
avg as (
SELECT
SUM(daily_transaction) / 14 as avg
FROM
main
)
SELECT
daily,
daily_transaction,
avg
FROM
main , avg