RamaharVelodrome Locks
    Updated 2023-07-15
    WITH base AS (
    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,
    DATEDIFF(month, block_timestamp, locked_date) as locking_period
    FROM optimism.core.fact_event_logs
    WHERE --block_number = 12097159 and
    topics [0] :: STRING = '0xff04ccafc360e16b67d682d17bd9503c4c6b9a131f6be6325762dc9ffc7de624'
    --AND tx_hash = '0xca7013b9f06996a8fc81d899c0730351ea70b2dd93c6b9016d7354b479c2152a'
    )
    SELECT
    locking_period,
    count(distinct tx_hash) as NoOfDeposits,
    count(distinct provider) as depositors,
    sum(value) as total_velo_deposited
    FROM
    base
    group by 1

    Run a query to Download Data