MySQL query to retrieve DISTINCT COUNT between moving DATE period

Question

I'm trying to write a query that returns a list of dates and the DISTINCT COUNT of User IDs for the 7 days preceding each date. The table I'm working with is simple, and looks like this:

Started                 UserId
"2012-09-25 00:01:04"   164382
"2012-09-25 00:01:39"   164382
"2012-09-25 00:02:37"   166121
"2012-09-25 00:03:35"   155682
"2012-09-25 00:04:18"   160947
"2012-09-25 00:08:19"   165806

I can write the query for output of an individual COUNT as follows:

SELECT COUNT(DISTINCT UserId)
FROM Session 
WHERE Started BETWEEN '2012-09-18 00:00' AND '2012-09-25 00:00';

But what I'm trying to do is output this COUNT for every day in the table AND the 7 days preceding it. To clarify, the value for September 25th would be the count of DISTINCT User IDs between the 18th and 25th, the 24th the count between 17th and 24th, etc.

I tried the following query but it provides just the COUNT for each day:

SELECT 
DATE(A.Started),
Count(DISTINCT A.UserId)
FROM Session AS A
WHERE DATE(A.Started) BETWEEN DATE(DATE_SUB(DATE(DATE(A.Started)),INTERVAL 7 DAY)) AND DATE(DATE(A.Started))
GROUP BY DATE(A.Started)
ORDER BY DATE(A.Started);

And the output looks like this:

DATE(A.Started) "Count(DISTINCT A.UserId)"
2012-09-18          709
2012-09-19          677
2012-09-20          658
2012-09-21          556
2012-09-22          530
2012-09-23          479
2012-09-24          528
2012-09-25          480
...

But as I said, those are just the daily counts. Initially I thought I could just sum the 7 day values, but that will invalidate the DISTINCT clause. I need the DISTINCT UserId counts for each 7 day period preceding a given date.

Ross Smith II Ross Smith II · Accepted Answer · 2012-09-28T00:47:26

This query should work for you:

SELECT
    DATE_FORMAT(d1.Started, '%Y-%m-%d') AS Started,
    COUNT(DISTINCT d2.UserID) Users
FROM
(
    SELECT
        DATE(Started) AS Started
    FROM
        Session
    GROUP BY
        DATE(Started)
) d1 
INNER JOIN
(
    SELECT DISTINCT
        DATE(Started) AS Started,
        UserID
    FROM
        Session
) d2
ON d2.Started BETWEEN d1.Started - INTERVAL 7 DAY AND d1.Started
GROUP BY
    d1.Started
ORDER BY
    d1.Started DESC

Visit http://sqlfiddle.com/#!2/9339c/5 to see this query in action.

MySQL query to retrieve DISTINCT COUNT between moving DATE period

2 Answers