crypto_gostwhale count
Updated 2024-11-23
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 buyers as (
select
platform_name,
buyer_address,
sum(TOTAL_PRICE_USD) as buys_usd
from aptos.nft.ez_nft_sales
WHERE YEAR(BLOCK_TIMESTAMP) = 2024
and platform_name in ('Topaz', 'Wapal', 'Mercato','Okx','BlueMove')
group by platform_name, buyer_address
),
cutoff as (
select
platform_name,
percentile_cont(0.95) within group (order by buys_usd asc) as whale_cutoff
from buyers
group by platform_name
),
whale_buyers as (
select
b.platform_name,
b.buyer_address,
b.buys_usd
from buyers b
inner join cutoff c
on b.platform_name = c.platform_name
and b.buys_usd >= c.whale_cutoff
),
loyal_whales as (
select
buyer_address,
count(distinct platform_name) as platforms_transacted
from whale_buyers
group by buyer_address
having platforms_transacted = 1
),
total_whales_by_platform as (
QueryRunArchived: QueryRun has been archived