#AA 03 AA aggregation table of EVM chains

The EntryPoint contract of ERC4337 has been deployed on multiple EVM chains. You can view the list of all chains that have deployed the EntryPoint contract here:

If you want to analyze the combined data of all EVM chains, it would clearly be too cumbersome to write the analyses one by one. Therefore, based on the spellbook of the dune platform, we have aggregated the data of the EntryPoint contracts of two versions on 9 major blockchains into two new data tables. These tables can currently be directly used on the dune platform for easy analysis. The specific source code can be found at:

https://github.com/duneanalytics/spellbook/tree/main/models/account_abstraction/erc4337

account_abstraction_erc4337.userops: contains UserOperationEvent event data from 9 chains, also joined with the transactions table and price table to get information like the bundler of each userOp and the gas fee paid for that transaction.
account_abstraction_erc4337.account_deployed: includes AccountDeployed event data from 9 chains

Including blockchains:

userops

For a detailed definition of UserOps, see the link below:

https://spellbook-docs.dune.com/#!/model/model.spellbook.account_abstraction_erc4337_userops

The aggregated table UserOps contains more information; the included field information is as follows:

The analysis will then be much simpler. For example, analyzing the distribution of user operations across 9 chains:

with total_op as (
    select blockchain as chain, * from account_abstraction_erc4337.userops 
)
select chain, count(*) as nums from total_op group by chain order by nums desc

For another example, analyzing the monthly profit of bundlers:

with agg_ops as (
    select 
      row_number() over (partition by tx_hash order by userop_hash) as rn
    , sum(op_fee) over(PARTITION by tx_hash) as op_fee
    , sum(op_fee_usd) over(PARTITION by tx_hash) as op_fee_usd
    , tx_fee
    , tx_fee_usd
    , blockchain as chain
    , gas_symbol as symbol
    , block_time
    from account_abstraction_erc4337.userops 
)
, revenue as (
    select
        date_trunc('month', block_time) as dt
        , sum(op_fee_usd - tx_fee_usd) as BundlerRevenueUSD
    from agg_ops
    where rn = 1
    group by 1
)
select  dt, 
    sum(BundlerRevenueUSD) as TotalBundlerRevenueUSD,
    case 
        when sum(BundlerRevenueUSD)>=0 then 'Positive'
        when sum(BundlerRevenueUSD)<0 then 'Negative'
    end as PosNeg
        
from revenue group by 1

account_deployed

The information contained in account_deployed is relatively small, it just integrates the data of the two contract versions and several EVM chains together. See the link for detailed definitions:

https://spellbook-docs.dune.com/#!/model/model.spellbook.account_abstraction_erc4337_userops

The fields included in the aggregated account_deployed table are as follows:

Using account_deployed aggregated tables makes analysis simpler as well. For example, getting the number of wallet factories on each chain:

with total_op as (
    select blockchain as chain, * from account_abstraction_erc4337.account_deployed
)
select chain, count(distinct factory) as Factories from total_op group by 1 order by 2 desc

For complete examples, please refer to the following link. This dashboard contains analyses of various aspects of EntryPoint:

https://dune.com/sixdegree/account-abstraction-overview

Previous#AA 02 Account abstraction data analysis

Last updated 8 months ago