SQL query of active users

Question

I am trying to display active users in webapp. My database consists of two main tables: user and task . task has a user_id .

My definition of active users : is the user who created more than two tasks within two weeks from the specified end date.

So, I need an SQL query that gives me the number of active users between the start and end dates. I was able to do this by putting the query inside the loop, pulling the results each time, but I would like to do it in one query.

The returned data should look something like this:

 Date Count 2010-01-01 4 2010-01-02 3 2010-01-03 5 

Modify to clarify the required data set.

Answer 1

 SELECT t.taskdate, COUNT(DISTINCT u.user_id) FROM user u INNER JOIN task t ON u.user_id = t.user_id AND t.taskdate BETWEEN DATE_ADD(@EndDate, INTERVAL -2 WEEK) AND @EndDate GROUP BY t.taskdate ORDER BY t.taskdate 

Answer 2

A GROUP BY with a HAVING should be enough for all users

 SELECT u.user_id FROM user u INNER JOIN task t ON t.user_id = u.user_id WHERE date BETWEEN DATEADD(week, -2, <EndDate>) AND <EndDate> GROUP BY u.user_id HAVING COUNT(*) > 1 

and get the total counter ending it in another statement

 SELECT COUNT(*) FROM ( SELECT u.user_id FROM user u INNER JOIN task t ON t.user_id = u.user_id WHERE date BETWEEN DATEADD(week, -2, <EndDate>) AND <EndDate> GROUP BY u.user_id HAVING COUNT(*) > 1 ) u 

Edit

Fixed a 2 week requirement using the code sent by Joe.

Answer 3

This is not for mysql, but it can work (I'm not sure about task_date + 14).

 select * from user u where (select count(*) form task t where t.user_id = u.user_id and t.task_date + 14 > @end_date) > 2 

The internal query select count... receives tasks for a user who was created 14 or less days before the specified end_date