with cnft_txs as ( select block_timestamp , 'magic eden' as marketplace , trader , sales_amount , label_side from ( select block_timestamp , instruction :accounts[0] ::string as trader , abs(post_balances[0] - pre_balances[0] + iff((post_balances[0] + fee) < pre_balances[0], +fee, -fee)) / pow(10,9) as sales_amount , 'buy' as label_side from solana.core.fact_events inner join solana.core.fact_transactions using(block_timestamp, tx_id, succeeded) where succeeded and program_id = 'M3mxk5W2tt27WGT7THox7PmgRDp4m6NEhL5xvxrBfS1' and array_contains('Program log: Instruction: BuyNow' ::variant, log_messages) and block_timestamp >= '2023-07-01' and block_timestamp < '2023-10-01' union all select block_timestamp , instruction :accounts[1] ::string as trader , abs(post_balances[0] - pre_balances[0] + iff((post_balances[0] + fee) < pre_balances[0], +fee, -fee)) / pow(10,9) as sales_amount , 'sell' as label_side from solana.core.fact_events inner join solana.core.fact_transactions using(block_timestamp, tx_id, succeeded) where succeeded and program_id = 'M3mxk5W2tt27WGT7THox7PmgRDp4m6NEhL5xvxrBfS1' and array_contains('Program log: Instruction: BuyNow' ::variant, log_messages) and block_timestamp >= '2023-07-01' and block_timestamp < '2023-10-01' ) union all select block_timestamp , 'tensorswap' as marketplace , trader , sales_amount , label_side from ( select block_timestamp , case when regexp_replace(logs.value, '^Program log: Instruction: ') = 'Buy' then instruction :accounts[9] ::string when regexp_replace(logs.value, '^Program log: Instruction: ') = 'TakeBidFullMeta' then instruction :accounts[12] ::string when regexp_replace(logs.value, '^Program log: Instruction: ') = 'TakeBidLegacy' then instruction :accounts[3] ::string when regexp_replace(logs.value, '^Program log: Instruction: ') = 'TakeBidMetaHash' then instruction :accounts[12] ::string end as trader , abs(post_balances[0] - pre_balances[0] + iff((post_balances[0] + fee) < pre_balances[0], +fee, -fee)) / pow(10,9) as sales_amount , 'buy' as label_side from solana.core.fact_events inner join solana.core.fact_transactions using(tx_id, block_timestamp, succeeded) inner join lateral flatten (input => log_messages) as logs where succeeded and fact_events.program_id = 'TCMPhJdwDryooaGtiocG1u3xcYbRpiJzb283XfCZsDp' and log_messages[logs.index -1] = 'Program TCMPhJdwDryooaGtiocG1u3xcYbRpiJzb283XfCZsDp invoke [1]' and regexp_replace(logs.value, '^Program log: Instruction: ') in ('Buy', 'TakeBidFullMeta', 'TakeBidLegacy', 'TakeBidMetaHash') and block_timestamp >= '2023-07-01' and block_timestamp < '2023-10-01' union all select block_timestamp , case when regexp_replace(logs.value, '^Program log: Instruction: ') = 'Buy' then instruction :accounts[11] ::string when regexp_replace(logs.value, '^Program log: Instruction: ') = 'TakeBidFullMeta' then instruction :accounts[2] ::string when regexp_replace(logs.value, '^Program log: Instruction: ') = 'TakeBidLegacy' then instruction :accounts[1] ::string when regexp_replace(logs.value, '^Program log: Instruction: ') = 'TakeBidMetaHash' then instruction :accounts[2] ::string end as trader , abs(post_balances[0] - pre_balances[0] + iff((post_balances[0] + fee) < pre_balances[0], +fee, -fee)) / pow(10,9) as sales_amount , 'sell' as label_side from solana.core.fact_events inner join solana.core.fact_transactions using(tx_id, block_timestamp, succeeded) inner join lateral flatten (input => log_messages) as logs where succeeded and fact_events.program_id = 'TCMPhJdwDryooaGtiocG1u3xcYbRpiJzb283XfCZsDp' and log_messages[logs.index -1] = 'Program TCMPhJdwDryooaGtiocG1u3xcYbRpiJzb283XfCZsDp invoke [1]' and regexp_replace(logs.value, '^Program log: Instruction: ') in ('Buy', 'TakeBidFullMeta', 'TakeBidLegacy', 'TakeBidMetaHash') and block_timestamp >= '2023-07-01' and block_timestamp < '2023-10-01' ) ), unc_txs as ( select block_timestamp , case when lower(marketplace) = 'solana monkey business marketplace' then 'SMB Market' when lower(marketplace) like 'magic eden%' then 'magic eden' when lower(marketplace) = 'coral cube' then 'coral cube' else marketplace end as marketplace , trader , sales_amount , label_side from ( select block_timestamp, marketplace, seller as trader, sales_amount, 'sell' as label_side from solana.nft.fact_nft_sales sales where succeeded and block_timestamp >= '2023-07-01' and block_timestamp < '2023-10-01' union all select block_timestamp, marketplace, purchaser as trader, sales_amount, 'buy' as label_side from solana.nft.fact_nft_sales sales where succeeded and block_timestamp >= '2023-07-01' and block_timestamp < '2023-10-01' ) ), aggregated as ( select trader , count(distinct marketplace) as marketplaces , count(1) as trades , sum(sales_amount) as vol_sol , vol_sol / sum(vol_sol) over () * 100 as vol_pct , sum(case when label_side = 'buy' then sales_amount else 0 end) as vol_buy , sum(case when label_side = 'sell' then sales_amount else 0 end) as vol_sell from ( select * from cnft_txs union all select * from unc_txs ) group by 1 ) select trader , marketplaces as "Marketplaces" , trades , '||' as "||" , round(vol_pct, 2) as "Vol. %" , round(vol_sol, 2) as "Vol. SOL" , round(vol_buy, 2) as "Vol. Buy" , round(vol_sell, 2) as "Vol. Sell" from aggregated order by vol_sol desc limit 100

FKN NFTs - Solana Market Report Q3 2023