crypto_gostwhale count
    Updated 2024-11-23
    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