macro_session_3.sql

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