Afonso_Diazgrouping transactions whale
Updated 2024-09-05
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 t as (
select
tx_hash,
block_timestamp,
nvl(amount_in_usd, amount_out_usd) as amount_usd,
origin_from_address as swapper,
CASE
WHEN block_timestamp <= '2024-04-10' THEN '0.15 Fee'
ELSE '0.25 Fee'
END AS timespan
from ethereum.defi.ez_dex_swaps
where tx_hash IN (
SELECT DISTINCT tx_hash
FROM ethereum.core.ez_decoded_event_logs
WHERE origin_to_address in ('0x3fc91a3afd70395cd496c647d5a6cc9d4b2b7fad', '0xef1c6e67703c7bd7107eed8303fbe6ec2554bf6b')
)
and block_timestamp >= '2023-10-27'
and amount_usd <= 1e6
),
users_type as (
select
origin_from_address as swapper,
sum(nvl(amount_in_usd, amount_out_usd)) as volume_usd,
iff(ntile(15) over (order by volume_usd desc) = 1, 'Whale', 'Small User') as swapper_type
from ethereum.defi.ez_dex_swaps
where nvl(amount_in_usd, amount_out_usd) <= 1e6
and tx_hash IN (
SELECT DISTINCT tx_hash
FROM ethereum.core.ez_decoded_event_logs
WHERE origin_to_address in ('0x3fc91a3afd70395cd496c647d5a6cc9d4b2b7fad', '0xef1c6e67703c7bd7107eed8303fbe6ec2554bf6b')
)
group by 1
),
t2 as (
QueryRunArchived: QueryRun has been archived