SELECT

*,

CONCAT('0x', SUBSTR(topics [1] :: STRING, 27, 42)) AS provider,

ethereum.public.udf_hex_to_int(

topics [2] :: STRING

) :: DATE AS locked_date,

regexp_substr_all(SUBSTR(DATA, 3, len(DATA)), '.{64}') AS segmented_data,

ethereum.public.udf_hex_to_int(

segmented_data [0] :: STRING

) AS tokenID,

ethereum.public.udf_hex_to_int(

segmented_data [1] :: STRING

)/pow(10,18) AS VALUE,

ethereum.public.udf_hex_to_int(

segmented_data [2] :: STRING

) AS deposit_type,

ethereum.public.udf_hex_to_int(

segmented_data [3] :: STRING

) AS TIMESTAMP

FROM

optimism.core.fact_event_logs

WHERE

topics [0] :: STRING = '0xff04ccafc360e16b67d682d17bd9503c4c6b9a131f6be6325762dc9ffc7de624'

),

duration as (select tx_hash, block_timestamp, provider, deposit_type,

locked_date,

datediff(day, block_timestamp, locked_date) as duration

from base where origin_function_signature = '0x65fc3873' and origin_to_address = '0x9c7305eb78a432ced5c4d14cac27e8ed569a2e26'

and tx_status = 'SUCCESS'

)

select duration || ' Days' as duration, count(distinct provider) as common_provider

from duration group by duration order by common_provider desc