KaskoazulCount of New_users_transactions
Updated 2022-03-09
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
›
⌄
-- Question 160: Make a table containing wallet addresses whose first transaction was 90 days ago or less.
-- Analyze how active they have been since that first transaction, based on either
-- 1) the number of transactions;
-- 2) the number of protocols interacted with, or
-- 3) the number of different types of transactions undertaken. (deposit, delegate, vote)
-- Note: grand prize-winning submissions will assess at least two of the activity metrics above.
WITH NEW_USER_ALLTIME AS(
SELECT
tx_id, tx_from as new_user,
min(block_timestamp) as creation_date
FROM
terra.transactions
WHERE TX_STATUS = 'SUCCEEDED'
GROUP BY tx_id, tx_from
),
NEW_USER_90DAYS AS(
SELECT
tx_id, new_user[0] as users
FROM
NEW_USER_ALLTIME
WHERE
creation_date >= CURRENT_DATE - 90
AND array_size (new_user) = 1
)
SELECT
count(distinct tx_id), count(distinct users)
--TX_FROM as WALLET, COUNT(TX_ID) AS TRANSACTIONS_NUMBER
FROM
NEW_USER_90DAYS
Run a query to Download Data