with transactions as ( select block_timestamp, tx_receiver, tx_signer, tx_hash from near.core.fact_transactions where block_timestamp >= current_date() - interval '180 day' ), first_transactions as ( select tx_signer as wallet, min(block_timestamp) as first_tx from near.core.fact_transactions where block_timestamp >= current_date() - interval '60 days' group by 1 ), past_days as ( select CASE WHEN tr.tx_receiver IN ('token.sweat','tge-lockup.sweat','e589457354361489a89039b8be6737cbc2db4d13919b6ccf23889a60f3b0d8f3','ccb91e1db61e8d7e1d4ae3e043001140132959a86ee35a548b6563a46284a6ea','staking-pool.sweatmint.near') THEN 'Sweat Economy' WHEN tr.tx_receiver = 'embr.playember_reserve.near' THEN 'Play Ember' WHEN tr.tx_receiver LIKE '%fewandfar.near' THEN 'Few and Far' WHEN tr.tx_receiver in ('tickets.stlb.near', 'collectables.stlb.near', 'token.stlb.near') THEN 'SeatLab' WHEN tr.tx_receiver in ('social.near') THEN 'NEAR Social' WHEN tr.tx_receiver in ('app.nearcrowd.near','mdteam.near') THEN 'NEAR Crowd' WHEN tr.tx_receiver in ('login.learnclub.near','referralnft.learnclub.near','redeem.learnclub.near','referral.learnclub.near', 'learnernft.learnclub.near') THEN 'Learn NEAR Club' WHEN tr.tx_receiver in ('boostfarm.ref-labs.near','token.v2.ref-finance.near','v2.ref-finance.near','xtoken.ref-finance.near', 'v2.ref-farming.near', 'dclv2.ref-labs.near') THEN 'REF' WHEN tr.tx_receiver in ('marketplace.paras.near','x.paras.near','token.paras.near','staking.paras.near') THEN 'Paras' WHEN tr.tx_receiver LIKE '%.factory.bridge.near' THEN 'Rainbow Bridge' WHEN tr.tx_receiver LIKE '%.pool%' THEN 'Staking Pools' WHEN tr.tx_receiver in ('5c33c6218d47e00ef229f60da78d0897e1ee9665312550b8afd5f9c7bc6957d2','d73888a2619c7761735f23c798536145dfa87f9306b5f21275eb4b1a7ba971b9', '4b8a5f175fe1df178782f8c28ef71ad55363b5eddee1b525574fec447ff8a96c','1e6ef8464ce09f96c6fc22583ead00fa0b65119128daa9732366d2a2e856e960', '423df0a6640e9467769c55a573f15b9ee999dc8970048959c72890abf5cc3a8e','d93f6f23f55e5a4723e4c4dd063233947bb124d58ef948f6fab04a582e046c00', '2937e06f4ef2ad010c922bf7527cdaa9070a202a29f416f02f0138af2f3a9b72') THEN 'Exchanges' WHEN tr.tx_receiver in ('wrap.near') THEN 'Wrapped NEAR' WHEN tr.tx_receiver in ('spot.spin-fi.near','v2_0_2.perp.spin-fi.near','v1.vault.spin-fi.near') THEN 'SPIN' WHEN tr.tx_receiver in ('asset-manager.orderly-network.near') THEN 'Orderly' WHEN tr.tx_receiver in ('jumptoken.jumpfinance.near','jumphunter.near','nftstaking.jumpfinance.near','lockedjumptoken.jumpfinance.near','xjumptoken.jumpfinance.near', 'launchpad.jumpfinance.near') THEN 'Jump Finance' WHEN tr.tx_receiver in ('canvas-war.feiyu.near') THEN 'Feiyu' WHEN tr.tx_receiver in ('token.v1.realisnetwork.near') THEN 'Realis Network' WHEN tr.tx_receiver in ('ftv2.nekotoken.near','stake.nekotoken.near') THEN 'Neko' WHEN tr.tx_receiver in ('meta-pool.near','meta-token.near') THEN 'Metapool' WHEN tr.tx_receiver in ('storage.herewallet.near', 'nft.herewallet.near') THEN 'HERE Wallet' WHEN tr.tx_receiver in ('contract.main.burrow.near','token.burrow.near') THEN 'Burrow' WHEN tr.tx_receiver in ('v2-nearx.stader-labs.near') THEN 'Stader' WHEN tr.tx_receiver in ('linear-protocol.near') THEN 'LiNEAR' WHEN tr.tx_receiver = 'mintbus.near' or tr.tx_receiver like '%mintbase%' THEN 'Mintbase' WHEN tr.tx_receiver in ('token.pembrock.near','v1.pembrock.near','rewards.v1.pembrock.near') THEN 'Pembrock' WHEN tr.tx_receiver in ('fusotao.octopus-registry.near') THEN 'Octopus' WHEN tr.tx_receiver in ('zomland.near','ft.zomland.near') THEN 'Zomland' WHEN tr.tx_receiver in ('%.playible.near') THEN 'Playible' WHEN tr.tx_receiver in ('app.l2e.near','gold.l2e.near') THEN 'Land 2 Empire' WHEN tr.tx_receiver in ('usdt.tether-token.near') THEN 'USDT' WHEN tr.tx_receiver in ('devgovgigs.near') THEN 'devgovgigs' WHEN tr.tx_receiver in ('citizen.bodega-lab.near') THEN 'Bodega Lab' WHEN tr.tx_receiver in ('bearversegame_wallet.near') THEN 'Beavers' WHEN tr.tx_receiver in ('app-rewards.lazyfi-wallet.near.near','app-rewards.lazyfi-wallet.near') THEN 'LazyFI' WHEN tr.tx_receiver in ('market.tradeport.near') THEN 'TradePort' WHEN tr.tx_receiver in ('bvrs_the_last_winter.near') THEN 'Bearverse' WHEN tr.tx_receiver in ('token.cheddar.near') THEN 'Cheddar Farm' WHEN tr.tx_receiver in ('pixeltoken.near') THEN 'Cryptoheroes' WHEN tr.tx_receiver in ('artex.marbledao.near','dex.marbledex.near','farming.marbledex.near') THEN 'Marble DAO' WHEN tr.tx_receiver in ('phoenix-bonds.near') THEN 'Phoenix Bonds' WHEN tr.tx_receiver in ('token.dangelfund.near') THEN 'dAngel Fund' WHEN tr.tx_receiver in ('asac.marketplaces.near') THEN 'ASAC' WHEN tr.tx_receiver in ('token.elcafecartel.near','staking.elcafecartel.near') THEN 'El Cafe Cartel' WHEN tr.tx_receiver in ('launchpad.bocachica_mars.near') THEN 'Boca Chica' WHEN tr.tx_receiver in ('megapont.marketplaces.near') THEN 'Megapont' WHEN tr.tx_receiver in ('Bitverse_games.near') THEN 'Bitverse' when tr.tx_receiver in ('pack_minter.basketball.playible.near', 'game.basketball.playible.near', 'athlete.promotional.basketball.playible.near', 'pack.promotional.basketball.playible.near', 'open_pack.promotional.baskbetball.playible.near', 'pack_minter.playible.near', 'open_pack.nfl.playible.near', 'pack.pack_minter.playible.near', 'athlete.nfl.playible.near', 'game.nfl.playible.near', 'athlete.promotional.nfl.playible.near', 'pack.promotional.nfl.playible.near', 'open_pack.promotional.nfl.playible.near') then 'Playible' when tr.tx_receiver = 'raigarh_x_airchains.near' then 'Forest Aadhar' when tr.tx_receiver = 'sankalp_taru_zk.near' then 'Sankalpataru' when tr.tx_receiver = 'zupple_hpcl.near' then 'HPCL' when tr.tx_receiver in ('thelittles.near', 'stars.thelittles.near', 'prizes.thelittles.near') then 'The Little' when tr.tx_receiver in ('dock-sailgp.near', 'nft.dock-sailgp.near') then 'SailGP' when tr.tx_receiver in ('clashrowtoken.near', 'nft.clashrow.near') then 'Hamthor' when tr.tx_receiver = 'jungly-smartcontract-nft.near' then 'Nestle Jungly' when tr.tx_receiver = 'dropt.wallets.fewandfar.near' then 'Dropt' when tr.tx_receiver = 'stacksports.near' then 'StackSports' when tr.tx_receiver = 'rownd.near' then 'Rownd' when tr.tx_receiver = 'arterra.near' then 'Arterra Labs' when tr.tx_receiver in ('main.arkana.near', 'u.arkana.near') then 'Arkana by Paras' when tr.tx_receiver in ('fractal.i-am-human.near', 'registry.i-am-human.near') then 'I-AM-HUMAN' when tr.tx_receiver in ('dragon.recurforever.near', 'recur.recurforever.near', 'papaya.recurforever.near', 'pear.recurforever.near', 'mango.recurforever.near', 'apple.recurforever.near', 'cherry.recurforever.near', 'cherry.recurforever.near') then 'Recur' when tr.tx_receiver in ('wallet.kaiching', 'earn.kaiching','hotwallet.kaiching') then'Kai-Ching' when tr.tx_receiver in ('earn.preprod-kaiching.near', 'wallet.preprod-kaiching.near') then 'Cosmose AI - Preprod' when tr.tx_receiver like '%nearapac%' then 'NEAR APAC' when tr.tx_receiver like '%hideyourcash%' then 'HideYourCash' when tr.tx_receiver = 'starbox.herewallet.near' then 'HERE Wallet' when tr.tx_receiver = 'app.coinflow.near' then 'Coinflow' when tr.tx_receiver = '17208628f84f5d6ad33f0da3bbbeb27ffcb398eac501a31bd6ad2011e36133a1' then 'USDC' when tr.tx_receiver = 'a0b86991c6218b36c1d19d4a2e9eb0ce3606eb48.factory.bridge.near' then 'USDC.e' when tr.tx_receiver = 'anthropocene.seventhage.near' then 'Seventh Age' when tr.tx_receiver in ('elections.ndc-gwg.near', 'nominations.ndc-gwg.near', 'devgovgigs') then 'NDC' when tr.tx_receiver = 'fusotao-token.near' then 'Fustao' when tr.tx_receiver like '%jumbo_exchange%' then 'Jumbo Exchange' when right(tr.tx_receiver,5) <> '.near' THEN 'OTHER' else tr.tx_receiver end as "Contract Address", count(distinct tr2.tx_hash) as "Total Transactions", count(distinct case when tr.block_timestamp >= current_date - interval '30 Days' AND tr.block_timestamp < current_date THEN tr.tx_hash END) as "Transactions - 30 Days", count(distinct case when tr.block_timestamp >= current_date - interval '30 days' and tr.block_timestamp < current_date then tr.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 tr.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 tr.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 tr.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 tr.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 tr.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 tr.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 tr.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 tr.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 tr.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 tr.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 tr.tx_signer end)) / 7) as "Daily Average" --sum(tr.fee) as "Tx Fee" from transactions tr join first_transactions b on tr.tx_signer = b.wallet left join transactions tr2 on tr.block_timestamp = tr2.block_timestamp where tr.block_timestamp >= current_date - interval '90 days' and tr.block_timestamp < current_date group by 1 ), final as (select * from past_days union select 'Overall' as "Contract Address", count(distinct tr.tx_hash) as "Total Transactions", count(distinct case when tr.block_timestamp >= current_date - interval '30 Days' AND tr.block_timestamp < current_date THEN tr.tx_hash END) as "Transactions - 30 Days", 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" --sum(tr.fee) as "Tx Fee" from transactions tr join first_transactions b on tr.tx_signer = b.wallet where tr.block_timestamp >= current_date - interval '90 days' and tr.block_timestamp < current_date order by 2 desc limit 50) 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", "Total Transactions", --"Tx Fee", "Transactions - 30 Days", CASE WHEN "Past 30 Days" > 0 THEN ("Transactions - 30 Days" / "Past 30 Days")::DECIMAL(18,2) ELSE 0 END AS "Avg Txn per MAU" from final order by 2 desc

Query	Creator	Created
Atlas - New Aurora MAAs aurora near evm	No Name Saloon	Nov 8, 2023
AD - Total Supply (Atlas) near	No Name Saloon	Nov 14, 2023
Atlas - Top Apps	No Name Saloon	Nov 8, 2023
Atlas - Accounts Created near	No Name Saloon	Nov 8, 2023
Atlas - New NEAR MAAs	No Name Saloon	Nov 8, 2023
AD - Total Supply (Atlas) near	No Name Saloon	Nov 8, 2023
Atlas - MAAs near	No Name Saloon	Nov 8, 2023
Atlas - Accounts Created near	No Name Saloon	Nov 8, 2023

No Name Saloon

2 Members