-- forked from ELE Rewards Per Capita [Weekly] @ https://flipsidecrypto.xyz/edit/queries/3514b2da-6969-4c47-b6db-450b1947372a with prices AS ( select DATE_TRUNC('day', block_timestamp) AS date, sum(case when (swap_from_mint = '8A9HYfj9WAMgjxARWVCJHAeq9i8vdN9cerBmqUamDj7U' AND swap_to_mint = 'EPjFWdd5AufqSSqeM2qN1xzybapC8G4wEGGkZwyTDt1v') then swap_from_amount when (swap_to_mint = '8A9HYfj9WAMgjxARWVCJHAeq9i8vdN9cerBmqUamDj7U' AND swap_from_mint = 'EPjFWdd5AufqSSqeM2qN1xzybapC8G4wEGGkZwyTDt1v') then swap_to_amount end) as ATLAS, sum(case when (swap_from_mint = '8A9HYfj9WAMgjxARWVCJHAeq9i8vdN9cerBmqUamDj7U' AND swap_to_mint = 'EPjFWdd5AufqSSqeM2qN1xzybapC8G4wEGGkZwyTDt1v') then swap_to_amount when (swap_to_mint = '8A9HYfj9WAMgjxARWVCJHAeq9i8vdN9cerBmqUamDj7U' AND swap_from_mint = 'EPjFWdd5AufqSSqeM2qN1xzybapC8G4wEGGkZwyTDt1v') then swap_from_amount end) as USDC, USDC/ATLAS as close from solana.defi.fact_swaps WHERE succeeded = 'true' AND program_id IN ( 'JUP6LkbZbjS1jKKwapdHNy74zcZ3tLUZoi5QNyVTaV4', 'JUP5cHjnnCx2DppVsufsLrXs8EBZeEZzGtEK9Gdz6ow', 'JUP4Fb2cqiRUcaTHdrPC8h2gNsA2ETXiPDD33WcGuJB', 'JUP3c2Uh3WA4Ng34tw6kPd2G4C5BB21Xo36Je1s32Ph', 'JUP2jxvXaqu7NQY1GmNF4m1vodw12LVXYxbFL2uJvfo') AND date_trunc('day', block_timestamp) >= '2024-01-23' --AND date_trunc('day', block_timestamp) < '2023-01-01' group by 1 ), ---- eme emitted ---- a1 AS ( SELECT -- 'SAGE ATLAS SINKS' as name, block_timestamp, -- tx_id, signers[0] as wallet, -- value:parsed:info:authority as wallet, sum(value:parsed:info:amount/pow(10,9)) as value from solana.core.fact_events, lateral flatten(input => inner_instruction:instructions) WHERE program_id = 'ELEMisgsfkmp58w1byRvrdpGG1HcapQoCrmMJeorBCxq' AND value:parsed:info:authority = '8ZCqJ3GXiXsmwqLnXbyBL5U7Tu59k6p4HwXc8ijfgQfU' -- signers[0] AND value:parsed:info:source = 'Cgig1KrCVH5PBCTnCwkFwsDYDvFCvYuKQ7tz7ZjFiwJi' and value:parsed:info:amount > 0 AND date_trunc('day', block_timestamp) >= '2024-01-23' --AND date_trunc('day', block_timestamp) < '2023-01-01' GROUP BY 1,2 ), s1 as ( SELECT wallet, sum(zeroifnull(value*prices.close)) as ele_emitted FROM a1 left join prices on date_trunc('day', a1.block_timestamp) = prices.date GROUP BY 1 ), a2 as ( select block_timestamp, signers[0] as wallet, sum(value:parsed:info:amount/pow(10,9)) as value from solana.core.fact_events, lateral flatten(input => inner_instruction:instructions) WHERE program_id = 'ELEMisgsfkmp58w1byRvrdpGG1HcapQoCrmMJeorBCxq' AND value:parsed:info:mint = '8A9HYfj9WAMgjxARWVCJHAeq9i8vdN9cerBmqUamDj7U' AND value:parsed:info:authority = signers[0] and value:parsed:type = 'burn' and value:parsed:info:amount > 0 AND date_trunc('day', block_timestamp) >= '2024-01-23' --AND date_trunc('day', block_timestamp) < '2023-01-01' GROUP by 1,2 ), s2 as ( SELECT wallet, sum(zeroifnull(value*prices.close)) as ele_burned FROM a2 left join prices on date_trunc('day', a2.block_timestamp) = prices.date GROUP BY 1 ), -- with nft_addresses1 AS ( SELECT -- * -- collection_id, -- count(mint) as mints -- authority, -- -- NFT_NAME, CASE WHEN collection_id = '4n4zLe1BcREy9XQyHwSMJJHR4YHn7AgP2dx4jL6X8GGR' THEN 'Rabbit' WHEN collection_id = 'BsoRSTtiE4DSuNXTkwjTjnZADKa9fWct8a4CncaUYdDD' THEN 'Inventor' WHEN collection_id = 'GuNkYyXPKCy3zL99GkiBZ23cyUqAkwpKszhq2my5A8ei' THEN 'Inventor' WHEN collection_id = 'GsWtWwZLkS9Ee9Rb13MuTG3JfQx3nhh16ps36ZNEPcdW' THEN 'Crystals' WHEN collection_id = 'C2Frjbg6DosmE3GSbb8veTxGg8H7kS73FzduYh3b8er9' THEN 'Crystals' -- WHEN NFT_NAME = 'Element Collection' THEN 'ELEMENTS' when collection_id = 'CdES51P2ThUZsgAeqFG42k59QchQMWBR9hLLeUGeB2gL' THEN 'Elements' WHEN collection_id = '56mePgFm5bJmKLABBvezK6Pr8q5xXbcMxhGFR8Cso9uL' THEN 'Chests' ELSE 'Other' END AS NFT_Label, mint FROM solana.nft.dim_nft_metadata WHERE -- authority = '4oZFNzopnabpEFz1TM2j3B4EasPGVcBzaVea2Qp1h2Ep' -- MINT_AUTHORITY = '7ZGx76bdL83ZCtXWteZ3XEXsEH6QypXBAQUVTgYd7Txe' -- mint = '9FVA6NUmkte9DgPG366yzYdF6n2cLHzMzuHi2Ss9FmGX' -- collection_id in ('4n4zLe1BcREy9XQyHwSMJJHR4YHn7AgP2dx4jL6X8GGR','BsoRSTtiE4DSuNXTkwjTjnZADKa9fWct8a4CncaUYdDD', -- 'C2Frjbg6DosmE3GSbb8veTxGg8H7kS73FzduYh3b8er9','CdES51P2ThUZsgAeqFG42k59QchQMWBR9hLLeUGeB2gL','56mePgFm5bJmKLABBvezK6Pr8q5xXbcMxhGFR8Cso9uL') -- NFT_COLLECTION_NAME LIKE '%Elementerra%' authority IN('8ZCqJ3GXiXsmwqLnXbyBL5U7Tu59k6p4HwXc8ijfgQfU','DfGoQrVBxvbnCeqcwwomRHn74xN5gBGKDCpgBGsSbYmm','8KAv7uSqg5gHzM1b6NSm8zq3j9PttLGg55JeFjBtqQSu') ), xx_atlas as ( select DISTINCT 'Other' as NFT_Label, value:parsed:info:mint as mint from solana.core.fact_events, lateral flatten(input => inner_instruction:instructions) WHERE program_id = 'ELEMisgsfkmp58w1byRvrdpGG1HcapQoCrmMJeorBCxq' AND date_trunc('day', block_timestamp) >= '2024-01-01' --AND date_trunc('day', block_timestamp) < '2023-01-01' and value:parsed:info:mint NOT IN (SELECT mint FROM nft_addresses1) ), -- a2 as ( -- select -- block_timestamp, -- tx_id, -- value:parsed:info:mint, -- signers[0] as wallet, -- value:parsed:info:amount as value -- from solana.core.fact_events, lateral flatten(input => inner_instruction:instructions) -- WHERE program_id = 'ELEMisgsfkmp58w1byRvrdpGG1HcapQoCrmMJeorBCxq' -- AND value:parsed:info:mint != '8A9HYfj9WAMgjxARWVCJHAeq9i8vdN9cerBmqUamDj7U' -- AND value:parsed:info:authority = signers[0] -- and value:parsed:type = 'burn' -- and value:parsed:info:amount > 0 -- AND date_trunc('day', block_timestamp) > '2024-01-23' --AND date_trunc('day', block_timestamp) < '2023-01-01' -- GROUP by 1,2 -- ), -- xx_atlas as ( -- select -- DISTINCT -- tx_id, -- 'Other' as NFT_Label, -- value as mint -- from solana.core.fact_transactions, lateral flatten(input=> account_keys) -- WHERE -- date_trunc('day', block_timestamp) > '2024-04-23' --AND date_trunc('day', block_timestamp) < '2023-01-01' -- and value = '2T6UGAQRGgjcqZNjzjApHnFjRo9hNSaqZg3vFyWP4k7b' -- -- GROUP BY 1,2 -- ) -- -- BsoRSTtiE4DSuNXTkwjTjnZADKa9fWct8a4CncaUYdDD nft_addresses2 as ( SELECT * FROM nft_addresses1 UNION SELECT * FROM xx_atlas ), buyers as( SELECT -- date_trunc('hour', s.block_timestamp) AS date, na.NFT_Label, purchaser AS wallet, count(DISTINCT tx_id) as purchased_amount, sum(s.SALES_AMOUNT*prices1.close) AS purchased_usd, purchased_usd/purchased_amount as avg_price_purchased FROM solana.nft.fact_nft_sales s -- WHERE -- s.mint ='2T6UGAQRGgjcqZNjzjApHnFjRo9hNSaqZg3vFyWP4k7b' JOIN nft_addresses2 na ON s.mint = na.mint left join solana.price.ez_token_prices_hourly prices1 on DATE_TRUNC('hour', s.BLOCK_TIMESTAMP) = prices1.recorded_hour WHERE s.succeeded = 'true' AND prices1.token_address = 'So11111111111111111111111111111111111111112' AND date_trunc('day', s.block_timestamp) >= '2024-01-23' --AND date_trunc('day', block_timestamp) < '2023-01-01' GROUP BY 1,2), sellers as( SELECT na.NFT_Label, seller AS wallet, count(DISTINCT tx_id) as sold_amount, sum(s.SALES_AMOUNT*prices1.close) AS sold_usd, sold_usd/sold_amount as avg_price_sold FROM solana.nft.fact_nft_sales s JOIN nft_addresses2 na ON s.mint = na.mint left join solana.price.ez_token_prices_hourly prices1 on DATE_TRUNC('hour', s.BLOCK_TIMESTAMP) = prices1.recorded_hour WHERE s.succeeded = 'true' AND prices1.token_address = 'So11111111111111111111111111111111111111112' AND date_trunc('day', s.block_timestamp) >= '2024-01-23' --AND date_trunc('day', block_timestamp) < '2023-01-01' GROUP BY 1,2), -- p4 as ( -- SELECT DISTINCT -- CAST(wallet AS VARCHAR(16777216)) AS wallet, -- CAST(date AS VARCHAR(16777216)) AS date -- FROM ( -- SELECT CAST(wallet AS VARCHAR(16777216)) AS wallet, CAST(date AS VARCHAR(16777216)) AS date FROM p1 -- UNION -- SELECT CAST(wallet AS VARCHAR(16777216)) AS wallet, CAST(date AS VARCHAR(16777216)) AS date FROM p2 -- UNION -- SELECT CAST(wallet AS VARCHAR(16777216)) AS wallet, CAST(date AS VARCHAR(16777216)) AS date FROM p3 -- ) -- ), ac1 as ( SELECT DISTINCT * FROM ( SELECT CAST(wallet AS VARCHAR(16777216)) AS wallet FROM s1 UNION SELECT CAST(wallet AS VARCHAR(16777216)) AS wallet FROM s2 UNION SELECT CAST(wallet AS VARCHAR(16777216)) AS wallet FROM buyers UNION SELECT CAST(wallet AS VARCHAR(16777216)) AS wallet FROM sellers )), ac2 as ( SELECT DISTINCT * FROM ( SELECT NFT_Label FROM buyers UNION SELECT NFT_Label FROM sellers )), ac AS ( SELECT wallet, NFT_Label FROM ac1 CROSS JOIN ac2 ), ff as ( SELECT ac.wallet, sum(zeroifnull(sold_usd)) as sold_usd, sum(zeroifnull(purchased_usd)) as purchased_usd, round(sum(zeroifnull(sold_usd) - zeroifnull(purchased_usd)),2) as net_purchased_usd, round(sum(CASE WHEN ac.NFT_Label = 'Rabbit' then zeroifnull(sold_usd) - zeroifnull(purchased_usd) else 0 end),2) as rabbits, round(sum(CASE WHEN ac.NFT_Label = 'Inventor' then zeroifnull(sold_usd) - zeroifnull(purchased_usd) else 0 end),2) as inventors, round(sum(CASE WHEN ac.NFT_Label = 'Crystals' then zeroifnull(sold_usd) - zeroifnull(purchased_usd) else 0 end),2) as crystals, round(sum(CASE WHEN ac.NFT_Label = 'Chests' then zeroifnull(sold_usd) - zeroifnull(purchased_usd) else 0 end),2) as chests, -- round(sum(CASE WHEN ac.NFT_Label = 'Other' then zeroifnull(sold_usd) - zeroifnull(purchased_usd) else 0 end),2) as other, round(avg(CASE WHEN ac.NFT_Label = 'Rabbit' then zeroifnull(avg_price_purchased) else 0 end),2) as rabbits_avg_pur_price, round(avg(CASE WHEN ac.NFT_Label = 'Rabbit' then zeroifnull(avg_price_sold) else 0 end),2) as rabbits_avg_sold_price FROM ac LEFT OUTER JOIN buyers using (wallet,NFT_Label) LEFT OUTER JOIN sellers using (wallet,NFT_Label) WHERE ac.NFT_Label != 'Other' GROUP BY 1), a4 as ( SELECT block_timestamp, signers[0] as wallet, sum(value:parsed:info:lamports/pow(10,9)) as amount from solana.core.fact_events, lateral flatten(input => inner_instruction:instructions) WHERE program_id = 'ELEMisgsfkmp58w1byRvrdpGG1HcapQoCrmMJeorBCxq' AND value:parsed:info:source = 'EnRTj2Fb34naS6jiUhJKRX4GmtKLadcez7TvQe1PL5FC' and value:parsed:info:lamports > 0 AND date_trunc('day',block_timestamp) >= '2024-01-23' --AND date_trunc('day', block_timestamp) < '2023-01-01' GROUP BY 1,2), s3 as( SELECT wallet, sum(s.amount*prices1.close) AS invention_earnings FROM a4 s left join solana.price.ez_token_prices_hourly prices1 on DATE_TRUNC('hour', s.BLOCK_TIMESTAMP) = prices1.recorded_hour WHERE prices1.token_address = 'So11111111111111111111111111111111111111112' GROUP BY 1), a5 as ( SELECT block_timestamp, signers[0] as wallet, sum(value:parsed:info:lamports/pow(10,9)) as amount from solana.core.fact_events, lateral flatten(input => inner_instruction:instructions) WHERE program_id = 'ELEMisgsfkmp58w1byRvrdpGG1HcapQoCrmMJeorBCxq' AND value:parsed:info:destination = '4oZFNzopnabpEFz1TM2j3B4EasPGVcBzaVea2Qp1h2Ep' AND value:parsed:info:source = signers[0] and value:parsed:info:lamports > 0 AND date_trunc('day',block_timestamp) >= '2024-01-23' --AND date_trunc('day', block_timestamp) < '2023-01-01' GROUP BY 1,2), s4 as( SELECT wallet, sum(s.amount*prices1.close) AS minting_fees FROM a5 s left join solana.price.ez_token_prices_hourly prices1 on DATE_TRUNC('hour', s.BLOCK_TIMESTAMP) = prices1.recorded_hour WHERE prices1.token_address = 'So11111111111111111111111111111111111111112' GROUP BY 1), s14 as ( SELECT x.signers[0] as wallet, round(SUM(f.fee*prices.close/POW(10,9)),2) as tx_fees from solana.core.fact_events x INNER JOIN solana.core.fact_transactions f using(tx_id,block_timestamp) left join solana.price.ez_token_prices_hourly prices on DATE_TRUNC('hour', f.BLOCK_TIMESTAMP) = prices.recorded_hour and prices.TOKEN_ADDRESS = 'So11111111111111111111111111111111111111112' WHERE program_id::string IN ('ELEMisgsfkmp58w1byRvrdpGG1HcapQoCrmMJeorBCxq') AND x.block_timestamp >= '2024-01-23' --AND block_timestamp < current_date AND f.block_timestamp >= '2024-01-23'--AND block_timestamp < current_date GROUP BY 1 ) SELECT ac1.wallet, round(zeroifnull(ele_emitted) + zeroifnull(sold_usd) - zeroifnull(ele_burned) - zeroifnull(purchased_usd) + zeroifnull(invention_earnings)- zeroifnull(minting_fees)-zeroifnull(tx_fees),2) as "Net Earnings (USD)", round(zeroifnull(ele_emitted) - zeroifnull(ele_burned),2) as "Net ELE Earnings", round(zeroifnull(ele_emitted),2) as "ELE Emissions", round(zeroifnull(ele_burned),2) as "ELE Burned", round(zeroifnull(invention_earnings),2) as "Inventor Earnings", round(zeroifnull(minting_fees),2) as "Minting Fees", round(zeroifnull(purchased_usd),2) as "Items Purchased", round(zeroifnull(sold_usd),2) as "Items Sold", round(zeroifnull(rabbits),2) as "Net Rabbits Sold", round(zeroifnull(rabbits_avg_pur_price),2) as "Avg Price of Rabbits Purchased", round(zeroifnull(rabbits_avg_sold_price),2) as "Avg Price of Rabbits Sold", round(zeroifnull(inventors),2) as "Net Inventors Sold", round(zeroifnull(crystals),2) as "Net Crystals Sold", round(zeroifnull(chests),2) as "Net Chests Sold", -- round(zeroifnull(other),2) as "Net Other Sold", round(zeroifnull(tx_fees),2) as "Sol TX Fees" -- sold_usd, -- purchased_usd, FROM ac1 LEFT OUTER JOIN s1 using (wallet) LEFT OUTER JOIN s2 using (wallet) LEFT OUTER JOIN ff using (wallet) LEFT OUTER JOIN s3 using (wallet) LEFT OUTER JOIN s4 using (wallet) LEFT OUTER JOIN s14 using (wallet) ORDER by 3 DESC; -- -- -- with ele_rew1 as ( -- select -- date_trunc('week', block_timestamp::date) AS date, -- -- CASE -- -- WHEN log_messages[1] = 'Program log: Create' THEN 'Create' -- -- WHEN log_messages[1] = 'Program log: Instruction: ClaimElementerra' THEN 'ClaimElementerra' -- -- WHEN log_messages[1] = 'Program log: Instruction: ClaimNft' THEN 'ClaimNft' -- -- WHEN log_messages[1] = 'Program log: Instruction: Claim' THEN 'Claim' -- -- WHEN log_messages[1] = 'Program log: Instruction: UnstakeNft' THEN 'UnstakeNft' -- -- WHEN log_messages[1] = 'Program log: Instruction: UnstakeElementerra' THEN 'UnstakeElementerra' -- -- WHEN log_messages[3] = 'Program log: Instruction: ClaimElementerra' THEN 'ClaimElementerra' -- -- WHEN log_messages[3] = 'Program log: Instruction: ClaimNft' THEN 'ClaimNft' -- -- WHEN log_messages[3] = 'Program log: Instruction: Claim' THEN 'Claim' -- -- WHEN log_messages[3] = 'Program log: Instruction: UnstakeNft' THEN 'UnstakeNft' -- -- WHEN log_messages[3] = 'Program log: Instruction: UnstakeElementerra' THEN 'UnstakeElementerra' -- -- WHEN log_messages[3] = 'Program log: Instruction: ClaimCnft' THEN 'ClaimCnft' -- -- WHEN log_messages[5] = 'Program log: Instruction: ClaimElementerra' THEN 'ClaimElementerra' -- -- WHEN log_messages[5] = 'Program log: Instruction: ClaimNft' THEN 'ClaimNft' -- -- WHEN log_messages[5] = 'Program log: Instruction: Claim' THEN 'Claim' -- -- WHEN log_messages[5] = 'Program log: Instruction: UnstakeNft' THEN 'UnstakeNft' -- -- WHEN log_messages[5] = 'Program log: Instruction: UnstakeElementerra' THEN 'UnstakeElementerra' -- -- WHEN log_messages[5] = 'Program log: Instruction: ClaimCnft' THEN 'ClaimCnft' -- -- else 'Other' -- -- END AS Label, -- -- REPLACE(log_messages[0], 'Program log: Instruction: ', '') AS label, -- -- REPLACE(log_messages[1], 'Program log: Instruction: ', '') AS label1, -- -- REPLACE(log_messages[2], 'Program log: Instruction: ', '') AS label2, -- -- REPLACE(log_messages[3], 'Program log: Instruction: ', '') AS label3, -- -- REPLACE(log_messages[4], 'Program log: Instruction: ', '') AS label4, -- -- REPLACE(log_messages[5], 'Program log: Instruction: ', '') AS label5, -- -- REPLACE(log_messages[6], 'Program log: Instruction: ', '') AS label6, -- -- REPLACE(log_messages[7], 'Program log: Instruction: ', '') AS label7, -- -- block_timestamp, -- -- SIGNERS[0] AS wallets, -- -- tx_to, -- tx_from, -- -- count(distinct tx_from) as from_wallets, -- count(distinct tx_to) as to_wallets, -- -- count(DISTINCT TX_ID) AS txs, -- sum(amount) as value -- -- instruction:accounts[4] as cw -- from solana.core.fact_events -- -- LEFT join solana.core.fact_transactions -- -- using(tx_id,block_timestamp, succeeded) -- LEFT join solana.core.fact_transfers -- using(block_timestamp,tx_id) -- where succeeded -- and fact_events.block_timestamp > '2024-02-01' -- -- and block_id > 245286497 -- and fact_events.program_id = 'ELEMisgsfkmp58w1byRvrdpGG1HcapQoCrmMJeorBCxq' -- and fact_transfers.mint = '8A9HYfj9WAMgjxARWVCJHAeq9i8vdN9cerBmqUamDj7U' -- and amount > 0 -- and SIGNERS[0] = tx_to -- and fact_transfers.tx_to != '8ZCqJ3GXiXsmwqLnXbyBL5U7Tu59k6p4HwXc8ijfgQfU' -- -- instruction:accounts[4] -- -- and not fact_transfers.tx_to = fact_transfers.tx_from -- -- and array_contains('Program log: Instruction: LevelUp' ::variant, log_messages) -- GROUP by 1,2 -- -- ) -- SELECT -- date_trunc('week', s.block_timestamp::date) AS date, -- -- cs.wallet_label, -- SUM(zeroifnull(s.AMOUNT)) AS ele_reward, -- COUNT(distinct s.TX_TO) as ele_rwds_wallets -- FROM solana.core.fact_transfers s -- -- FROM cs -- -- LEFT JOIN solana.core.fact_transfers s ON cs.wallet = s.TX_TO -- WHERE s.tx_from = '8ZCqJ3GXiXsmwqLnXbyBL5U7Tu59k6p4HwXc8ijfgQfU' -- and s.mint = '8A9HYfj9WAMgjxARWVCJHAeq9i8vdN9cerBmqUamDj7U' -- AND block_timestamp > '2024-01-01' --AND block_timestamp < current_date -- GROUP BY 1 -- -- ele_rew AS ( -- SELECT -- s.date, -- s.Label, -- -- cs.wallet_label, -- count (DISTINCT s.wallets) as ClaimersElementerra, -- zeroifnull(SUM(s.value)) AS ClaimElementerra -- FROM -- ele_rew1 s -- -- FROM -- -- cs -- -- LEFT JOIN -- -- ele_rew1 s ON cs.wallet = s.wallets -- -- WHERE -- -- s.Label = 'ClaimElementerra' -- GROUP BY -- 1, 2 -- -- )

Elementerra Tokenomics Dashboard