3

In MySQL 5.7, I have a table user that contains the columns registered and lastLogin. My goal is to group the users by calendar months of registration and then select for each group how many of these users were still active after 1 month, after 2 months and so on.

20,000 users have registered in June 2015, from these 10,000 have been active in July 2015 or later, 5,000 have been active in August 2015 or later, etc. Continue this output for all users registered in the past months until today.

I understand how I can select any of the activity months by hand, for example select all users that were active one calendar month after their registration:

SELECT year(registered), month(registered), count(id)
FROM user
WHERE lastLogin >= DATE_ADD(DATE_SUB(date(registered), INTERVAL DAYOFMONTH(registered) - 1 DAY), INTERVAL 1 MONTH)
GROUP BY year(registered), month(registered)

This would give me the number of users who were active one month after their registration or later.

But in addition I would also like to select the number of users who were active 2 months after their registration, 3 months after their registration and so on up to the current month.

The results would be something like this (not necessarily look like this, but it should get the idea across):

Columns:      Cohort |    total | 1 month | 2months | 3months | ... | month n 
             07/2015 |   20,000 | 10,100  | 5,500   | 2,600   | ... | 150
             08/2015 |   21,000 | 9,800   | 4,300   | 1,300   | ... | 180
             09/2015 |   19,700 | 11,400  | 6,200   | 3,500   | ... | 200
    ...
             08/2017 |   25,300 |  13,000 
             09/2017 |   12,500 

How do I change the query to select all months of activity instead of a specific month?

  • Is there only one row per user (as the column name lastlogin implies)? Or many rows (as implied by "users that were active...")? – Rick James Sep 19 '17 at 13:45
  • Yes we only have one row per user as lastLogin implies and I understand that this only partially reflects "users that were active...", but I think it's acceptable. – rewb0rn Sep 19 '17 at 15:01
3

With the help of Rick to look for pivot table I was able to create the final query as described in this post: https://stackoverflow.com/questions/7674786/mysql-pivot-table

Query for a 6 months analysis:

SELECT 
    YEAR(registered), MONTH(registered), COUNT(*) as total,
    SUM(lastLogin >= DATE(registered) - INTERVAL DAYOFMONTH(registered) - 1 DAY + INTERVAL 1 MONTH) AS month1,
    SUM(lastLogin >= DATE(registered) - INTERVAL DAYOFMONTH(registered) - 1 DAY + INTERVAL 2 MONTH) AS month2,
    SUM(lastLogin >= DATE(registered) - INTERVAL DAYOFMONTH(registered) - 1 DAY + INTERVAL 3 MONTH) AS month3,
    SUM(lastLogin >= DATE(registered) - INTERVAL DAYOFMONTH(registered) - 1 DAY + INTERVAL 4 MONTH) AS month4,
    SUM(lastLogin >= DATE(registered) - INTERVAL DAYOFMONTH(registered) - 1 DAY + INTERVAL 5 MONTH) AS month5,
    SUM(lastLogin >= DATE(registered) - INTERVAL DAYOFMONTH(registered) - 1 DAY + INTERVAL 6 MONTH) AS month6
FROM
    user
WHERE 
    registered >= "2015-01-01"
GROUP BY YEAR(registered) , MONTH(registered)

The part DATE_SUB(DATE(registered), INTERVAL DAYOFMONTH(registered) - 1 DAY) will return the first of a month to make sure the analysis is based on calendar months. I am not sure if it could be simplified. I was looking at STR_TO_DATE(LEFT(registered, 7), "%y-%m") but I was reading working on Strings is slower and should be avoided. If someone knows a better / cleaner way of doing that, please comment and I will include it in the answer.

Here is some exemplary output of the query:

  year, month, total , month1, month2, month3, month4, month5, month6
'2015',  '1', '14776', '4302', '3225', '2827', '2547', '2330', '2163'
'2015',  '2', '12162', '3859', '2866', '2465', '2259', '2046', '1890'
'2015',  '3', '10770', '3831', '2841', '2507', '2288', '2135', '1987'
'2015',  '4', '15685', '3731', '2641', '2273', '2045', '1872', '1730'
'2015',  '5', '18130', '3686', '2403', '1993', '1744', '1528', '1363'
  • The COUNT(case,...) can be shortened to SUM(lastLogin >= DATE(registered) - INTERVAL DAYOFMONTH(registered) - 1 DAY + INTERVAL 6 MONTH) – Rick James Sep 21 '17 at 13:39
2

Start with this

SELECT  LEFT(registered, 7) AS ym_reg,
        LEFT(lastlogin, 7)  AS ym_lastlogin,
        COUNT(*)  AS ct
    FROM tbl
    GROUP BY 1,2;

Then explain what output you would like.

If that SELECT gives you the data you want, then this question is closed. If you want a 2-dimensional layout (reg rows, login in columns), then that is a pivot-table question. Follow that tag to find ways to do it.

"one month after registering" -- What if register June 30 and lastlogin is July 1; does that count? Or do you want lastlogin >= registered + INTERVAL 1 MONTH?

  • Thanks, but this is not quite what I am looking for. I have updated my question above to reflect better what I am looking for, sorry for the confusion. To your question: Yes, if register is June 30 and lastLogin is July 1 then this counts as a user being active one month after registration. I will also look up pivot-table and see if this might be related to my question. – rewb0rn Sep 20 '17 at 6:37
  • Is the data of my SELECT the data you are looking for? That is step 1. Step 2 is to "pivot" it so that rows are turned into columns. – Rick James Sep 20 '17 at 14:40
  • I think this is partly related to the data I want, but it is ignoring users who had their last login after a given month (a user with last login in August 2015 should count as active in July 2015). Btw I think it should be LEFT(..., 7) instead of LEFT(..., 5)? However the hint for pivot table put me on the right track, I was able to alter my query with the reply in this post: stackoverflow.com/questions/7674786/mysql-pivot-table – rewb0rn Sep 21 '17 at 7:05
  • Doh! I can't count. Fixing my Answer. "7" refers to "2017-08" part of "2017-08-31...". – Rick James Sep 21 '17 at 13:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.