7.3[Advanced]Footprint 上的常用查询：代币持有者、总供应量、账户余额

Footprint Analytics2023-01-06 15:32

此文章是 #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，进行换算。

withtoken_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 如下：

withtoken_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_supplyfrom token_balancewhere amount > 0

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

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

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

withtoken_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 来管理 SQLdbt-footprint

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