Which products are your stars, which are your dogs?

WITH product_sales AS (
  SELECT
    li.product_id,
    MAX(li.title)                               AS product_name,
    SUM(li.quantity)                            AS units_sold,
    ROUND(SUM(li.price * li.quantity)::numeric, 2) AS revenue,
    COUNT(DISTINCT o.customer_id)              AS unique_buyers
  FROM shopify_order_line_items li
  JOIN shopify_orders o ON o.id = li.order_id
  WHERE o.financial_status IN ('paid', 'partially_refunded')
    AND o.cancelled_at IS NULL
    AND o.created_at >= NOW() - INTERVAL '90 days'
  GROUP BY li.product_id
),
product_refunds AS (
  SELECT
    li.product_id,
    SUM(li.quantity)                            AS units_refunded
  FROM shopify_refunds r
  JOIN shopify_order_line_items li ON li.order_id = r.order_id
  WHERE r.created_at >= NOW() - INTERVAL '90 days'
  GROUP BY li.product_id
),
repeat_buyers AS (
  SELECT product_id, COUNT(DISTINCT customer_id) AS repeat_count
  FROM (
    SELECT li.product_id, o.customer_id
    FROM shopify_order_line_items li
    JOIN shopify_orders o ON o.id = li.order_id
    WHERE o.financial_status IN ('paid', 'partially_refunded')
      AND o.cancelled_at IS NULL
    GROUP BY li.product_id, o.customer_id
    HAVING COUNT(DISTINCT o.id) > 1
  ) sub
  GROUP BY product_id
)
SELECT
  ps.product_name,
  ps.revenue,
  ps.units_sold,
  ps.unique_buyers,
  COALESCE(pr.units_refunded, 0)               AS units_refunded,
  ROUND(COALESCE(pr.units_refunded, 0)::numeric * 100
    / NULLIF(ps.units_sold, 0), 1)             AS refund_rate_pct,
  COALESCE(rb.repeat_count, 0)                 AS repeat_buyers,
  ROUND(COALESCE(rb.repeat_count, 0)::numeric * 100
    / NULLIF(ps.unique_buyers, 0), 1)          AS repeat_buyer_pct
FROM product_sales ps
LEFT JOIN product_refunds pr USING (product_id)
LEFT JOIN repeat_buyers rb USING (product_id)
WHERE ps.units_sold >= 10
ORDER BY ps.revenue DESC
LIMIT 30