BlockTrackerosmosis copy
Updated 2023-04-19
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
36
›
⌄
⌄
-- forked from osmosis @ https://flipsidecrypto.xyz/edit/queries/4cedda57-2c6a-4467-a3dc-58c3568f047b
/*SELECT
date_trunc('day', block_timestamp) AS date,
count(DISTINCT tx_from) AS n_active_users
FROM
osmosis.core.fact_transactions
WHERE
BLOCK_TIMESTAMP >= '2022-09-01'
AND BLOCK_TIMESTAMP < CURRENT_TIMESTAMP
GROUP by
1
ORDER by date;*/
with active_users as (SELECT date_trunc('day', block_timestamp) as date,
COUNT(DISTINCT tx_from) as n_active_users
FROM osmosis.core.fact_transactions
GROUP BY date),
new_users as (
SELECT date_trunc('day', first_txn) as date,
COUNT(tx_from) as n_new_users
FROM
(SELECT tx_from,
min(block_timestamp) as first_txn -- ?
FROM osmosis.core.fact_transactions
GROUP BY tx_from) -- why croup by tx_from
GROUP BY date),
tx_succeeded as (
SELECT
date_trunc('day', block_timestamp) AS date,
sum(CASE WHEN tx_succeeded = 'TRUE' THEN 1 ELSE 0 END) as successful_txns,
sum(CASE WHEN tx_succeeded != 'TRUE' THEN 1 ELSE 0 END) as unsuccessful_txns,
successful_txns + unsuccessful_txns as n_txns
FROM
Run a query to Download Data