0xHaM-dTFM Time based Cohort Wide (by Week) copy
Updated 2023-11-16
999
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 TFM Time based Cohort Wide (by Week) @ https://flipsidecrypto.xyz/edit/queries/857ffc32-545f-4122-979e-e31d5135634a
-- forked from Time based Cohort Wide (by Week) @ https://flipsidecrypto.xyz/edit/queries/19c5404b-21e9-466d-aba6-2dfd789ade0d
WITH create_daos as (
select
distinct tx_id
from terra.core.fact_msg_attributes_standard
where attribute_value = 'terra19hz374h6ruwtzrnm8ytkae782uv79h9yt9tuytgvt94t26c4793qnfg7vn'
)
,
qmain as (
select
block_timestamp,
tx_id,
TX_SENDER
from terra.core.fact_transactions
where tx_id in (SELECT * FROM create_daos)
)
,
base_table as ( -- this is data prep
select
TX_SENDER as address
, date_trunc('week', block_timestamp) as date
, min(date_trunc('week', block_timestamp)::date) over(partition by address) as earliest_date
, datediff(
'week'
, min(date_trunc('week', block_timestamp)) over(partition by address) -- earliest_date
, date_trunc('week', block_timestamp) -- current date in day
) as difference
from qmain
where block_timestamp BETWEEN '{{Start_date}}' and '{{End_date}}'
HAVING date_trunc('week', block_timestamp) < date_trunc('week', current_date())
)
, count_new_users as(
select
Run a query to Download Data