theericstonepaxos tagged actions: ethereum
Updated 2023-12-12
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
›
⌄
⌄
-- get all USDP transfers from the last 30 days on ethereum
-- summarize by label, example tags
-- incorporate swaps, deposits/withdrawals to LPs, and liquid staking as transfer subtypes
/*
select lt.label, count(1) FROM
ethereum.core.ez_token_transfers etx
join ethereum.core.dim_labels lt
on lt.address = etx.to_address
where etx.block_timestamp > current_date - 5
and etx.contract_address = lower('0x8E870D67F660D95d5be530380D0eC0bd388289E1')
group by 1;
*/
with whales as (select
distinct address
from crosschain.core.dim_tags tags
where tag_name = 'wallet millionaire'
and creator = 'flipside'
and tags.end_date IS NOT NULL
)
SELECT
block_timestamp::date as date,
from_address, to_address,
case when lt.label_subtype = 'deposit_wallet' then 'cex deposit'
when (lf.label_subtype = 'hot wallet' and lt.label_type <> 'cex') then 'cex_withdrawal'
when tx_hash in (select tx_hash from ethereum.defi.ez_dex_swaps) then 'swap'
when to_address in (select pool_address from ethereum.defi.dim_dex_liquidity_pools) then 'dex deposit'
when from_address in (select pool_address from ethereum.defi.dim_dex_liquidity_pools) then 'dex withdrawal'
when tx_hash in (select tx_hash from ethereum.defi.ez_liquid_staking_deposits) then 'liquid staking deposit'
when tx_hash in (select tx_hash from ethereum.defi.ez_liquid_staking_withdrawals) then 'liquid staking withdrawal'
else 'transfer' end as transfer_type,
case when lt.label in ('1inch','binance','makerdao','curve','curve fi','thorchain','aave') then lt.label
when etx.to_address in (select address from whales) then 'eth whale'
else coalesce(lt.label_type,'unlabeled user') end as destination_label,
case when lf.label in ('1inch','binance','makerdao','curve','curve fi','thorchain','aave') then lf.label