h4wkAvalanche deUSDC Pool Activity
Updated 2024-02-14
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
›
⌄
-- forked from Avalanche deUSDC Pool Activity @ https://flipsidecrypto.xyz/edit/queries/c0897d17-9e32-4b29-9a16-507cc9fe9c04
-- forked from Arbitrum deUSDC Pool Activity @ https://flipsidecrypto.xyz/edit/queries/725db840-091c-4132-89b4-063b07e5a828
-- forked from arb stable pool @ https://flipsidecrypto.xyz/edit/queries/353c1e89-db1d-443d-9635-0e6f0dc08b93
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')
-- group by 1,2
-- order by date desc
)
, base as (
select block_timestamp,
'avalanche_deusdc' as pool,
type as action,
-- token,
token_amount,
origin_from_address as user,
tx_hash
from base_raw