mucrypto2023-03-06 02:01 PM
Updated 2023-03-06
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
›
⌄
with wallets as (select
date_trunc('day', block_timestamp) as day,
from_address as sending,
to_address as receiving,
count(tx_hash) as interactions
from ethereum.core.fact_transactions
where block_timestamp::date between '2022-05-04' and '2022-05-15'
and to_address is not null
group by 1,2,3
qualify row_number() over (partition by day order by interactions desc) <= 5),
labels_from as (select
address as sending_address,
address_name as sending_name
from ethereum.core.dim_labels
where address in (select sending from wallets)
group by 1,2),
labels_to as (select
address as receiving_address,
address_name as receiving_name
from ethereum.core.dim_labels
where receiving_address in (select receiving from wallets)
group by 1,2)
select wallets.day,
wallets.interactions,
labels_from.sending_name,
labels_from.sending_address,
labels_to.receiving_name,
wallets.sending,
wallets.receiving,
case
when labels_from.sending_name = 'coinbase 5' and labels_to.receiving_name = 'hop protocol: usdt l1canonicaltoken' then 'Coinbase with USDT'
when wallets.sending = ('0x4d9ff50ef4da947364bb9650892b2554e7be5e2b') and labels_to.receiving_address = ('0x0b95993a39a363d99280ac950f5e4536ab5c5566') then 'Binance with Binance'
when labels_from.sending_name = 'coinbase 11' and labels_to.receiving_name = 'terra: luna token' then 'Coinbase with LUNA'
Run a query to Download Data