flipside

    bitcoin 7
    cristinatintobitcoin 7
    -- Calculate average number of transactions per block and miner rewards per month
    WITH transactions_per_block AS (
    SELECT
    DATE_TRUNC('MONTH', BLOCK_TIMESTAMP) AS MONTH,
    BLOCK_NUMBER,
    avg(tx_count) AS NUM_TRANSACTIONS
    FROM bitcoin.core.fact_blocks
    GROUP BY MONTH, BLOCK_NUMBER
    ),
    miner_rewards AS (
    SELECT
    DATE_TRUNC('MONTH', BLOCK_TIMESTAMP) AS MONTH,
    BLOCK_NUMBER,
    AVG(TOTAL_REWARD) AS AVERAGE_REWARD
    FROM bitcoin.core.ez_miner_rewards
    GROUP BY MONTH, BLOCK_NUMBER
    )
    SELECT
    t.MONTH,
    AVG(NUM_TRANSACTIONS) AS AVERAGE_TRANSACTIONS_PER_BLOCK,
    AVG(AVERAGE_REWARD) AS AVERAGE_MINER_REWARD
    FROM transactions_per_block t
    JOIN miner_rewards m
    ON t.MONTH = m.MONTH AND t.BLOCK_NUMBER = m.BLOCK_NUMBER
    GROUP BY t.MONTH
    ORDER BY t.MONTH
    Run a query to Download Data