SELECT COUNT(customer_id) AS total_customer,
COUNT(CASE WHEN diff_day>=29 THEN 1 END) AS retention_customer, -- 30일 주기, ELSE 안쓴 경우 NULL로 RETURN
COUNT(CASE WHEN diff_day>=29 THEN 1 END) / COUNT(customer_id) AS rolling_retention_30 --retention비율
FROM(
SELECT customer_id, DATE(MIN(invoice_date)) AS first_purchase,
DATE(MAX(invoice_date)) AS recent_purchase,
DATE_DIFF(DATE(MAX(invoice_date)),DATE(MIN(invoice_date)),DAY) AS diff_day
FROM data.sales
GROUP BY customer_id
)