Kruys-Collinsadorable-aqua
Updated 2025-03-17
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 base AS (
SELECT
DATE_TRUNC('day', block_timestamp) AS day,
CASE
WHEN token_in = LOWER('0xb2F85b7AB3c2b6f62DF06dE6aE7D09c010a5096E') THEN 'sell'
WHEN token_out = LOWER('0xb2F85b7AB3c2b6f62DF06dE6aE7D09c010a5096E') THEN 'buy'
END AS tag,
CASE
WHEN token_in = LOWER('0xb2F85b7AB3c2b6f62DF06dE6aE7D09c010a5096E') THEN symbol_out
WHEN token_out = LOWER('0xb2F85b7AB3c2b6f62DF06dE6aE7D09c010a5096E') THEN symbol_in
END AS swapped_with_symbol,
amount_in_usd,
amount_out_usd,
origin_from_address AS swapper
FROM
avalanche.defi.ez_dex_swaps
WHERE
(token_in = LOWER('0xb2F85b7AB3c2b6f62DF06dE6aE7D09c010a5096E')
OR token_out = LOWER('0xb2F85b7AB3c2b6f62DF06dE6aE7D09c010a5096E'))
AND block_timestamp >= DATEADD(day, -{{lookback_period}}, CURRENT_TIMESTAMP)
),
daily_swaps AS (
SELECT
day,
tag,
swapped_with_symbol,
COUNT(*) AS num_swaps,
SUM(CASE WHEN tag = 'buy' THEN amount_out_usd ELSE -amount_in_usd END) AS total_volume,
COUNT(DISTINCT swapper) AS total_swappers
FROM
base
GROUP BY
day, tag, swapped_with_symbol
)
QueryRunArchived: QueryRun has been archived