theericstonelabel coverage: tokens
Updated 2023-05-05
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
›
⌄
-- forked from label coverage: dexes @ https://flipsidecrypto.xyz/edit/queries/ba6afd0c-ceab-4e5d-bc85-44a2362f8dac
with tokens as (
select
distinct contract_address,
case when topics[0] = '0xc3d58168c5ae7397731d063d5bbf3d657854427343f4c083240f7aacaa2d0f62' then 'erc-1155'
when (topics[0] = '0xddf252ad1be2c89b69c2b068fc378daa952ba7f163c4a11628f55a4df523b3ef'
and topics[3] IS NOT NULL) then 'erc-721'
else 'erc-20' end as token_standard
from {{chain}}.core.fact_event_logs
where block_timestamp > current_date - 90
and topics[0] in (
'0xddf252ad1be2c89b69c2b068fc378daa952ba7f163c4a11628f55a4df523b3ef', --erc20s and 721s
'0xc3d58168c5ae7397731d063d5bbf3d657854427343f4c083240f7aacaa2d0f62' --1155s
)),
tokenact as (
SELECT
to_address as contract_address,
count(1) as n_txns
from {{chain}}.core.fact_transactions txns
where txns.block_timestamp > current_date - 90
group by 1
),
labeled as (
select
tokens.contract_address,
case when labs.address IS NOT NULL then 'labeled'
else 'unlabeled' end as label_flag
from tokens
left join {{chain}}.core.dim_labels labs
on tokens.contract_address = labs.address
where tokens.contract_address in (
select contract_address from tokenact
where n_txns >= {{min_txns}}
)
)
Run a query to Download Data