levanaCohort Retention
Updated 2024-10-09
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 Levana Daily traders osmo @ https://flipsidecrypto.xyz/edit/queries/46940d71-c7f1-4f30-ac75-b1c6fb9e8f90
with main AS (
SELECT a_pos_owner.tx_id, a_pos_owner.block_timestamp,
a_pos_owner.attribute_value AS trader
FROM
osmosis.core.fact_msg_attributes AS a_pos_owner
WHERE a_pos_owner.msg_type like 'wasm-position%'
AND a_pos_owner.attribute_key = 'pos-owner' and TX_SUCCEEDED=TRUE ),
-- forked from NEAR Cohort Retention @ https://flipsidecrypto.xyz/TheLaughingMan/q/EETNzgMr7QCN/near-cohort-retention
-- by
base_table as (
select
Trader as user
, date_trunc('week', block_timestamp) as date
, min(date_trunc('week', block_timestamp)) over(partition by user) as earliest_date
, datediff(
'Week'
, min(date_trunc('week', block_timestamp)) over(partition by user) -- earliest_date
, date_trunc('week', block_timestamp) -- current date in Week
) as difference
from main
where 1=1
and block_timestamp >= current_timestamp() - interval '1 year'
)
, count_new_users as(
select
earliest_date
, count(distinct user) as new_users
from base_table
group by 1
)
, count_returning_users as(
QueryRunArchived: QueryRun has been archived