SELECT

TO_DATE(BLOCK_TIMESTAMP) AS date,

SENDER

FROM

aptos.core.fact_transactions

WHERE date >= '2022-10-20'

GROUP BY

TO_DATE(BLOCK_TIMESTAMP),

SENDER

),

DailyActiveUsers AS (

SELECT

date,

COUNT(DISTINCT SENDER) AS dau

FROM

ActiveUsers

WHERE date >= '2022-10-20'

GROUP BY

date

),

UserRetention AS (

SELECT

du.date,

(COUNT(CASE WHEN du_next.date = DATEADD(DAY, 1, du.date) THEN du_next.dau END) / du.dau) * 100 AS retention_rate

FROM

DailyActiveUsers du

LEFT JOIN

DailyActiveUsers du_next ON du_next.date = DATEADD(DAY, 1, du.date)

GROUP BY

du.date,

du.dau

), dateRange AS (

SELECT

MIN(date) AS minDate,

MAX(date) AS maxDate