Get users registered 30 days prior to date (inclusive)-CodePudding

I have a table users that saves user_id and the timestamp created_at when user registers. I want to find number of registered users each day and number of registered users 30 days prior to each day.

To find the number of registered users per day i have:

SELECT DATE_FORMAT(date(created_at),'%d %M %Y') AS Days, COUNT(user_id) as Profiles
FROM users
GROUP BY YEAR(created_at), MONTH(created_at), DAY(created_at);

To find the number of registered users in the last 30 days I have this:

SELECT current_date(),COUNT(user_id) 
FROM users 
WHERE created_at >=  NOW() - INTERVAL 30 DAY;

Now I need to do the same but instead of the current_date() or now() date i need to do it for all dates generated from first query.

Your help is appreciated!

CodePudding user response：

SELECT DISTINCT users.created_at, SUM(Profiles)
FROM users
JOIN (
    SELECT created_at, COUNT(user_id) as Profiles
    FROM users
    GROUP BY created_at
) counts
ON counts.created_at BETWEEN users.created_at - INTERVAL 30 DAY AND users.created_at
GROUP BY users.created_at;

CodePudding user response：

You can make a separate query which uses a window function to make the running sum, then join this data to the first query:

SELECT SUBSTR(created_at,1,10) AS "Dtm", COUNT(user_id), T1.SumCnt
FROM users 
LEFT JOIN 
(
    SELECT
        Dtm,
        Cnt,
        SUM(Cnt) OVER (ORDER BY Dtm) AS SumCnt
    FROM (
        SELECT
            SUBSTR(created_at, 1,10) AS "Dtm",
            COUNT(user_id) as "Cnt"
        FROM users
        WHERE users.created_at > NOW() - INTERVAL 30 DAY
        GROUP BY Dtm
            ) S1
) T1 ON (SUBSTR(users.created_at,1,10)=T1.Dtm)  
WHERE users.created_at > NOW() - INTERVAL 30 DAY
GROUP BY Dtm ORDER BY 1

Note: this needs MySQL 8 to work.