date spine ex

WITH calendar AS (
SELECT
date,
EXTRACT(DAYOFWEEK FROM date) day_of_week,
EXTRACT(MONTH FROM date) month,
EXTRACT(YEAR FROM date) year
FROM UNNEST(GENERATE_DATE_ARRAY('2000-01-01', '2020-12-31')) AS date
),
date_spine AS (
SELECT * FROM calendar
)
SELECT
(SUM(CASE WHEN t1.purchased_again = TRUE THEN 1 ELSE 0 END) / COUNT(*)) * 100 AS repurchase_rate
FROM
(SELECT customer_id, MIN(purchase_date) first_purchase_date, MAX(purchase_date) last_purchase_date
FROM purchases
GROUP BY customer_id) t1
JOIN purchases t2
ON t1.customer_id = t2.customer_id
AND t2.purchase_date BETWEEN t1.first_purchase_date AND t1.last_purchase_date
AND t2.purchase_date > t1.first_purchase_date