-- 19.055441478439427 (fee rate) * (128 + 15_000)(account size including metadata) * ((365.25/2) * 2)(epochs in 2 years) WITH metaplex_txs as ( select tx_id, 'Metaplex' as tx_type from solana.core.fact_events where block_timestamp::DATE >= CURRENT_DATE - 14 AND block_timestamp::DATE < CURRENT_DATE AND succeeded = 'TRUE' AND program_id IN ( -- 'qntmGodpGkrM42mN68VCZHXnKqDCT8rdY23wFcXCLPd', -- 'gdrpGjVffourzkdDRrQmySw4aTHr8a3xmQzzxSwFD1a', -- 'faircnAB9k59Y4TXmLabBULeuTLgV7TkGMGNkjnA15j', -- 'hausS13jsjafwWwGqZTUQRmWyvyxn9EQpqMwV1PBBmk', 'cndyAnrLdpjq1Ssp1z8xxDsB8dxe7u4HL5Nxi2K5WXZ', 'cndy3Z4yapfJBmL3ShUp5exZKqR3z33thTzeNMm2gRZ') ) , outer_instructions AS ( SELECT block_timestamp::DATE AS block_date, -- i.value['parsed']['type'] as create_type, CASE WHEN tx_type = 'Metaplex' then 'Metaplex: CandyMachine' WHEN array_contains('Program BPFLoaderUpgradeab1e11111111111111111111111 success'::VARIANT, log_messages) THEN 'Deploy Program' WHEN array_contains('Program log: Instruction: InitializeAccount3'::VARIANT, log_messages) THEN 'Create Associated Account' WHEN log_messages::string ilike '%Program M2mx93ekt1fmXSVkTrUL9xVFHkmME8HTUi5Cyc5aF7K%' THEN 'MagicEden Buy/Sell' WHEN log_messages::string ilike '%Program LCNTy2Q4HsKUecKEETwwzujjbAx9DGFJort8SjGzGFj%' then 'Ladder Caster' WHEN log_messages::string ilike '%Program DDZDcYdQFEMwcu2Mwo75yGFjJ1mUQyyXLWzhZLEVFcei%' then 'Audius' ELSE 'Unknown' END AS event, greatest( 19.055441478439427/1e9 * i.value['parsed']['info']['space'] * ((365.25/2) * 2), 0.00203928) AS rent FROM solana.core.fact_transactions t left join metaplex_txs m on t.tx_id = m.tx_id, LATERAL FLATTEN(instructions) AS i WHERE i.value['parsed']['type'] ilike 'create%' AND succeeded AND block_timestamp::DATE >= CURRENT_DATE - 14 AND block_timestamp::DATE < CURRENT_DATE ORDER BY 1 ) ,inner_instructions AS ( SELECT block_timestamp::DATE AS block_date, -- i.value['parsed']['type'] as create_type, CASE WHEN tx_type = 'Metaplex' then 'Metaplex: CandyMachine' WHEN array_contains('Program BPFLoaderUpgradeab1e11111111111111111111111 success'::VARIANT, log_messages) THEN 'Deploy Program' WHEN array_contains('Program log: Instruction: InitializeAccount3'::VARIANT, log_messages) THEN 'Create Associated Account' WHEN log_messages::string ilike '%Program M2mx93ekt1fmXSVkTrUL9xVFHkmME8HTUi5Cyc5aF7K%' THEN 'MagicEden Buy/Sell' WHEN log_messages::string ilike '%Program LCNTy2Q4HsKUecKEETwwzujjbAx9DGFJort8SjGzGFj%' then 'Ladder Caster' WHEN log_messages::string ilike '%Program DDZDcYdQFEMwcu2Mwo75yGFjJ1mUQyyXLWzhZLEVFcei%' then 'Audius' ELSE 'Unknown' END AS event, greatest( 19.055441478439427/1e9 * i.value['parsed']['info']['space'] * ((365.25/2) * 2), 0.00203928) AS rent FROM solana.core.fact_transactions t left join metaplex_txs m on t.tx_id = m.tx_id, LATERAL FLATTEN(inner_instructions) AS i_tmp, LATERAL FLATTEN(i_tmp.value['instructions']) AS i WHERE i.value['parsed']['type'] ilike 'create%' AND succeeded AND block_timestamp::DATE >= CURRENT_DATE - 14 AND block_timestamp::DATE < CURRENT_DATE ORDER BY 1 ), base as (SELECT rent_type, event, count(1) AS tx_count, sum(rent) as total_volume FROM ( SELECT *, case when rent = 0.00203928 then '1.Rent = 0.00203928 SOL' when rent < 0.005 then '2.Rent < 0.005 SOL' when rent < 0.01 then '3.Rent < 0.01 SOL' when rent < 0.1 then '4.Rent < 0.1 SOL' else '5.Rent > 0.1 SOL' end as rent_type FROM outer_instructions UNION ALL SELECT *, case when rent = 0.00203928 then '1.Rent = 0.00203928 SOL' when rent < 0.005 then '2.Rent < 0.005 SOL' when rent < 0.01 then '3.Rent < 0.01 SOL' when rent < 0.1 then '4.Rent < 0.1 SOL' else '5.Rent > 0.1 SOL' end as rent_type FROM inner_instructions ) group by 1,2) select *, case when event = 'Create Associated Account' then '1. Create Associated Account' when event = 'Deploy Program' then '2. Deploy Program' when event = 'Unknown' then '3. Unknown' when event = 'MagicEden Buy/Sell' then '4. MagicEden Buy/Sell' when event = 'Metaplex: CandyMachine' then '5. Metaplex: CandyMachine' when event = 'Audius' then '6.Audius' when event = 'Ladder Caster' then '7. Ladder Caster' end as numberings from base order by numberings asc

Solana Rent