SQL Scenarios Questions

WITH recent_purchases AS (
  SELECT user_id
  FROM purchases
  WHERE ts > now() - interval '30 days'
)
SELECT u.user_id
FROM users u
JOIN (
  SELECT user_id
  FROM recent_purchases
  GROUP BY user_id
  HAVING COUNT(*) > 5
) p ON p.user_id = u.user_id;

SELECT u.user_id
FROM users u
JOIN (
  SELECT user_id
  FROM purchases
  WHERE ts > now() - interval '30 days'
  GROUP BY user_id
  HAVING COUNT(*) > 5
) p ON p.user_id = u.user_id;

-- Merge staging_user_features into features.user_features
MERGE INTO features.user_features AS tgt
USING staging_user_features AS src
  ON tgt.user_id = src.user_id
WHEN MATCHED AND
     -- Only update when incoming row is newer (handle NULLs: treat NULL as very old)
     (src.last_updated IS NOT NULL AND 
      (tgt.last_updated IS NULL OR src.last_updated > tgt.last_updated))
     AND
     (
       -- Only update when any feature actually differs (IS DISTINCT FROM handles NULLs)
       src.feature_a IS DISTINCT FROM tgt.feature_a
       OR src.feature_b IS DISTINCT FROM tgt.feature_b
       -- optionally check last_updated differs to avoid noop updates if timestamps equal
       OR src.last_updated IS DISTINCT FROM tgt.last_updated
     )
THEN UPDATE SET
  feature_a = src.feature_a,
  feature_b = src.feature_b,
  last_updated = src.last_updated
WHEN NOT MATCHED THEN
  INSERT (user_id, feature_a, feature_b, last_updated)
  VALUES (src.user_id, src.feature_a, src.feature_b, src.last_updated);

WITH
-- source tables normalized to expected feature columns
web AS (
  SELECT user_id,
         last_page AS web_last_page,
         preferred_category AS web_pref_cat,
         recency_score AS web_recency_score
  FROM raw.web_events
),
purchase AS (
  SELECT user_id,
         last_page AS pur_last_page,
         preferred_category AS pur_pref_cat,
         lifetime_value AS pur_ltv
  FROM raw.purchase_history
),
third AS (
  SELECT user_id,
         last_page AS third_last_page,
         preferred_category AS third_pref_cat,
         lifetime_value AS third_ltv,
         recency_score AS third_recency_score
  FROM raw.third_party
),

-- join all sources by user_id
joined AS (
  SELECT
    COALESCE(w.user_id, p.user_id, t.user_id) AS user_id,
    w.web_last_page, p.pur_last_page, t.third_last_page,
    w.web_pref_cat, p.pur_pref_cat, t.third_pref_cat,
    w.web_recency_score, t.third_recency_score,
    p.pur_ltv, t.third_ltv
  FROM web w
  FULL OUTER JOIN purchase p ON w.user_id = p.user_id
  FULL OUTER JOIN third t ON COALESCE(w.user_id, p.user_id) = t.user_id
)

SELECT
  user_id,

  -- Resolve last_page with precedence and provenance
  CASE
    WHEN web_last_page IS NOT NULL THEN web_last_page
    WHEN pur_last_page IS NOT NULL THEN pur_last_page
    ELSE third_last_page
  END AS last_page,
  CASE
    WHEN web_last_page IS NOT NULL THEN 'web_events'
    WHEN pur_last_page IS NOT NULL THEN 'purchase_history'
    WHEN third_last_page IS NOT NULL THEN 'third_party'
    ELSE NULL
  END AS last_page_source,

  -- Resolve preferred_category
  CASE
    WHEN web_pref_cat IS NOT NULL THEN web_pref_cat
    WHEN pur_pref_cat IS NOT NULL THEN pur_pref_cat
    ELSE third_pref_cat
  END AS preferred_category,
  CASE
    WHEN web_pref_cat IS NOT NULL THEN 'web_events'
    WHEN pur_pref_cat IS NOT NULL THEN 'purchase_history'
    WHEN third_pref_cat IS NOT NULL THEN 'third_party'
    ELSE NULL
  END AS preferred_category_source,

  -- Resolve recency_score (note purchase may not have it)
  COALESCE(web_recency_score, third_recency_score) AS recency_score,
  CASE
    WHEN web_recency_score IS NOT NULL THEN 'web_events'
    WHEN third_recency_score IS NOT NULL THEN 'third_party'
    ELSE NULL
  END AS recency_score_source,

  -- Resolve lifetime_value preferring purchase then third_party
  COALESCE(pur_ltv, third_ltv) AS lifetime_value,
  CASE
    WHEN pur_ltv IS NOT NULL THEN 'purchase_history'
    WHEN third_ltv IS NOT NULL THEN 'third_party'
    ELSE NULL
  END AS lifetime_value_source

FROM joined;