امتیازدهی کامل RFM: تازگی، فراوانی و مبلغ تو پنجک‌ها

WITH base AS (
    SELECT customer_id,
           CURRENT_DATE - MAX(order_date) AS recence_j,
           COUNT(*)    AS frequence,
           SUM(amount) AS montant
    FROM orders
    WHERE order_date >= CURRENT_DATE - INTERVAL '24 months'
    GROUP BY customer_id
),
scores AS (
    SELECT *,
        6 - NTILE(5) OVER (ORDER BY recence_j) AS score_r,
        NTILE(5) OVER (ORDER BY frequence)     AS score_f,
        NTILE(5) OVER (ORDER BY montant)       AS score_m
    FROM base
)
SELECT customer_id, recence_j, frequence, montant,
       score_r || score_f || score_m AS rfm,
       CASE
           WHEN score_r >= 4 AND score_f >= 4 AND score_m >= 4 THEN 'Champion'
           WHEN score_r <= 2 AND score_m >= 4 THEN 'VIP à réactiver'
           WHEN score_r <= 2 AND score_f <= 2 THEN 'Perdu'
           ELSE 'Standard'
       END AS segment
FROM scores
ORDER BY montant DESC
LIMIT 6;