rackhaelNear Social 7 - Returning Users
Updated 2023-06-30
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 Ario / Near Social / Returning Users @ https://flipsidecrypto.xyz/Ario/q/RGjL1u880A0L/near-social-returning-users
-- Checkout more of Ario's work here : https://flipsidecrypto.xyz/Ario/dive-into-near-social-HiV4sg
--CC @adriaparcerisas
-- forked from adriaparcerisas / near social 230623 2 @ https://flipsidecrypto.xyz/adriaparcerisas/q/h3mfWj4uFlfL/near-social-230623-2
WITH
news AS (
SELECT DISTINCT
signer_id,
MIN(TRUNC(block_timestamp, 'week')) AS debut
FROM
near.social.fact_addkey_events
GROUP BY
1
),
user_activity AS (
SELECT
TRUNC(block_timestamp, 'week') AS week,
signer_id,
COUNT(DISTINCT tx_hash) AS events
FROM
near.social.fact_addkey_events
GROUP BY
1,
2
),
retention AS (
SELECT
ua1.week AS week,
COUNT(DISTINCT ua1.signer_id) AS users,
COUNT(DISTINCT ua2.signer_id) AS returning_users
FROM
user_activity ua1
LEFT JOIN user_activity ua2 ON ua1.signer_id = ua2.signer_id
AND ua2.week < ua1.week
JOIN news n ON ua1.week = n.debut
Run a query to Download Data