SELECT *, rank()over(partition by tx_sender ORDER by block_timestamp) as rank

FROM terra.core.fact_transactions

),

basic as (

SELECT date_trunc('day',block_timestamp) as date,

count( DISTINCT tx_sender) as wallet

from terra.core.fact_transactions

group BY date

),

advance as (

SELECT date_Trunc('day',b.block_timestamp) as date,

count(DISTINCT a.tx_sender) as wallet

FROM ranked a, ranked b

WHERE a.rank = b.rank-1 and a.tx_sender = b.tx_sender

AND datediff (day, a.block_timestamp , a.block_timestamp)<=30

group BY date

SELECT x.date,

x.wallet as measure_a,

y.wallet as measure_b

FROM basic x

LEFT JOIN advance y

ON x.date = y.date

WHERE x.date <= CURRENT_DATE-1