SocioCryptoAverage TPS in past 7 days
    Updated 2024-01-15
    -- forked from TPS @ https://flipsidecrypto.xyz/edit/queries/b1cb6c76-f437-4420-aae6-5e4b11c34292

    SELECT
    avg(TPS)
    FROM
    (
    SELECT
    date,
    num_of_txns,
    num_of_txns /DATEDIFF(second,min_block_timestamp,max_block_timestamp)as TPS,
    ((num_of_txns - LAG(num_of_txns)over(ORDER BY date))/LAG(num_of_txns)over(ORDER BY date))*100 as rate_of_growth_txns
    FROM (
    SELECT
    date_trunc('day', block_timestamp) as date,
    count(DISTINCT tx_id) as num_of_txns,
    min(block_timestamp) as min_block_timestamp,
    max(block_timestamp) as max_block_timestamp
    FROM sei.core.fact_transactions
    GROUP BY 1
    )
    )
    WHERE date between current_date - 7 and current_date -1

    QueryRunArchived: QueryRun has been archived