headitmanagerTotal users who have used MARS
Updated 2022-03-07
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
›
⌄
with tbl1 as
(
select distinct tx_from[0] as all_address from terra.transactions where
date(block_timestamp) <= DATEADD(DAY, -90, CURRENT_DATE) and tx_from[0] is not null
)
,tbl2 as
(
select distinct tx_from[0] as new_address from terra.transactions where
date(block_timestamp) > DATEADD(DAY, -90, CURRENT_DATE) and tx_from[0] is not null
)
,tbl3 as
(select new_address from tbl2 left join tbl1 on tbl2.new_address=tbl1.all_address where all_address is null
)
select count(distinct msg_value:sender) as users ,
(
case
WHEN msg_value:contract='terra1ar3xdk4fnu4tm69w7gwv20nnam7aqc7wqjnp8y' then 'Manifesto'
WHEN msg_value:contract='terra1cx2c7pkchltqfstev03hns79d93tmqeht5msxt' then 'Manifesto Medal'
WHEN msg_value:contract='terra1n38982txtv2yygtcfv3e9wp2ktmjyxl6z88rma' then 'Lockdrop'
end
) as projects
from terra.msgs
where
(
msg_value:contract in
(
'terra1ar3xdk4fnu4tm69w7gwv20nnam7aqc7wqjnp8y', 'terra1cx2c7pkchltqfstev03hns79d93tmqeht5msxt', 'terra1n38982txtv2yygtcfv3e9wp2ktmjyxl6z88rma'
)
)
and msg_value:sender in (select new_address from tbl3)
group by projects
order by users desc
Run a query to Download Data