SELECT DAY, AVG(CALLS) AS AVG_CALL_PER_BLOCK FROM(
SELECT
DATE_TRUNC('DAY',BLOCK_TIMESTAMP) AS DAY,
BLOCK_ID,
COUNT(TX_ID) AS CALLS
FROM flipside_prod_db.algorand.application_call_transaction
WHERE BLOCK_TIMESTAMP BETWEEN '2021-06-01' AND '2022-06-30'
GROUP BY DAY, BLOCK_ID
ORDER BY 2 DESC
)
GROUP BY DAY
ORDER BY 1 DESC