token_name,

SALES_AMOUNT

FROM solana.core.fact_nft_sales

LEFT outer JOIN solana.core.dim_nft_metadata

ON solana.core.fact_nft_sales.mint = solana.core.dim_nft_metadata.mint

where not token_name is NULL

)

SELECT token_name as collection,

sum(royalty) as royalty_volume

FROM (SELECT token_name,

solana.core.fact_transfers.tx_id,

max(amount),

sum(amount),

(sum(amount) - max(amount)) / 2 as royalty

FROM solana.core.fact_transfers

LEFT outer join sales

on sales.tx_id = solana.core.fact_transfers.tx_id

WHERE mint LIKE 'So11111111111111111111111111111111111111112'

and solana.core.fact_transfers.tx_id in (SELECT tx_id

from sales

)

GROUP BY 1,2

)

GROUP BY 1

ORDER BY 2 DESC

LIMIT 10