此文章是 #Footprint Analytics for SQL 系列 的其中一个章节。

ERC20代币的价格可以通过多种方式查询，但是有时我们也需要获取其他信息，如某个代币的持有者数量、代币总供应量（流通量）、各持有者的账户余额（例如持有最多的账号的余额）。不同于比特币使用未花费的交易产出（UTXO）来跟踪账户余额，Ethereum区块链使用账户余额模型。每个账号地址都有转入和转出ERC20代币的记录，将这些转入和转出数据汇总后可以得到账户的当前余额。但是区块链本身并没有保存每个地址的当前余额，所以需要进行计算得出。

为了计算某个账户下某种ERC20代币的余额，或者统计所有持有某种代币的账户的余额，我们需要将转入和转出数据合并在一起。转入数据以用户地址为to_address，金额为正数；转出数据则以用户地址为from_address，金额乘以-1变成负数。可以使用union all将所有数据合并起来。

以下，以arbitrum_token_transfers为例，通过 token_address = '0xfc5a1a6eb076a2c7ad06ed22c90d7e710e35ad0a'，筛选出 GMX 这个 token的所有流水。 由于链上的数据都是以超大整数保存的，所以要换算成真正的个数，需要关联 token_info 表，找到这个token的 decimals，进行换算。

with
token_transfers as (
    select
        tt.from_address
        ,tt.to_address
        ,tt.amount_raw / power(10, coalesce(ti.decimals, 18)) as amount
        ,ti.token_slug
    from footprint.arbitrum_token_transfers tt
    left join
    footprint.token_info ti
    on tt.token_address = ti.token_address
    where 1=1
    and tt.token_address = '0xfc5a1a6eb076a2c7ad06ed22c90d7e710e35ad0a'
    and ti.chain = 'Arbitrum'
    
),
outflow as (
    select
        from_address as wallet_address
        ,token_slug
        ,-amount as amount
    from token_transfers
),
inflow as (
    select
        to_address as wallet_address
        ,token_slug
        ,amount as amount
    from token_transfers
),
union_flow as (
    select * from outflow
    union all
    select * from inflow
)
select * from union_flow

在上述查询中，我们使用union all将每个账户的转入和转出GMX Token的明细数据合并起来。 我们还可以将上述查询的结果放入一个CTE（Common Table Expression，通用表表达式）中，然后对CTE执行统计汇总。考虑到某些代币的持有者数量可能非常多（几万甚至更多），我们通常关注的是持有者总数、总流通量以及持有量最多的一部分地址。因此我们也可以将按地址汇总的查询放入一个CTE中，方便根据需要进一步统计。在这里，我们首先统计持有者总数，并且在查询时过滤掉当前代币余额为0的地址。新的SQL如下：

with
token_transfers as (
    select
        tt.from_address
        ,tt.to_address
        ,tt.amount_raw / power(10, coalesce(ti.decimals, 18)) as amount
        ,ti.token_slug
    from footprint.arbitrum_token_transfers tt
    left join
    footprint.token_info ti
    on tt.token_address = ti.token_address
    where 1=1
    and tt.token_address = '0xfc5a1a6eb076a2c7ad06ed22c90d7e710e35ad0a'
    and ti.chain = 'Arbitrum'
    
),
outflow as (
    select
        from_address as wallet_address
        ,token_slug
        ,-amount as amount
    from token_transfers
),
inflow as (
    select
        to_address as wallet_address
        ,token_slug
        ,amount as amount
    from token_transfers
),
union_flow as (
    select * from outflow
    union all
    select * from inflow
),
token_balance as (
    select
        wallet_address
        ,sum(amount) as amount
    from union_flow
    group by wallet_address
    order by amount desc
)

select 
count(*) as holder_count
,sum(amount) as total_supply
from token_balance
where amount > 0

在上述查询中，我们在CTE token_balance中按地址统计了账户余额，然后在最后的查询中计算了余额大于0的地址数量（持有者数量）和所有账户的余额汇总（流通总量）。

这张chart展示了GMX 代币 的分布情况，约75%的代币质押在项目方的stake GMX合约中。大量的流通代币质押在合约中，有利于降低代币流通量，从而推高币价。

进阶玩法 - 账户每日余额 跟当前账户余额类似，多了一步日期填充，直接上代码

with
token_transfers as (
   select
       tt.from_address
       ,tt.to_address
       ,tt.amount_raw / power(10, coalesce(ti.decimals, 18)) as amount
       ,ti.token_slug
       ,tt.block_timestamp
   from footprint.arbitrum_token_transfers tt
   left join
   footprint.token_info ti
   on tt.token_address = ti.token_address
   where 1=1
   and tt.token_address = lower('0xfc5a1a6eb076a2c7ad06ed22c90d7e710e35ad0a')
   and ti.chain = 'Arbitrum'
  
),
outflow as (
   select
       block_timestamp
       ,from_address as wallet_address
       ,token_slug
       ,-amount as amount
   from token_transfers
),
inflow as (
   select
       block_timestamp
       ,to_address as wallet_address
       ,token_slug
       ,amount as amount
   from token_transfers
),
union_flow as (
   select * from outflow
   union all
   select * from inflow
),
double_entry_book_grouped_by_date as (
   select wallet_address, token_slug, sum(amount) as balance, Date(block_timestamp) as on_date
   from union_flow
   group by wallet_address, token_slug, Date(block_timestamp)
),
daily_balances_with_gaps as (
   select
       wallet_address,
       token_slug,
       on_date,
       sum(balance) over (partition by wallet_address, token_slug order by on_date)            as balance,
       lead(on_date, 1, current_date) over (partition by wallet_address, token_slug order by on_date) as next_date
   from double_entry_book_grouped_by_date
),
calendar AS (
   select on_date from unnest(sequence(FROM_ISO8601_DATE('2021-07-21'), current_date, interval '1' day)) t(on_date)
),
daily_holder_balance as (
   select wallet_address, token_slug, calendar.on_date, balance
   from daily_balances_with_gaps dbwg
   right join calendar
   on dbwg.on_date <= calendar.on_date
   and calendar.on_date < dbwg.next_date
   and dbwg.balance > 0
   order by calendar.on_date asc, dbwg.balance desc
)
select * from daily_holder_balance

进阶工具 - 使用DBT来管理SQL dbt-footprint

以上代码均在项目中，可以切换不同的token_address，得到不同token的余额。

如何在Footprint上创建一个token monitor：

在这个看板里，Token Monitor。你只需要改变token_address，就可以搭建属于你自己的token monitor。

推荐阅读

#EVM Analysis

#DeFi Analysis

#NFT Analysis

#GameFi Analysis

#Wallet Analysis

#Footprint for Developer

#Footprint Analytics for SQL

Footprint Analytics 是首家 Crypto 领域支持无代码数据分析平台。平台还提供一个统一的数据 API，让用户可以快速检索超过23条公链生态的 NFT，GameFi 以及 DeFi 数据。

如果您对该课程有任何反馈或建议，您可以通过以下方式联系我们。

Footprint Website:  https://www.footprint.network

Discord: https://discord.gg/3HYaR6USM7

Twitter: https://twitter.com/Footprint_Data

此文章是 #Footprint Analytics for SQL 系列 的其中一个章节。

ERC20代币的价格可以通过多种方式查询，但是有时我们也需要获取其他信息，如某个代币的持有者数量、代币总供应量（流通量）、各持有者的账户余额（例如持有最多的账号的余额）。不同于比特币使用未花费的交易产出（UTXO）来跟踪账户余额，Ethereum区块链使用账户余额模型。每个账号地址都有转入和转出ERC20代币的记录，将这些转入和转出数据汇总后可以得到账户的当前余额。但是区块链本身并没有保存每个地址的当前余额，所以需要进行计算得出。

为了计算某个账户下某种ERC20代币的余额，或者统计所有持有某种代币的账户的余额，我们需要将转入和转出数据合并在一起。转入数据以用户地址为to_address，金额为正数；转出数据则以用户地址为from_address，金额乘以-1变成负数。可以使用union all将所有数据合并起来。

以下，以arbitrum_token_transfers为例，通过 token_address = '0xfc5a1a6eb076a2c7ad06ed22c90d7e710e35ad0a'，筛选出 GMX 这个 token的所有流水。 由于链上的数据都是以超大整数保存的，所以要换算成真正的个数，需要关联 token_info 表，找到这个token的 decimals，进行换算。

with
token_transfers as (
    select
        tt.from_address
        ,tt.to_address
        ,tt.amount_raw / power(10, coalesce(ti.decimals, 18)) as amount
        ,ti.token_slug
    from footprint.arbitrum_token_transfers tt
    left join
    footprint.token_info ti
    on tt.token_address = ti.token_address
    where 1=1
    and tt.token_address = '0xfc5a1a6eb076a2c7ad06ed22c90d7e710e35ad0a'
    and ti.chain = 'Arbitrum'
    
),
outflow as (
    select
        from_address as wallet_address
        ,token_slug
        ,-amount as amount
    from token_transfers
),
inflow as (
    select
        to_address as wallet_address
        ,token_slug
        ,amount as amount
    from token_transfers
),
union_flow as (
    select * from outflow
    union all
    select * from inflow
)
select * from union_flow

在上述查询中，我们使用union all将每个账户的转入和转出GMX Token的明细数据合并起来。 我们还可以将上述查询的结果放入一个CTE（Common Table Expression，通用表表达式）中，然后对CTE执行统计汇总。考虑到某些代币的持有者数量可能非常多（几万甚至更多），我们通常关注的是持有者总数、总流通量以及持有量最多的一部分地址。因此我们也可以将按地址汇总的查询放入一个CTE中，方便根据需要进一步统计。在这里，我们首先统计持有者总数，并且在查询时过滤掉当前代币余额为0的地址。新的SQL如下：

with
token_transfers as (
    select
        tt.from_address
        ,tt.to_address
        ,tt.amount_raw / power(10, coalesce(ti.decimals, 18)) as amount
        ,ti.token_slug
    from footprint.arbitrum_token_transfers tt
    left join
    footprint.token_info ti
    on tt.token_address = ti.token_address
    where 1=1
    and tt.token_address = '0xfc5a1a6eb076a2c7ad06ed22c90d7e710e35ad0a'
    and ti.chain = 'Arbitrum'
    
),
outflow as (
    select
        from_address as wallet_address
        ,token_slug
        ,-amount as amount
    from token_transfers
),
inflow as (
    select
        to_address as wallet_address
        ,token_slug
        ,amount as amount
    from token_transfers
),
union_flow as (
    select * from outflow
    union all
    select * from inflow
),
token_balance as (
    select
        wallet_address
        ,sum(amount) as amount
    from union_flow
    group by wallet_address
    order by amount desc
)

select 
count(*) as holder_count
,sum(amount) as total_supply
from token_balance
where amount > 0

在上述查询中，我们在CTE token_balance中按地址统计了账户余额，然后在最后的查询中计算了余额大于0的地址数量（持有者数量）和所有账户的余额汇总（流通总量）。

这张chart展示了GMX 代币 的分布情况，约75%的代币质押在项目方的stake GMX合约中。大量的流通代币质押在合约中，有利于降低代币流通量，从而推高币价。

进阶玩法 - 账户每日余额 跟当前账户余额类似，多了一步日期填充，直接上代码

with
token_transfers as (
   select
       tt.from_address
       ,tt.to_address
       ,tt.amount_raw / power(10, coalesce(ti.decimals, 18)) as amount
       ,ti.token_slug
       ,tt.block_timestamp
   from footprint.arbitrum_token_transfers tt
   left join
   footprint.token_info ti
   on tt.token_address = ti.token_address
   where 1=1
   and tt.token_address = lower('0xfc5a1a6eb076a2c7ad06ed22c90d7e710e35ad0a')
   and ti.chain = 'Arbitrum'
  
),
outflow as (
   select
       block_timestamp
       ,from_address as wallet_address
       ,token_slug
       ,-amount as amount
   from token_transfers
),
inflow as (
   select
       block_timestamp
       ,to_address as wallet_address
       ,token_slug
       ,amount as amount
   from token_transfers
),
union_flow as (
   select * from outflow
   union all
   select * from inflow
),
double_entry_book_grouped_by_date as (
   select wallet_address, token_slug, sum(amount) as balance, Date(block_timestamp) as on_date
   from union_flow
   group by wallet_address, token_slug, Date(block_timestamp)
),
daily_balances_with_gaps as (
   select
       wallet_address,
       token_slug,
       on_date,
       sum(balance) over (partition by wallet_address, token_slug order by on_date)            as balance,
       lead(on_date, 1, current_date) over (partition by wallet_address, token_slug order by on_date) as next_date
   from double_entry_book_grouped_by_date
),
calendar AS (
   select on_date from unnest(sequence(FROM_ISO8601_DATE('2021-07-21'), current_date, interval '1' day)) t(on_date)
),
daily_holder_balance as (
   select wallet_address, token_slug, calendar.on_date, balance
   from daily_balances_with_gaps dbwg
   right join calendar
   on dbwg.on_date <= calendar.on_date
   and calendar.on_date < dbwg.next_date
   and dbwg.balance > 0
   order by calendar.on_date asc, dbwg.balance desc
)
select * from daily_holder_balance

进阶工具 - 使用DBT来管理SQL dbt-footprint

以上代码均在项目中，可以切换不同的token_address，得到不同token的余额。

如何在Footprint上创建一个token monitor：

在这个看板里，Token Monitor。你只需要改变token_address，就可以搭建属于你自己的token monitor。

推荐阅读

#EVM Analysis

#DeFi Analysis

#NFT Analysis

#GameFi Analysis

#Wallet Analysis

#Footprint for Developer

#Footprint Analytics for SQL

Footprint Analytics 是首家 Crypto 领域支持无代码数据分析平台。平台还提供一个统一的数据 API，让用户可以快速检索超过23条公链生态的 NFT，GameFi 以及 DeFi 数据。

如果您对该课程有任何反馈或建议，您可以通过以下方式联系我们。

Footprint Website:  https://www.footprint.network

Discord: https://discord.gg/3HYaR6USM7

Twitter: https://twitter.com/Footprint_Data