kaibladeNear Dapps Retention table copy copy
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 Near Dapps Retention table copy @ https://flipsidecrypto.xyz/edit/queries/f2fe82cd-f10a-4740-bdb7-13d2655755c6
-- forked from Near Dapps Retention table @ https://flipsidecrypto.xyz/edit/queries/3bda21f6-620e-4374-b123-5ea6b40585e3
WITH raw_data AS
(SELECT tx_receiver, COUNT(tx_hash) AS counter
FROM near.core.fact_transactions
WHERE BLOCK_TIMESTAMP::date >= CURRENT_DATE() -INTERVAL '7 months'
GROUP BY tx_receiver
ORDER BY counter DESC
LIMIT 5
),
users AS
(SELECT DISTINCT (date_trunc('month',block_timestamp)) AS active_period
,tx_signer AS address,
tx_receiver AS dapp
FROM near.core.fact_transactions
WHERE BLOCK_TIMESTAMP::date >= CURRENT_DATE() -INTERVAL '7 months'
AND tx_receiver IN (SELECT tx_receiver FROM raw_data)
),
initial_cohorts AS (
SELECT address,
dapp,
MIN(active_period) AS initial_period
FROM users
GROUP BY dapp, address
),
cohort_diff AS (
SELECT
user.address,
user.dapp,
user.active_period,
initial.initial_period,
Run a query to Download Data