select

buyer_address

, sum(price_usd) as total_volume

from ethereum.nft.ez_nft_sales

where platform_name = 'blur'

and block_timestamp >= current_timestamp() - interval '1 year'

group by 1

order by total_volume desc

, get_cumulative_count as (

select

* , sum(total_volume) over(order by total_volume desc) as cumulative_count

from get_volume_per_user

, get_pct_of_cumulative_count as(

select

*, cumulative_count / max(cumulative_count) over() as pct_of_cumu_count

from get_cumulative_count

, pareto_label as(

select

*

, case when pct_of_cumu_count <= 0.8 then 'power' else 'normie' end as label

from get_pct_of_cumulative_count

select

*

, row_number() over(order by total_volume desc) as ranker -- for sanity check

from pareto_label

where total_volume is not null

order by total_volume desc