MySQL count daily new users VS returned users (cohort analysis)

Question

The table structure is: user_id, Date (I'm used to work with timestamp)

for example

user id | Date (TS)
A       | '2014-08-10 14:02:53' 
A       | '2014-08-12 14:03:25' 
A       | '2014-08-13 14:04:47'
B       | '2014-08-13 04:04:47'
...

and for the next week I have

user id | Date (TS)
A       | '2014-08-17 09:02:53'     
B       | '2014-08-17 10:04:47'
B       | '2014-08-18 10:04:47'
A       | '2014-08-19 10:04:22'
C       | '2014-08-19 11:04:47'
...

and for today I have

user id | Date (TS)
A       | '2015-05-27 09:02:53'     
B       | '2015-05-27 10:04:47'
C       | '2015-05-27 10:04:22'
D       | '2015-05-27 17:04:47'

I need to know how to perform a single query to find the number of users which are a "returned" user from the very beginning of their activity.

Expected results :

date        | New user | returned User
2014-08-10  |  1       | 0
2014-08-11  |  0       | 0
2014-08-12  |  0       | 1 (A was active on 08/11)
2014-08-13  |  1       | 1 (A was active on 08/12 & 08/11)
...
2014-08-17  |  0       | 2 (A & B were already active )
2014-08-18  |  0       | 1 
2014-08-19  |  1       | 1 
...
2015-05-27  |  1       | 3 (D is a new user)

After some long search on Stackoverflow I found some material provided by https://meta.stackoverflow.com/users/107744/spencer7593 here : Weekly Active Users for each day from log but I didn't succeed to change his query to output my expected results.

Thanks for your help

score 4 · Answer 1 · answered May 27 '15 at 18:50

Assuming you have a date table somewhere (and using t-sql syntax because I know it better...) the key is to calculate the mindate for each user separately, calculate the total number of users on that day, and then just declaring a returning user to be a user who wasn't new:

SELECT DateTable.Date, NewUsers, NumUsers - NewUsers AS ReturningUsers
FROM
DateTable
    LEFT JOIN
        (
        SELECT MinDate, COUNT(user_id) AS NewUsers
        FROM (
                SELECT user_id, min(CAST(date AS Date)) as MinDate
                FROM Table
                GROUP BY user_id
            ) A
        GROUP BY MinDate
        ) B ON DateTable.Date = B.MinDate
    LEFT JOIN
        (
        SELECT CAST(date AS Date) AS Date, COUNT(DISTINCT user_id) AS NumUsers
        FROM Table
        GROUP CAST(date AS Date)
        ) C ON DateTable.Date = C.Date

there is an `sql syntax` error here, on the last `group`. it's missing a `by` keyword. `GROUP BY CAST(date AS Date)` — Richard Zilahi, Jul 23 '19 at 21:48

score 2 · Answer 2 · answered May 28 '15 at 06:34

Thanks to Stephen, I made a short fix on his query, which works well even it's a bit time consuming on large database :

SELECT 
    DATE(Stats.Created),
    NewUsers,
    NumUsers - NewUsers AS ReturningUsers
FROM
    Stats
LEFT JOIN
    (
        SELECT
            MinDate,
            COUNT(user_id) AS NewUsers
        FROM (
            SELECT
                user_id,
                MIN(DATE(Created)) as MinDate
            FROM Stats
            GROUP BY user_id
        ) A
        GROUP BY MinDate
    ) B
ON DATE(Stats.Created) = B.MinDate
LEFT JOIN
    (
        SELECT 
            DATE(Created) AS Date,
            COUNT(DISTINCT user_id) AS NumUsers
        FROM Stats
        GROUP BY DATE(Created)
    ) C
ON DATE(Stats.Created) = C.Date
GROUP BY DATE(Stats.Created)

MySQL count daily new users VS returned users (cohort analysis)

2 Answers2