-- 获取所有符合条件的交易

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

TO_ADDRESS as wallet,

current_tx_hash,

current_tx_time,

-- current_tx_seq,