WITH lock AS (
SELECT *,
CONCAT('0x', SUBSTR(topics [1] :: STRING, 27, 42)) AS locker,
ethereum.public.udf_hex_to_int(topics [2] :: STRING) :: DATE AS lock_date,
regexp_substr_all(SUBSTR(DATA, 3, len(DATA)), '.{64}') AS segmented,
ethereum.public.udf_hex_to_int(segmented [1] :: STRING)/pow(10,18) AS velo_amount
FROM
optimism.core.fact_event_logs
WHERE origin_to_address = '0x9c7305eb78a432ced5c4d14cac27e8ed569a2e26'
and origin_function_signature = '0x65fc3873'
and topics [0] :: STRING = '0xff04ccafc360e16b67d682d17bd9503c4c6b9a131f6be6325762dc9ffc7de624'
),
main1 as (
select
block_timestamp::date as date,
tx_hash,
locker,
timediff(month,block_timestamp::date,lock_date) as locktime,
velo_amount
from lock
where block_timestamp::date>=CURRENT_DATE()-28
)
select
distinct locker,
count(tx_hash)
from main1
group by 1
order by 2 DESC
limit 20