Saaz Rai Saaz Rai - 5 months ago 33
SQL Question

Daily count of Active Users for a given date range

I need to find the Daily total count of Active Users based on the Start Date and End Date.

REGISTRATION TABLE

id registration_no start_date end_date
1 1000 2014/12/01 2014/12/03
2 1001 2014/12/01 2014/12/03
3 1002 2014/12/02 2014/12/04
4 1003 2014/12/02 2014/12/04
5 1004 2014/12/02 2014/12/04
6 1005 2014/12/03 2014/12/05
7 1006 2014/12/05 2014/12/06
8 1007 2014/12/05 2014/12/09
9 1008 2014/12/06 2014/12/10
10 1009 2014/12/07 2014/12/11


The result should be in the following format.

Date Active Users
2014-12-01 2
2014-12-02 5
2014-12-03 6
2014-12-04 4
2014-12-05 3
2014-12-06 3
2014-12-07 3
2014-12-08 3
2014-12-09 3
2014-12-10 2
2014-12-11 1
2014-12-12 0


I know the following query is not working.

SELECT start_date, count(*) FROM registration
WHERE start_date >= '2014/12/01' AND end_date <='2014/12/12'
GROUP BY start_date


Which is not the desired output :

2014-12-01 2
2014-12-02 3
2014-12-03 1
2014-12-05 2
2014-12-06 1
2014-12-07 1


Any help would be much appreciated.

Answer

You need to create a "calendar" with all the days you need and then use a query like:

SELECT calDay as `Date`, count(id) as `Active Users`
FROM   (SELECT cast('2014-12-01' + interval `day` day as date) calDay
        FROM   days31
        WHERE  cast('2014-12-01' + interval `day` day as date) < '2014-12-12') calendar
LEFT JOIN registration on (calDay between start_date and end_date)
GROUP BY calDay
ORDER BY calDay;

You can see it working in this fiddle, where days31 is just a view with integers 0-30. This allows the query to work in any calendar up to a period of 31 days. You can add more days to the view or generate them on the fly using cross joins. See http://www.artfulsoftware.com/infotree/qrytip.php?id=95