with base_raw as (

select block_timestamp,

case when from_address = '0x0000000000000000000000000000000000000000' then 'Deposit'

else 'Withdrawal' end as type,

raw_amount_precise::float/1e18 as token_amount,

origin_from_address,

tx_hash

-- count(distinct tx_hash) as tx_count,

-- count(distinct origin_from_address) as user_count,

-- sum(case when from_address = '0x0000000000000000000000000000000000000000' then raw_amount_precise::float/1e18 else 0 end) as deposit_vol,

-- sum(case when to_address = '0x0000000000000000000000000000000000000000' then -raw_amount_precise::float/1e18 else 0 end) as withdrawal_vol,

-- deposit_vol + withdrawal_vol as net_vol,

-- sum(net_vol) over (order by date) as TVL

from arbitrum.core.fact_token_transfers

where 1 = 1

-- and tx_hash = '0xc3869010f5f9a85ccd3d252c7640f8c937fb5f787118c893687386245dd7d383'

and contract_address = '0x76b44e0cf9bd024dbed09e1785df295d59770138'

and (from_address = '0x0000000000000000000000000000000000000000'

or to_address = '0x0000000000000000000000000000000000000000')

, base as (

select origin_from_address as user,

min(case when type = 'Deposit' then block_timestamp end) as first_tx,

min(case when type = 'Withdrawal' then block_timestamp end) as last_tx,

datediff(day, first_tx, last_tx) as date_diff,

case when date_diff is null then 'g.Still Providing'

when date_diff <= 1 then 'a.Held < 1 Day'

when date_diff <= 7 then 'b.Held < 7 Days'

when date_diff <= 30 then 'c.Held < 30 Days'

when date_diff <= 90 then 'd.Held < 90 Days'

when date_diff <= 365 then 'e.Held < 365 Days'