check_skedArbitrum Activity Share (Individual)
Updated 2023-04-06Copy Reference Fork
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 label_txs AS (
SELECT
label_type,
COUNT(*) AS txs
FROM arbitrum.core.fact_token_transfers
LEFT OUTER JOIN arbitrum.core.dim_labels ON contract_address = address
WHERE label_type IN ('dex', 'defi', 'dapp', 'nft', 'layer2', 'flotsam', 'cex', 'operator', 'chadmin')
AND block_timestamp >= CURRENT_DATE - interval '7 day'
GROUP BY label_type
)
SELECT
txs_dex,
ROUND(100 * txs_dex::NUMERIC / total_txs, 2) AS pct_share_dex,
txs_nft,
ROUND(100 * txs_nft::NUMERIC / total_txs, 2) AS pct_share_nft,
txs_defi,
ROUND(100 * txs_defi::NUMERIC / total_txs, 2) AS pct_share_defi,
txs_dapp,
ROUND(100 * txs_dapp::NUMERIC / total_txs, 2) AS pct_share_dapp,
txs_layer2,
ROUND(100 * txs_layer2::NUMERIC / total_txs, 2) AS pct_share_layer2,
txs_flotsam,
ROUND(100 * txs_flotsam::NUMERIC / total_txs, 2) AS pct_share_flotsam,
txs_cex,
ROUND(100 * txs_cex::NUMERIC / total_txs, 2) AS pct_share_cex,
txs_operator,
ROUND(100 * txs_operator::NUMERIC / total_txs, 2) AS pct_share_operator,
txs_chadmin,
ROUND(100 * txs_chadmin::NUMERIC / total_txs, 2) AS pct_share_chadmin
FROM (
SELECT
COALESCE(SUM(CASE WHEN label_type = 'dex' THEN txs END), 0) AS txs_dex,
COALESCE(SUM(CASE WHEN label_type = 'nft' THEN txs END), 0) AS txs_nft,
COALESCE(SUM(CASE WHEN label_type = 'defi' THEN txs END), 0) AS txs_defi,
COALESCE(SUM(CASE WHEN label_type = 'dapp' THEN txs END), 0) AS txs_dapp,
COALESCE(SUM(CASE WHEN label_type = 'layer2' THEN txs END), 0) AS txs_layer2,
Run a query to Download Data