KARTODSUSHI New/Old Users per month
Updated 2022-06-25Copy 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
›
⌄
SELECT
ssq.time,
new_users as new,
(unique_users - new_users) as old
FROM (
SELECT -- new users per day
sq.time,
count(*) as new_users
FROM (
SELECT -- first trade made by user
origin_from_address as user,
MIN(date_trunc('month', BLOCK_TIMESTAMP)) as time
FROM ethereum.sushi.ez_swaps
GROUP BY 1
ORDER BY 1) sq
GROUP BY 1
) ssq
LEFT JOIN (
SELECT --unique users per month
date_trunc('month', BLOCK_TIMESTAMP) AS time,
COUNT(DISTINCT origin_from_address) as unique_users
FROM ethereum.sushi.ez_swaps
GROUP BY 1
ORDER BY 1
) t2 ON t2.time = ssq.time
ORDER BY 1 DESC
Run a query to Download Data