mr_d$EDOG SWAP ACTIVITY
Updated 2024-11-24
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 swaps AS (
SELECT
*,
DATE_TRUNC('day', BLOCK_TIMESTAMP) AS day,
CASE
WHEN BLOCK_TIMESTAMP >= DATEADD(hour, -24, CURRENT_TIMESTAMP) THEN 'last_24_hours'
WHEN BLOCK_TIMESTAMP >= DATEADD(day, -7, CURRENT_TIMESTAMP) THEN 'last_7_days'
ELSE 'All time'
END AS period
FROM aptos.defi.ez_dex_swaps
WHERE
token_in = '0x5e975e7f36f2658d4cf146142899c659464a3e0d90f0f4d5f8b2447173c06ef6::EDOG::EDOG'
OR token_out = '0x5e975e7f36f2658d4cf146142899c659464a3e0d90f0f4d5f8b2447173c06ef6::EDOG::EDOG'
),
metrics AS (
SELECT
period,
COUNT(*) AS total_GUI_swaps,
SUM(amount_in_usd) AS total_GUI_swap_volume,
AVG(amount_in_usd) AS average_GUI_swap_size,
MEDIAN(amount_in_usd) AS median_GUI_swap_size,
COUNT(DISTINCT swapper) AS total_unique_GUI_swappers
FROM swaps
GROUP BY period
)
SELECT
'⌚' || period as Period,
'💱' || COALESCE(total_GUI_swaps, 0) AS "Total $EDOG Swaps",
'💲' || COALESCE(total_GUI_swap_volume, 0) AS "Total $EDOG Swap Volume",
'💲' || COALESCE(average_GUI_swap_size, 0) AS "Average $EDOG Swap Size",
'💲' || COALESCE(median_GUI_swap_size, 0) AS "Median $EDOG Swap Size",
'👤' || COALESCE(total_unique_GUI_swappers, 0) AS "Total Swappers/Traders"
FROM metrics
ORDER BY
CASE
WHEN period = 'last_24_hours' THEN 1
QueryRunArchived: QueryRun has been archived