alirsop-distribution2-16dao
    Updated 2022-12-24
    with main as(select DATE_TRUNC('Day',BLOCK_TIMESTAMP) as Date,PLATFORM_NAME,buyer_address--,nvl(PROJECT_NAME,NFT_ADDRESS)as PROJECT_NAME,NFT_ADDRESS,EVENT_TYPE,
    ,sum(CREATOR_FEE_USD) as Royalty,sum(PRICE_USD ) as Volume, --,royalty / PRICE * 100 as Royality_Percent
    row_number() over (order by royalty DESC) as Rank,
    count(distinct tx_hash) as sales_count,
    count (distinct seller_address) as unique_Seller_count
    from ethereum.core.ez_nft_sales
    where PLATFORM_NAME='opensea' and date(BLOCK_TIMESTAMP)>='2022-10-19' and date(BLOCK_TIMESTAMP)<CURRENT_DATE
    group by 1,2,3
    order by Rank)
    select date,
    case when Royalty < 5 then 'a. Less Than $5'
    when Royalty>= 5 and Royalty < 10 then 'b. Between $5 and $10'
    when Royalty >= 10 and Royalty < 25 then 'c. Between $10 and $25'
    when Royalty >= 25 and Royalty < 50 then 'd. Between $25 and $50'
    when Royalty >= 50 and Royalty < 100 then 'e. Between $50 and $100'
    else 'f. More Than $100' end as distribution,
    count (distinct buyer_address) as Buyers
    from main
    group by 1,2
    order by 2 desc
    Run a query to Download Data