instruction:accounts as user,

TX_ID,

COALESCE(inner_instruction:instructions[4]:parsed:info:lamports * POWER(10, -9), 0) AS platform_fee,

COALESCE(inner_instruction:instructions[5]:parsed:info:lamports * POWER(10, -9), 0) AS collection_royalty

FROM solana.core.fact_events e

LEFT JOIN solana.core.dim_labels l ON l.address = inner_instruction:instructions[0]:parsed:info:mint::string

where instruction:programId = 'hausS13jsjafwWwGqZTUQRmWyvyxn9EQpqMwV1PBBmk'

)

select

'opensea' as platform,

'0% royalty' as royalty,

count(tx_id) as tx_count,

count(DISTINCT user) as user_count

from main

where collection_royalty = '0'

and platform_fee = '0'

group by 1,2

union

select

'opensea' as platform,

'more than 0% royalty' as royalty,

count(tx_id) as tx_count,

count(DISTINCT user) as tx_count

from main

where collection_royalty > '0'

and platform_fee > '0'

group by 1,2