Sitecore CDP Batch Segmentation - Risky of churn customers

rfm.sql

SELECT g.meta_ref as guest_ref
    FROM (
          SELECT o.meta_guest_ref, 
            NTILE(5) OVER (ORDER BY R DESC) as R_S,
            NTILE(5) OVER (ORDER BY F ASC) as F_S,
            NTILE(5) OVER (ORDER BY M ASC) as M_S
          FROM (
             SELECT o.meta_guest_ref,
                     DATE_DIFF('day', max(date(core_ordered_at)), current_date) AS R,
                     COUNT(*) AS F,
                     SUM(o.core_price) AS M
             FROM orders o
             WHERE date(o.core_ordered_at) > date('2023-01-01')
             GROUP BY o.meta_guest_ref
           ) o
       ) 
    INNER JOIN guests g
      ON meta_guest_ref = g.meta_ref
    WHERE R_S<3 and F_S=5 and M_S>3
    GROUP BY g.meta_ref