Using Labels to Break Down Token Supply

In this guide, we're going to look at how supply is distributed across the network for particular ERC20 tokens.
The question we want to answer is: at any given point in time, who is holding this token?
And because we look at groups of accounts using Flipside's base labels, you can get an aggregate view of the entire network.
Our balances tables are also designed to make answering this question very straightforward:
1.Each date in the table represents a snapshot of "current balances" for all holders, so you only have to query one day to see total supply
2.Labels are already injected into the addresses, so all you have to do is group by existing fields in the table
Let's look at the basic query:
1
SELECT
2
  symbol,
3
  label_type,
4
  round(sum(amount_usd)) as total
5
FROM
6
  ethereum.erc20_balances
7
WHERE
8
  balance_date = '2020-10-31'
9
  AND amount_usd > 0
10
  AND contract_address = '0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48'
11
GROUP BY 1,2
12
ORDER BY symbol, total DESC
Copied!
We want to select:
label_type for the pre-generated Flipside label category, 
symbol for context in our results set
a rounded sum of the total usd-converted balance on this first pass, so we can get a sense for the relative sizes of each group
Which returns:
Symbol
Label Type
Total
USDC
​
1537642229
USDC
distributor
871468974
USDC
project
395089998
USDC
other
5313082
Notice that the label_type is missing for our largest group. Flipside's base label set identifies accounts like exchanges, foundations, and operators, but not basic user accounts. We leave those accounts to more context- and behavior- specific classification later, so here, a large "unlabeled" set is expected.
In a later guide, we will cover how to start to break down the unlabeled user set into "Top Holders" and "Smaller Wallets", which is useful to analyzing token flows.
If you want to drill down further, you can repeat the query above with label_subtype instead of label_type:
1
SELECT
2
  symbol,
3
  label_subtype,
4
  round(sum(amount_usd)) as total
5
FROM
6
  ethereum.erc20_balances
7
WHERE
8
  balance_date = '2020-10-31'
9
  AND amount_usd > 0
10
  AND contract_address = '0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48'
11
GROUP BY 1,2,3
12
ORDER BY symbol, total DESC
Copied!
Which returns:
Symbol
Label Sub Type
Total
USDC
​
1537642229
USDC
distributor_dex
544272742
USDC
project_token_contract
341612731
USDC
distributor_cex
228821492
USDC
distributor_cex_satellite
57614929
USDC
project_contract
51637001
USDC
distributor_dex_balancer
40759810
USDC
other_financial
4192633
USDC
project_other
1840265
USDC
other_misc
919132
USDC
other_single_use
201317
As you become more familiar with Flipside's labeling system, you may decide to group certain labels together to express different relationships.
For example, if you wanted to distinguish between centralized and decentralized exchange wallets, you could combine satellite and hot wallet addresses.
Here's a straightforward pattern for playing with combinations in your query using a CASE statement:
1
SELECT
2
  symbol,
3
  CASE
4
  WHEN label_subtype LIKE 'distributor_cex%' THEN 'centralized exchanges'
5
  WHEN label_subtype LIKE 'distributor_dex%' THEN 'decentralized exchanges'
6
  WHEN label_type IS NULL THEN 'user accounts'
7
  ELSE 'other' END as label,
8
  round(sum(amount_usd)) as total
9
FROM
10
  ethereum.erc20_balances
11
WHERE
12
  balance_date = '2020-10-31'
13
  AND amount_usd > 0
14
  AND contract_address = '0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48'
15
GROUP BY 1,2,3
16
ORDER BY symbol, total DESC
Copied!
Here we:
distinguished between centralized and decentralized exchanges at the subtype level
grouped unlabeled address together as "user accounts", which is probably an over-simplification
grouped all other labels together as "other", which you probably would want to tease apart in your next query, given how large the result set is
Results:
You can see how you can continue to slice and dice the supply based on label types and subtypes and eventually other tags in our system to tease out groups of accounts.
Symbol
Label Type
Total
USDC
user accounts
1537642229
USDC
decentralized exchanges
585032552
USDC
other
400403080
USDC
centralized exchanges
286436421
The useful feature of Flipside's base label set is that it allows you to characterize the entire network at once. The alternative is zooming in on a specific set of addresses, which can sometimes limit your ability to put your results in a broader context.

Getting Started with Ethereum ERC20 Balances

Finding Centralized Exchange Deposits and Withdrawals

Last modified 1yr ago

Copy link