select

tx_hash

,block_timestamp

,contract_address

,'0x' || SUBSTR(topics [1] :: STRING, 27, 42) AS provider

,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(topics [2] :: STRING ) :: DATE AS lock_date

,ethereum.public.udf_hex_to_int(segmented_data [0] :: STRING) AS tokenID

from

optimism.core.fact_event_logs

where

topics [0] :: STRING = '0xff04ccafc360e16b67d682d17bd9503c4c6b9a131f6be6325762dc9ffc7de624'

and block_number >= 12097159

and origin_function_signature = '0x65fc3873' and origin_to_address = '0x9c7305eb78a432ced5c4d14cac27e8ed569a2e26'

and tx_status = 'SUCCESS'

,lst_sum as (select

datediff('day',BLOCK_TIMESTAMP,lock_date) as diff_days

,date_trunc(day,block_timestamp)::date as date

,deposit_type

,count(distinct tx_hash) as transactions

,count(distinct provider) as providers

,sum(VALUE) as amount

from lst_all

group by 1,2,3)

select

case

when diff_days <3 then '1 To 3 days'

when diff_days >=3 and diff_days<5 then '1 To 5 days'

when diff_days >=5 and diff_days<7 then '6 To 7 days'

when diff_days >=7 and diff_days<15 then '7 To 14 days'

when diff_days >=15 and diff_days<100 then '15 To 100 days'