0xaimanprractice new user inflow
Updated 2021-10-22
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
›
⌄
⌄
--to define the frst date user joined, we must identify the date with the first nonzero balance
--dailybalances table is too be used to identifyy the and filterr the wallets balance on the corresponding date
/*with userjoindate as (select address, min(db.date) as datejoin, datediff(day,min(db.date),CURRENT_DATE) as durationsince
-- this is to filter the first date that get positive bbalance
FROM terra.daily_balances db
where balance_usd> 0
group by address
)
-- this operation is to idenify the number of user that shares the first non-0zero balance
select datejoin, count(address) as nuser
from userjoindate
where datejoin > '2021-01-01'
group by datejoin order by datejoin asc
*/
select msg_value:sender::string,
min(case when msg_value:contract='terra15gwkyepfc6xgca5t5zefzwy42uts8l2m4g40k6' then date(block_timestamp)end) as mirjoindate,
min(case when msg_value:contract='terra1sepfj7s0aeg5967uxnfk4thzlerrsktkpelm5s' then date(block_timestamp)end) as ancjoindate
from terra.msgs
group by 1
limit 10
Run a query to Download Data