datavortexdry-red
Updated 2024-10-12
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
›
⌄
WITH swaps_metrics AS (
SELECT
swapper AS "total swappers",
symbol_in AS "sold symbol", -- Renamed for clarity
symbol_out AS "bought symbol", -- Renamed for clarity
COALESCE(amount_in_usd, 0) AS "sold amount", -- Amount for sold tokens
COALESCE(amount_out_usd, 0) AS "bought amount" -- Amount for bought tokens
FROM
aptos.defi.ez_dex_swaps
WHERE
(symbol_in = 'USDT' OR symbol_out = 'USDT')
AND block_timestamp BETWEEN '2024-01-01' AND '2024-12-31'
)
SELECT
"total swappers",
SUM(CASE
WHEN "sold symbol" = 'USDT' THEN COALESCE("sold amount", 0) -- Total USDT sold
ELSE 0
END) AS "Total USDT Volume Sold", -- Volume of USDT sold
SUM(CASE
WHEN "bought symbol" = 'USDT' THEN COALESCE("bought amount", 0) -- Total USDT bought
ELSE 0
END) AS "Total USDT Volume Bought" -- Volume of USDT bought
FROM
swaps_metrics
GROUP BY
"total swappers"
ORDER BY
"Total USDT Volume Sold" DESC -- Order by the volume of USDT sold
LIMIT
10;
QueryRunArchived: QueryRun has been archived