macro_session_4.sql

SELECT "user_id", 
       "user_id" || SUM("flag_macro_session_limits") OVER (
            PARTITION BY "user_id" 
            ORDER BY "timestamp" ASC 
           )
           - "flag_macro_session_limits" AS "user_macro_session",
       "channel",
       "timestamp"
FROM(
    SELECT *, 
           CASE WHEN 
               "time_difference" >= INTERVAL '30 days' THEN 1  ELSE 0 END AS "flag_macro_session_limits"
    FROM(
        SELECT *,
               LAG("timestamp",1)  OVER (
                   PARTITION BY "user_id" 
                   ORDER BY "timestamp" DESC
                    )
               - "timestamp" as "time_difference"
        FROM(
            SELECT "user_id",
                    "channel",
                    "timestamp"
            FROM(
                SELECT  "user_id",
                        "channel",
                        "timestamp",
                        SUM("keep_touchpoint") as "keep_touchpoint"
                FROM "non_converting_macro_sessions"
                GROUP BY "user_id", "channel", "timestamp"
                ) t1
            WHERE "keep_touchpoint" = 0
            ) t2
        ) t3
    ) t4