SELECT

TX_HASH AS hash1,

substring(TO_ADDRESS, 3, 40) as argv1,

substring(INPUT, 3, LENGTH(INPUT) - 2) as argv2,

FROM

bsc.core.fact_traces

WHERE

TX_STATUS = 'SUCCESS' and

TYPE = 'CALL' and

TRACE_STATUS = 'SUCCESS' and

block_timestamp > current_date - interval '30 days'

limit 100000

),

T2 AS (

SELECT

t2.TX_HASH as hash,

t2.FROM_ADDRESS as who,

t2.TO_ADDRESS as target,

t2.block_timestamp as time,

COUNT(*) OVER (PARTITION BY target) AS tx_count,

ROW_NUMBER() OVER (PARTITION BY target ORDER BY time) AS rn

FROM

bsc.core.fact_transactions t2 ,T1 t1

WHERE

t1.hash1 = t2.TX_HASH and

t2.INPUT_DATA LIKE CONCAT('%', t1.argv1, '%') and

t2.INPUT_DATA LIKE CONCAT('%', t1.argv2, '%')

-- block_timestamp > current_date - interval '30 days'

QUALIFY

rn=1

SELECT

target,

tx_count,

hash