cloudr3nUntitled Query
    Updated 2022-11-26
    with avax_vault as (
    select block_timestamp, tx_hash, event_name,
    case when event_name='Deposit' then event_inputs:piMinted*pow(10,-18)
    when event_name='Withdraw' then event_inputs:piBurned*(-1)*pow(10,-18)
    end as amount,
    case when event_name='Deposit' then event_inputs:undelyingDeposited*pow(10,-18)
    when event_name='Withdraw' then event_inputs:underlyingWithdrawn*(-1)*pow(10,-18)
    end as receiptToken,
    origin_from_address as depositor, event_inputs
    from avalanche.core.fact_event_logs
    where contract_address=lower('0x7a6fc041274ff996e2761e02f4b3b4b0f16e955a') 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 avax_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 avax_vault
    where event_name='Withdraw'
    group by withdrawer
    order by withdraw_amt desc
    )


    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,
    Run a query to Download Data