Skip to content

Instantly share code, notes, and snippets.

@dsottimano
Created January 6, 2023 00:26
Show Gist options
  • Save dsottimano/4176d12a6a42b6deab5f27dc5d92ee44 to your computer and use it in GitHub Desktop.
Save dsottimano/4176d12a6a42b6deab5f27dc5d92ee44 to your computer and use it in GitHub Desktop.
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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment