Which products turn first-time buyers into loyal customers?

WITH first_orders AS (
  SELECT
    o.customer_id,
    MIN(o.created_at)                              AS first_order_date,
    MIN(o.shopify_order_id)                        AS first_order_id
  FROM shopify_orders o
  WHERE o.is_test_order = false
    AND o.cancelled_at IS NULL
    AND o.customer_id IS NOT NULL
  GROUP BY o.customer_id
),
first_products AS (
  SELECT
    fo.customer_id,
    li.product_title                               AS first_product
  FROM first_orders fo
  JOIN shopify_order_line_items li ON li.shopify_order_id = fo.first_order_id
),
repeat_buyers AS (
  SELECT DISTINCT customer_id
  FROM shopify_orders
  WHERE is_test_order = false
    AND cancelled_at IS NULL
  GROUP BY customer_id
  HAVING COUNT(shopify_order_id) > 1
)
SELECT
  fp.first_product,
  COUNT(DISTINCT fp.customer_id)                   AS total_first_buyers,
  COUNT(DISTINCT rb.customer_id)                   AS repeat_buyers,
  ROUND(COUNT(DISTINCT rb.customer_id) * 100.0
    / NULLIF(COUNT(DISTINCT fp.customer_id), 0), 1) AS repeat_rate_pct
FROM first_products fp
LEFT JOIN repeat_buyers rb ON rb.customer_id = fp.customer_id
GROUP BY fp.first_product
HAVING COUNT(DISTINCT fp.customer_id) >= 10
ORDER BY repeat_rate_pct DESC
LIMIT 25