cybergenlab[DEX Overview] Project Transactional Activity copy
Updated 2024-11-16
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
›
⌄
⌄
-- forked from [Defi Overview] Project Transactional Activity @ https://flipsidecrypto.xyz/studio/queries/b22362df-a147-4484-943e-bd020d3433c5
-- forked from [Ecosystem metrics] Project Activity (monthly) @ https://flipsidecrypto.xyz/studio/queries/cdbde7fd-ac6f-4f2a-83f8-3ebaa0c137d3
--Format monthly Dapp sectoral users for Pie Chart graph
/* Existing sectors in dim_labels
dex, dapp, operator, games, chadmin, bridge, token, nft, flotsam, cex, defi
*/
with sectors as (
select
date_trunc('month', block_timestamp) as time,
coalesce((label_type), 'other') as category,
label as protocol,
count(distinct tx_hash) as transactions,
from ethereum.core.fact_transactions as transactions
left join ethereum.core.dim_labels labels on transactions.to_address = labels.address
where block_timestamp >= dateadd(month, -1, date_trunc('month',current_date()))
and block_timestamp < date_trunc('month',current_date())
and category in ('dex')
and status = 'SUCCESS'
group by 1,2,3
)
select
time,
initcap(protocol) as protocol,
transactions
from sectors
order by 3
QueryRunArchived: QueryRun has been archived