datavortexNew User Change
Updated 2024-11-26
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 weekly_data AS (
SELECT
DATE_TRUNC('week', block_timestamp) AS "week",
COUNT(DISTINCT tx_hash) AS "weekly swaps",
COUNT(DISTINCT sender) AS "weekly users",
SUM(amount_in_usd) AS "weekly swap volume",
COUNT(DISTINCT CASE WHEN sender NOT IN (SELECT DISTINCT sender FROM avalanche.defi.ez_dex_swaps WHERE block_timestamp < '2024-01-01') THEN sender END) AS "new_users"
FROM
avalanche.defi.ez_dex_swaps
WHERE
block_timestamp >= '2024-01-01'
AND block_timestamp <= '2024-12-31'
AND platform = 'uniswap-v3'
GROUP BY
DATE_TRUNC('week', block_timestamp)
),
weekly_change AS (
SELECT
"week",
"new_users",
LAG("new_users") OVER (ORDER BY "week") AS "previous_week_new_users",
CASE
WHEN LAG("new_users") OVER (ORDER BY "week") IS NULL THEN '0%'
WHEN "new_users" > LAG("new_users") OVER (ORDER BY "week") THEN
CONCAT(ROUND((( "new_users" - LAG("new_users") OVER (ORDER BY "week") ) / LAG("new_users") OVER (ORDER BY "week") ) * 100, 2), '% 🔼')
ELSE
CONCAT(ROUND((( "new_users" - LAG("new_users") OVER (ORDER BY "week") ) / LAG("new_users") OVER (ORDER BY "week") ) * 100, 2), '% 🔻')
END AS "new_user_change_percentage"
FROM
weekly_data
)
SELECT
weekly_change."week",
weekly_change."new_users",
QueryRunArchived: QueryRun has been archived