Query editor
with nft_trades as (SELECTdate_trunc('year',block_time) as year,nft_contract_address,SUM(royalty_fee_amount_usd) as total_royalties,row_number() OVER (PARTITION BY date_trunc('year',block_time) ORDER BY SUM(royalty_fee_amount_usd) DESC) as rankingFROM nft.tradesINNER JOIN (SELECT distinct unique_trade_id as uid FROM nft_ethereum.wash_trades WHERE is_wash_trade = False) ON uid = unique_trade_idWHERE blockchain = 'ethereum'GROUP BY 1,2),nft_contracts as (SELECTyear,nft_contract_address,total_royaltiesFROM nft_tradesWHERE ranking <= 100),trade_agg as (SELECTdate_trunc('year',block_time) as date_time,SUM(royalty_fee_amount_usd) as total_royalties,SUM(CASE WHEN nc.nft_contract_address IS NOT NULL THEN royalty_fee_amount_usd END) as top_collection_royaltiesFROM nft.trades tLEFT JOIN nft_contracts nc ON nc.nft_contract_address = t.nft_contract_address and nc.year = date_trunc('year',block_time)GROUP BY 1)SELECTdate_time,total_royalties,top_collection_royalties,top_collection_royalties / total_royalties as perc_of_royalFROM trade_aggORDER BY date_time DESC