select *

from ethereum.core.ez_nft_sales

where block_timestamp::date >= '2022-06-01' and platform_name in('opensea', 'rarible', 'looksrare', 'x2y2')

),

percentage as (

select

project_name,

sum(creator_fee_usd) as total_royalty,

count(distinct tx_hash) as total_sales,

sum(price_usd) as total_volume,

avg(creator_fee_usd) as avg_royalty,

avg(price_usd) as avg_nft_price,

(avg_royalty / avg_nft_price) * 100 as "% royalty fee"

from ethereum.core.ez_nft_sales

where creator_fee_usd > 0 and project_name is not null and project_name != 'opensea'

group by 1

order by 2 desc

select case

when "% royalty fee" <= 2.5 then 'a. 0-2.5%'

when "% royalty fee" <= 5 then 'b. 2.5-5%'

when "% royalty fee" <= 7.5 then 'c. 5-7.5%'

when "% royalty fee" <= 10 then 'd. 7.5-10%'

when "% royalty fee" <= 12.5 then 'e. 10-12.5%'

when "% royalty fee" <= 15 then 'f. 12.5-15%'

else 'g. > 15%' end as dis, count(*) as "count"

from percentage

group by 1

order by 2 desc