Example on how to get timestamp for second login. Used to educate about Non-Equi JOIN in SQL

second_login.sql

WITH
  base AS (
    SELECT
      SAFE_CAST (
        JSON_EXTRACT_SCALAR (payload, '$.timestamp') AS timestamp
      ) AS event_time,
      JSON_EXTRACT_SCALAR (payload, '$.action') AS action_type,
      JSON_EXTRACT_SCALAR (payload, '$.actor_id') AS actor_id,
      JSON_EXTRACT_SCALAR (payload, '$.actor_username') AS actor_username,
    FROM
      `test-project-355508.demosupabase_auth.audit_log_entries`
  ),
  signup_time AS (
    SELECT
      actor_id,
      MIN(event_time) AS event_time
    FROM
      base
    WHERE
      action_type = 'login'
    GROUP BY
      1
  ),
  second_login AS (
    SELECT
      t1.actor_id,
      t1.actor_username,
      MIN(t1.event_time) AS second_login_time,
      MIN(t2.event_time) AS signup_time
    FROM
      base t1
      JOIN signup_time t2 ON t1.actor_id = t2.actor_id
      AND t1.event_time > t2.event_time -- HERE WE HAVE THE NON-EQUI JOIN
    WHERE
      t1.action_type = 'login'
    GROUP BY
      1,
      2
  )
SELECT
  actor_id,
  actor_username,
  second_login_time,
  signup_time
FROM
  second_login