COUNT(DISTINCT tx_signer) - COUNT(DISTINCT CASE WHEN first_tx >= day - INTERVAL '30 Days' AND first_tx < day THEN wallet END) AS Returning_MAAs,

COUNT(DISTINCT CASE WHEN first_tx >= day - INTERVAL '30 Days' AND first_tx < day THEN wallet END) AS New_MAAs,

COUNT(DISTINCT tx_signer) AS MAAs

FROM (SELECT DISTINCT block_timestamp::date AS day FROM near.core.fact_transactions WHERE block_timestamp >= '2021-01-01') a

LEFT OUTER JOIN (SELECT DISTINCT tx_signer, block_timestamp::date AS active_day

FROM near.core.fact_transactions) b

ON active_day >= day - INTERVAL '30 DAYS'

AND active_day < day

JOIN (SELECT tx_signer AS wallet, MIN(block_timestamp) AS first_tx FROM near.core.fact_transactions tr GROUP BY 1) c

ON b.tx_signer = c.wallet

GROUP BY 1

ORDER BY 1 DESC;