Mark addresses from FlipsideCrypto labels

[olegabu]

Flipsidecrypto crowd sourced ethereum address labels of 10 types: cex, dex etc. Each type has a subtype like hot_wallet, deposit_wallet or swap_contract, token_contract.
This metadata can be used to identify contracts that emit events (as well as accounts that are events' payload like to and from in a Transfer).
These label's can make up the contract's event table like dex.token_contract_evt_Transfer.

Open questions:

• flipside table ethereum.core.dim_labels has four levels of address marking with additional label and address_name columns. How to combine these three into two level labels used by other sources? Should we ignore label and address_name so the resulting composite label is like cex.deposit_wallet or concatenate them into cex_deposit_wallet.kucoin?

ADDRESS	ADDRESS_NAME	LABEL_TYPE	LABEL_SUBTYPE	LABEL
0xa02f45adc6a063b1fe9898c8cb11a94d47fecdfc	kucoin deposit_wallet	cex	deposit_wallet	kucoin

These queries can help

select address, address_name, label_type, label_subtype, label 
from ethereum.core.dim_labels 
where blockchain = 'ethereum'
limit 100000 offset 100000;

select DISTINCT address_name
from ethereum.core.dim_labels 
where blockchain = 'ethereum'
limit 10

select *
from ethereum.core.dim_labels 
where blockchain = 'ethereum'
limit 10

select *
from ethereum.core.dim_labels 
where blockchain = 'ethereum' and label = 'aave'
limit 1000

select distinct label_type, label_subtype
from ethereum.core.dim_labels 
where blockchain = 'ethereum' and label = 'aave'
limit 1000

select distinct label_type, label_subtype, account_name
from ethereum.core.dim_labels 
where blockchain = 'ethereum' and label = 'aave'
limit 10;

select count(1) 
from ethereum.core.dim_nft_metadata 
where blockchain = 'ethereum'
and contract_name is not null

select count(1) from ethereum.core.dim_contracts where decimals is null;

select * from ethereum.core.dim_contracts where decimals is null LIMIT 10;

select count(*) from crosschain.core.address_tags 
where tag_name = 'contract address' -- 208 534 673

select count(*) from (select distinct address from crosschain.core.address_tags 
where blockchain = 'ethereum' and tag_name = 'contract address' ) -- 56 652 960

select distinct label_type, label_subtype from crosschain.core.address_labels
order by 1, 2

select distinct tag_type, tag_name from crosschain.core.address_tags
order by 1, 2

select distinct tag_type from crosschain.core.address_tags
where blockchain = 'ethereum'
order by 1

select distinct tag_name from crosschain.core.address_tags
where blockchain = 'ethereum'
order by 1

select distinct label_type from crosschain.core.address_labels
where blockchain = 'ethereum'
order by 1

select distinct label_subtype from crosschain.core.address_labels
where blockchain = 'ethereum'
order by 1

select distinct address_name from crosschain.core.address_labels
where blockchain = 'ethereum'
order by 1

select distinct project_name from crosschain.core.address_labels
where blockchain = 'ethereum'
order by 1

select count(distinct project_name, address_name) from crosschain.core.address_labels
where blockchain = 'ethereum'
order by 1

select count(distinct project_name, address_name) from crosschain.core.address_labels
where blockchain = 'ethereum'
order by 1 -- 129 028

select count(distinct project_name, label_type, label_subtype, address_name) from crosschain.core.address_labels
where blockchain = 'ethereum'
order by 1 -- 129 623


select * from crosschain.core.address_labels
where blockchain = 'ethereum'
and project_name = '1inch'
and label_type = 'dex'
order by address_name limit 1000