SQL Behavioral Analytics — Applied Window Functions and CTEs

WITH purchase_counts AS (
  -- Aggregate purchase counts per user
  SELECT
    user_id,
    COUNT(*) AS purchase_count
  FROM   purchases
  GROUP BY user_id
),
scored AS (
  -- Score in four levels with NTILE(4) (1=low frequency, 4=high frequency)
  SELECT
    user_id,
    purchase_count,
    NTILE(4) OVER (
      ORDER BY purchase_count ASC  -- Fewest first → tile1=low frequency
    ) AS freq_score
  FROM   purchase_counts
)
SELECT
  user_id,
  purchase_count,
  freq_score,
  (freq_score = 4) AS is_high_value  -- Score 4 is the highest-frequency segment
FROM   scored
ORDER BY user_id;

/*
  Logical evaluation order:
  1. Define CTE purchase_counts  → Evaluate grouping and aggregate functions
  2. Define CTE scored            → Evaluate the window function (row count is preserved)
  3. SELECT                       → Evaluate columns (freq_score, is_high_value)
  4. ORDER BY user_id             → Sort and output
*/

WITH step_lagged AS (
  SELECT
    user_id,
    step,
    stepped_at,
    LAG(stepped_at) OVER (     -- Get the previous step timestamp for the same user
      PARTITION BY user_id
      ORDER BY     stepped_at ASC
    ) AS prev_stepped_at
  FROM   step_events
)
SELECT
  user_id,
  stepped_at,
  prev_stepped_at,
  (stepped_at - prev_stepped_at)       AS days_from_prev,  -- date-date → integer
  (stepped_at - prev_stepped_at) <= 7  AS within_7days     -- Boolean: within 7 days
FROM   step_lagged
WHERE  step = 'signup'                  -- Signup rows only (exclude page_view rows)
ORDER BY user_id;

/*
  Logical evaluation order:
  1. Define CTE step_lagged  → Evaluate the window function (row count is preserved)
  2. WHERE step = 'signup'   → Filter rows
  3. SELECT                  → Evaluate columns (days_from_prev, within_7days)
  4. ORDER BY user_id        → Sort and output
*/

WITH cohorts AS (
  SELECT
    user_id,
    DATE_TRUNC('month', registered_at)::date AS cohort_month
  FROM users
),
activity AS (
  SELECT DISTINCT
    user_id,
    DATE_TRUNC('month', event_date)::date   AS active_month
  FROM login_events
),
offsets(offset_month) AS (            -- Generate Month 1, 2, and 3 offset values
  SELECT UNNEST(ARRAY[1, 2, 3])
)
SELECT
  c.cohort_month,
  o.offset_month,
  COUNT(DISTINCT c.user_id)  AS cohort_size,
  COUNT(DISTINCT a.user_id)  AS retained,
  ROUND(
    COUNT(DISTINCT a.user_id) * 100.0 /
    NULLIF(COUNT(DISTINCT c.user_id), 0), 1  -- Guard against division by zero
  ) AS retention_pct
FROM       cohorts  c
CROSS JOIN offsets  o                  -- All combinations (2 cohorts × 3 offsets)
LEFT JOIN  activity a
  ON  a.user_id     = c.user_id
  AND a.active_month = c.cohort_month + (o.offset_month || ' month')::interval
GROUP BY c.cohort_month, o.offset_month
ORDER BY c.cohort_month, o.offset_month;

/*
  Logical evaluation order:
  1. Define CTE cohorts       → Normalize values
  2. Define CTE activity      → Remove duplicates
  3. Define CTE offsets       → Evaluate the derived table
  4. CROSS JOIN offsets o     → Join (Cartesian product)
  5. LEFT JOIN activity a     → Join (preserve every left-side row)
  6. GROUP BY                 → Group rows
  7. SELECT                   → Evaluate aggregates (cohort_size, retained)
  8. ROUND(...)               → Format the value
  9. ORDER BY                 → Sort and output
*/

WITH RECURSIVE date_series AS (
  SELECT '2024-01-01'::date AS dt  -- Anchor: aggregation start date
  UNION ALL
  SELECT (dt + INTERVAL '1 day')::date
  FROM   date_series
  WHERE  dt < '2024-01-07'              -- Termination condition: through 01-07
),
daily_dau AS (
  SELECT
    session_date,
    COUNT(DISTINCT user_id) AS dau
  FROM   user_sessions
  GROUP BY session_date
)
SELECT
  ds.dt,
  COALESCE(d.dau, 0) AS dau          -- Fill NULL dates with 0
FROM      date_series ds
LEFT JOIN daily_dau   d ON d.session_date = ds.dt
ORDER BY  ds.dt;

/*
  Logical evaluation order:
  1. WITH RECURSIVE date_series
  2. CTE daily_dau
  3. FROM date_series ds
  4. COALESCE(d.dau, 0)
  5. ORDER BY ds.dt  → Ascending date order
  */