aPrioriFaucet Rate-Limit Bugs Trend
    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
    date(current_tx_time) as stat_date
    ,count(distinct current_tx_hash) as Rate_Limit_Bugs_tx_count
    Last run: 14 days ago
    STAT_DATE
    RATE_LIMIT_BUGS_TX_COUNT
    RATE_LIMIT_BUGS_WALLET_COUNT
    1
    2025-02-19 00:00:00.000245229
    2
    2025-02-20 00:00:00.00022
    3
    2025-02-26 00:00:00.00021
    4
    2025-02-27 00:00:00.000204174
    5
    2025-02-28 00:00:00.00021871385
    6
    2025-03-01 00:00:00.00021831175
    7
    2025-03-02 00:00:00.0009788
    8
    2025-03-03 00:00:00.000145110
    9
    2025-03-04 00:00:00.00022
    9
    315B
    8s