Cohort Analysis in SQL

This seems like a tricky problem. No matter how difficult it seems to you or not, it's never a bad idea to start working on it from a lesser problem.

You could start, for example, with a query that returns all users (users only) that have been logged in in the last week, that is, starting on a six day day, as per your requirement:

SELECT *
FROM users
WHERE created_at >= CURDATE() - INTERVAL 6 DAY

      

        
        
        
      

    

The next step could be to group the results by date and count the rows in each group:

SELECT
  created_at,
  COUNT(*) AS user_count
FROM users
WHERE created_at >= CURDATE() - INTERVAL 6 DAY
GROUP BY created_at

      

        
        
        
      

    

If created_at

is datetime

or timestamp

, use DATE(created_at)

as grouping criterion:

SELECT
  DATE(created_at) AS created_at,
  COUNT(*) AS user_count
FROM users
WHERE created_at >= CURDATE() - INTERVAL 6 DAY
GROUP BY DATE(created_at)

      

        
        
        
      

    

However, you don't want absolute dates in the output, just relative ones, for example today

, today - 1 day

etc. In this case, you can use DATEDIFF()

which returns the number of days between two dates to produce (numeric) offsets from today and a group by those values:

SELECT
  DATEDIFF(CURDATE(), created_at) AS created_at,
  COUNT(*) AS user_count
FROM users
WHERE created_at >= CURDATE() - INTERVAL 6 DAY
GROUP BY DATE(created_at)

      

        
        
        
      

    

Your column created_at

will contain "dates" such as 0

, 1

etc. before 6

. Convert them to today

, today-1

etc. This is trivial and you will see it in the final request. However, so far we have reached the point where we need to take one step back (or perhaps it is half a step to the right), because we really don't need to count the users, but their return.So the actual working dataset from users

which is required for now, will be as follows:

SELECT
  id,
  DATEDIFF(CURDATE(), created_at) AS day_offset
FROM users
WHERE created_at >= CURDATE() - INTERVAL 6 DAY

      

        
        
        
      

    

We need user ids to join this rowset (the one to be obtained from) sessions

and we need it day_offset

as a grouping criterion.

Moving on, a similar conversion needs to be done on the table sessions

, and I won't go into details about that. Suffice it to say that the resulting query will be very identical to the last one, with two exceptions:

• id
  
  is replaced by user_id
  
  ;

• DISTINCT applies to the entire subset.

The reason for DISTINCT is to return at most one row per user and day: I understand that even though there are many sessions that a user might have on a particular day, you want to count them as one return. So, here's what comes out of sessions

:

SELECT DISTINCT
  user_id,
  DATEDIFF(CURDATE(), created_at) AS day_offset
FROM sessions
WHERE created_at >= CURDATE() - INTERVAL 6 DAY

      

        
        
        
      

    

Now all that remains is to join the two views, apply grouping, and use conditional aggregation to get the results you want:

SELECT
  CONCAT('today', IFNULL(CONCAT('-', NULLIF(u.DayOffset, 0)), '')) AS created_at,
  SUM(s.DayOffset = 0) AS d0,
  SUM(s.DayOffset = 1) AS d1,
  SUM(s.DayOffset = 2) AS d2,
  SUM(s.DayOffset = 3) AS d3,
  SUM(s.DayOffset = 4) AS d4,
  SUM(s.DayOffset = 5) AS d5,
  SUM(s.DayOffset = 6) AS d6
FROM (
  SELECT
    id,
    DATEDIFF(CURDATE(), created_at) AS DayOffset
  FROM users
  WHERE created_at >= CURDATE() - INTERVAL 6 DAY
) u
LEFT JOIN (
  SELECT DISTINCT
    user_id,
    DATEDIFF(CURDATE(), created_at) AS DayOffset
  FROM sessions
  WHERE created_at >= CURDATE() - INTERVAL 6 DAY
) s
ON u.id = s.user_id
GROUP BY u.DayOffset
;

      

        
        
        
      

    

I have to admit that I haven't tested / debugged this, but if need be I will be happy to work with the sample data you provided after you provided it. :)