adriaparcerisasNew Terra User Inflow
Updated 2022-05-10
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
›
⌄
WITH join_date_per_user as (
-- Derive join date for each wallet address by getting the earliest date with a positive balance (whether staked or not)
SELECT
ADDRESS,
MIN(db.DATE) join_date,
DATEDIFF(day, MIN(db.DATE), CURRENT_DATE) user_age_days
FROM terra.daily_balances db
WHERE balance_usd > 0
GROUP BY 1
ORDER by 2
),
join_date_per_user_dapps as (
SELECT
msg_value:sender::string sender_address,
MIN(CASE WHEN msg_value:contract = 'terra15gwkyepfc6xgca5t5zefzwy42uts8l2m4g40k6' THEN DATE(block_timestamp) END) mirror_join_date,
MIN(CASE WHEN msg_value:contract = 'terra1sepfj7s0aeg5967uxnfk4thzlerrsktkpelm5s' THEN DATE(block_timestamp) END) anchor_join_date,
DATEDIFF(day, MIN(CASE WHEN msg_value:contract = 'terra15gwkyepfc6xgca5t5zefzwy42uts8l2m4g40k6' THEN DATE(block_timestamp) END), CURRENT_DATE + 1) mirror_user_age_days,
DATEDIFF(day, MIN(CASE WHEN msg_value:contract = 'terra1sepfj7s0aeg5967uxnfk4thzlerrsktkpelm5s' THEN DATE(block_timestamp) END), CURRENT_DATE + 1) anchor_user_age_days
FROM terra.msgs
GROUP BY 1
ORDER BY 2
)
SELECT
join_date,
COUNT(ADDRESS) new_users -- Addresses are already unique
FROM
join_date_per_user u
LEFT JOIN join_date_per_user_dapps d ON u.ADDRESS = d.sender_address
WHERE join_date >= CURRENT_DATE-90 -- We'll focus last 3 moths
GROUP BY 1
ORDER BY 1
Run a query to Download Data