Skip to content

Instantly share code, notes, and snippets.

@kiyoto

kiyoto/retention_2.sql Secret

Created July 19, 2016 21:34
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save kiyoto/f26c994200f4989425e9b782b87e3f3e to your computer and use it in GitHub Desktop.
Save kiyoto/f26c994200f4989425e9b782b87e3f3e to your computer and use it in GitHub Desktop.
Download ZIP
Raw
retention_2.sql
WITH by_week
AS (SELECT
user_id,
TD_DATE_TRUNC('week', login_time) AS login_week
FROM logins
GROUP BY 1, 2)
SELECT
user_id,
login_week,
FIRST_VALUE(login_week) OVER (PARTITION BY user_id ORDER BY login_week) AS first_week
FROM by_week
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment