nsa2000osmo met2
Updated 2023-02-11
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
›
⌄
with new_osmosis_users AS (
SELECT
tx_from as address,
count(distinct tx_id) as tx_counts,
count(distinct date(block_timestamp)) as days_active,
min(date(block_timestamp)) as first_tx,
max(date(block_timestamp)) as last_tx,
datediff('day', last_tx, getdate()) as days_last_active,
datediff('day', first_tx, getdate()) as age_today
FROM osmosis.core.fact_transactions
group by 1
),
new_users AS (
SELECT
first_tx,
count(distinct address) as "New Users",
sum("New Users") over (order by first_tx) as "Cumulative New"
FROM new_osmosis_users
GROUP BY 1
),
active_users AS (
SELECT
date(block_timestamp) as date,
count(distinct tx_from) as "Distinct Wallets",
count(distinct tx_id) as tx_counts
FROM osmosis.core.fact_transactions
where date >='2023-01-01'
GROUP BY 1
)
SELECT
*
FROM active_users
LEFT JOIN new_users ON first_tx = date
ORDER BY 1 DESC
Run a query to Download Data