macro_session_2.sql

SELECT *, 
       CASE WHEN 
           "timestamp" >= "conversion_timestamp" - interval '30 days' 
           AND "timestamp" <= "conversion_timestamp" 
           THEN 1 else 0 END as "keep_touchpoint"
FROM(
  SELECT "conversions".user_id,
         "conversions".conversion_timestamp,
          "touchpoints".channel,
          "touchpoints".timestamp,
          "conversions".user_id || extract(epoch from "conversions"."conversion_timestamp") AS "macro_user_session"
  FROM "conversions" "conversions"
  LEFT JOIN "user_touchpoints" "touchpoints"
  ON "conversions"."user_id" = "touchpoints"."user_id"
    ) t