-- forked from TheLaughingMan / Total Ecosystem Raises - Results @ https://flipsidecrypto.xyz/TheLaughingMan/q/z-QYkj_Xe3bX/total-ecosystem-raises---results with article_data as ( select 1 as rn,'null' as catogery,'Lore 1 ♡ Introducing Mibera' as title, 'Lore 1 ♡ Introducing Mibera' as articles, '0x6b31859e5e32a5212f1ba4d7b377604b9d4c7a60' as contract_address, 4713 as total_supply union all select 2 as rn,'null' as catogery,'Lore 2 ♡[HONEY] Online to get Offline: Clear pill vs Rave pill' as title, 'Lore 2 ♡[HONEY] Online to get Offline: Clear pill vs Rave pill' as articles, '0x9247edf18518c4dccfa7f8b2345a1e8a4738204f' as contract_address, 2355 as total_supply union all select 3 as rn,'null' as catogery,'Lore 3 ♡ [BERA] Kali/acc vs Cybernetic Psychedelic Mysticism' as title, 'Lore 3 ♡ [BERA] Kali/acc vs Cybernetic Psychedelic Mysticism' as articles, '0xb2c7f411aa425d3fce42751e576a01b1ff150385' as contract_address, 1175 as total_supply union all select 4 as rn,'null' as catogery,'Lore 4 ♡ [BGT] Network Spirituality (Spirit) vs Network Mysticism (Soul)' as title, 'Lore 4 ♡ [BGT] Network Spirituality (Spirit) vs Network Mysticism (Soul)' as articles, '0xa12064e3b1f6102435e77aa68569e79955070357' as contract_address, 571 as total_supply union all select 5 as rn,'null' as catogery,'Lore 5 ♡ Mibera Initiation Ritual' as title, 'Lore 5 ♡ Mibera Initiation Ritual ' as articles, '0x6ca29eed22f04c1ec6126c59922844811dcbcdfa' as contract_address, 271 as total_supply union all select 6 as rn,'null' as catogery,'Lore 6 ♡ MiberaMaker Design Document' as title, 'Lore 6 ♡ MiberaMaker Design Document' as articles, '0x7988434E1469d35fa5F442e649dE45D47c3df23C' as contract_address, 126 as total_supply --0x96C200Ec4CcA0bc57444cFEe888cfbA78a1ddBD8 union all select 7 as rn,'null' as catogery,'Lore 7 ♡ MiberaMaker Design Document' as title, 'Lore 7 ♡ MiberaMaker Design Document' as articles, '0x96C200Ec4CcA0bc57444cFEe888cfbA78a1ddBD8' as contract_address, 107 as total_supply ) , zora_mint_data as ( select 1 as token_id, 'mibera lore 1 ♡ poster' as title, 0.0033 as price, 271 as supply union all select 2 as token_id, 'mibera lore 1 ♡ video' as title, 0.0042 as price, 333 as supply union all select 3 as token_id, 'mibera lore 1 ♡ music [Liquid Bears by babybera]' as title, 0.0069 as price, 420 as supply union all select 4 as token_id, 'mibera lore 2 ♡ poster' as title, 0.0033 as price, 271 as supply union all select 5 as token_id, 'mibera lore 2 ♡ video' as title, 0.0042 as price, 333 as supply union all select 6 as token_id, 'mibera lore 2 ♡ music [Okite by babybera]' as title, 0.0069 as price, 420 as supply union all select 7 as token_id, 'mibera lore 3 ♡ poster' as title, 0.0033 as price, 271 as supply union all select 8 as token_id, 'mibera lore 3 ♡ video' as title, 0.0042 as price, 333 as supply union all select 9 as token_id, 'mibera lore 3 ♡ music [Sleezy B by babybera]' as title, 0.0069 as price, 420 as supply union all select 10 as token_id, 'mibera lore 4 ♡ poster' as title, 0.0033 as price, 271 as supply union all select 11 as token_id, 'mibera lore 4 ♡ video' as title, 0.0042 as price, 333 as supply union all select 12 as token_id, 'mibera lore 4 ♡ music [Declension B by babybera]' as title, 0.0069 as price, 420 as supply union all select 13 as token_id, 'mibera lore 5 ♡ poster' as title, 0.0033 as price, 271 as supply union all select 14 as token_id, 'mibera lore 5 ♡ video' as title, 0.0042 as price, 333 as supply union all select 15 as token_id, 'mibera lore 5 ♡ music [AKTV babybera]' as title, 0.0069 as price, 420 as supply union all select 16 as token_id, 'mibera lore 6 ♡ poster' as title, 0.0033 as price, 126 as supply union all select 17 as token_id, 'mibera lore 6 ♡ video' as title, 0.0042 as price, 333 as supply union all select 18 as token_id, 'mibera lore 6 ♡ music [AKTV babybera]' as title, 0.0069 as price, 420 as supply union all select 19 as token_id, 'mibera lore 7 ♡ poster' as title, 0.33 as price, 107 as supply union all select 20 as token_id, 'mibera lore 7 ♡ video' as title, 0.42 as price, 107 as supply union all select 21 as token_id, 'mibera lore 7 ♡ music' as title, 0.69 as price, 107 as supply ) , thj_articles as ( select 1 as rn,'general' as catogery,'Henlo, World!' as title, 'Henlo, World!' as articles, '0xaff376b6a630b07b57f676a939748a321ff14734' as contract_address, 10000 as total_supply union select 2,'general','Berachain, Cults and the Dawn of The Honey Jar','Berachain, Cults and the Dawn of The Honey Jar' ,'0x31e851ed8b927e07366eaf76d0b5e0a3d13c57ee',10000 union select 3,'Bong Bear','Bonga Bera 101','Bonga Bera 101' ,'0xd7ccc7b8ec095644db35a1ac2dc58104294927c3',107 union select 4,'Bond Bear','The Honey Jar 101','The Honey Jar 101' ,'0xb8c6145f958f141db0d506a4c1f6c0546ade78ab',126 union select 5,'general','The Honey Jar FAQs','The Honey Jar FAQs' ,'0x68ae28fb8cd9664cc08090b3c8f241d97012c99b',10000 union select 6,'Boo Bear','Gen 1 Partnooorships 101: Very Sweet Perks','Gen 1 Partnooorships 101: Very Sweet Perks' ,'0x5ffe4df7c0908232b46edef4ecbbafdb2c1f8cf5',271 union select 7,'Baby Bear','BeraFi: Bong Bears x NFTFi','BeraFi: Bong Bears x NFTFi' ,'0x093312465e593c65b1f00e0ab8c5885634b2aa83',571 union select 8,'Band Bear','Bong Bears Data Insights','Bong Bears Data Insights' ,'0x3c13dcd0e6eaef7151f5877f4757951b55cac01c',1175 union select 9,'Bit Bear','Honey Jar Ponzinomics','Honey Jar Ponzinomics' ,'0x1cbc0e6d9592a45a20a2682397b78cf5bc7db91c',2355 union select 10,'B Bear','HoneyBlitzing: Blitzscaling Berachain or Operation Bear Hug?','HoneyBlitzing: Blitzscaling Berachain or Operation Bear Hug?' ,'0x1460a7292b1485a6bb0f752b5d61bccab260e2bf',4713 union select 11,'general','THJ Gen 2 Campaign: IRL activities','THJ Gen 2 Campaign: IRL activities' ,'0x7fcc15a092d8f958c7e16fda6e171083da4a2e18',420 union select 12,'general','THJ Gen 2 Campaign: IRL activities1','THJ Gen 2 Campaign: IRL activities1' ,'0x513e650948227476a7b86bf7c6fafc6b3b263130',420 union select 13,'general','Once Upon A Mint','Once Upon A Mint' ,'0x677c71fe7a674567d07cccea354592646fb2b079',420 union select 14,'general','A Honeycomb Retrospective','A Honeycomb Retrospective' ,'0x21542878aab3571337445b84bb66f5f9db04c5e9',420 union select 15,'general','The Honey Jar Generation 2 Mint Guide','The Honey Jar Generation 2 Mint Guide' ,'0xe8085309623507ca0ae93f82f260c3bd8f5824c3',420 ) , merger as ( SELECT block_timestamp , tx_hash , from_address as user , VALUE as cost_eth , 'Hiberanation (Dream Masks)' as label from arbitrum.core.fact_transactions t where 1=1 AND to_address = '0x5996cea2c456259d1dd9ae72b3df5192a1cbae01' and ORIGIN_FUNCTION_SIGNATURE = '0x1249c58b' AND STATUS = 'SUCCESS' UNION ALL SELECT block_timestamp , tx_hash , NFT_TO_ADDRESS as user , mint_price_eth as cost_eth -- , cost_eth * price as cost_usd , 'Beramonium Chronicles' as label from ethereum.nft.ez_nft_mints WHERE 1=1 AND NFT_ADDRESS = '0xb44adddbf9b19bcdd12f978df8b36723488b9b8b' UNION ALL SELECT block_timestamp , tx_hash , FROM_ADDRESS as user , VALUE as cost_eth -- , cost_eth * price as cost_usd , 'insrt finance (BongBear ShardVaults)' as label from ethereum.core.fact_transactions WHERE 1=1 AND block_timestamp>='2023-04-10' AND TO_ADDRESS IN ('0x5e60026d0e63325c47bac35c8820953320930f15', '0x9467b523c9eeea7853be0c7f0f1e3e8d4ac698ab', '0x4832b4423c35311f9dc0c2d03b29e68b176d40cc') AND ORIGIN_FUNCTION_SIGNATURE = '0x98b1e06a' AND STATUS = 'SUCCESS' UNION ALL SELECT block_timestamp , tx_hash , NFT_TO_ADDRESS as user , MINT_PRICE_ETH as cost_eth -- , cost_eth * price as cost_usd , 'The HoneyCast(THC NFT)' as label from ethereum.nft.ez_nft_mints WHERE 1=1 AND NFT_ADDRESS ='0xd65f95a40b6de26f6c390fcb8e3377d382fe678b' UNION ALL SELECT block_timestamp , tx_hash , FROM_ADDRESS as user , VALUE as cost_eth , 'BeraPunk' as label from arbitrum.core.fact_transactions WHERE 1=1 AND tx_hash IN ( SELECT tx_hash from arbitrum.nft.ez_nft_transfers WHERE 1=1 AND NFT_ADDRESS = '0xc46233dd3775c858a926b2b2d398bea7ca69499c' AND NFT_FROM_ADDRESS = '0x0000000000000000000000000000000000000000' ) UNION ALL SELECT block_timestamp , tx_hash , FROM_ADDRESS as user , VALUE as cost_eth , 'BeraDoge (Gen1)' as label from arbitrum.core.fact_transactions WHERE 1=1 AND tx_hash IN ( SELECT tx_hash from arbitrum.nft.ez_nft_transfers WHERE 1=1 AND NFT_ADDRESS = '0x75395f24644f1e65f66e346a414ff3412f131dd5' AND NFT_FROM_ADDRESS = '0x0000000000000000000000000000000000000000' ) UNION ALL SELECT block_timestamp , tx_hash , FROM_ADDRESS as user , VALUE as cost_eth -- , cost_eth * price as cost_usd , 'Berachein (Baka Bears)' as label from arbitrum.core.fact_transactions WHERE 1=1 AND STATUS = 'SUCCESS' AND TO_ADDRESS IN ('0xe5b68556c12f37fa12f15ae87ea32f15a8c4e7b9') AND ORIGIN_FUNCTION_SIGNATURE = '0x84bb1e42' UNION ALL SELECT block_timestamp , tx_hash , FROM_ADDRESS as user , VALUE as cost_eth , 'Ramen Finance (HungryBera)' as label from arbitrum.core.fact_transactions WHERE 1=1 AND tx_hash IN ( SELECT tx_hash from arbitrum.nft.ez_nft_transfers WHERE 1=1 AND NFT_ADDRESS = '0xac59f7e7e5da0dc4f416a7aeff7a49ac284f10ca' AND NFT_FROM_ADDRESS = '0x0000000000000000000000000000000000000000' ) UNION ALL SELECT block_timestamp , tx_hash , FROM_ADDRESS as user , VALUE as cost_eth , 'Honeypot Finance' as label from arbitrum.core.fact_transactions WHERE 1=1 AND tx_hash IN ( SELECT tx_hash from arbitrum.nft.ez_nft_transfers WHERE 1=1 AND NFT_ADDRESS = '0x7c2f99c405fa3102519f2637f186c5a06c074a0f' AND NFT_FROM_ADDRESS = '0x0000000000000000000000000000000000000000' ) UNION ALL SELECT block_timestamp , tx_hash , FROM_ADDRESS as user , VALUE as cost_eth , 'Yeet (Yeetard NFT)' as label from arbitrum.core.fact_transactions WHERE 1=1 AND tx_hash IN ( SELECT tx_hash from arbitrum.nft.ez_nft_transfers WHERE 1=1 AND NFT_ADDRESS = '0x2e660787bceccd39f67b8190a5bc4fc3ad3b64f7' AND NFT_FROM_ADDRESS = '0x0000000000000000000000000000000000000000' ) UNION ALL SELECT block_timestamp , tx_hash , NFT_TO_ADDRESS as user , mint_price_eth as cost_eth -- , cost_eth * price as cost_usd , '$ER (Article NFT)' as label from ethereum.nft.ez_nft_mints WHERE 1=1 AND NFT_ADDRESS = '0x80ad01392b9b4a85535ec0bbff13b74c8da81070' UNION ALL SELECT block_timestamp , tx_hash , FROM_ADDRESS as user , VALUE as cost_eth , 'CubHub (Merch Edition#1)' as label from arbitrum.core.fact_transactions WHERE 1=1 AND tx_hash IN ( SELECT tx_hash from arbitrum.nft.ez_nft_transfers WHERE 1=1 AND NFT_ADDRESS = '0x2359d3f04fc33d1b8223fe5798d7bfdae7a93d9d' AND NFT_FROM_ADDRESS = '0x0000000000000000000000000000000000000000' ) UNION ALL SELECT block_timestamp , tx_hash , NFT_TO_ADDRESS as user , price as cost_eth , 'MiBera (Zora Collection)' as label from optimism.nft.ez_nft_transfers t LEFT JOIN zora_mint_data d on t.tokenid = d.token_id WHERE 1=1 AND NFT_ADDRESS = '0x427a8f2e608e185eece69aca15e535cd6c36aad8' AND NFT_FROM_ADDRESS = '0x0000000000000000000000000000000000000000' UNION ALL SELECT block_timestamp , tx_hash , FROM_ADDRESS as user , VALUE as cost_eth , 'MiBera (Mirror Articles)' as label from optimism.core.fact_transactions WHERE 1=1 AND tx_hash IN ( SELECT tx_hash from optimism.nft.ez_nft_transfers WHERE 1=1 AND NFT_ADDRESS IN ( SELECT lower(contract_address) from article_data) AND NFT_FROM_ADDRESS = '0x0000000000000000000000000000000000000000' ) UNION ALL SELECT block_timestamp , tx_hash , FROM_ADDRESS as user , VALUE as cost_eth , 'BeraPlug (NFT Vid)' as label from optimism.core.fact_transactions WHERE 1=1 AND tx_hash IN ( SELECT tx_hash from optimism.nft.ez_nft_transfers WHERE 1=1 AND NFT_ADDRESS = '0x1a652a2c9065465c1cca864bfd2a09cbf511c3a3' AND NFT_FROM_ADDRESS = '0x0000000000000000000000000000000000000000' ) UNION ALL SELECT block_timestamp , tx_hash , FROM_ADDRESS as user , VALUE as cost_eth , 'BeraPlug (Mirror Articles)' as label from optimism.core.fact_transactions WHERE 1=1 AND tx_hash IN ( SELECT tx_hash from optimism.nft.ez_nft_transfers WHERE 1=1 AND NFT_ADDRESS = '0xcaa5ed9c4a66e53a6f9fcf2aa378884af17600ae' AND NFT_FROM_ADDRESS = '0x0000000000000000000000000000000000000000' ) UNION ALL SELECT block_timestamp , tx_hash , FROM_ADDRESS as user , VALUE as cost_eth , 'The Honey Jar' as label from optimism.core.fact_transactions WHERE 1=1 AND tx_hash IN ( SELECT tx_hash from optimism.nft.ez_nft_transfers WHERE 1=1 AND NFT_ADDRESS IN (SELECT contract_address FROM thj_articles) AND NFT_FROM_ADDRESS = '0x0000000000000000000000000000000000000000' ) UNION ALL SELECT block_timestamp , tx_hash , FROM_ADDRESS as user , VALUE as cost_eth , 'The Honey Jar' as label from ethereum.core.fact_transactions WHERE 1=1 AND ORIGIN_FUNCTION_SIGNATURE IN ('0x5d84bbfa', '0x950db017') AND TO_ADDRESS = '0xb474f03c2c1e15596260cda307f7827cdd3fb749' AND STATUS = 'SUCCESS' UNION ALL SELECT block_timestamp , tx_hash , FROM_ADDRESS as USER , AMOUNT_USD/price as cost_eth , 'The Honey Jar' as label from ethereum.core.ez_token_transfers t LEFT JOIN ( SELECT price , hour from crosschain.price.ez_prices_hourly WHERE 1=1 AND SYMBOL='WETH' AND blockchain='ethereum' AND TOKEN_ADDRESS = lower('0xC02aaA39b223FE8D0A0e5C4F27eAD9083C756Cc2') ) p on date_trunc('hour', t.block_timestamp) = p.hour WHERE 1=1 AND tx_hash in ( SELECT tx_hash from ethereum.core.fact_transactions WHERE 1=1 AND ORIGIN_FUNCTION_SIGNATURE IN ('0x2a8d40e7', '0xe5c9b6b4') AND TO_ADDRESS = '0xb474f03c2c1e15596260cda307f7827cdd3fb749' AND STATUS = 'SUCCESS' ) --AND t.SYMBOL = 'OHM' ) , combo as ( SELECT SUM(cost_eth) as eth_raised --, SUM(cost_eth * p.price) as usd_raised , label as project , 'Ecosystem/Community Fundraise' as tag from merger m GROUP BY label ) SELECT * from merger where label = 'The HoneyCast(THC NFT)'

Query	Creator	Created
F opt Optimism copy optimism l2 evm	brian-terra	Oct 11, 2023
2024-03-16 10:18 PM optimism l2 evm	brian-terra	Mar 17, 2024
Optimism - MAU optimism l2 evm	brian-terra	Oct 12, 2023
Total Ecosystem Raises - Results copy arbitrum ethereum optimism nft l2	brian-terra	May 21, 2024

brian-terra