efer1. Daily Average Transactions
    Updated 2023-04-13
    WITH transactions AS (
    SELECT
    BLOCK_TIMESTAMP::date AS date,
    COUNT(*) AS txns
    FROM flow.core.fact_transactions
    WHERE date >= '2022-01-01'
    GROUP BY date
    ORDER BY date DESC
    ), total_average AS (
    SELECT avg(txns) AS average FROM transactions
    )

    SELECT
    date,
    txns,
    avg(txns) OVER (ORDER BY date ROWS BETWEEN 10 PRECEDING AND CURRENT ROW) AS MA10,
    SUM(txns) OVER(ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative,
    (SELECT average FROM total_average) AS average,
    CASE
    WHEN txns >= average THEN 'Above Average'
    ELSE 'Below Average'
    END AS type
    FROM transactions
    ORDER BY date DESC

    Run a query to Download Data