SELECT

TX_HASH,

SUM(CREATOR_FEE_USD) as Royalty,

SUM(PRICE_USD) as volume,

DIV0(Royalty , Volume) * 100 as percent_ro

FROM ethereum.core.ez_nft_sales

WHERE PLATFORM_NAME = 'opensea'

AND block_timestamp::DATE >= '2022-10-20'

and project_name is not null

and price_usd > 0

GROUP BY 1

)

select

WHEN percent_ro = 0 THEN '0% Royalty'

WHEN percent_ro BETWEEN 0 AND 0.5 THEN '0%-0.5% Royalty'

WHEN percent_ro BETWEEN 0.5 AND 1 THEN '0.5%-1% Royalty'

WHEN percent_ro BETWEEN 1 AND 2 THEN '1%-2% Royalty'

WHEN percent_ro BETWEEN 2 AND 5 THEN '2%-5% Royalty'

WHEN percent_ro >=5 THEN 'more than 5% Royalty'

END as status,

count (distinct TX_HASH) as sale_count

from base

group by 1