Abbas_ra21flow retention #10
Updated 2022-12-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 tb AS (select
min(date_trunc('month',BLOCK_TIMESTAMP)) AS month,
BUYER
from flow.core.ez_nft_sales where BLOCK_TIMESTAMP::date >= '2022-01-01' and TX_SUCCEEDED='TRUE' group by 2),
tb1 AS (
select
month,
COUNT(DISTINCT BUYER) AS "new buyers number"
from tb group by 1
),
tb2 AS (select
date_trunc('month',BLOCK_TIMESTAMP) AS month,
count(distinct BUYER) AS "buyers number"
from flow.core.ez_nft_sales where BLOCK_TIMESTAMP::date >= '2022-01-01' and TX_SUCCEEDED='TRUE' group by 1),
flow AS (select
A.month,
"new buyers number",
"buyers number"-"new buyers number" AS "Returning buyers number"
from tb1 A inner join tb2 B on A.month=B.month),
te AS (select
min(date_trunc('month',BLOCK_TIMESTAMP)) AS month,
BUYER_ADDRESS
from ethereum.core.ez_nft_sales where BLOCK_TIMESTAMP::date >= '2022-01-01' group by 2),
te1 AS (
select
month,
COUNT(DISTINCT BUYER_ADDRESS) AS "new buyers number"
from te group by 1
),
te2 AS (select
date_trunc('month',BLOCK_TIMESTAMP) AS month,
count(distinct BUYER_ADDRESS) AS "buyers number"
from ethereum.core.ez_nft_sales where BLOCK_TIMESTAMP::date >= '2022-01-01' group by 1),
ETH AS (select
A.month,
"new buyers number",
Run a query to Download Data