SajjadiiiFL Us Retention
Updated 2022-12-19Copy 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
25
26
27
28
29
30
31
32
33
34
35
36
›
⌄
WITH base_Flow AS (
SELECT date_trunc('MONTH' , block_timestamp) AS Date , Count(DISTINCT proposer) AS users_count, Count(DISTINCT tx_id) AS tx_count
FROM flow.core.fact_transactions WHERE proposer IN (SELECT proposer FROM flow.core.fact_transactions WHERE block_timestamp::date >= '2022-01-01' AND tx_succeeded = TRUE GROUP BY 1 HAVING COUNT(DISTINCT tx_id) > 2000 )
AND block_timestamp::date >= '2022-01-01' AND tx_succeeded = TRUE GROUP BY 1
),
base_ethereum AS (
SELECT date_trunc('MONTH' , block_timestamp)AS Date, Count(DISTINCT from_address) AS users_count , Count(DISTINCT tx_hash) AS tx_count
FROM ethereum.core.fact_transactions WHERE from_address IN (SELECT from_address FROM ethereum.core.fact_transactions WHERE block_timestamp::date >= '2022-01-01' AND status = 'SUCCESS' GROUP BY 1 HAVING COUNT(DISTINCT tx_hash) > 2000 )
AND block_timestamp::date >= '2022-01-01' AND status = 'SUCCESS' GROUP BY 1
),
base_optimism AS (
SELECT date_trunc('MONTH' , block_timestamp)AS Date, Count(DISTINCT from_address) AS users_count , Count(DISTINCT tx_hash) AS tx_count
FROM optimism.core.fact_transactions WHERE from_address IN (SELECT from_address FROM optimism.core.fact_transactions WHERE block_timestamp::date >= '2022-01-01' AND status = 'SUCCESS' GROUP BY 1 HAVING COUNT(DISTINCT tx_hash) > 2000 )
AND block_timestamp::date >= '2022-01-01' AND status = 'SUCCESS' GROUP BY 1
),
base_gnosis AS (
SELECT date_trunc('MONTH' , block_timestamp)AS Date, Count(DISTINCT from_address) AS users_count , Count(DISTINCT tx_hash) AS tx_count
FROM gnosis.core.fact_transactions WHERE from_address IN (SELECT from_address FROM gnosis.core.fact_transactions WHERE block_timestamp::date >= '2022-01-01' AND status = 'SUCCESS' GROUP BY 1 HAVING COUNT(DISTINCT tx_hash) > 2000 )
AND block_timestamp::date >= '2022-01-01' AND status = 'SUCCESS' GROUP BY 1
),
base_avalanche AS (
SELECT date_trunc('MONTH' , block_timestamp)AS Date, Count(DISTINCT from_address) AS users_count , Count(DISTINCT tx_hash) AS tx_count
FROM avalanche.core.fact_transactions WHERE from_address IN (SELECT from_address FROM avalanche.core.fact_transactions WHERE block_timestamp::date >= '2022-01-01' AND status = 'SUCCESS' GROUP BY 1 HAVING COUNT(DISTINCT tx_hash) > 2000 )
AND block_timestamp::date >= '2022-01-01' AND status = 'SUCCESS' GROUP BY 1
),
base_osmosis AS (
SELECT date_trunc('MONTH' , block_timestamp)AS Date, Count(DISTINCT tx_from) AS users_count , Count(DISTINCT tx_id) AS tx_count
FROM osmosis.core.fact_transactions WHERE tx_from IN (SELECT tx_from FROM osmosis.core.fact_transactions WHERE block_timestamp::date >= '2022-01-01' AND tx_status = 'SUCCEEDED' GROUP BY 1 HAVING COUNT(DISTINCT tx_id) > 2000 )
Run a query to Download Data