rajsOsmosis
Updated 2022-11-01Copy Reference Fork
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
›
⌄
WITH days AS (
SELECT DATE_TRUNC('DAY', block_timestamp) as day, tx_from as user_address
FROM osmosis.core.fact_transactions
WHERE block_timestamp BETWEEN current_date - 367 and current_date - 2
GROUP BY day, tx_from
),
week AS (
SELECT DATE_TRUNC('WEEK', day) as week, user_address, COUNT(*) as num_days
FROM days
GROUP BY week, user_address
HAVING num_days >= {{num_days}}
),
active_users AS (
SELECT week, COUNT(*) as num_active_users
FROM week
GROUP BY week
)
SELECT *
FROM active_users
order by 1
Run a query to Download Data