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

-- block_number = 12097159 and

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

),

locks AS (

SELECT

tx_hash,

tokenID,

VALUE

FROM

base

WHERE

(

DEPOSIT_TYPE = '1'