Rolling count in MySQL

Question

I need to get the following ratio (daily sign up count)/(last 30 days rolling sign up count for each day)

The daily numbers are straight forward

SELECT a.DailySignup
FROM
(
SELECT COUNT(1) AS DailySignup, date
FROM users
WHERE date BETWEEN datestart and dateend
GROUP BY date
) a

but how can I compute the last 30 days count for each day? I know I need to join it against the same table again but I cant seem to wrap my head around it as to how.

UPDATE Sorry I've not been very clear.

SIGNUP_COUNT DATE          Rolling_Signup_Count
10           2011-01-01    Sign ups from 2010-12-01 to 2011-01-01
12           2011-01-02    Sign ups from 2010-12-02 to 2011-01-02
4            2011-01-03    Sign ups from 2010-12-03 to 2011-01-03

Hope this table helps to illustrate what I mean by rolling sign up count

Answer 1

You could try a subquery:

SELECT DISTINCT t.date, 
(SELECT COUNT(*) FROM users u where u.date BETWEEN DATE_ADD(t.date, INTERVAL -30 day) AND t.date) as c 
FROM users t

For a given date t.date, the subquery calculates the count for the 30-day period ending with t.date.

EDIT: To calculate the ratio (logins per day)/(logins within 30 days), you could try

SELECT t.date, count(*)/(SELECT COUNT(*) FROM users u 
       WHERE u.date BETWEEN DATE_ADD(t.date, INTERVAL -30 day) AND t.date) as c 
FROM users t GROUP BY t.date

Answer 2

You can group by MONTH(date) to get for each month, try something like this:

SELECT a.DailySignup, month AS MONTH(date)
FROM users
INNER JOIN (
    SELECT COUNT(*) AS DailySignup, MONTH(date) AS month
            FROM users
    GROUP BY MONTH(date)
) a ON a.month = month
    GROUP BY month