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 ),

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(