Revisions to Select user activity over past months grouped by cohorts

deleted 600 characters in body

Source Link

edited Sep 22, 2017 at 7:43

123
1
13

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

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

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)

added 107 characters in body

Source Link

edited Sep 21, 2017 at 7:30

rewb0rn

123
1
13

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.

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.

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.

Source Link

created Sep 21, 2017 at 7:15

rewb0rn

123
1
13

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,
    COUNT(
        CASE
            WHEN lastLogin >= DATE_ADD(DATE_SUB(DATE(registered), INTERVAL DAYOFMONTH(registered) - 1 DAY), INTERVAL 1 MONTH)
            THEN 1
            ELSE NULL
        END
    ) AS month1,
    COUNT(
        CASE
            WHEN lastLogin >= DATE_ADD(DATE_SUB(DATE(registered), INTERVAL DAYOFMONTH(registered) - 1 DAY), INTERVAL 2 MONTH)
            THEN 1
            ELSE NULL
        END
    ) AS month2,
    COUNT(
        CASE
            WHEN lastLogin >= DATE_ADD(DATE_SUB(DATE(registered), INTERVAL DAYOFMONTH(registered) - 1 DAY), INTERVAL 3 MONTH)
            THEN 1
            ELSE NULL
        END
    ) AS month3,
    COUNT(
        CASE
            WHEN lastLogin >= DATE_ADD(DATE_SUB(DATE(registered), INTERVAL DAYOFMONTH(registered) - 1 DAY), INTERVAL 4 MONTH)
            THEN 1
            ELSE NULL
        END
    ) AS month4,
    COUNT(
        CASE
            WHEN lastLogin >= DATE_ADD(DATE_SUB(DATE(registered), INTERVAL DAYOFMONTH(registered) - 1 DAY), INTERVAL 5 MONTH)
            THEN 1
            ELSE NULL
        END
    ) AS month5,
    COUNT(
        CASE
            WHEN lastLogin >= DATE_ADD(DATE_SUB(DATE(registered), INTERVAL DAYOFMONTH(registered) - 1 DAY), INTERVAL 6 MONTH)
            THEN 1
            ELSE NULL
        END
    ) 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.

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'

Stack Exchange Network

Return to Answer