purifCubquest - Badges per wallet
    Updated 2024-10-07
    with badge_minters as (
    select
    distinct minter as minter,
    count(badge_id) as minted_badges
    from
    (
    select
    decoded_log ['to'] as minter,
    decoded_log ['id'] as badge_id
    from
    berachain.testnet.fact_decoded_event_logs
    where
    CONTRACT_ADDRESS = lower('0x886D2176D899796cD1AfFA07Eff07B9b2B80f1be')
    and decoded_log ['from'] = lower('0x0000000000000000000000000000000000000000')
    )
    group by 1
    )

    select minted_badges, address_count,
    sum(address_count) over (order by minted_badges) as unique_users
    from (
    select minted_badges, count(distinct address) as address_count from (
    select minter as address, minted_badges
    from
    (select
    b.minter,
    minted_badges,
    from
    badge_minters b
    )
    )
    group by 1
    order by minted_badges desc
    )
    order by minted_badges desc
    QueryRunArchived: QueryRun has been archived