WITH BuyerTransactions AS (
SELECT
BUYER_ADDRESS,
COUNT(TOKENID) AS PURCHASE_COUNT
FROM
aptos.nft.ez_nft_sales
WHERE
PROJECT_NAME = 'The Loonies'
AND block_timestamp >= DATEADD({{Period}}, -1, CURRENT_DATE)
GROUP BY BUYER_ADDRESS
)
SELECT
COUNT(BUYER_ADDRESS) AS TOTAL_BUYERS,
COUNT(CASE WHEN PURCHASE_COUNT > 1 THEN 1 END) AS REPEAT_BUYERS,
(COUNT(CASE WHEN PURCHASE_COUNT > 1 THEN 1 END) / NULLIF(COUNT(BUYER_ADDRESS), 0)) AS REPEAT_BUYER_RATIO
FROM
BuyerTransactions;