Cohort Analysis With Amazon Redshift / Postgresql

I'm trying analyze user retention using a cohort analysis based on event data stored in Redshift. For example, in Redshift I have: timestamp action user id --------

Solution 1:

Eventually I found the query below to satisfy my requirements.

WITH 

users AS (
  SELECT
    user_id,
    date_trunc('day', min(timestamp)) as activated_at
    fromtablegroupby1
  )
,

events AS (
  SELECT user_id,
         action,
         timestampAS occurred_at
    FROMtable
)

SELECT DATE_TRUNC('day',u.activated_at) AS signup_date,


       TRUNC(EXTRACT('EPOCH'FROM e.occurred_at - u.activated_At)/(3600*24)) AS user_period,


       COUNT(DISTINCT e.user_id) AS retained_users
  FROM users u
  JOIN events e
    ON e.user_id = u.user_id
   AND e.occurred_at >= u.activated_at
 WHERE u.activated_at >= getdate() -INTERVAL'11 day'GROUPBY1,2ORDERBY1,2

It produces a slightly different table than I described above (but is better for my needs):

signup_dateuser_periodretained_users------------------------------------2015-05-05   0802015-05-05   1602015-05-05   2402015-05-05   3202015-05-06   01002015-05-06   1802015-05-06   2402015-05-06   320