Here's an example:

The previous example shows an interesting fact: before the SCR airdrop event on this chain in October 2024, the retention rate was high, but after this event the share of users who came later dropped significantly, indicating 'farming' activity.

In this post, I’ll show you how to dissect a query to build these tables on Dune Analytics, which you can easily adapt to your own needs. The goal of this tutorial is to create a query that groups new users into monthly cohorts and measures how many return over time—also known as MoM retention—specifically for Scroll, using Dune.

If you just want to see the query go to the end of the article.

For this we need to create 4 Common Table Expressions:

• user_cohorts: Finds every unique address that interacted with the protocol and the first time they interacted.

• following_months: Finds the addresses from the original cohort that transacted in the months following.

• cohort_size: Counts the number of addresses in the original cohort

• retention_table: Counts the number of addresses from the original cohort in the months following.

Step 1: User Cohorts

Our starting point is to gather addresses that had sent a transaction on scroll, se we need to use the scroll.transactions table, where we use the columns "from" which indicates the address of the user sending a transaction and "block_date", the date of transactions.

WITH user_cohorts AS (
    SELECT "from" as address,
           date_format(min(date_trunc('month', block_date)), '%Y-%m') as cohort_month_text,
           min(date_trunc('month', block_date)) as cohort_month_date
    FROM apechain.transactions
    WHERE success = true
    GROUP BY 1
)

What this does:

• Groups all transactions by sender address

• Finds the earliest month each address transacted

• Creates both a formatted text version (2024-08) and date version for calculations (this is to use the pivot table element of dune at the end)

Now you have a long list of addresses and the first month they sent a transaction to Scroll.

Step 2: Following Months

Purpose: Identify which addresses from the original cohort are returning each month and calculate how many months have passed since their first transaction.

What you need to identify are the unique addresses from the original cohort that are returning each month. Therefore, join the user_cohorts table (first CTE with the cohort addresses) with the transactions table on the address column. This join will enable you to find the number of addresses matching the unique addresses in the original cohort.

following_months AS (
    SELECT tx."from" as address,
           lpad(cast(date_diff('month', uc.cohort_month_date, date_trunc('month', tx.block_date)) as varchar), 2, '0') as month_number
    FROM scroll.transactions tx
    LEFT JOIN user_cohorts uc ON tx."from" = uc.address
    WHERE tx.success = true
    GROUP BY 1, 2
)

Now you need to find how many months have passed since the original cohort. The original cohort month is represented as cohort_month_date in the user_cohorts table. Use the function date_diff() to find the difference between two dates, this function needs to be passed three arguments labeled unit, startdate and enddate:

date_diff('unit', startdate, enddate)

These 3 mandatory arguments combine into the SELECT statement:

1. unit: 'month'

2. startdate: uc.cohort_month_date - The date of the cohort

3. enddate: date_trunc('month', tx.block_date) - Each month between the cohort date and the current transaction date

date_diff('month', uc.cohort_month_date, date_trunc('month', tx.block_date))

What this does:

• Joins every transaction back to the user cohort data

• Calculates months elapsed since first transaction using date_diff()

• Uses lpad() to format month numbers with leading zeros (01, 02, ... 10, 11, 12) for proper pivot table sorting

Interpreting the results: Every address will show up at least once with 0 as month_number (month 0 represents the month they first transacted). If the address has 1 in the month_number column, then that address returned the next month. In the example above:

• Address 0xffffff21a685e8cfdd04576ae2c018858edf0d7a only transacted in their first month

• Address 0xffffff044f0734354d308bb120b6916bf85a797a returned in months 1, 2, 3, 4, and 7 after they first transacted

Step 3: Cohort Size

The next CTE - cohort_size - is designed to count the number of addresses in the original cohort - so it’s extremely easy. The following query is counting every entry in user_cohorts table by cohortMonth.

cohort_size AS (
    SELECT uc.cohort_month_text as cohort_month,
           count(*) as new_users
    FROM user_cohorts uc
    GROUP BY 1
)

Step 4: Retention Table

The fourth and final CTE - retention_table - aims to count how many addresses from the initial cohort transact in the months to follow. This table will show the number of users from the original cohort x number of months after they first joined.

There are two tables that you need to query FROM:

1. following_months is needed to count the number of addresses from the original cohort that transact in the months following

2. user_cohorts is needed to match addresses from the original cohort with ones in the months following

Now you can LEFT JOIN the following_months table with the user_cohorts table by the addresses:

retention_table AS (
    SELECT c.cohort_month_text as cohort_month,
           fm.month_number,
           count(*) as retained_users
    FROM following_months fm
    LEFT JOIN user_cohorts c ON fm.address = c.address
    GROUP BY 1, 2
)

As you can see, the retention_table returns the number of users from the original cohort x number of months after.

Step 5: Retention Rate

MoM retention doesn't require individual addresses so the FROM statement shouldn't include user_cohorts and following_months - these tables were used in the other CTEs.

To calculate retention you need to query the user counts from the retention_table and cohort_size tables. These two tables can be joined on the cohort month:

SELECT *
FROM retention_table r
LEFT JOIN cohort_size s 
    ON r.cohort_month = s.cohort_month

Now you need to have four columns to create a retention table. The SELECT statement should show:

1. The initial cohort date - r.cohort_month

2. The initial cohort size - s.new_users

3. The number of months since the initial cohort month - r.month_number

4. The percentage of the original cohort that remains x months (r.month_number) after the initial cohort (r.cohort_month). This can be found by taking the number of users that remain (r.retained_users) and dividing it by the size of the initial cohort (s.new_users).

Retention rate = r.retained_users / s.new_users

The final SELECT statement looks like this:

SELECT r.cohort_month,
       s.new_users,
       r.month_number,
       cast(r.retained_users as double) / cast(s.new_users as double) as retention_rate
FROM retention_table r
LEFT JOIN cohort_size s ON r.cohort_month = s.cohort_month
WHERE r.month_number != '00'  -- Exclude month 0 (first transaction month)
ORDER BY r.cohort_month, r.month_number

Interpreting the results: From the October 2023 cohort of 268,396 new users:

• 54.49% returned in October 24 (month 12) (Airdrop month)

• 21.02% returned in December 24 (month 13)

• 14.23% returned in January 25 (month 14)

Final query:

So the final query looks like:

WITH user_cohorts AS (
    SELECT "from" as address,
           date_format(min(date_trunc('month', block_date)), '%Y-%m') as cohort_month_text,
           min(date_trunc('month', block_date)) as cohort_month_date
    FROM scroll.transactions
    WHERE success = true
    GROUP BY 1
),
following_months AS (
    SELECT tx."from" as address,
           lpad(cast(date_diff('month', uc.cohort_month_date, date_trunc('month', tx.block_date)) as varchar), 2, '0') as month_number
    FROM scroll.transactions tx
    LEFT JOIN user_cohorts uc ON tx."from" = uc.address
    WHERE tx.success = true
    GROUP BY 1, 2
),
cohort_size AS (
    SELECT uc.cohort_month_text as cohort_month,
           count(*) as new_users
    FROM user_cohorts uc
    GROUP BY 1
),
retention_table AS (
    SELECT c.cohort_month_text as cohort_month,
           fm.month_number,
           count(*) as retained_users
    FROM following_months fm
    LEFT JOIN user_cohorts c ON fm.address = c.address
    GROUP BY 1, 2
)
SELECT r.cohort_month,
       s.new_users,
       r.month_number,
       cast(r.retained_users as double) / cast(s.new_users as double) as retention_rate
FROM retention_table r
LEFT JOIN cohort_size s ON r.cohort_month = s.cohort_month
WHERE r.month_number != '00'
ORDER BY r.cohort_month, r.month_number

Key Technical Notes for Dune

1. Month Number Formatting: Use lpad() with zero-padding to ensure proper sorting in pivot tables

2. Date Formatting: Use date_format() to convert dates to strings for better pivot table compatibility

3. Casting: Always cast retention calculations as double for decimal precision

4. Filtering: Exclude month 0 (WHERE month_number != '00') since 100% retention in first month is guaranteed

Visualization of the Pivot Table

• Rows: cohort_month

• Columns: month_number

• Values: retention_rate

• Format: Use percentage format for retention rates (0.00%)

Account dusting and address poisoning are deceptive practices on the Solana blockchain that exploit users through low-value transactions. Dusting floods account addresses with tiny SOL transfers to promote something (bots, casinos, tools, etc.) - it's crypto spam, while address poisoning tricks users into sending value to malicious account addresses that mimic legitimate ones. This report details our investigation into these actions and provides a public dashboard on Dune and an open source API that uses Solscan endpoints to assess transaction risk in real time, improving the security of wallets and explorers.

Ogbame Justus (✸,✸)

@ogbame_justus

I don't know how this works so someone please explain to me, why do I almost always see sol casino or flip.gg stuff in my TX when I scan it, I don't use any of it, why does my tx route through there, wtf goes on there. Is it a node or what

1

7:13 AM • Jan 6, 2025

Deliverables:

• Dune Dashboard

• API

Account Dusting

On Solana, SOL is the native currency used for gas fees and network staking. Dusting involves sending negligible SOL amounts—often less than 10 lamports (1 billion (1,000,000,000) lamports is 1 SOL)—to thousands of account addresses, typically to advertise shady platforms like casinos, bots or 'tools'. Over the past week, 5.12 million dust transactions targeted 4.23 million addresses, totaling just $11 USD. These transfers often cluster around specific domains or wallets, with identifiable “duster” addresses executing thousands of transactions.

On chain data reveals a tightly coordinated operation dominated by a few key players. The Accounts Dusted Hourly chart, covering seven days of data, shows that approximately 150,000 account addresses are targeted every hour by around 40 consistent duster account addresses, each executing at least 100 transactions to 100 distinct account addresses. One duster, linked to a Solana domain name associated with “Odin bot,” drives massive spikes every 12 hours, suggesting manual launches or cron-timed automation. These bursts indicate a deliberate strategy to spam account addresses at predictable intervals.

The SOL Dusting Market Share area chart underscores the concentration of this activity. Account addresses tied to “casino flip.gg” consistently dominate, accounting for 74% of dusting transactions. When combined with an unknown account address, 3U6G, and the Odin bot-related account address, these three players control 93% of the market share. During the Odin bot’s 12-hourly surges, their grip tightens to 97%, revealing a near-monopoly on dusting campaigns.

The Odin bot-related account address targets 20,000 account addresses every 12 hours, while casino flip.gg bots collectively hit 10,000. Smaller dusters, by contrast, rarely exceed 500 account addresses per hour, emphasizing the disparity between major players and fringe operators. This pattern points to a hierarchical structure, with well-resourced account addresses orchestrating large-scale spam while others play a minor role.

The Total SOL Dusted by Hour (USD) reveals the negligible value transferred: less than $0.06 USD per hour to target 150,000 account addresses. This trivial cost, paired with the high volume of transactions, explains the profitability of dusting. Spammers can flood accounts with minimal investment, banking on even a tiny conversion rate to scams or bots to turn a profit.

In summary, dusting activity is concentrated among four key account addresses, primarily promoting casino tools and bots. Their coordinated, automated approach exploits Solana’s low-cost transactions to spam millions of account addresses with ease. A complete list of these duster account addresses and their activities is available on the Dune dashboard.

Account Poisoning

Address poisoning is a sophisticated scam that, like dusting, leverages low-value transactions but with a malicious twist: attackers send tiny SOL or token transfers from account addresses mimicking legitimate ones (e.g., sharing the first four characters) to trick users into sending funds to the wrong account address.

Scam Sniffer | Web3 Anti-Scam

@realScamSniffer

Another massive loss:
$2.91M lost - Solana user copied wrong CEX deposit address from tx history.

163

12:19 AM • Nov 25, 2024

Investigating this is no small feat, as Solana’s transaction volume surpasses EVM blockchains by orders of magnitude, demanding careful resource allocation for queries. To understand how poisoning typically unfolds, we built a base query around a common pattern:

• A "high-value" withdrawal is performed, usually to an identified CEX account address.

• A micro-SOL transfer occurs from a similar-looking account address to the victim’s destination account address, typically minutes later.

• We check afterward if the victim sent funds to the scammer’s account address.

  
  

This pattern shows how scammers work: they hope users quickly check their recent transactions and mix up a fake account address with one they trust, like mistaking a stranger for a friend. Our query, run over a 12-hour period, uncovered a large wave of these scams. Scammers targeted 1,577 account addresses, creating 4,349 fake account addresses to launch 9,234 poisoning attempts. These attacks were fast, hitting just 9.74 minutes after a legitimate transaction on average, taking advantage of when users are likely to check their recent activity.

Due to data constraints, we only tracked 10 minutes after each poisoning attempt to see if victims sent funds to the fake account address, keeping the query simple for faster results. Extending this window would require more processing time, but an alternative could be checking if these fake account addresses later received funds from victims. The Time to Poisoning Attempt scatter plot tracks this quick timing across recent hours, while the Poisoning Activity column chart highlights the focus on high-value transfers (>$10k), and the Targeted Accounts & Poisoning Attempts line chart shows how often these scams strike, with up to four attempts per transaction. Notably, some fake account addresses aren’t newly created; they seem to mimic CEX account addresses, possibly to track deposits. A list of these account addresses is available, and future work—limited by time—could trace their funding sources, attempt frequency, and success rates to better understand these tactics.

Building an API

To combat address poisoning, we built an open-source API for real-time detection, designed to slot into wallets, explorers, or RPC layers. It takes two inputs: from_address_check (sender account address) and to_address_check (recipient account address). Using Solscan’s account/transfer and transaction/detail endpoints, it digs into the sender’s transaction history, pinpointing prior interacting account addresses and their first interaction block.

API Functionality

1. Prefix Matching: The API checks if any account address that previously sent funds to the sender shares the first four characters with to_address_check. The earliest such account address (by block number) is marked safe (risk score: 0); later matches are flagged as risky (score: 50).

2. Low-Value Check: If the first interaction was a low-value SOL transfer (<10 lamports), the risk score jumps to 90, signaling a potential dusting or poisoning attempt.

3. Token Transfer Validation: For non-SOL transfers, the API assumes legitimate test transfers are ≥$1. Subsequent transfers in the same token must be ≥1/5 of the original value; otherwise, the risk score hits 90.

This setup keeps false positives low, as the odds of two random Solana account addresses sharing the first four characters are ~0.0005. For high-transaction account addresses (>1,000 interactions), the collision probability creeps to 0.512%, so our layered checks add reliability. The API’s lean design ensures fast queries, critical for real-time wallet integration.

Technical Architecture

• Inputs: from_address_check, to_address_check

• Data Source: Solscan API (/v2.0/account/transfer, /v2.0/transaction/detail)

• Logic Flow:

  • Fetch sender’s transaction history.

  • Identify prior senders and their first interaction blocks.

  • Compare to_address_check’s first four characters with prior senders.

  • Apply risk scoring based on timing, SOL value, and token transfer thresholds.

• Output: Risk score (0, 50, 90) for the recipient account address.

Challenges & Solutions

• False Positives: Mitigated by trusting the earliest matching account address and enforcing strict value thresholds.

• Scalability: Optimized Solscan API calls for low latency, caching frequent queries.

• Evolving Tactics: Adaptive risk scoring handles multi-wallet obfuscation.

Conclusion

Account dusting is dominated by a small number of persistent account addresses that automate spam-like behavior to promote bots and gambling tools. Address poisoning, while less frequent, poses a more deceptive risk and requires further investigation to fully understand how these fake addresses are created and funded. Although our data window is limited, the Dune dashboard offers a clear view of these activities over the past seven days, and our open-source API provides real-time risk scoring to help wallets, explorers, and developers guard against suspicious interactions.

Realms appeared in 2022, marking a turning point with a peak in activity that same year. While it is true that there have been dips and falls, it is undeniable that in recent months there has been a notable increase in activity. Many of these DAOs are trial runs, but it is clear that some of them have great potential and deserve to be noticed.

The platform processes around 800 and 400 proposals per month, with around 60 and 120 active DAOs using it, of course not all of them are 'DAOs', in fact if you are a purist you might think that there are no DAOs here. Some of them have just a few members, others a few dozen, with more or less, as we will see, some of them manage treasuries of millions of dollars, others none, but they are still interesting because they determine important chain activity on Solana.

The purpose of this article is to highlight some of the DAOs on Realms. I have taken metrics from DAOs that have been active in the current year.

The data used to compile this article comes from Flipside, which has tables to track proposal and voting activity in realms. Unfortunately, this table does not include Pyth and Metaplex, two quite important DAOs in the Solana ecosystem. Anyway, I'll talk about them later.

Alongside this article I built a dashboard tracking Realms activity here.

BONK DAO

Website, Realms, Token Address, X

The most obvious starting point is the DAO with the highest number of unique voters: BONK DAO, a DAO made from a memecoin, which has 14,805 unique voters in its entire history. However, this year it has only had two submissions with eight voters, so it is not a strong contender.

BONK!!!

@bonk_inu

Two new @bonk_dao proposals have been made:

- One for a BONK November Burn of 100B $BONK
- Another for a burn of 1T $BONK in celebration of the 2nd anniversary of BONK

Users who hold $BONK or have $BONK locked in BONKrewards can vote on them here

bonkdao.com

2,145

3:53 PM • Nov 8, 2024

The latest proposal, from November 2024, which proposed to remove a large number of BONK tokens from circulation, received 9,000 votes that month. However, what is not small is the treasury of around 60 million US dollars, equivalent to 4.4 billion BONK tokens.

SolBlaze DAO

Website, Realms, Token Address, X

SolBlaze DAO governs BlazeStake, a Solana liquid staking protocol. In 2025, it recorded 1 proposal with 18 unique voters and 1 proposer, last active on January 29. The latest proposal aimed to tweak delegation strategies and pool parameters to boost APY while supporting diverse validators (SolBlaze Docs). Governance uses BLZE tokens, with voting power amplified by locking tokens, potentially unlocking rewards like staking bonuses.

While the DAO’s activity is minimal this year, its focus on optimizing staking returns and validator support offers tangible value. Members can influence treasury allocations, but outdated docs suggest governance hasn’t evolved much since 2024.

Mango

Website, Realms, Token Address, X

Mango DAO, tied to a once-thriving decentralized exchange and lending protocol, announced a platform wind-down, yet governance persists. In 2025, it saw 39 proposals, 18 unique voters, and 6 proposers, active from January 2 to April 2. Its treasury, holding ~$240M USD, fuels ongoing decisions, likely on asset management or protocol transitions. The low voter count suggests concentrated governance, but the high proposal volume shows commitment.

Mango

@mangomarkets

Reminder

Mango Markets is closing on February 3rd, 2025.

Please close all positions and exit your liquidity with us, we do not want any users force liquidated on Feb 3rd.

17

12:54 PM • Jan 23, 2025

Members govern via MNGO tokens. Mango’s v4 never surpassed Mango's v3 in usage.

There are no longer any benefits for MNGO holders, it looks like most of the liquidity has been taken out, but it will be interesting to see how the $240m treasury is managed.

Island DAO

Website, Realms, Contract Address, X

Island DAO fosters Solana’s developer community through events like co-working retreats, offering networking and collaboration perks. In 2025, it recorded 62 proposals, 57 unique voters, and 7 proposers, active from January 3 to April 10. Most proposals focus on treasury management, leveraging the ISLAND token for governance.

The high proposal count reflects an engaged community shaping events and funding. Members enjoy exclusive access to retreats, boosting Solana’s ecosystem through real-world connections—a unique value proposition using Realms infrastructure.

Artrade DAO

Website, Realms, Token Address, X

Artrade DAO governs a marketplace for real-world art and luxury items—like watches and sculptures—authenticated and sold in $ATR tokens at a 10% discount. Staking ATR grants DAO membership, unlocking up to 40% discounts, revenue shares, ATR APY, and voting on listings and platform governance (Artrade Collection). In 2025, it had 6 proposals, 62 unique voters, and 2 proposers, active from January 9 to April 10.

Artrade

@ArtradeApp

One of the addresses that votes for this proposal will win this Longines Master Collection watch.

Free shipping included.

Good luck $ATR stakers

app.realms.today/dao/SATR/propo…

121

11:10 AM • Apr 5, 2025

The limited proposers suggest centralized governance, but high voter turnout shows community interest. Artrade’s tokenization of physical assets offers a novel use case, blending blockchain with tangible goods.

DarkDAO

Website, Realms, Token Address, X

DarkDAO focuses on open-source, zkSNARK-powered anonymity on Solana. In 2025, it had 2 proposals with 23 unique voters and 2 proposers, active from February 15 to February 21. A notable proposal requested 11-24 SOL to deploy a ZK-SNARK program and launch shielded pools, marking a step toward verifiable privacy.

Dark Protocol

@privateLP

Contributors have finalised the first fully distributed and anonymous deployment of a Solana contract - funded by historic DAO revenue.

"dark-protocol-core" otherwise known as legacy zkSNARK privacy akin to 'tornado-cash' is now deployed to @Solana

explorer.solana.com/address/DARK4o…

114

6:01 PM • Jan 3, 2025

With minimal activity and a treasury of 100k USD of mostly Dark Tokens, perks are unclear, but contributors may earn for protocol work. DarkDAO’s niche focus on privacy tech makes it intriguing, though its small scale limits broader impact.

Saga Mobile DAO

Website, Realms, Token Address, X

Saga Mobile DAO leads 2025 metrics with 199 unique voters and 11 proposals, active from 11 February to 5 April, governing community-native hardware such as Android phones. Members shape product features, distribution and token rewards through proposals.

However, concerns remain: governance tokens lack liquidity and the Saga treasury holds two tokens. Which is the real one? Difficult to say, and all the token information platforms seem incapable of gathering information about the tokens.

Most voters were active in February, and that's what makes it the second DAO with the most unique voters for 2025 on Realms, but the later activity in March and this month is very low.

Also, having an incomplete website is a red flag about the value they bring, are they just going by reactions on social?

Update: After posting a thread with this information the Saga X account commented that a Soulbound Saga Genesis NFT is attached to each Solana Mobile Saga Phone, Limiting 1 vote to 1 device. So the governance tokens are not tokens but NFT, also their website is being updated soon.

Saga DAO

@SagaMobileDAO

Interesting metrics! If you want info on our Governance Token, we actually use the Soulbound Saga Genesis NFT that is attached to each Solana Mobile Saga Phone. Limiting 1 vote to 1 device.

Also the website that has some Latin Text is still being worked on, will be done soon!

1

2:25 PM • Apr 14, 2025

Orca

Website, Realms, Token Address, X

Orca DAO, governing one of Solana’s leading decentralized exchange, ranks second with 109 unique voters for 5 proposals in 2025, active from February 14 to April 7. ORCA token holders vote on AMM operations, potentially earning trading fees or liquidity rewards. Governance is user-friendly, focusing on exchange upgrades and community initiatives.

Orca DAO has maintained steady governance activity since 2022, though proposals historically attracted fewer than 30 voters—until a pivotal April 2025 vote. The current "Tokenholder Proposal for xORCA, Buybacks, and Burn" (under voting at the moment of this writing) could mark a turning point, already drawing 100+ voters. The plan outlines:

• $10M in open-market ORCA buybacks (funded by treasury reserves).

• Burning 25% of total token supply to reduce circulation.

• Long-term funding for the initial dev team (2+ years).

• Redirecting protocol fees to buybacks for staking incentives.

The surge in voter participation suggests heightened community alignment, likely driven by the proposal’s direct economic impact (e.g., deflationary burns and buybacks). If passed, it would be Orca’s most consequential governance decision to date—a test of whether decentralized exchanges can balance incentives, sustainability, and decentralization.

AdrenaDAO

Website, Realms, Token Address, X

AdrenaDAO governs a trading protocol improving on GMX’s 2-token model, with a $500K treasury. In 2025, it had 30 proposals, 69 unique voters, and 2 proposers, active from January 14 to April 10. ADX token holders vote on governance, earning USDC yield from fees, with staked ADX offering bonuses and multipliers. Revenue splits 70% to ALP holders, 20% to ADX stakers, and 10% for ADX buybacks.

High proposal volume suggests active governance, with clear benefits such as yield and influence over trading mechanics, making Adrena a dynamic DAO, yet the number of voters is low.

EpicentralDAO

Website, Realms, Token Address, X

EpicentralDAO runs the show for Epicentral Labs - a Solana project building decentralized options trading through their Solana Options Standard (SOS) and OPX exchange. So far in 2025, they've seen modest activity: 18 proposals, just 24 unique voters, and only 4 people actually putting proposals forward.

What's interesting is the LABS token distribution. With 54.6 million tokens split between public sales, DeFi incentives, and treasury holdings, they've got the resources to make moves. But here's the catch - their protocol isn't fully live yet.

Epicentral Labs

@EpicentralLabs

Shoutout to some of our amazing contributing DAO members for adding @CabanaExchange on our main website!

Using the provided link below will automatically grant you 10% discount on all trades & supports the growth of our DAO Treasury

Trade $LABS on Cabana

17

1:08 AM • Apr 10, 2025

This DAO could actually matter if they:

1. Successfully launch their options trading platform

2. Keep using the DAO for real governance decisions

3. Get more than two dozen people to care about voting

Right now it's all potential. The voter numbers are weak, but if the tech delivers and traders start using it, EpicentralDAO might transform from a sleepy governance experiment into something that actually impacts Solana's derivatives market.

Collaterize DAO

Website, Realms, Token Address, X

Collaterize DAO governs a mobile app and L1 protocol for tokenizing and trading real-world assets (RWAs). In 2025, it had 31 proposals, 32 unique voters, and 6 proposers, active from February 18 to April 12. COLLAT holders manage a treasury investing in RWAs like real estate or bonds, driving returns that strengthen the ecosystem.

With active governance since february of the current year, members vote on asset allocations, enjoying potential yields and influence, making Collaterize a promising RWA-focused DAO.

However, my personal view is that RWA protocols have a long way to go before they prove their viability, and many more projects are coming on stream, so it will become a competitive sector.

Grape

Website, Realms, Token Address, X

Grape DAO governs a unique identity and access protocol on Solana, they offer several tools for DAO managers like this one. Their "Dynamic Balance-Based Membership" system grants permissions (e.g., exclusive community access) based on token holdings—supporting SPL tokens, LP pairs, and multi-coin configurations. The DAO also emphasizes censorship resistance, storing user roles and achievements on-chain to rebuild communities if de-platformed.

In 2025, Grape recorded 22 proposals, 35 unique voters and 8 proposers active from January to April. Proposals ranged from budget approvals for crypto conferences (e.g. "2025 Event Participation") to internal treasury transfers (e.g. moving 332K USDC and 657M GRAPE tokens). Despite a treasury of $840K, voter participation has been declining from its peak in 2022.

Mail DAO

Website, Realms, Token Address, X

Mail DAO governs SolMail, an experimental communication protocol that lets users send mail using just a wallet address. On paper, it solves a real need - decentralized, spam-resistant communication tied to wallet identities. But right now, the numbers don't inspire confidence: just 2 proposals, 3 voters, and a modest $100K treasury since launching in 2024.

For now, it's more of a "watchlist" candidate than an active governance success. But in a world where wallet-based identity grows, don't be surprised if Mail DAO either quietly disappears or becomes one of those "why didn't we take this seriously earlier?" projects.

Pyth DAO

Website, Realms, Token Address, X, Forum, Constitution

Pyth DAO governs the Pyth Network, a key player in Solana’s ecosystem that delivers real-time price feeds for DeFi apps. It kicked off with a 250 million PYTH token airdrop, where about 181 million tokens (72%) were claimed by 56,000 wallets out of 90,000 eligible ones (source). Using Realms, PYTH token holders can propose and vote on things like oracle fees and network upgrades, making it a hub for community-driven decisions.

Pyth DAO’s governance is defined by its constitution, approved on February 13, 2024, with 627,748,981 votes in favor and 873,059 against (Pyth Constitution Live). The constitution outlines:

• Pythian Council: An eight-member elected body managing operational tasks like oracle program upgrades and data request fees. In March 2024, four members were elected with over 2,000 voters participating (Pythian Council Launch).

• Price Feed Council: Oversees data feed integrity and publisher onboarding.

• Community Council: Supports broader initiatives, including grants and ecosystem growth.

• Pyth Improvement Proposals (PIPs): Proposals start as discussions on the Pyth Forum and move to Realms for on-chain voting, typically over seven days (Voting Guide).

Staking PYTH tokens grants voting power over Pyth DAO, with potential benefits from network growth. The DAO actively funds development through its Pyth Grants program, supporting builders and researchers, while contributors can earn recognition or funding by submitting Pyth Improvement Proposals (PIPs). The process is streamlined: stake tokens to participate, discuss ideas on the forum, submit formal PIPs, and vote on-chain through Realms.

In April 2025, Pyth DAO approved OP-PIP-59, greenlighting four new Pythian Council members (including reps from Douro Labs and Euler Labs) with 100,000 PYTH allocated for their 6-month stipends. The vote saw clear dominance from institutional players - Douro Labs' candidate secured 51.8M votes, dwarfing other nominees. Notably, this operational proposal required no immediate token transfers, focusing instead on wallet address updates for council multisig access.

Metaplex DAO

Website, Realms, Token Address, X

Metaplex provides core infrastructure for Solana's NFT and token ecosystem. Their protocols handle metadata standards and minting tools used by most Solana NFT projects, processing over 1 million token mints monthly. With a $40M treasury, it's one of the better-funded DAOs in the ecosystem.

The DAO makes big moves:

• Recently approved 1M MPLX tokens ($2M+) for liquidity incentives

• Executed major token buybacks (100bps of supply in January)

• Processes millions in protocol fees monthly

Discussions happen on their GitHub. Flipside's analytics can't track it due to their custom program AEauWRrpn9Cs6GXujzdp1YhMmv2288kBt3SdEcPYEerr, Realm's UI also fails to display member counts, making actual decentralization hard to assess.

Conclusions

As you have seen, there are many types of DAOs: from thousands of members to just a few; from managing treasuries of millions to managing none at all. The options are diverse and many projects use the Realms platform to create and manage their DAOs, which speaks to the strength it has maintained since 2022.

Many projects look promising but it is important that you DO YOUR OWN RESEARCH before participating in any governance.

In my opinion, there is still a lack of tools that can contribute to the decentralization and the accountability of the DAOs.

• On-chain information is limited; at the moment, only proposals and votes have been decoded, but the actual instructions of the program that manages the Realms (DAOs) have not yet been decoded on any platform and there are even governances that are not being tracked on the blockchain, such as Pyth and Metaplex.

• There are more instructions that should be decoded, such as when creating a new treasury or governances within the same DAO, among other instructions that I don't even know about.

• As voting power can depend on third party programs and governance, there is no easy way to calculate the voting power of each voter, which makes it difficult to analyse the concentration of power of each DAO.

Resources

Realms | mrwildcat | Flipside

Explore insights on Realms from mrwildcat on Flipside.

https://flipsidecrypto.xyz

On Chain Program for Realms

Dune Query to Return Created Accounts for Realms Program

Governor Program for Pyth

Governor Program for Metaplex

Realms Team GitHub

Mythic Project

Mythic Project has 30 repositories available. Follow their code on GitHub.

https://github.com

Key Points

1. DAOS.FUN has a combined market cap of 435 million but manages less than 7 million in assets, showing a big gap between value and actual funds.

2. The platform raised 8.19 million USD across 265 DAOs, using Solana’s Token2022 standard for fundraising, with most of funds going to risky assets like memecoins.

3. Most DAOs performed poorly, with only 31 out of 161 having positive returns, while 40 lost 80-100% of their value, highlighting the high risk and volatility of the platform.

Since its September 2024 launch on Solana, DAOS.FUN has turned the blockchain into a hub for memecoin-fueled DAOs, raising $8.19M USD across 265 launches by February 26, 2025. Founded by @baoskee and backed by Alliance DAO, this platform merges meme culture with structured fundraising via Token2022—a Solana standard for programmable tokens. Creators set targets (50–4,000 SOL) over a week, with successful DAOs investing 90% of funds in volatile assets and 10% seeding liquidity on AMMs. Yet, its $435M market cap dwarfs a less than $7M AUM, hinting at speculative fragility.

Key features include:

• Tokenized DAOs: All DAOs are represented as a Token2022 Program. Once created, the DAO sets fundraising targets (e.g., 50–4,000 SOL) and a 1-week deadline. If the target is met, the token is issued to investors at a fixed price, ensuring a fair launch.

  

• Risk-Reward Dynamics: Investors gain exposure to high-risk memecoins while retaining redemption options if fundraising fails. If the target isn’t met, investors can redeem their SOL with a 10% penalty. If successful, 90% of the raised SOL is invested in Solana protocols or memecoins, while 10% is used to create liquidity for the DAO token on an AMM. This setup allows the token’s price to fluctuate based on trading activity, providing liquidity and exit opportunities for investors.

• Transparent Metrics: Real-time tracking of portfolio value, market capitalization, and fundraising progress.

The platform has launched 265 DAOs (including test/failed projects), with total assets under management (AUM) of less than $7M and a combined market cap of 435M at the time of this writing.

daos.fun

@daosdotfun

who wants to start a hedge fund?

now live on Solana
daos.fun

362

5:29 PM • Sep 26, 2024

ai16z, the leading DAO on DAOS.FUN, has captured the crypto community’s imagination with its AI-driven investment model powered by Eliza, an AI framework inspired by early natural language processing systems. Launched in late October 2024, it soared to a $2 billion market cap by December 2024, fueled by its innovative approach and humorous branding. As of now, ai16z boasts a market capitalization of $379 million, a portfolio value of $662,610, and an impressive 796% return on investment. It leads in holders (115,000), traders, market cap, and portfolio value. However, its market-to-asset ratio of 572.7 underscores the speculative frenzy surrounding its token, raising questions about sustainability while solidifying its status as a groundbreaking experiment in decentralized finance.

Solana’s memecoin mania exploded in late 2024, driven by platforms like pump.fun, where daily token launches averaged 8,000–9,000 in October, spiking to 66,000 by late January 2025. DAOS.FUN capitalized on this frenzy, with ai16z’s AI-meme fusion and KOTOPIA’s 4,206 SOL haul reflecting peak hype. However, the bubble showed cracks after high-profile failures like the Melania token and Javier Milei’s Libra token, where insiders exploited early access to pump-and-dump schemes, souring sentiment. This slowdown saw assets flee Solana, underscoring the mania’s unsustainability and DAOS.FUN’s reliance on fleeting trends.

As mentioned, 265 DAOs have been created on the platform, including test, duplicated, and failed launches. One even attempts to inject a script in its name, possibly exposing a naming vulnerability. According to the latest data, 137 DAOs remain active (with portfolio holdings or tokens being traded).

ai16z is the DAO with the most holders, with around 115,000 holders. It is followed by Big Pharmai ($DRUGS) with almost 9,000 holders. Only 15 DAOs have more than 1,000 holders.

After a peak in valuation and activity on January 1, 2025, most DAOs have seen their number of holders drop slightly.

KOTOPIA raised the most funds, totaling 4,206 SOL (approximately $734,000 USD) from 576 unique depositors, part of a platform-wide 40,170 SOL from 12,696 depositors. Six DAOs have raised at least 1,000 SOL. Note that USD conversions reflect transaction-time rates, explaining variability.

The top fundraising day was November 27, 2024, with a total of 5,540 SOL ($964,000) raised from 762 depositors. The top DAOs financed that day were KOTOPIA (4,207 SOL) and Degen Capital Guild (1,000 SOL).

January 1st, 4th, and 6th were the days with the highest number of depositors, each surpassing 1,000. The DAOs funded on these days included REDACTED (████████) with 1,220 SOL and BERADIGM with 1,100 SOL, among others.

You may notice that the number of depositors is 0 on some dates. This is due to how the query was structured—some depositor fields are empty, and the exact cause is still unknown.

At the time of this writing, there were around 30,000 unique deposits to fundraising DAOs. The average deposit is 1.44 SOL, but the median is only 0.36 SOL. The 75th percentile is 1 SOL, meaning 75% of deposits are for less than 1 SOL.

After fundraising, DAO managers can use the funds to invest in other Solana assets. An interesting detail is that the latest Token2022 standard is not supported for the assets. It’s evident that most DAOs invest in high-risk assets like memecoins, which is reflected in their performance.

The Assets Under Management of DAOs peaked on January 7, 2025, surpassing $80 million before falling to less than $7 million at the time of writing. These fluctuations mirror the volatility of the memecoin market, where a token can be highly valuable one moment and nearly worthless the next.

For some strange reason, probably related to the tables used, the query that calculates the value of the portfolio has gaps in the dates that have been insurmountable, and attempts to repair it have generated errors in other DAOs, this is the result with the information available in daos.fun, as there are prices of some tokens that DAOs have that are not tracked in Dune or not updated for several days, this is also seen in the market capitalization of some DAOs that returns 0.

Out of 161 DAOs, only 31 had a positive portfolio return since their fundraising up to the date. The rest had negative returns, with 40 DAOs experiencing drawdowns of -80% to -100%. Some DAOs have a market-to-asset ratio of several multiples, indicating significant overvaluation that may not hold over time.

Some assets are not being tracked, i.e. they do not have an available price to refer to, which results in some DAOs such as 'INNOVATIVE BIORESEARCH VOUGHT' showing a portfolio that is worthless, although the DAOS.FUN frontend does show an AUM of $63k.

However, many others have brought the value of the funds raised to zero and we should not consider it normal for so many DAOs to fail, at least we should not aspire to returns lower than those of other safer activities, such as staking. These failures hide rug pulls of insider traders, unscrupulous or incompetent managers that end up deteriorating the confidence in these type of products.

Trading activity peaked on January 17, 2025, with over 30,000 unique traders. Bot traders accounted for less than 20% of the activity. The identification of bots comes from a Dune table, where there are at least five million addresses listed as such. The list is not exhaustive, but it does give us an idea of the percentage of activity that corresponds to bots automating exchanges.

A total of 216 pools were created, most of which involved pairs of ai16z tokens. Logically, the pairs that concentrate the most volume are Solana's native currency, SOL, and the USDC stablecoin; the rest correspond to other DAOs or other memecoins.

The volume of tokens exchanged also clearly reflects this trend, with exponential growth after the last weeks of December until the last week of January this year, with volume exceeding $100 million most days. That said, the ai16z token always leading the exchanged pairs. It is important to recognize that the volume shown does not capture wash trading, an activity that involves selling the same token in short periods of time to artificially increase volume, which would allow us to show how much of the activity is “fake”.

While DAOS.FUN is a fun and innovative idea, creating short-term investment vehicles focused on memecoins is extremely high-risk. Each DAO has its own accountability, and the meme narrative is showing signs of exhaustion, especially with the increasing number of rug pulls and scams in the memecoin space. The platform’s success hinges on balancing innovation with risk mitigation. By fostering transparency and community involvement, DAOS.FUN could pave the way for a new era of decentralized finance. However, investors must remain vigilant and conduct thorough due diligence to navigate the platform’s inherent risks.

Looking ahead, DAOS.FUN’s viability may depend on addressing its opaque governance and portfolio tracking. With 40 DAOs losing 80–100% of value and ai16z’s outlier 796% return skewing perceptions, the platform risks alienating investors unless it provides detailed manager stats and real-time asset evolution data. Without such reforms, its $435M market cap could evaporate as trust wanes in this speculative experiment.

All information has been obtained from my Dune dashboard.

Introduction

Events are crucial in the Ethereum Virtual Machine (EVM) networks. Interestingly, half of the transactions to smart contracts on Scroll are to non-decoded contracts on Dune. To unlock full data insights, you need to submit the ABI (Application Binary Interface) to access decoded tables on Dune but they can still be limited and there are handy functions to decode data.

Events are a critical communication mechanism for smart contracts, primarily serving as a bridge between the blockchain and front-end applications. They are logs stored in a specialized data structure within the blockchain, allowing efficient access and filtering by external applications like decentralized apps (dApps) or off-chain services. Each emitted event is intrinsically linked to the smart contract that generated it.

Dune stands out as one of the most prominent blockchain data platforms. With an account, you can gain access to vast amounts of data at your fingertips. Many important contracts are already decoded, providing human-readable data that can be easily analyzed.

While you might typically rely on the chain.logs_decoded table to gather logs information, these tables often provide only a superficial overview. They frequently capture just a brief detail about the event being emitted, rather than the complete parameter details.

A typical EVM transaction can be sent to two primary types of addresses:

1. Externally Owned Accounts (EOA) - for simple operations like sending native currency

2. Smart Contracts - which can execute more complex operations

{
    "to": "0xd29687c813d741e2f938f4ac377128810e217b1b",
    // [...]
}

When a function executes successfully, the contract is likely to emit events (or logs). This data is stored in tables like scroll.logs

A raw log entry might look cryptic:

{
    "contract_address": "0xd29687c813d741e2f938f4ac377128810e217b1b",
    "topic0":"0xdec2bacdd2f05b59de34da9b523dff8be42e5e38e818c82fdb0bae774387a724",
	"topic1":"0x000000000000000000000000588faac6b3aa91bb4cd8127ab357515716541f1b",
	"topic2":"",
	"topic3":"",
"data":"0x00000000000000000000000000000000000000000000000496bda902a80a80000000000000000000000000000000000000000000000000000000000000000000",
}

Dune provides two primary decoding functions:

1. decode_evm_event(): Decodes logs or events

2. decode_evm_function_call(): Decodes internal functions or traces

The article focus on the event decoding function.

A typical decoding query follows this structure:

SELECT 
    *
FROM TABLE(
    decode_evm_event(
    abi => '{EVENT ABI}',
    input => TABLE(
        SELECT block_date, topic0, topic1, topic2, topic3, data, tx_hash, tx_from, contract_address
        FROM arbitrum.logs
        WHERE
            contract_address IN 
            ({CONTRACTS})
        AND
            topic0 = {EVENT SIGNATURE}
        )
    )
)

To get the event ABI, you need to obtain the contract ABI and find the event functions. You can do this through various methods:

• Using a blockchain explorer

• Compiling the contract

A typical event looks like this:

{"anonymous":false,"inputs":[{"indexed":true,"internalType":"address","name":"voter","type":"address"},{"indexed":false,"internalType":"uint256","name":"proposalId","type":"uint256"},{"indexed":false,"internalType":"uint8","name":"support","type":"uint8"},{"indexed":false,"internalType":"uint256","name":"weight","type":"uint256"},{"indexed":false,"internalType":"string","name":"reason","type":"string"}],"name":"VoteCast","type":"event"}

Here's a concrete example of generating a query to return Transfer events for the Scroll Token (SCR):

SELECT
    block_time,
  "from",
  "to",
  value / POWER(10, 18) AS value_
FROM TABLE(
  DECODE_EVM_EVENT(
    abi => '{"anonymous":false,"inputs":[{"indexed":true,"internalType":"address","name":"from","type":"address"},{"indexed":true,"internalType":"address","name":"to","type":"address"},{"indexed":false,"internalType":"uint256","name":"value","type":"uint256"}],"name":"Transfer","type":"event"}',
    input => TABLE(
      SELECT
        *
      FROM scroll.logs
      WHERE
        contract_address IN (0xd29687c813d741e2f938f4ac377128810e217b1b) -- SCR Token Address
        AND topic0 = 0xddf252ad1be2c89b69c2b068fc378daa952ba7f163c4a11628f55a4df523b3ef -- Event Signature
    ),
    null_on_error => FALSE
  )
)

Some event attributes are reserved SQL words, such as "from" or "to". To use these, you need to enclose them in double quotes in the attribute name. If you use single quotation marks, you will get the literal attribute.

The results you obtain will provide detailed information about the event.

For each case, you need to consider specific nuances. In this example, it's necessary to divide the "value" attribute by the 18 decimal places of the token to obtain the integer token number.

Now you have all the data for a specific event, which allows you to tweak the query to:

• Track how a variable evolves over time

• Explore various aspects of your contract

After building several dashboards using this technique, I found myself doing repetitive work:

• Going to the contract address

• Ensuring the contract is decoded

• Finding event signatures

• Copying and pasting into query templates

This process was time-consuming. I realized there must be a better way, so I asked Claude to help me develop a Python script to automate this process.

Key features of the script

• Load and parse ABI files

• Generate event signatures

• Create query templates

• Support for multiple blockchain analysis scenarios

Before running the script, remember to install the required dependencies:

pip install keccak
python -m pip install "eth-hash[pycryptodome]"

After creating a virtual environment to use Python without conflicts and installing the required packages, copy the provided script and save it with a descriptive name like evm_abi_parser.py.

import json
import os
import sys
from eth_utils import keccak

class EVMABIParser:
    """
    A comprehensive tool for parsing and working with Ethereum ABI files.
    """

    def __init__(self, file_path=None):
        """
        Initialize the ABI parser with an optional file path.

        Args:
            file_path (str, optional): Path to the ABI JSON file.
        """
        self.abi_contents = None
        self.parsed_abi = None

        if file_path:
            self.load_abi_from_file(file_path)

    def load_abi_from_file(self, file_path):
        """
        Load ABI from a JSON file.

        Args:
            file_path (str): Path to the ABI JSON file.

        Returns:
            bool: True if loading was successful, False otherwise.
        """
        try:
            # Expand user home directory if needed
            file_path = os.path.expanduser(file_path)
            
            # Check if file exists
            if not os.path.exists(file_path):
                print(f"Error: File '{file_path}' does not exist.")
                return False
            
            # Check file extension
            if not file_path.lower().endswith(('.json', '.jsonc')):
                print("Error: File must be a JSON or JSONC file.")
                return False
            
            # Read and parse the file
            with open(file_path, 'r', encoding='utf-8') as file:
                try:
                    self.abi_contents = json.load(file)
                    
                    # Validate ABI structure
                    if not isinstance(self.abi_contents, list):
                        print("Error: ABI must be a JSON array.")
                        return False
                    
                    self.parsed_abi = self.parse_abi()
                    return True
                
                except json.JSONDecodeError as e:
                    print(f"JSON Parsing Error: {e}")
                    return False
        
        except Exception as e:
            print(f"Unexpected error reading file: {e}")
            return False

    def parse_abi(self):
        """
        Parse the ABI contents and extract functions and events.

        Returns:
            dict: A dictionary with lists of functions and events.
        """
        functions = []
        events = []

        for item in self.abi_contents:
            # Validate each ABI item
            if not isinstance(item, dict):
                continue

            if item.get("type") == "function":
                functions.append({
                    "name": item.get("name", "Unnamed Function"),
                    "inputs": item.get("inputs", []),
                    "outputs": item.get("outputs", []),
                    "stateMutability": item.get("stateMutability", "Unknown")
                })
            elif item.get("type") == "event":
                events.append({
                    "name": item.get("name", "Unnamed Event"),
                    "inputs": item.get("inputs", []),
                    "anonymous": item.get("anonymous", False)
                })

        return {"functions": functions, "events": events}

    @staticmethod
    def generate_event_signatures(events):
        """
        Generate text and hex signatures for events using Keccak-256.

        Args:
            events (list): List of event dictionaries.

        Returns:
            list: List of dictionaries with event signatures.
        """
        event_signatures = []

        for event in events:
            # Generate text signature
            input_types = [inp.get('type', '') for inp in event.get('inputs', [])]
            text_signature = f"{event['name']}({','.join(input_types)})"
            
            # Generate full topic0 using Keccak-256 of the text signature
            # Use eth_utils.keccak for correct Keccak-256 implementation
            topic0 = '0x' + keccak(text_signature.encode()).hex()

            event_signatures.append({
                'name': event['name'],
                'text_signature': text_signature,
                'topic0': topic0
            })

        return event_signatures

    def display_event_signatures(self):
        """
        Display event signatures in a formatted manner.
        """
        if not self.parsed_abi or not self.parsed_abi['events']:
            print("No events found in the ABI.")
            return

        event_signatures = self.generate_event_signatures(self.parsed_abi['events'])
        
        print("\n--- Event Signatures ---")
        for sig in event_signatures:
            print(f"Event: {sig['name']}")
            print(f"  Text Signature: {sig['text_signature']}")
            print(f"  Topic0: {sig['topic0']}\n")

    def generate_query_template(self, event_name, contract_addresses=None):
        """
        Generate a query template for a specific event.

        Args:
            event_name (str): Name of the event.
            contract_addresses (list, optional): List of contract addresses.

        Returns:
            str: SQL query template or None if event not found.
        """
    # Find the event in the parsed ABI
        target_event = None
        for event in self.abi_contents:  # Use self.abi_contents instead of self.parsed_abi['events']
            if event.get('name') == event_name and event.get('type') == 'event':
                target_event = event
                break

        if not target_event:
            print(f"Event '{event_name}' not found in the ABI.")
            return None

        # Generate text signature to get topic0
        signatures = self.generate_event_signatures([target_event])
        topic0 = signatures[0]['topic0']

        # Construct query template
        contract_address_clause = (
            f"contract_address IN ({', '.join(str(addr) for addr in contract_addresses)})"
            if contract_addresses 
            else "1=1"
        )

        query_template = f"""
    SELECT 
        *
    FROM TABLE(
        decode_evm_event(
            abi => '{json.dumps(target_event)}',
            input => TABLE(
                SELECT block_date, topic0, topic1, topic2, topic3, data, tx_hash, tx_from, contract_address
                FROM scroll.logs
                WHERE
                    {contract_address_clause}
                AND
                    topic0 = {topic0}
            )
        )
    )
    """
        return query_template

def main():
    """
    Main function to interact with the EVM ABI Parser.
    """
    print("EVM ABI Parser and Query Generator")
    
    while True:
        print("\nChoose an option:")
        print("1. Load ABI file")
        print("2. Display Event Signatures")
        print("3. Generate Query Template")
        print("4. Exit")
        
        choice = input("Enter your choice (1-4): ").strip()
        
        if choice == '1':
            file_path = input("Enter the path to your ABI JSON file: ").strip()
            parser = EVMABIParser(file_path)
        
        elif choice == '2':
            if not parser.parsed_abi:
                print("Please load an ABI file first.")
                continue
            parser.display_event_signatures()
        
        elif choice == '3':
            if not parser.parsed_abi:
                print("Please load an ABI file first.")
                continue
            
            event_name = input("Enter the event name: ").strip()
            include_addresses = input("Do you want to specify contract addresses? (y/n): ").strip().lower()
            
            contract_addresses = None
            if include_addresses == 'y':
                addresses_input = input("Enter contract addresses (comma-separated): ").strip()
                contract_addresses = [addr.strip() for addr in addresses_input.split(',')]
            
            query = parser.generate_query_template(event_name, contract_addresses)
            if query:
                print("\n--- Query Template ---")
                print(query)
        
        elif choice == '4':
            print("Exiting the script.")
            break
        
        else:
            print("Invalid choice. Please try again.")

if __name__ == "__main__":
    try:
        main()
    except KeyboardInterrupt:
        print("\nOperation cancelled by user.")
        sys.exit(0)

Before running the Python script:

1. Copy the contract's ABI to your clipboard

2. Save the ABI as a JSON file

3. Load this file when executing the script

If the contract you are calling is a proxy you still need to use the underlying contract ABI for the proxy, not the proxy ABI.

When we run our query, we can now see the detailed results:

With the base query in place, we now have a powerful starting point to extract useful information about blockchain events. For example, we can modify the query to analyze the distribution of SCR token claims during the Scroll airdrop over time:

This script represents an initial proof of concept and requires further refinement as it's not battle-tested. I found that some contracts couldn't be properly decoded like the dmail contract, in which I wasn’t able to get the events as the query generated returned an error on Dune.

Still, I think this script will be helpful, as these functions have been instrumental in my analysis of the Scroll airdrop and the dashboard for governance in Scroll. Among the things that need to be added is support for internal functions using the decode_evm_function_call().

Although Devcon was scheduled for mid-November, I arrived a couple of weeks earlier in Thailand. After long hours of travel, I reached my destination: Chiang Mai, Thailand. A vibrant city with warm weather and approximately one hundred thousand inhabitants. My first stop was Punspace, where The Mu was taking place, a coworking space where I met friends I had made in MuBuenos. Whenever I arrive at Mu, I feel very comfortable sharing space with people passionate about blockchain technology, working in areas such as smart contract development, security, front-end, and many other disciplines.

The goal of The Mu is to create a permissionless and inclusive popup city that engages locals and foster crypto projects for societal benefit.

Chiang Mai was also home to other pop-up cities like MegaZu, Lanna, and Invisible Garden, each offering slightly different experiences but sharing similar goals: connecting and building within this ecosystem.

The Mu has ambitious plans for 2025, including more pop-up cities and participation in the Japan Expo 2025 in Osaka from April 13 to October 13, expecting 28 million visitors and 161 participating countries.

After The Mu, I attended the Lanna Hackathon. While many of my friends were working on their own projects and I didn’t participate directly, I had the chance to discover fascinating initiatives like FindSpaceTime (an app for locating friends and sharing travel plans), Syntonikka (an incredible artist), and Collab.Land.

The hackathon also featured high-level talks on diverse topics, available at StreamETH.

As The Mu concluded, MegaZu hosted a Demo Day, showcasing a month's work on projects like Maitri and Wysdom, which focus on collaborative notebooks for web3 projects. Tools like Celo Composer, a boilerplate for deploying dApps on Celo, and more daring initiatives like KissOrSlap also stood out. With so many projects, it was hard to keep track of them all.

Before leaving Chiang Mai, I had time to practice Muay Thai and enjoy local cuisine. Then I departed for Bangkok to attend the DeFi Security Summit (DSS). There, I began with the first Damn Vulnerable DeFi workshop by Tincho Abatte, a member of the red guild and WebTres, where the most prominent web3 developers in LATAM are located. The talks are available on the DSS channel, such as DeFi Security 101.

A talk by Pablo Sabatella highlighted how most recent fund losses were due to hacking people using web2 techniques like social engineering, 0-day exploits, DNS hijacking, SIM swaps, malware, and private key leakage, among others.

There were many talks that evolved around formal verification. The full agenda is available at https://defisecuritysummit.org/

After three days of DSS, I attended Governance Day, where I met other scholarship recipients, and we collaborated to ensure the event's success. I interacted with academics, and there was a wide variety of talks, including governance tools, some theory, and discussions about the role of delegates. Representatives from Scroll and Morpho, who are initiating their web3 governance, were present.

The very next day, I went to Datapalooza, my first data event related to The Graph. I enjoyed talks about On-Chain Privacy by Lisa from Aztec and "Making Sense of Data in an Interoperable World," among others. Most talks can be found on The Graph's YouTube channel.

The following Monday, I attended DuneCon, an event that excited me greatly. Ten new chains on Dune were announced, the launch of Echo as a real-time multichain platform, and the acquisition of smlXL, an integration that promises to be fascinating.

The conference was excellent, but I gained the best insights in the wizards' space. There, I met analysts like Kofi, who explained how to filter spam, bots, and Sybils in data. We also explored the oneinch.swaps table, a central tool for analyzing the 1inch protocol, and I discovered that all Polkadot data is now available on Dune.

Then I attended 'Unlocking Real Insights, Metrics That Matter' by Tim Conard of Slice Analytic, where he showed the importance of generating metrics with more context. Instead of just 'active addresses' over time, he suggested showing address segmentation by fees, activity, value, and address quality score, ideally based on cross-chain activity, evaluated through metrics like transaction frequency and volume.

There was also a great presentation about the evolution of Uniswap and how v4 will work and save data on-chain.

If the DeFi Security Summit seemed massive, Devcon far surpassed it. It occupied three floors of the Queen Sirikit National Convention Center. I arrived at noon and dedicated myself to exploring the venue, reuniting with friends and people I had met in previous days.

The merch hunt was tremendous, but I stuck with this beautiful magazine, which is written in Chinese, it has amazing visualizations. I'm fascinated by mushrooms and believe we have to learn more about them.

In the venue there were many artistic interventions which was cool.

There were also many stands with useful resources besides merchandise, like the following graph about resource allocation through history.

Several Latin American representatives were present in the talks, such as Eth Kipu and Crecimiento. A group of the Global Majority was formed, gathering thinkers from the 'global south' with different backgrounds.

I tried prediction markets for the first time in Chiang Mai, using Seer, betting on the results of the US presidential election. It was fun and sparked an interest in this fascinating subject. The talk I enjoyed most was by Robin Hanson about Designing Conditional Markets and Futarchy. Conditional markets allow predicting outcomes from potential decisions, enabling what is called futarchy governance.

There was another fascinating talk about a new style of data workflow that has emerged, using entirely open-source software and local-first processing. This new ecosystem of tools allows anyone to cheaply, easily, and robustly collect and analyze any EVM dataset from the comfort of their own laptop.

Overall, the experience was excellent. I felt privileged to attend such an event. If you've read this far, you might think the Devcon part of the article is brief, and you'd be right. What happens at Devcon is highly social, and much of the experience occurs through chance encounters, during lunch, at a workshop, or at another event.

As the event was about to end, I realized that many fascinating people I spoke with, I only did so once and for brief periods. With some, I didn't exchange any social networks to stay in touch, and with others, I didn't seem to have much to discuss, and some didn't even return my messages—they'll be strangers again. With others, I was able to say goodbye, and it was crazy to think that each of them would soon be jumping to different parts of the globe, a "see you soon."

I often found myself with little energy to start new conversations, as many are superficial—"What's your name, and what do you do?"—and responding by repeating what I had already said to other people at least a couple of times. Still, it's okay for everyone to have their own experience, but if I were to give advice if the experience were to be repeated, it would be to value each conversation as if it were the last and to dare to speak to anyone who piques your curiosity.

I thank each person I met and my friends who made this trip fun and pleasant. It's not trivial to be on the other side of the world, constantly receiving new stimuli.

Being a scholarship recipient was a great boost to attend Devcon and reaffirm my chosen path. Crypto is not easy; I'm far from having high salaries or having my life solved by betting on the right memecoins. On the contrary, we are constantly intellectually challenged and must continue working to add value to the ecosystem.

Finally, the words that resonate in my mind about Ethereum are 'credible neutrality'—the commitment of a system to operate without favoring any participant, agenda, or outcome.

"We believe that all humans should have the right to use strong cryptography, the right to transact freely, and the right to freedom of speech." - Harry Halpin

Introduction and Context

Uniswap is one of the most important decentralized cryptocurrency exchanges, with most of its activity coming from Layer 2 networks. This article is part of a Research Challenge to explore the impact of gas subsidies on Layer 2 chains. Since this is a rather hypothetical idea, not yet tested with any campaign or direct/indirect subsidy, I'll take the liberty of using a carefully selected sample of transactions to Uniswap on various chains to explore the potential effects on protocol activity.

The selected sample includes transactions subsidized by Rabby, a wallet that offers the possibility to 'subsidize' transactions when users run out of gas. The blockchains (chains or Layer 2 chains) explored are Arbitrum, Binance Smart Chain (BSC), Optimism, Polygon, and Avalanche. I'll use the term "Layer 2" to refer to them, although this isn't strictly correct as some don't depend on Ethereum to function. However, they are EVM-compatible and were created after Ethereum to offer cheaper and faster transactions.

The queries are open, see: https://flipsidecrypto.xyz/mrwildcat/the-impact-of-gas-subsidies-on-uniswap-v1OH_P

To better understand the protocol, let's look at how volume has behaved on these chains.

Key observations:

• The average daily volume across these chains is approximately 500M USD.

• Weekend days typically show lower trading volumes.

Looking at the daily average volume from March to July 2024, we observe how Arbitrum outpaces all other chains by a factor of two, with an average daily volume of 300M USD, followed by Base, Polygon, and Optimism.

Exploring user dynamics, we can see that most users come from Base, with around 70,000 users making exchanges on Uniswap, followed by Polygon.

• Base and Polygon lead in user numbers.

• Arbitrum shows an interesting pattern: highest volume but fewer users compared to Base and Polygon.

• BSC appears to be losing its share of Uniswap users over time.

On March 13, the Dencun hard fork was activated, enabling one of Ethereum's most anticipated features: proto-danksharding (also known as EIP-4844, or blobs). This feature allowed for cheaper transactions on Layer 2 chains.

In Arbitrum's case, we see how at least 60 ETH (around 160k USD today) was spent weekly on Uniswap transactions, and now it's closer to 10 ETH spent on fees.

Before Dencun, the growth in transaction spending seemed more exponential, but now it's much more linear as much less is spent on transaction fees. As BSC, Avalanche, and Polygon don't use this technology and transactions are made in their native currency, I don't expect to see major changes in cost on those networks.

We also see how the transaction costs decrease has occurred in Optimism, from an average of 100 ETH spent on transaction fees weekly, it has dropped to around 20 ETH. Base's case is more paradigmatic because after March 13, in the week of March 25, 1750 ETH was spent on transactions to use Uniswap.

As mentioned at the beginning, we want to understand how gas subsidies could optimize user engagement and improve liquidity provision, particularly on Layer 2 under various economic conditions and in different liquidity pools. For this, we'll use data from Rabby's gas campaign, where some of the transactions ended up being transactions to the Uniswap protocol to perform a token exchange.

Metrics about the campaign: https://flipsidecrypto.xyz/mrwildcat/rabby-gas-campaing-NOq5Yj

The data begins on April 29, with the start of Rabby's campaign, so we can forget about the effects that blobs had on transaction fees.

The particularity of the subsidy that Rabby provides is that it's given when a user, using this wallet, doesn't have enough native currency to pay for transaction fees. Once the user decides to receive the subsidy, Rabby sends the native currency from an account to the user and allows them to pay for the transaction for which they didn't have sufficient funds.

As these subsidies are delivered through the Rabby interface, it's expected that they're not bots. There may be users with more than one account, but the data is expected to correspond more to a 'retail' user than a 'professional' one, as we'll see, the average exchange volume is less than 200 dollars.

Most of the volume generated by these subsidized transactions has occurred on Arbitrum, followed by Base and Optimism.

It's important to consider that the daily volume is almost always less than 50k dollars, a very low number considering that Avalanche, the chain that generates the least volume for Uniswap, generates around 1 million dollars in volume.

Most of the subsidized users are found on Arbitrum and BSC, with almost 5000 subsidized users.

The cost of subsidizing a user varies by chain, with four chains costing less than 10 cents and BSC and Avalanche having the highest cost, around 38 cents to subsidize users on these chains.

From the table, we can conclude that Binance Smart Chain is the most expensive network to subsidize. Despite ranking fourth in terms of generated volume, it has the lowest average swap volume at just $36.

With a fraction of what was spent on BSC, Arbitrum generated a volume of $1M during the gas subsidy campaign.

As we can see, most of the pools used contain a stablecoin such as USDC, USDT, or DAI, with the other counterpart in the pool usually being WETH, WBTC, or the wrapped coin of each chain, such as WBNB or WAVAX.

Chains using blobs like Arbitrum, Base, and Optimism have become cheaper to subsidize, though not as cheap as Polygon, as it costs less than 10 cents to subsidize a swap on these networks. In contrast, BSC and Avalanche are the most expensive, with a cost close to 40 cents per transaction.

The total cost of Rabby's campaign to subsidize transactions on Uniswap was $3,172, benefiting 16,510 users (or addresses) and generating a volume of around $3M during the month and a half of subsidies.

If there were a fixed budget for subsidies, determining how much money to allocate to each chain would be a linear programming exercise, aiming to maximize the volume generated for each chain considering the cost per user. It's just a matter of formulating the right equations.

This sample is limited, and it remains to be seen whether these users use Uniswap more than non-subsidized users, analyze the retention of these users in the following weeks, and compare these results with other DEXs. More data is needed.

Although there hasn't been a direct comparison with non-subsidized users, it's useful to observe how the activity of a retail user, who is using a wallet and not a bot, behaves.

Data Collection and Processing

All data for this analysis was collected using FlipsideCrypto's databases. The following tables were utilized:

1. Native transfers:

   • optimism.core.ez_native_transfers

   • arbitrum.core.ez_native_transfers

   • polygon.core.ez_native_transfers

   • bsc.core.ez_native_transfers

   • avalanche.core.ez_native_transfers

   • base.core.ez_native_transfers

2. Transaction data:

   • optimism.core.fact_transactions

   • arbitrum.core.fact_transactions

   • polygon.core.fact_transactions

   • bsc.core.fact_transactions

   • avalanche.core.fact_transactions

   • base.core.fact_transactions

3. DEX swap data:

   • optimism.defi.ez_dex_swaps

   • arbitrum.defi.ez_dex_swaps

   • polygon.defi.ez_dex_swaps

   • bsc.defi.ez_dex_swaps

   • avalanche.defi.ez_dex_swaps

   • base.defi.ez_dex_swaps

The data collection and analysis process consisted of the following steps:

1. Identifying Rabby subsidies: We first identified all native token transfers from the Rabby wallet (address: 0x76dd65529dc6c073c1e0af2a5ecc78434bdbf7d9) to user addresses across six different chains (Optimism, Arbitrum, Polygon, BSC, Avalanche, and Base). These transfers represent gas subsidies provided by Rabby.

2. Tracking subsequent transactions: For each subsidized address, we identified the next transaction made by that address after receiving the subsidy.

3. Filtering for Uniswap swaps: We then filtered these subsequent transactions to include only those that were swaps on Uniswap (v2 or v3) platforms.

4. Aggregating data: Finally, we aggregated the data by chain to calculate various metrics such as the number of unique swappers, total swap volume, average swap volume, total subsidy amount, and cost per user.

The analysis covers transactions from March 1, 2024, onwards. This allows us to focus on recent activity and the impact of gas subsidies on Uniswap usage across different Layer 2 and EVM-compatible chains.

Limitations

It's important to note a few limitations of this methodology:

1. We only consider the immediate next transaction after a subsidy. This may not capture all Uniswap activity influenced by the subsidy if users made multiple transactions.

2. The analysis doesn't account for users who received subsidies but didn't perform a Uniswap swap as their next transaction.

3. The data doesn't include comparison with non-subsidized transactions, which could provide additional context on the impact of subsidies.

If you have any comments, contact me on x.com/mrwildcat7