SELECT "Contract Address", "Past 30 Days", "30-60 Days Ago", CASE WHEN "30-60 Days Ago" > 0 THEN (("Past 30 Days"/"30-60 Days Ago"-1)*100)::DECIMAL(18,2) ELSE 0 END AS "M/M", "New MAAs", CASE WHEN "M2 New MAAs" > 0 THEN ("Retained MAAs"/ "M2 New MAAs" * 100)::DECIMAL(18,2) ELSE 0 END AS "M2 Retention", "Daily Average", CASE WHEN "Past 30 Days" > 0 THEN ("Daily Average"/"Past 30 Days" * 100)::DECIMAL(18,2) ELSE 0 END AS "DAU / MAU" FROM (SELECT CASE WHEN tx_receiver IN ('token.sweat','tge-lockup.sweat','e589457354361489a89039b8be6737cbc2db4d13919b6ccf23889a60f3b0d8f3','ccb91e1db61e8d7e1d4ae3e043001140132959a86ee35a548b6563a46284a6ea','staking-pool.sweatmint.near') THEN 'Sweat Economy' WHEN tx_receiver = 'embr.playember_reserve.near' THEN 'Play Ember' WHEN tx_receiver in ('market.fewandfar.near', 'onboarding.wallets.fewandfar.near', 'royalties.fewandfar.near','fnf-club.collections.fewandfar.near') THEN 'Few and Far' WHEN tx_receiver in ('tickets.stlb.near', 'collectables.stlb.near', 'token.stlb.near') THEN 'SeatLab' WHEN tx_receiver in ('social.near') THEN 'NEAR Social' WHEN tx_receiver in ('app.nearcrowd.near') THEN 'NEAR Crowd' WHEN tx_receiver in ('login.learnclub.near') THEN 'Learn NEAR Club' WHEN tx_receiver in ('boostfarm.ref-labs.near','token.v2.ref-finance.near','v2.ref-finance.near','xtoken.ref-finance.near') THEN 'REF' WHEN tx_receiver in ('marketplace.paras.near','x.paras.near','token.paras.near','staking.paras.near') THEN 'Paras' WHEN tx_receiver LIKE '%.factory.bridge.near' THEN 'Rainbow Bridge' WHEN tx_receiver LIKE '%.pool%' THEN 'Staking Pools' WHEN tx_receiver in ('5c33c6218d47e00ef229f60da78d0897e1ee9665312550b8afd5f9c7bc6957d2','d73888a2619c7761735f23c798536145dfa87f9306b5f21275eb4b1a7ba971b9','4b8a5f175fe1df178782f8c28ef71ad55363b5eddee1b525574fec447ff8a96c','1e6ef8464ce09f96c6fc22583ead00fa0b65119128daa9732366d2a2e856e960') THEN 'Exchange' WHEN tx_receiver in ('wrap.near') THEN 'Wrapped NEAR' WHEN tx_receiver in ('spot.spin-fi.near','v2_0_2.perp.spin-fi.near') THEN 'SPIN' WHEN tx_receiver in ('asset-manager.orderly-network.near') THEN 'Orderly' WHEN tx_receiver in ('jumptoken.jumpfinance.near','nftstaking.jumpfinance.near','lockedjumptoken.jumpfinance.near') THEN 'Jump Finance' WHEN tx_receiver in ('canvas-war.feiyu.near') THEN 'Feiyu' WHEN tx_receiver in ('token.v1.realisnetwork.near') THEN 'Realis Network' WHEN tx_receiver in ('ftv2.nekotoken.near','stake.nekotoken.near') THEN 'Neko' WHEN tx_receiver in ('meta-pool.near','meta-token.near') THEN 'Metapool' WHEN tx_receiver in ('storage.herewallet.near') THEN 'HERE Wallet' WHEN tx_receiver in ('contract.main.burrow.near','token.burrow.near') THEN 'Burrow' WHEN tx_receiver in ('v2-nearx.stader-labs.near') THEN 'Stader' WHEN tx_receiver in ('linear-protocol.near') THEN 'LiNEAR' WHEN tx_receiver in ('simple.market.mintbase1.near') THEN 'Mintbase' WHEN tx_receiver in ('token.pembrock.near','v1.pembrock.near','rewards.v1.pembrock.near') THEN 'Pembrock' WHEN tx_receiver in ('fusotao.octopus-registry.near') THEN 'Octopus' WHEN tx_receiver in ('zomland.near') THEN 'Zomland' WHEN tx_receiver in ('game.basketball.playible.near','athlete.basketball.playible.near','athlete.promotional.basketball.playible.near') THEN 'Playible' WHEN tx_receiver in ('app.l2e.near','gold.l2e.near') THEN 'Land 2 Empire' WHEN tx_receiver in ('usdt.tether-token.near') THEN 'USDT' WHEN tx_receiver in ('devgovgigs.near') THEN 'devgovgigs' ELSE CONCAT(LEFT(tx_receiver,20),'...') END AS "Contract Address", COUNT(DISTINCT CASE WHEN tr.block_timestamp >= CURRENT_DATE - INTERVAL '30 Days' AND tr.block_timestamp < Current_date THEN tx_signer END) as "Past 30 Days", COUNT(DISTINCT CASE WHEN tr.block_timestamp >= CURRENT_DATE - INTERVAL '60 Days' AND tr.block_timestamp < Current_date - INTERVAL '30 Days' THEN tx_signer END) as "30-60 Days Ago", COUNT(DISTINCT CASE WHEN first_tx >= current_date - INTERVAL '30 DAYS' AND first_tx < current_date THEN tx_signer END) AS "New MAAs", COUNT(DISTINCT CASE WHEN first_tx >= current_date - INTERVAL '60 DAYS' AND first_tx < current_date - INTERVAL '30 DAYS' AND tr.block_timestamp >= CURRENT_DATE - INTERVAL '30 Days' AND tr.block_timestamp < Current_date THEN tx_signer END) AS "Retained MAAs", COUNT(DISTINCT CASE WHEN first_tx >= current_date - INTERVAL '60 DAYS' AND first_tx < current_date - INTERVAL '30 DAYS' THEN tx_signer END) AS "M2 New MAAs", ((COUNT(DISTINCT CASE WHEN tr.block_timestamp >= CURRENT_DATE - INTERVAL '24 Hours' AND tr.block_timestamp < Current_date THEN tx_signer END) + COUNT(DISTINCT CASE WHEN tr.block_timestamp >= CURRENT_DATE - INTERVAL '48 Hours' AND tr.block_timestamp < Current_date - INTERVAL '24 Hours' THEN tx_signer END) + COUNT(DISTINCT CASE WHEN tr.block_timestamp >= CURRENT_DATE - INTERVAL '72 Hours' AND tr.block_timestamp < Current_date - INTERVAL '48 Hours' THEN tx_signer END) + COUNT(DISTINCT CASE WHEN tr.block_timestamp >= CURRENT_DATE - INTERVAL '96 Hours' AND tr.block_timestamp < Current_date - INTERVAL '72 Hours' THEN tx_signer END) + COUNT(DISTINCT CASE WHEN tr.block_timestamp >= CURRENT_DATE - INTERVAL '120 Hours' AND tr.block_timestamp < Current_date - INTERVAL '96 Hours' THEN tx_signer END) + COUNT(DISTINCT CASE WHEN tr.block_timestamp >= CURRENT_DATE - INTERVAL '144 Hours' AND tr.block_timestamp < Current_date - INTERVAL '120 Hours' THEN tx_signer END) + COUNT(DISTINCT CASE WHEN tr.block_timestamp >= CURRENT_DATE - INTERVAL '168 Hours' AND tr.block_timestamp < Current_date - INTERVAL '144 Hours' THEN tx_signer END)) / 7) AS "Daily Average" FROM (SELECT block_timestamp, tx_receiver, tx_signer FROM near.core.fact_transactions) tr JOIN (SELECT tx_signer AS wallet, MIN(block_timestamp) AS first_tx FROM near.core.fact_transactions tr GROUP BY 1) b ON tr.tx_signer = b.wallet WHERE tr.block_timestamp >= CURRENT_DATE - INTERVAL '90 Days' AND tr.block_timestamp < Current_date GROUP BY 1) a ORDER BY 2 DESC LIMIT 100

NEAR Ecosystem Health