with usdc_vault as ( select block_timestamp, tx_hash, event_name, case when event_name='Deposit' then event_inputs:piMinted*pow(10,-6) when event_name='Withdraw' then event_inputs:piBurned*(-1)*pow(10,-6) end as amount, case when event_name='Deposit' then event_inputs:undelyingDeposited*pow(10,-6) when event_name='Withdraw' then event_inputs:underlyingWithdrawn*(-1)*pow(10,-6) end as receiptToken, origin_from_address as depositor, event_inputs from avalanche.core.fact_event_logs where contract_address=lower('0x49d743e645c90ef4c6d5134533c1e62d08867b14') and event_name in ('Deposit', 'Withdraw') -- and tx_hash='0x33c4a6cb1f8965d891066551ea55e6762fe643ecc8c758a6ef92115148aab224' order by block_timestamp desc ), deposits as ( select min(block_timestamp) as first_deposit, max(block_timestamp) as last_deposit, count(distinct tx_hash) as deposit_count, sum(amount) as deposited_amt, depositor from usdc_vault where event_name='Deposit' group by depositor order by deposited_amt desc ), withdrawals as ( select min(block_timestamp) as first_withdraw, max(block_timestamp) as last_withdraw, count(distinct tx_hash) as withdraw_count, sum(amount) as withdraw_amt, depositor as withdrawer from usdc_vault where event_name='Withdraw' group by withdrawer order by withdraw_amt desc ) select count(distinct address) as address_count, round(date_diff/30) as months, sum(address_count) over (partition by deposit_group order by months desc rows between unbounded preceding and current row) as cumulative_user, deposit_group from ( select depositor as address, first_deposit, last_deposit, deposit_count, deposited_amt, case when first_withdraw is null then CURRENT_DATE else first_withdraw end as first_withdraw1, case when last_withdraw is null then CURRENT_DATE else last_withdraw end as last_withdraw1, case when withdraw_count is null then 0 else withdraw_count end as withdraw_count1, case when withdraw_amt is null then 0 else withdraw_amt end as withdraw_amt1, case when deposited_amt>1000000 then '>1,000,000' when deposited_amt>100000 and deposited_amt<=1000000 then '>100,000' when deposited_amt>10000 and deposited_amt<=100000 then '>10,000' when deposited_amt>1000 and deposited_amt<=10000 then '>1,000' when deposited_amt>100 and deposited_amt<=1000 then '>100' else '<=100' end as deposit_group, datediff(day,first_deposit,last_withdraw1) as date_diff, deposit_count-withdraw_count1, deposited_amt-withdraw_amt1 from withdrawals w1 full join deposits d1 on w1.withdrawer=d1.depositor where depositor is not null ) group by deposit_group, months /* select count(distinct address) as address_count, round(date_diff/30) as months, sum(address_count) over (order by months desc rows between unbounded preceding and current row) from ( select depositor as address, first_deposit, last_deposit, deposit_count, deposited_amt, case when first_withdraw is null then CURRENT_DATE else first_withdraw end as first_withdraw1, case when last_withdraw is null then CURRENT_DATE else last_withdraw end as last_withdraw1, case when withdraw_count is null then 0 else withdraw_count end as withdraw_count1, case when withdraw_amt is null then 0 else withdraw_amt end as withdraw_amt1, case when deposited_amt>1000000 then '>1,000,000' when deposited_amt>100000 and deposited_amt<=1000000 then '>100,000' when deposited_amt>10000 and deposited_amt<=100000 then '>10,000' when deposited_amt>1000 and deposited_amt<=10000 then '>1,000' when deposited_amt>100 and deposited_amt<=1000 then '>100' else '<=100' end as deposit_group, datediff(day,first_deposit,last_withdraw1) as date_diff, deposit_count-withdraw_count1, deposited_amt-withdraw_amt1 from withdrawals w1 full join deposits d1 on w1.withdrawer=d1.depositor where depositor is not null ) group by months */

RoboVault (Avalanche) - Deposits & Withdrawals