datavortexTotals
Updated 2024-11-27
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
›
⌄
⌄
/*
SELECT COUNT(DISTINCT tx_hash) AS "total swaps",
COUNT(DISTINCT origin_from_address) AS "total users",
SUM(amount_in_usd) AS "total swap volume",
COUNT(DISTINCT tx_hash) *1.0 /COUNT(DISTINCT origin_from_address) AS "average tx per user",
SUM(amount_in_usd) *1.0 /COUNT(DISTINCT origin_from_address) AS "average usd per user"
FROM kaia.defi.ez_dex_swaps
WHERE block_timestamp > CURRENT_TIMESTAMP - INTERVAL '30 days';
*/
WITH total AS (
SELECT
SUM(amount_in_usd) AS "total swap volume",
COUNT(DISTINCT tx_hash) AS "total swaps",
COUNT(DISTINCT origin_from_address) AS "total users"
FROM
kaia.defi.ez_dex_swaps
WHERE
block_timestamp > CURRENT_TIMESTAMP - INTERVAL '30 days'
),
averages AS (
SELECT
"total swap volume" / NULLIF("total users", 0) AS "average usd per user",
"total swaps" * 1.0 / NULLIF("total users", 0) AS "average tx per user"
FROM
total
),
daily AS (
SELECT
DATE(block_timestamp) AS "day",
SUM(amount_in_usd) AS "daily volume",
COUNT(DISTINCT tx_hash) AS "daily swaps",
COUNT(DISTINCT origin_from_address) AS "daily users"
QueryRunArchived: QueryRun has been archived