SQL CTE and WITH — Applied Multiple CTEs, Totals, RFM

WITH
user_stats AS (                          -- ① Aggregate purchase statistics by user (base data for evaluating the three conditions)
  SELECT                                 -- SELECT: columns to retrieve
    user_id,
    COUNT(*)        AS order_count,      -- COUNT(*): number of records = number of purchases
    SUM(amount)     AS total_amount,     -- SUM(): sum of the amount column = total purchase amount
    MAX(order_date) AS last_order_date   -- MAX(): greatest date value = latest purchase date
  FROM     orders                        -- FROM: source table
  GROUP BY user_id                       -- GROUP BY: group and aggregate by user
),
qualified_users AS (                     -- ② Apply all three conditions and filter
  SELECT *                               -- SELECT *: retrieve every table column
  FROM   user_stats                      -- FROM: reference the CTE defined above
  WHERE                                  -- WHERE: retain only rows that meet every condition below
    order_count     >= 2                 -- Condition ①: at least 2 purchases
    AND total_amount    >= 50000         -- AND: also require condition ②, total amount at least 50000
    AND last_order_date >= '2024-03-01'  -- AND: also require condition ③, latest purchase on or after 2024-03-01
)

-- ③ Add names from users for qualifying users and return them
SELECT
  q.user_id,
  u.user_name,                    -- Retrieve the name from users
  q.order_count,
  q.total_amount,
  q.last_order_date
FROM       qualified_users q      -- FROM: table of users who passed the conditions
INNER JOIN users           u      -- INNER JOIN: join users and retain only rows present in both tables
        ON q.user_id = u.user_id  -- ON: join matching user_id values
ORDER BY   q.user_id;   -- Sort by user ID as shown in the expected table

/*
  Execution order:
  1. CTE user_stats                 → Aggregate COUNT/SUM/MAX by user
  2. CTE qualified_users            → Filter by aggregate values
  3. Main INNER JOIN users          → Join on user_id
  4. Main ORDER BY user_id           → Sort by user ID ascending and return the output
  */

WITH monthly_agg AS (

  -- ① Aggregate sales by month
  SELECT                         -- SELECT: specify columns to retrieve
    order_month,
    SUM(sales) AS monthly_sales  -- SUM(): combine sales in the same month
  FROM     monthly_sales         -- FROM: source table
  GROUP BY order_month           -- GROUP BY: group by month

)

-- ② Calculate cumulative sales and the achievement rate
SELECT
  order_month,
  monthly_sales,
  SUM(monthly_sales) OVER (           -- SUM() OVER(...): window function; calculate running totals without collapsing rows (without GROUP BY)
    ORDER BY order_month              -- ORDER BY: sort months ascending, then accumulate
    ROWS BETWEEN UNBOUNDED PRECEDING  -- ROWS BETWEEN: specify the calculation frame; UNBOUNDED PRECEDING means from the first row
      AND CURRENT ROW                 -- CURRENT ROW: through the current row, producing the total from the start through now
  ) AS cumulative_sales,              -- AS: assign the column alias cumulative_sales
  ROUND(                                 -- Achievement rate = cumulative total / annual target × 100
    SUM(monthly_sales) OVER (            -- Same running-total calculation as above
      ORDER BY order_month
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) * 100.0 / 800000                   -- Multiply by 100.0 for a percentage and divide by the annual target (800000)
  , 1) AS achievement_rate               -- Round to one decimal place and assign an alias

FROM     monthly_agg   -- FROM: reference the CTE
ORDER BY order_month;  -- ORDER BY: sort the result by month ascending

/*
  Execution order:
  1. CTE monthly_agg          → Aggregate monthly sales
  2. Outer SUM() OVER(...)    → Calculate the running total
  3. Outer ROUND(...)         → Calculate the achievement rate
  4. Outer ORDER BY order_month → Sort and return the output
  */

WITH purchase_with_next AS (

  -- ① Add the next purchase date to each row with LEAD
  SELECT                        -- SELECT: columns to retrieve
    user_id,
    order_date,
    LEAD(order_date, 1) OVER (  -- LEAD(column, n): retrieve the value n rows later (NULL if absent)
      PARTITION BY user_id      -- PARTITION BY: divide by user and process each user independently
      ORDER BY     order_date   -- ORDER BY: sort dates ascending to define the next row
    ) AS next_date              -- AS: assign an alias
  FROM purchase_log             -- FROM: source table

)

-- ② With the next purchase date established, calculate the interval in days
SELECT
  user_id,
  order_date,
  next_date,                   -- Calculate the day difference by subtracting dates
  next_date - order_date       -- Next purchase date - current purchase date = interval in days
    AS days_to_next            -- If next_date is NULL, the calculation result is also NULL (NULL propagation)
FROM     purchase_with_next    -- FROM: reference the CTE
ORDER BY user_id, order_date;  -- ORDER BY: sort by user ID ascending, then date ascending

/*
  Execution order:
  1. CTE FROM purchase_log               → Read all rows
  2. CTE LEAD(order_date,1) OVER(...)    → Add the next purchase date to each row
  3. Outer FROM purchase_with_next       → Reference the CTE result
  4. Outer next_date - order_date        → Calculate the difference in days
  5. Outer ORDER BY user_id, order_date  → Sort and return the output
  */

WITH
rfm_raw AS (                                               -- ① Aggregate raw RFM data by user
  SELECT                                                   -- SELECT: columns to retrieve
    user_id,
    '2024-04-01'::date - MAX(order_date) AS recency_days,  -- Recency: reference date - latest purchase date
    COUNT(*)        AS frequency,  -- Frequency: number of purchases
    SUM(amount)     AS monetary  -- Monetary: total purchase amount
  FROM     orders                -- FROM: source table
  GROUP BY user_id               -- GROUP BY: group by user
),
rfm_scored AS (                  -- ② Convert raw RFM data into scores of 1–3 points
  SELECT
    user_id,
    recency_days,
    frequency,
    monetary,
    CASE                                        -- R score: fewer elapsed days produce a higher score
      WHEN recency_days <  30 THEN 3             -- WHEN condition THEN value: return 3 if under 30 days
      WHEN recency_days <  90 THEN 2             -- Return 2 if under 90 days
      ELSE                       1              -- ELSE value: return 1 if no condition matches
    END AS r_score,                             -- END: finish CASE; AS assigns an alias
    CASE                                        -- F score: a higher Frequency (more purchases) produces a higher score
      WHEN frequency >= 3 THEN 3                -- Return 3 for at least 3 purchases
      WHEN frequency >= 2 THEN 2                -- Return 2 for at least 2 purchases
      ELSE                     1                -- Otherwise return 1
    END AS f_score,
    CASE                                        -- M score: a higher Monetary value (purchase amount) produces a higher score
      WHEN monetary >= 100000 THEN 3            -- Return 3 for at least 100000
      WHEN monetary >=  50000 THEN 2            -- Return 2 for at least 50000
      ELSE                         1            -- Otherwise return 1
    END AS m_score
  FROM rfm_raw                                  -- FROM: reference the first CTE
),
rfm_total AS (                                  -- ③ Calculate the total R+F+M score
  SELECT
    *,                                          -- *: retrieve every column unchanged
    r_score + f_score + m_score AS total_score  -- +: add the scores to calculate the total
  FROM rfm_scored                               -- FROM: reference the second CTE
)

-- ④ Determine the segment from the total score and return it
SELECT
  user_id,
  recency_days,
  frequency,
  monetary,
  r_score,
  f_score,
  m_score,
  total_score,
  CASE                                -- CASE WHEN converts the score into a segment name (string)
    WHEN total_score >= 8 THEN 'VIP'  -- Return the string 'VIP' for a total of at least 8
    WHEN total_score >= 5 THEN 'Standard'   -- Return 'Standard' for a total of at least 5
    ELSE                       'Dormant'   -- Otherwise return 'Dormant'
  END AS segment
FROM     rfm_total                    -- FROM: reference the third CTE
ORDER BY total_score DESC, user_id;   -- ORDER BY ... DESC: sort by total score descending, then user ID

/*
  Execution order:
  1. CTE rfm_raw: FROM orders        → GROUP BY user_id
  2. CTE rfm_scored: FROM rfm_raw    → Convert R/F/M to 1–3 points each with CASE WHEN
  3. CTE rfm_total: FROM rfm_scored  → Calculate total score with r_score+f_score+m_score
  4. Main: FROM rfm_total            → Determine the segment with CASE WHEN
  */