Faucet Rate-Limit Bugs Trend

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

315B