keshanOsmosis: Unique Users
Updated 2022-06-04
99
1
2
3
4
5
6
7
8
9
10
11
12
13
›
⌄
with newusers as (select date, count(distinct tx_from) as newusers from
(select tx_from, min(block_timestamp)::date as date from osmosis.core.fact_transactions where block_timestamp <= CURRENT_DATE - 1
group by tx_from)
group by date),
daily_users as (select block_timestamp::date as date, count(distinct tx_from) as unique_users
from osmosis.core.fact_transactions where block_timestamp <= CURRENT_DATE - 1
group by date)
select d.date, newusers as "New Users", unique_users as "Unique Users", newusers * 100 / unique_users as "New users percentage"
from newusers full outer join daily_users d using(date)