SELECT

DATE(e.BLOCK_TIMESTAMP) AS date,

e.tx_hash AS txn

FROM aptos.core.fact_events e

WHERE e.ACCOUNT_ADDRESS IN ('0x34ca84470e8d2907562f9a2f144c6c780282953b8b025ba220b0ecc5fc0aead9')

AND e.EVENT_TYPE = '0x1::coin::DepositEvent'

AND DATE(e.BLOCK_TIMESTAMP) >= CURRENT_DATE() - 34

),

sender_first_txn AS (

SELECT

SENDER,

MIN(DATE(BLOCK_TIMESTAMP)) AS first_txn_date

FROM aptos.core.fact_transactions

GROUP BY SENDER

),

retention AS (

SELECT

sft.SENDER,

sft.first_txn_date AS d0,

DATEADD(DAY, 1, sft.first_txn_date) AS d1,

DATEADD(DAY, 3, sft.first_txn_date) AS d3,

DATEADD(DAY, 7, sft.first_txn_date) AS d7,

DATEADD(DAY, 15, sft.first_txn_date) AS d15,

DATEADD(DAY, 30, sft.first_txn_date) AS d30

FROM aptos.core.fact_transactions t

left join sender_first_txn sft on sft.SENDER=t.SENDER

),

activity AS (

SELECT

t.SENDER,

DATE(t.BLOCK_TIMESTAMP) AS activity_date

FROM aptos.core.fact_transactions t