دليل الأداة

استعلام تقسيم العملاء كما نُفِّذ على قاعدة البيانات، بدالة النوافذ NTILE.

WITH base AS (
  SELECT customer_id,
         DATE '2026-07-01' - MAX(order_date) AS recency,
         COUNT(*) AS frequency, SUM(amount) AS monetary
  FROM orders WHERE status = 'completed'
  GROUP BY customer_id
), scored AS (
  SELECT *,
    NTILE(5) OVER (ORDER BY recency DESC, customer_id) AS r,
    NTILE(5) OVER (ORDER BY frequency, customer_id)    AS f,
    NTILE(5) OVER (ORDER BY monetary, customer_id)     AS m
  FROM base
)
SELECT CASE WHEN r >= 4 AND f + m = 10 THEN 'champions'
            WHEN r >= 3 AND f + m >= 6 THEN 'loyal'
            WHEN r >= 4                THEN 'new'
            WHEN f + m >= 6            THEN 'at_risk'
            ELSE 'dormant' END AS segment,
       COUNT(*) AS customers, SUM(monetary) AS gmv
FROM scored GROUP BY 1 ORDER BY gmv DESC;