0xHaM-dAvalanche - Pool's Swap Performance copy
Updated 2024-10-19
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
›
⌄
-- forked from Avalanche - Pool's Swap Performance @ https://flipsidecrypto.xyz/edit/queries/81e4518a-a232-47ed-8dba-b5c92496249f
-- forked from Velodrome - Pool's Swap Performance @ https://flipsidecrypto.xyz/edit/queries/170a6a68-72a9-4791-a1be-5189f0b5f058
with swap_detail as (
SELECT
SYMBOL_IN || ' => ' || SYMBOL_OUT as "Pair",
count(distinct ORIGIN_FROM_ADDRESS) as "Swapper Count",
count(tx_hash) as "swaps Count",
sum(COALESCE(amount_in_usd, AMOUNT_OUT_USD,0)) as "Total Volume ($)"
FROM avalanche.defi.ez_dex_swaps
group by 1
)
,LAST7 AS ( -- swap volume in the last 7 days
SELECT
SYMBOL_IN || ' => ' || SYMBOL_OUT as "Pair",
count(distinct ORIGIN_FROM_ADDRESS) as "7D Swappers",
count(tx_hash) as "7D Tx",
round(sum(COALESCE(amount_in_usd, AMOUNT_OUT_USD,0)),1) as "7D"
FROM avalanche.defi.ez_dex_swaps
WHERE date_trunc('hour', block_timestamp) BETWEEN DATEADD(HOUR, -168, GETDATE()) AND GETDATE()
GROUP BY 1
),
LAST14 AS (-- swap volume in the last 14 days
SELECT
SYMBOL_IN || ' => ' || SYMBOL_OUT as "Pair",
count(distinct ORIGIN_FROM_ADDRESS) as "14D Swappers",
count(tx_hash) as "14D Tx",
round(sum(COALESCE(amount_in_usd, AMOUNT_OUT_USD,0)),1) as "14D"
FROM avalanche.defi.ez_dex_swaps
WHERE date_trunc('hour', block_timestamp) BETWEEN DATEADD(HOUR, -336, GETDATE()) AND GETDATE()
GROUP BY 1
),
LAST30 AS ( -- swap volume in the last 30 days
SELECT
SYMBOL_IN || ' => ' || SYMBOL_OUT as "Pair",
QueryRunArchived: QueryRun has been archived