cloudr3nUntitled Query
Updated 2022-11-26Copy Reference Fork
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
›
⌄
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