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 [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'

to_char(date_trunc(month, locked_date), 'YYYY-MM') AS "Locked Date",

COUNT(DISTINCT TX_HASH) AS "Number of Transactions",

COUNT(DISTINCT provider) AS "Number of Providers",

SUM(VALUE) AS VALUE,

deposit_type

FROM base

GROUP BY 1, 5