Iacomustx count to project
Updated 2023-06-01
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 tx count to type @ https://flipsidecrypto.xyz/edit/queries/af2e5c91-92ad-4003-8b96-78fd27ab82c2
-- forked from tx count @ https://flipsidecrypto.xyz/edit/queries/b114d116-c524-45f2-af90-5dfd18884c39
--exclude mint/burn and then figure out contracts sent to
with tx as
(select block_timestamp, tx_hash,
from_address, to_address,
origin_from_address, origin_to_address,
symbol,amount
from avalanche.core.ez_token_transfers
where contract_address = '0xc891eb4cbdeff6e073e859e987815ed1505c2acd'
and block_timestamp >= date('2023-02-28') --contract creation
and (from_address <> '0x0000000000000000000000000000000000000000'
or to_address <> '0x0000000000000000000000000000000000000000')
),
labeled as
(select tx.*,
t.label_type to_type,
t.label_subtype to_subtype,
t.project_name to_project_name,
f.label_type from_type,
f.label_subtype from_subtype,
f.project_name from_project_name
from tx
left join avalanche.core.dim_labels t
on tx.origin_to_address = t.address
left join avalanche.core.dim_labels f
on tx.origin_from_address = f.address)
select coalesce(to_project_name, 'unidentified') type, count(*) tx_count
from labeled
group by 1