CURRENT_MONTH_VOLUME | PREVIOUS_MONTH_VOLUME | VOLUME_GROWTH_PERCENTAGE | CURRENT_MONTH_ACTIVE_USERS | PREVIOUS_MONTH_ACTIVE_USERS | ACTIVE_USERS_GROWTH_PERCENTAGE | CURRENT_MONTH_NEW_USERS | PREVIOUS_MONTH_NEW_USERS | NEW_USERS_GROWTH_PERCENTAGE | CURRENT_MONTH_SWAPS | PREVIOUS_MONTH_SWAPS | SWAPS_GROWTH_PERCENTAGE | CURRENT_MONTH_UNIQUE_TOKENS | PREVIOUS_MONTH_UNIQUE_TOKENS | UNIQUE_TOKENS_GROWTH_PERCENTAGE | CURRENT_MONTH_UNIQUE_PLATFORMS | PREVIOUS_MONTH_UNIQUE_PLATFORMS | UNIQUE_PLATFORMS_GROWTH_PERCENTAGE | CURRENT_MONTH_AVG_SWAP_VALUE | PREVIOUS_MONTH_AVG_SWAP_VALUE | AVG_SWAP_VALUE_GROWTH_PERCENTAGE | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
1 | 219675725.815492 | 206617217.525458 | 6.320145265 | 289647 | 41521 | 597.5916 | 253089 | 9332 | 2612.0553 | 13439936 | 12553406 | 7.0621 | 239 | 261 | -8.4291 | 11 | 11 | 0 | 16.344997909 | 16.459056413 | -0.6929832486 |
mr_dshared-cyan
Updated 9 days ago
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 user_first_swap AS (
SELECT
swapper,
MIN(block_timestamp) AS first_swap_timestamp -- First swap timestamp for each user
FROM
aptos.defi.ez_dex_swaps
GROUP BY
swapper
),
current_month AS (
SELECT
SUM(COALESCE(s.amount_in_usd, s.amount_out_usd, 0)) AS total_volume,
COUNT(DISTINCT s.swapper) AS active_users,
COUNT(DISTINCT CASE
WHEN ufs.first_swap_timestamp >= DATEADD(DAY, -30, CURRENT_DATE) THEN s.swapper
END) AS new_users,
COUNT(DISTINCT s.tx_hash) AS total_swaps,
COUNT(DISTINCT s.token_out) AS unique_tokens,
COUNT(DISTINCT s.platform) AS unique_platforms,
SUM(COALESCE(s.amount_in_usd, s.amount_out_usd, 0)) / NULLIF(COUNT(DISTINCT s.tx_hash), 0) AS avg_swap_value
FROM
aptos.defi.ez_dex_swaps s
LEFT JOIN
user_first_swap ufs
ON s.swapper = ufs.swapper
WHERE
s.block_timestamp >= DATEADD(DAY, -30, CURRENT_DATE) -- Last 30 days
),
previous_month AS (
SELECT
SUM(COALESCE(s.amount_in_usd, s.amount_out_usd, 0)) AS total_volume,
COUNT(DISTINCT s.swapper) AS active_users,
COUNT(DISTINCT CASE
WHEN ufs.first_swap_timestamp BETWEEN DATEADD(DAY, -60, CURRENT_DATE) AND DATEADD(DAY, -30, CURRENT_DATE) THEN s.swapper
END) AS new_users,
COUNT(DISTINCT s.tx_hash) AS total_swaps,
Last run: 9 days ago
1
182B
17s