zakkisyedUniswap: New users in April - Crosschain
Updated 2023-05-14
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_table AS (
SELECT
TRADER,
BLOCKCHAIN,
BLOCK_TIMESTAMP::DATE AS TX_DATE,
MIN(BLOCK_TIMESTAMP::DATE) OVER (PARTITION BY TRADER) AS FIRST_TX_DATE
FROM crosschain.core.ez_dex_swaps
WHERE PLATFORM LIKE 'uniswap%'
AND BLOCK_TIMESTAMP >= '2023-04-01' AND BLOCK_TIMESTAMP < '2023-05-01'
),
daily_new_users AS (
SELECT
BLOCKCHAIN,
TX_DATE,
COUNT(DISTINCT CASE WHEN TX_DATE = FIRST_TX_DATE THEN TRADER END) AS DAILY_NEW_USER_COUNT
FROM base_table
GROUP BY BLOCKCHAIN, TX_DATE
),
cumulative_new_users AS (
SELECT
BLOCKCHAIN,
TX_DATE,
SUM(DAILY_NEW_USER_COUNT) OVER (PARTITION BY BLOCKCHAIN ORDER BY TX_DATE) AS CUMULATIVE_USER_COUNT
FROM daily_new_users
),
daily_transactions AS (
SELECT
BLOCKCHAIN,
TX_DATE,
COUNT(*) AS DAILY_TX_COUNT
FROM base_table
GROUP BY BLOCKCHAIN, TX_DATE
),
Run a query to Download Data