CODEWITHSUNDEEP
sqlmysql
Ian McIntyre Silber
Ian McIntyre Silber

Reputation: 5663

Active Users SQL query

I'm trying to graph active users in a webapp. My database consists of two main tables, user and task. task has a user_id.

My definition of active users: is a user who has created more than 2 tasks within two weeks of a given end date.

So, I need an SQL query that gives me the number of active users between a given start and end date. I was able to do this by putting the query inside a loop, pulling the results each time, but I'd love to be able to do it in a single query.

The returned data should be something like: 

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

Edit to clarify desired dataset.

Upvotes: 2

Views: 2710

Answers (3)

Joe Stefanelli
Joe Stefanelli

Reputation: 135749

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

Upvotes: 2

Lieven Keersmaekers
Lieven Keersmaekers

Reputation: 58431

A GROUP BY with a HAVING clause should be sufficient to get 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 to get the total count wrap it up in an 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 the 2 week requirement using the code posted by Joe.

Upvotes: 1

sh_kamalh
sh_kamalh

Reputation: 3901

This is not for mysql but it could work (I am not sure of 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 inner query select count... gets the tasks for the user that have been created 14 or less days before the given end_date

Upvotes: 0

Related Questions