ThatGuyOsmo - Daily Active User 8. ibc from
Updated 2022-10-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
›
⌄
WITH
active_users AS
(
SELECT
DISTINCT active_users
FROM (
SELECT
date_trunc('week', block_timestamp) AS date,
tx_from AS active_users,
COUNT(DISTINCT date_trunc('day', block_timestamp)) AS unique_week_days
FROM osmosis.core.fact_transactions
WHERE tx_status ilike 'SUCCEEDED'
GROUP BY 1, 2
)
WHERE unique_week_days >= 4 -- four active days out of 7 days of a week (majority)
)
,
other_users as (
SELECT
DISTINCT tx_from as other_users
FROM osmosis.core.fact_transactions
WHERE tx_status ilike 'SUCCEEDED'
AND tx_from NOT IN (SELECT active_users FROM active_users)
)
SELECT
CASE WHEN STARTSWITH(LEFT(SENDER, POSITION('1' IN SENDER) - 1), '0x') THEN NULL ELSE INITCAP(LEFT(SENDER, POSITION('1' IN SENDER) - 1), ' ') END as "Sender Network",
count(tx_id) as "Tx Count"
FROM osmosis.core.fact_transfers
WHERE RECEIVER IN (SELECT active_users FROM active_users)
AND tx_status ilike 'SUCCEEDED'
AND "Sender Network" IS NOT NULL
GROUP BY 1
HAVING "Tx Count" > 10000
ORDER BY "Tx Count" DESC
Run a query to Download Data