select block_timestamp::date as date, from_address,

round(ethereum.public.udf_hex_to_int(substr(input_data,11,64)::string) / pow(10,18),4) as value,

round((ethereum.public.udf_hex_to_int(substr(input_data, 75, 128)) /(86400))) as days_locked,

case when days_locked between 7 and 9 then 'one week'

when days_locked between 29 and 32 then 'one month'

when days_locked between 364 and 367 then 'one year'

when days_locked between 1458 and 1462 then 'four years'

else 'other' end as lock_period

from optimism.core.fact_transactions

where to_address = '0x9c7305eb78a432ced5c4d14cac27e8ed569a2e26'

and origin_function_signature = '0x65fc3873'

order by 1

),

totals as (

select from_address,

sum(value) as total_locked

from base

group by 1

select count(*) as total_stakers,

sum(total_locked) as total_velo_locked,

avg(total_locked) as average_velo_locked

from totals