eip_1559_tx_adoption.sql

WITH

aggregated as (
    SELECT
        TIMESTAMP_SECONDS(300 * DIV(UNIX_SECONDS(block_timestamp) + 150, 300)) AS dt,
        CASE
            WHEN transaction_type = 2 THEN 1.0
            ELSE 0.0
        END as pct_1559
    from `bigquery-public-data.crypto_ethereum.transactions`
    where block_timestamp >= timestamp_sub(current_timestamp(), INTERVAL 24 HOUR)
)

select
    dt,
    AVG(pct_1559) AS pct_1559
from aggregated
group by 1
order by 1 DESC