micro_session_4.sql

SELECT "user_id",
       "channel",
       "timestamp"
FROM(       
    SELECT *,
            FIRST_VALUE("touchpoint") over(
                PARTITION BY "user_id", "user_micro_session"
                ORDER BY "timestamp") as "channel"
    FROM(
        SELECT *,
               "user_id" || '_' || SUM("new_micro_session")
               OVER (PARTITION BY "user_id"
                     ORDER BY "timestamp") AS "user_micro_session"
        FROM (
          SELECT
              *,
              CASE WHEN 
                  EXTRACT(epoch FROM "timestamp") 
                  - LAG(
                      EXTRACT(epoch FROM "timestamp")
                      ) OVER (
                  PARTITION BY "user_id"
                  ORDER BY "timestamp"
                              ) >= 30 * 60 THEN 1 ELSE 0 END as new_micro_session
          FROM "log_data"
          )t1
      )t2
  )t3
GROUP BY 1,2,3