WITH lagged AS (
  -- Retrieve the previous page-view time for each user with LAG
  SELECT
    user_id,
    viewed_at,
    LAG(viewed_at) OVER (
      PARTITION BY user_id
      ORDER BY     viewed_at
    ) AS prev_viewed_at
  FROM   page_views
),
flagged AS (
  -- Calculate the gap in minutes and the new-session-start flag
  SELECT
    user_id,
    viewed_at,
    EXTRACT(EPOCH FROM (viewed_at - prev_viewed_at)) / 60 AS gap_minutes,
    CASE WHEN prev_viewed_at IS NULL
              OR EXTRACT(EPOCH FROM (viewed_at - prev_viewed_at)) / 60 > 30
         THEN 1 ELSE 0
    END AS new_session_flag   -- First row or over 30 minutes → 1
  FROM   lagged
)
SELECT
  user_id,
  viewed_at,
  gap_minutes,
  SUM(new_session_flag) OVER (
    PARTITION BY user_id
    ORDER BY     viewed_at
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS session_id   -- Cumulative flag sum = session sequence number
FROM   flagged
ORDER BY user_id, viewed_at;

/*
  Logical evaluation order:
  1. CTE lagged:
     FROM page_views               → Load the rows
     LAG(viewed_at) OVER (...)     → Evaluate the window function (row count is preserved)
  2. CTE flagged:
     FROM lagged                   → Load the rows
     EXTRACT(EPOCH ...) / CASE     → Transform the values
  3. FROM flagged
     SUM(...) OVER (...) AS session_id → Evaluate the window function (row count is preserved)
  4. ORDER BY user_id, viewed_at   → Sort and output
*/

WITH numbered AS (
  -- Number each user's purchases in ascending order by date
  SELECT
    user_id, order_date,
    ROW_NUMBER() OVER (
      PARTITION BY user_id ORDER BY order_date
    ) AS rn
  FROM   daily_orders
),
grouped AS (
  -- Island key: consecutive dates share order_date - (rn-1)
  SELECT
    user_id, order_date,
    order_date - (rn - 1) AS grp
  FROM   numbered
),
streaks AS (
  -- Aggregate each island: start, end, and length
  SELECT
    user_id, grp,
    MIN(order_date) AS streak_start,
    MAX(order_date) AS streak_end,
    COUNT(*)         AS streak_days
  FROM   grouped
  GROUP BY user_id, grp
),
ranked AS (
  -- Rank the longest streak as rnk=1 and get the latest purchase date with a window function
  SELECT
    user_id, streak_start, streak_end, streak_days,
    ROW_NUMBER() OVER (
      PARTITION BY user_id
      ORDER BY     streak_days DESC, streak_end DESC
    ) AS rnk,
    MAX(streak_end) OVER (PARTITION BY user_id) AS user_latest_date
  FROM   streaks
)
SELECT
  user_id,
  streak_days AS max_streak_days,
  streak_start,
  streak_end,
  CASE WHEN streak_end = user_latest_date
       THEN 'TRUE' ELSE 'FALSE'
  END AS is_active   -- Longest island ends on latest purchase date → active
FROM   ranked
WHERE  rnk = 1
ORDER BY user_id;

/*
  Logical evaluation order:
  1. CTE numbered
  2. CTE grouped
  3. CTE streaks
  4. CTE ranked
  5. SELECT ... WHERE rnk = 1  → Filter rows and evaluate columns
  6. ORDER BY user_id          → Sort and output
  */

WITH RECURSIVE invite_tree AS (
  -- Anchor: start at depth=0 with users who have no inviter (root users)
  SELECT
    user_id,
    name,
    0 AS depth
  FROM   users
  WHERE  invited_by IS NULL
  UNION ALL
  -- Recursion: add children invited by the current tree rows as the next level
  SELECT
    u.user_id,
    u.name,
    t.depth + 1                -- Move down one level
  FROM   invite_tree t
  JOIN   users u ON u.invited_by = t.user_id
),
invite_counts AS (
  -- Count the number of users each person directly invited
  SELECT
    invited_by AS inviter_id,
    COUNT(*) AS direct_invitees
  FROM   users
  WHERE  invited_by IS NOT NULL
  GROUP BY invited_by
)
SELECT
  t.user_id,
  t.name,
  t.depth,
  COALESCE(c.direct_invitees, 0) AS direct_invitees   -- No invites: NULL → 0
FROM   invite_tree t
LEFT JOIN invite_counts c ON c.inviter_id = t.user_id
ORDER BY t.depth, t.user_id;

/*
  Logical evaluation order:
  1. CTE invite_tree (recursive):
     Evaluate the anchor (starting point)
     Recursive iteration 1 (expand children)
     Recursive iteration 2 (expand children)
     Recursive iteration 3 (expand children)
     Stop when no more rows are added
  2. CTE invite_counts:
     FROM users / WHERE      → Load and filter rows
     GROUP BY invited_by     → Group rows
     COUNT                   → Evaluate the aggregate
  3. FROM invite_tree t
     LEFT JOIN invite_counts c → Join while retaining every left-table row
     COALESCE(...)           → Normalize values
  4. ORDER BY t.depth, t.user_id → Sort and output
*/

WITH user_summary AS (
  -- Aggregate the latest purchase date and total amount per user
  SELECT
    user_id,
    MAX(purchase_date) AS last_purchase_date,
    SUM(amount)         AS total_amount
  FROM   customer_purchases
  GROUP BY user_id
),
scored AS (
  -- Apply NTILE(3) on two axes: Recency descending, Monetary ascending
  SELECT
    user_id, last_purchase_date, total_amount,
    NTILE(3) OVER (ORDER BY last_purchase_date DESC) AS r_rank,
    NTILE(3) OVER (ORDER BY total_amount ASC) AS m_score
  FROM   user_summary
),
final_scores AS (
  -- Reverse r_rank (small = recent) into r_score (large = recent) for an intuitive score
  SELECT
    user_id, last_purchase_date, total_amount,
    4 - r_rank AS r_score,   -- 1→3, 2→2, 3→1 (recent = high score)
    m_score
  FROM   scored
)
SELECT
  user_id, last_purchase_date, total_amount,
  r_score, m_score,
  CASE
    WHEN r_score = 3 AND m_score = 3 THEN 'VIP'
    WHEN r_score = 3 AND m_score = 2 THEN 'Prospective VIP'
    WHEN r_score = 3 AND m_score = 1 THEN 'New / nurture'
    WHEN r_score = 2 AND m_score = 3 THEN 'Churn watch'
    WHEN r_score = 2 AND m_score = 2 THEN 'Standard'
    WHEN r_score = 2 AND m_score = 1 THEN 'General'
    WHEN r_score = 1 AND m_score = 3 THEN 'Lost-customer risk'
    ELSE 'Dormant'          -- r=1 and m∈{1,2} are Dormant
  END AS segment
FROM   final_scores
ORDER BY r_score DESC, m_score DESC;

/*
  Logical evaluation order:
  1. CTE user_summary  → Define the CTE (aggregate Recency and Monetary)
  2. CTE scored        → Rank with NTILE
  3. CTE final_scores  → Reverse and format the scores
  4. SELECT            → Classify segments with CASE
  5. ORDER BY          → Sort and output
  */