nickpayiatis_2023-03-02 10:54 AM copy
Updated 2023-03-02
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 8f2af39f-126b-4029-9564-a1de1fe77de0
WITH quarter_cohort AS (
SELECT
block_timestamp,
DATE_TRUNC('quarter', block_timestamp::date) AS quarter,
TX_SIGNER
FROM near.core.fact_transactions
WHERE block_timestamp::date BETWEEN DATEADD(quarter, -6, CURRENT_DATE) AND CURRENT_DATE
AND TX_STATUS = 'Success'
GROUP BY 1, 2, 3
),
cohort_size AS (
SELECT
quarter,
COUNT(DISTINCT TX_SIGNER) AS quarter_cohort_size
FROM quarter_cohort
GROUP BY 1
)
--,retention AS (
SELECT
quarter_cohort.quarter,
cohort_size.quarter_cohort_size,
DATE_TRUNC('month', DATEADD(month, 1, quarter_cohort.quarter)) AS month1,
DATE_TRUNC('month', DATEADD(month, 2, quarter_cohort.quarter)) AS month2,
DATE_TRUNC('month', DATEADD(month, 3, quarter_cohort.quarter)) AS month3,
DATE_TRUNC('month', DATEADD(month, 4, quarter_cohort.quarter)) AS month4,
DATE_TRUNC('month', DATEADD(month, 5, quarter_cohort.quarter)) AS month5,
DATE_TRUNC('month', DATEADD(month, 6, quarter_cohort.quarter)) AS month6,
COUNT(DISTINCT CASE WHEN block_timestamp::date BETWEEN quarter_cohort.quarter AND DATEADD(month, 1, quarter_cohort.quarter)::date - 1 THEN TX_SIGNER END) AS month1_retention,
COUNT(DISTINCT CASE WHEN block_timestamp::date BETWEEN DATEADD(month, 1, quarter_cohort.quarter)::date AND DATEADD(month, 2, quarter_cohort.quarter)::date - 1 THEN TX_SIGNER END) AS month2_retention,
COUNT(DISTINCT CASE WHEN block_timestamp::date BETWEEN DATEADD(month, 2, quarter_cohort.quarter)::date AND DATEADD(month, 3, quarter_cohort.quarter)::date - 1 THEN TX_SIGNER END) AS month3_retention,
COUNT(DISTINCT CASE WHEN block_timestamp::date BETWEEN DATEADD(month, 3, quarter_cohort.quarter)::date AND DATEADD(month, 4, quarter_cohort.quarter)::date - 1 THEN TX_SIGNER END) AS month4_retention,
COUNT(DISTINCT CASE WHEN block_timestamp::date BETWEEN DATEADD(month, 4, quarter_cohort.quarter)::date AND DATEADD(month, 5, quarter_cohort.quarter)::date - 1 THEN TX_SIGNER END) AS month5_retention,
COUNT(DISTINCT CASE WHEN block_timestamp::date BETWEEN DATEADD(month, 5, quarter_cohort.quarter)::date AND DATEADD(month, 6, quarter_cohort.quarter)::date - 1 THEN TX_SIGNER END) AS month6_retention
FROM quarter_cohort
Run a query to Download Data