veadoASGARDEX SWAP volume
Updated 2023-05-19Copy 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
›
⌄
-- SWAP volume by identifier (999 = ASGARDEX)
-- Credits to @pietrekt and @Multipartite
-- See discussion at THORChain Discord https://discord.com/channels/838986635756044328/892129741790933092/1061211917852618862
WITH swaps_filtered AS (
SELECT DISTINCT MIN(DATE(block_timestamp)) as date,
tx_id,
MAX(from_amount_usd) as swap_volume_usd,
MAX(min_to_amount) as min_amount
FROM thorchain.core.fact_swaps GROUP BY tx_id),
total_volume AS (
SELECT DISTINCT date,
RIGHT(min_amount, 3) AS identifier,
SUM(swap_volume_usd) OVER(PARTITION BY date, identifier) as total
FROM swaps_filtered
WHERE (identifier = '999')
),
cumulatives AS (
SELECT *,
SUM(total) OVER(PARTITION BY identifier ORDER BY date ASC) AS totalcount
FROM total_volume
)
SELECT date, total, totalcount
FROM cumulatives
WHERE date IS NOT NULL
ORDER BY date DESC
Run a query to Download Data