Hossein2024-07-15 01:43 PM
Updated 2024-07-15
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 ethereum as (
select
block_timestamp::Date AS date,tx_hash,
amount_in_usd AS amountUSD,
origin_from_address as user
from ethereum.defi.ez_dex_swaps
where amount_in_usd<power(10,6)
and tx_hash in (select distinct(tx_hash) from ethereum.core.ez_decoded_event_logs where origin_to_address in ('0xef1c6e67703c7bd7107eed8303fbe6ec2554bf6b', '0x3fc91a3afd70395cd496c647d5a6cc9d4b2b7fad'))
and platform = 'uniswap-v3'
)
,users_type as (
select
user,
sum(amountUSD) as totalAmount,
CASE
when totalAmount < 100 then 'Plankton'
when totalAmount < 1000 then 'Shrimp'
when totalAmount < 5000 then 'Crab'
when totalAmount < 10000 then 'Octopus'
when totalAmount < 50000 then 'Fish'
when totalAmount < 100000 then 'Dolphin'
when totalAmount < 500000 then 'Shark'
when totalAmount < 1000000 then 'Whale'
when totalAmount < 10000000 then 'Humpback Whale'
else 'Leviathan'
END as userType
from ethereum
group by user
),
ethereum2 as (
select * from ethereum join users_type on ethereum.user = users_type.user
where date>='2023-01-01'
)
select
QueryRunArchived: QueryRun has been archived