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 avalanche.core.fact_token_transfers

where 1 = 1

-- and tx_hash = '0xc3869010f5f9a85ccd3d252c7640f8c937fb5f787118c893687386245dd7d383'

and contract_address = lower('0xd39016475200ab8957e9c772c949ef54bda69111')

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,