maybeyonasvelo_lock_top_lockers
    Updated 2022-08-18
    with
    velo_lock as (
    select
    block_timestamp,
    tx_hash,
    from_address as locker,
    ethereum.public.udf_hex_to_int(substr(input_data,11,64))/pow(10,18) as amount,
    ethereum.public.udf_hex_to_int(substr(input_data,75)) as duration,
    case
    when duration < 704800 then '1 week'
    when duration < 2729743 then '1 month'
    when duration < 31656926 then '1 year'
    when duration < 126327704 then '4 years'
    else 'sus' end as time_lock,
    case time_lock
    when '1 week' then amount/(4*52)
    when '1 month' then amount/(4*12)
    when '1 year' then amount/(4)
    when '4 years' then amount
    end as ve_velo
    from optimism.core.fact_transactions
    where to_address = '0x9c7305eb78a432ced5c4d14cac27e8ed569a2e26'
    and origin_function_signature = '0x65fc3873'
    )

    select
    locker,
    sum(amount) as velo_locked,
    sum(ve_velo) as approx_ve_velo
    -- count(distinct locker) as users
    from velo_lock
    group by 1
    order by approx_ve_velo desc
    -- where tx_hash = '0xe09a6ece9314d888aa91fb51ef520d3093798a59266b413fe6ac063fab7662c7'

    Run a query to Download Data