b.PROJECT_NAME as NFT_collection,

count(TX_ID) as sale_no,

count(distinct PURCHASER) as buyer_no,

sum(SALES_AMOUNT) as volume,

avg(SALES_AMOUNT) as average_volume,

sale_no/buyer_no as average_buyer,

buyer_no/count(distinct date_trunc(day, block_timestamp)) as average_buyer_day,

volume/count(distinct date_trunc(day, block_timestamp)) as average_volume_day,

row_number() over (order by volume desc) as rank

from solana.core.fact_nft_sales s left outer join solana.core.dim_nft_metadata b on s.mint=b.mint

where SUCCEEDED='TRUE'

and BLOCK_TIMESTAMP>=CURRENT_DATE- 90

group by 1 having NFT_collection is not null

order by 4 desc

limit 20),

tb2 as (select

PROJECT_NAME as NFT1,*

from solana.core.fact_nft_sales s left outer join solana.core.dim_nft_metadata b on s.mint=b.mint

where b.PROJECT_NAME in (select NFT_collection from tb1)),

tb3 as (select

NFT1,

s.block_timestamp,

s.tx_id,

b.PURCHASER,

sales_amount,

CASE WHEN log_messages::string ILIKE '%r 0 f%' THEN 'No Royalties' ELSE 'Has Royalties' end AS Royalty_indicator

from solana.core.fact_transactions s left join tb2 b on s.tx_id=b.tx_id

where s.tx_id in (select tx_id from tb2)

and s.block_timestamp>=CURRENT_DATE-30