date_trunc('day', block_timestamp) as day,

unlock_date,

datediff(day, day::date, unlock_date::date) as lock_time,

case

when lock_time > 1460 then 'over 4 years'

when lock_time > 1095 and lock_time <= 1460 then 'between 3 and 4 years'

when lock_time > 730 and lock_time <= 1095 then 'between 2 and 3 years'

when lock_time > 365 and lock_time <= 730 then 'between 1 and 2 years'

when lock_time > 180 and lock_time <= 365 then 'between 6 months and 1 year'

when lock_time > 30 and lock_time <= 180 then 'between 1 and 6 months'

when lock_time > 7 and lock_time <= 30 then 'between 1 week and 1 month'

when lock_time <= 7 then '1 week or less'

end as period,

velo_amount,

velo_amount_usd

from optimism.velodrome.ez_velo_locks

where velo_action = 'deposit')

select

date(day) as date, period, sum(velo_amount_usd) as total_locked

from data

group by day, period