binhachon3. [Hard] Alakazam - UST transaction volume
Updated 2021-12-20
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 degenbox_tx_id as(
select
tx_id
from
ethereum.udm_events
where
(from_address = '0xd96f48665a1410c0cd669a88898eca36b9fc2cce' or to_address = '0xd96f48665a1410c0cd669a88898eca36b9fc2cce')
), UST_transactions as (
select
distinct
block_timestamp,
tx_id,
amount,
case when (from_address = '0xd96f48665a1410c0cd669a88898eca36b9fc2cce' or to_address = '0xd96f48665a1410c0cd669a88898eca36b9fc2cce') then 'Degenbox'
else 'Others' end as category
from
ethereum.udm_events
where
contract_address = '0xa47c8bf37f92abed4a126bda807a7b7498661acd'
and
block_timestamp > getdate() - interval'180 days'
and tx_id not in (
select
tx_id
from
degenbox_tx_id
)
)
select
date_trunc('week', block_timestamp) as blocktime,
category,
coalesce(sum(amount),0) as amount,
count(tx_id) as frequency
from
UST_transactions
group by
Run a query to Download Data