Elprognerd1 - new users
Updated 2023-05-16Copy 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
27
28
29
30
31
32
33
34
35
36
›
⌄
WITH ethereum AS (
SELECT ORIGIN_FROM_ADDRESS AS users,
MIN(BLOCK_TIMESTAMP)::DATE AS min_date
FROM ethereum.core.ez_dex_swaps
WHERE PLATFORM ILIKE '%uniswap%' AND BLOCK_TIMESTAMP >= CURRENT_DATE - interval '365 days'
GROUP BY 1
),
optimism AS (
SELECT ORIGIN_FROM_ADDRESS AS users,
MIN(BLOCK_TIMESTAMP)::DATE AS min_date
FROM optimism.core.ez_dex_swaps
WHERE PLATFORM ILIKE '%uniswap%' AND BLOCK_TIMESTAMP >= CURRENT_DATE - interval '365 days'
GROUP BY 1
),
polygon AS (
SELECT ORIGIN_FROM_ADDRESS AS users,
MIN(BLOCK_TIMESTAMP)::DATE AS min_date
FROM polygon.core.ez_dex_swaps
WHERE PLATFORM ILIKE '%uniswap%' AND BLOCK_TIMESTAMP >= CURRENT_DATE - interval '365 days'
GROUP BY 1
)
SELECT 'Ethereum' AS chain,
DATE_TRUNC('month', min_date) AS month,
COUNT(DISTINCT users) AS new_users,
SUM(new_users) OVER (ORDER BY month) AS cumulative_new_users
FROM ethereum
GROUP BY 1, 2
UNION ALL
SELECT 'Optimism' AS chain,
DATE_TRUNC('month', min_date) AS month,
Run a query to Download Data