CODEWITHSUNDEEP
mysql
Saaz Rai
Saaz Rai

Reputation: 262

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.

Upvotes: 3

Views: 2554

Answers (2)

koriander
koriander

Reputation: 3258

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

Upvotes: 3

A l w a y s S u n n y
A l w a y s S u n n y

Reputation: 38502

Try it.... please note on where condition FOR 2014-12-02, as per comment

SELECT DATE_FORMAT(start_date,'%Y-%m-%d')as Date, count(*) as ActiveUser FROM registration
WHERE (start_date >= '2014/12/02' AND end_date <='2014/12/02')
GROUP BY start_date

Upvotes: 1

Related Questions