Count the number of users between certain dates

Question

I have a table of users with when they signed_up and when they were last_seen:

+--------+-----------+-----------+
| userid | signed_up | last_seen |
+--------+-----------+-----------+
|      1 | 1/1/14    | 1/3/14    |
|      2 | 1/1/14    | 1/5/14    |
|      3 | 1/3/14    | 1/5/14    |
|      4 | 1/6/14    | 1/7/14    |
+--------+-----------+-----------+

Lets assume each user comes to the site on everyday between signed_up and last_seen. I'd like to count how many total users there are on each day. The result I'm looking for is below:

+--------+----------------+
|  date  | count_of_users |
+--------+----------------+
| 1/1/14 |              2 |
| 1/2/14 |              2 |
| 1/3/14 |              3 |
| 1/4/14 |              2 |
| 1/5/14 |              2 |
| 1/6/14 |              1 |
| 1/7/14 |              1 |
+--------+----------------+

And just for clarification, here's how the numbers were calculated (I don't need this table, this is just an illustration)

+--------+----------------+-------+-------+-------+-------+
|  date  | count_of_users | user1 | user2 | user3 | user4 |
+--------+----------------+-------+-------+-------+-------+
| 1/1/14 |              2 |     1 |     1 |       |       |
| 1/2/14 |              2 |     1 |     1 |       |       |
| 1/3/14 |              3 |     1 |     1 |     1 |       |
| 1/4/14 |              2 |       |     1 |     1 |       |
| 1/5/14 |              2 |       |     1 |     1 |       |
| 1/6/14 |              1 |       |       |       |     1 |
| 1/7/14 |              1 |       |       |       |     1 |
+--------+----------------+-------+-------+-------+-------+

Not sure if this is getting beyond what should be done in MySql... Thanks for the help!

I hate to be the one to ask... but what have you tried so far? — Jonathan Hall, May 26 '14 at 19:17
Signed_up and last_seen are datetimes. I'm trying to create a "temp" table of (user,date) pairs for all the days in between and then do a group by. Wanted to see if I should continue down that path or if I'm just being stupid. — user3081257, May 26 '14 at 19:22
To clarify, trying to generate a list of all dates using this technique: http://stackoverflow.com/questions/510012/get-a-list-of-dates-between-two-dates. Then counting each user if they are between the specified dates. — user3081257, May 26 '14 at 19:31

score 1 · Accepted Answer · edited May 23 '17 at 11:57

Create a date range table:

mysql> CREATE TABLE IF NOT EXISTS calendar (date DATE NOT NULL PRIMARY KEY);
mysql> INSERT INTO calendar (SELECT calendar.date
    FROM (
        SELECT (SELECT MIN(signed_up) FROM users)
               + INTERVAL (a.val + (10 * b.val)) DAY
        as date
        FROM (select 0 as val union all select 1 union all select 2 union all
        select 3 union all select 4 union all select 5 union all select 6
        union all select 7 union all select 8 union all select 9)
        as a
        CROSS JOIN (select 0 as val union all select 1 union all select 2
        union all select 3 union all select 4 union all select 5 union all
        select 6 union all select 7 union all select 8 union all select 9)
        as b
    ) calendar
    WHERE calendar.date BETWEEN
          (SELECT MIN(signed_up) FROM users) AND
          (SELECT MAX(last_seen) FROM users));  

mysql> select * from calendar;
+------------+
| date       |
+------------+
| 2014-01-01 |
| 2014-01-02 |
| 2014-01-03 |
| 2014-01-04 |
| 2014-01-05 |
| 2014-01-06 |
| 2014-01-07 |
+------------+
7 rows in set (0.00 sec)

mysql> select * from users;
+--------+------------+------------+
| userid | signed_up  | last_seen  |
+--------+------------+------------+
|      1 | 2014-01-01 | 2014-01-03 |
|      2 | 2014-01-01 | 2014-01-05 |
|      3 | 2014-01-03 | 2014-01-05 |
|      4 | 2014-01-06 | 2014-01-07 |
+--------+------------+------------+
4 rows in set (0.00 sec)

Once you have the date range table, the query you are looking for can be expressed as a JOIN / GROUP BY operation:

mysql> SELECT c.date, count(c.date)
       FROM calendar c JOIN users u WHERE c.date BETWEEN u.signed_up AND u.last_seen
       GROUP BY c.date;

+------------+---------------+
| date       | count(c.date) |
+------------+---------------+
| 2014-01-01 |             2 |
| 2014-01-02 |             2 |
| 2014-01-03 |             3 |
| 2014-01-04 |             2 |
| 2014-01-05 |             2 |
| 2014-01-06 |             1 |
| 2014-01-07 |             1 |
+------------+---------------+
7 rows in set (0.00 sec)

If you are doing this kind of query often, it would pay to generate (and keep) the calendar table. However, if you don't want to create the calendar table, the result can be generated on the fly using a single query:

mysql> SELECT c.date, COUNT(c.date)
FROM (SELECT calendar.date
     FROM (
         SELECT (SELECT MIN(signed_up) FROM users)
                + INTERVAL (a.val + (10 * b.val)) DAY
         as date
         FROM (select 0 as val union all select 1 union all select 2 union all
         select 3 union all select 4 union all select 5 union all select 6
         union all select 7 union all select 8 union all select 9)
         as a
         CROSS JOIN (select 0 as val union all select 1 union all select 2
         union all select 3 union all select 4 union all select 5 union all
         select 6 union all select 7 union all select 8 union all select 9)
         as b
     ) calendar
     WHERE calendar.date BETWEEN
           (SELECT MIN(signed_up) FROM users) AND
           (SELECT MAX(last_seen) FROM users)) c
JOIN users u WHERE c.date BETWEEN u.signed_up AND u.last_seen
GROUP BY c.date

which yields

+------------+---------------+
| date       | COUNT(c.date) |
+------------+---------------+
| 2014-01-01 |             2 |
| 2014-01-02 |             2 |
| 2014-01-03 |             3 |
| 2014-01-04 |             2 |
| 2014-01-05 |             2 |
| 2014-01-06 |             1 |
| 2014-01-07 |             1 |
+------------+---------------+
7 rows in set (0.00 sec)

Note that, depending on how large a calendar you need, you may need to add more CROSS JOINS in the INSERT INTO calendar statement. See the link for an example.

Count the number of users between certain dates

1 Answers1