BlockTrackerBreaking down usage
Updated 2023-09-04
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
›
⌄
--Average + median swap amount
--% of users who have used Squid more than once
--Overall % of expressed vs. regular transactions **
--Broken down by chain
--Broken down by tx size (<$10, $10-250, $250-$1000, >1000)
with users_more_than_one as (
SELECT
sender,
count(DISTINCT tx_hash) as n_txs
FROM axelar.core.ez_squid
GROUP BY 1
HAVING n_txs >=2
),
tx_size as (
SELECT
sender,
CASE when amount<10 then '<$10'
when amount >= 10 AND amount<250 then '$10-250'
when amount >=250 AND amount < 1000 then '$250-$1000'
else '>1000' end as direction
FROM axelar.core.ez_squid
)
SELECT
source_chain ||' --> ' || destination_chain as chain_pair,
c.direction,
count(DISTINCT a.sender) as total_users,
count(DISTINCT b.sender) as user_more_than_one_txs,
AVG(amount) as avg_volume,
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY amount) as median_volume,
sum(amount) as volume
FROM axelar.core.ez_squid a
LEFT JOIN users_more_than_one b using(sender)
LEFT JOIN tx_size c using(sender)
GROUP BY 1 , 2
Run a query to Download Data