Elprognerd1 - average txn per day/week/month
    Updated 2023-06-04
    with txs AS (
    SELECT
    x.BLOCK_TIMESTAMP as BLOCK_TIMESTAMP,
    x.TX_ID as tx,
    x.PROPOSER as user,
    y.EVENT_TYPE as type,
    y.EVENT_CONTRACT
    FROM flow.core.fact_transactions x JOIN flow.core.fact_events y ON x.TX_ID = y.TX_ID
    WHERE x.TX_SUCCEEDED = 'true'
    )

    SELECT
    count( DISTINCT tx)/count( DISTINCT date_trunc('day', BLOCK_TIMESTAMP)) as "Average number of txns per a day",
    count( DISTINCT tx)/count( DISTINCT date_trunc('week', BLOCK_TIMESTAMP)) as "Average number of txns per a week",
    count( DISTINCT tx)/count( DISTINCT date_trunc('month', BLOCK_TIMESTAMP)) as "Average number of txns per a month",

    count( DISTINCT user)/count( DISTINCT date_trunc('day', BLOCK_TIMESTAMP)) as "Average number of active users per a day",
    count( DISTINCT user)/count( DISTINCT date_trunc('week', BLOCK_TIMESTAMP)) as "Average number of active users per a week",
    count( DISTINCT user)/count( DISTINCT date_trunc('month', BLOCK_TIMESTAMP)) as "Average number of active users per a month"
    from txs


    Run a query to Download Data