thejoyceprior-beige
    Updated 2024-09-01
    WITH daily_transactions AS (
    SELECT
    DATE_TRUNC('day', BLOCK_TIMESTAMP) AS time_,
    COUNT(DISTINCT TX_HASH) AS txns
    FROM
    base.core.fact_transactions
    WHERE
    BLOCK_TIMESTAMP BETWEEN '2024-08-01' AND '2024-08-31'
    GROUP BY
    1
    ORDER BY
    1 DESC
    )
    SELECT
    time_,
    txns,
    ROUND(AVG(txns) OVER (ORDER BY time_ ROWS BETWEEN 6 PRECEDING AND CURRENT ROW), 2) AS "7d_avg"
    FROM
    daily_transactions
    ORDER BY
    time_ DESC;