retention_3.sql

WITH by_week
AS (SELECT
  user_id,
  TD_DATE_TRUNC('week', login_time) AS login_week
FROM logins
GROUP BY 1, 2),
with_first_week
AS (SELECT
  user_id,
  login_week,
  FIRST_VALUE(login_week) OVER (PARTITION BY user_id ORDER BY login_week) AS first_week
FROM by_week)
SELECT
  user_id,
  login_week,
  first_week,
  (login_week - first_week) / (24 * 60 * 60 * 7) AS week_number
FROM with_first_week