CODEWITHSUNDEEP
mysqlselect
GafferG
GafferG

Reputation: 1043

MySQL Select number of rows where value has appeard before a date range

I have a table with the following columns

id | user_id | date

What i want to do is count the rows between a date range where the user_id has a row before that date range.

So for example if this table was tracking user log ins i want to count the users that have logged in this month, who have previously logged in before this month, essentially ignoring new users who joined this month.

Ideally a single query, any help appreciated.

Thanks

Upvotes: 0

Views: 36

Answers (1)

Luke
Luke

Reputation: 93

Try something like this:

SELECT Count(distinct user_id) usr_count
FROM userlogins u
WHERE 
    -- here we are getting those users for current period...
    login_date between '2014-02-01' and '2014-02-28'
    -- only where a record exists for that user in an earlier period
   and exists (Select * From userlogins 
         where user_id = u.user_id and login_date < '2014-02-01');

Upvotes: 0

Related Questions