flipside

    Faucet Rate-Limit Bugs Counter
    aPrioriFaucet Rate-Limit Bugs Counter
    Updated 2025-03-05
    -- forked from Faucet Rate-Limit Bugs Address @ https://flipsidecrypto.xyz/studio/queries/1978f86d-7c30-4053-9798-7c2392c01fb0

    WITH address_transactions AS (
    -- 获取所有符合条件的交易
    SELECT
    TO_ADDRESS,
    TX_HASH,
    BLOCK_TIMESTAMP,
    -- 按地址和时间排序
    ROW_NUMBER() OVER (PARTITION BY TO_ADDRESS ORDER BY BLOCK_TIMESTAMP) AS tx_seq
    FROM monad.testnet.fact_transactions
    WHERE FROM_ADDRESS = LOWER('0xD7a24d1F1435CD314E86736E139f8431D4498D4e')
    AND TX_SUCCEEDED
    AND BLOCK_TIMESTAMP >= '2025-02-19 14:00:00'
    ),
    transaction_pairs AS (
    -- 计算每个地址相邻交易之间的时间间隔，并获取前一笔交易的信息
    SELECT
    a.TO_ADDRESS,
    a.TX_HASH AS current_tx_hash,
    a.BLOCK_TIMESTAMP AS current_tx_time,
    a.tx_seq AS current_tx_seq,
    LAG(a.TX_HASH) OVER (PARTITION BY a.TO_ADDRESS ORDER BY a.BLOCK_TIMESTAMP) AS previous_tx_hash,
    LAG(a.BLOCK_TIMESTAMP) OVER (PARTITION BY a.TO_ADDRESS ORDER BY a.BLOCK_TIMESTAMP) AS previous_tx_time,
    LAG(a.tx_seq) OVER (PARTITION BY a.TO_ADDRESS ORDER BY a.BLOCK_TIMESTAMP) AS previous_tx_seq,
    -- 计算与前一笔交易的时间差
    DATEDIFF(SECOND, LAG(a.BLOCK_TIMESTAMP) OVER (PARTITION BY a.TO_ADDRESS ORDER BY a.BLOCK_TIMESTAMP), a.BLOCK_TIMESTAMP) AS time_gap_seconds,
    DATEDIFF(HOUR, LAG(a.BLOCK_TIMESTAMP) OVER (PARTITION BY a.TO_ADDRESS ORDER BY a.BLOCK_TIMESTAMP), a.BLOCK_TIMESTAMP) AS time_gap_hours,
    DATEDIFF(DAY, LAG(a.BLOCK_TIMESTAMP) OVER (PARTITION BY a.TO_ADDRESS ORDER BY a.BLOCK_TIMESTAMP), a.BLOCK_TIMESTAMP) AS time_gap_days
    FROM
    address_transactions a
    )

    select
    count(distinct current_tx_hash) as Rate_Limit_Bugs_tx_count
    ,count(distinct wallet) as Rate_Limit_Bugs_address_count
    Last run: 16 days ago
    RATE_LIMIT_BUGS_TX_COUNT
    RATE_LIMIT_BUGS_ADDRESS_COUNT
    LAST_BUG_TX
    FIRST_BUG_TX
    1
    		506721612025-03-04 06:22:40.0002025-02-19 15:53:27.000
    1
    65B
    3s