CODEWITHSUNDEEP
mysqlsql
Tony D
Tony D

Reputation: 194

SELECT users that appear daily

I have a question that appears easy on the surface but I'm finding challenging, hence the request for help. I have a table with two columns:

table: USERS

USER_ID | LOGGED_IN_DATE
  001   | 2015-05-01
  002   | 2015-05-01
  003   | 2015-05-01
  001   | 2015-05-02
  ...

What I need is a query that will return all of the IDs that were present every day for a given week, say 2015-05-01 through 2015-05-07. Not just anytime during the week, but there must be a record for that user every day. I need the fastest and most concise query possible. Any ideas?

What I tried already:

  1. Sub-queries
  2. Union Queries
  3. self-join

With no success.

Thanks!

Upvotes: 2

Views: 1266

Answers (2)

Tony D
Tony D

Reputation: 194

After thinking about it, instead of trying to do some complicated SQL query, I asked myself what does it mean to be online daily. It means that the number of unique dates in that given time period should equal 7. So this query I think works well:

select sub.user_id, sub.count 
FROM (select user_id, count(1) as count from users where logged_in_date >= '2015-05-01' AND logged_in_date < '2015-05-08' group by user_id) sub 
where sub.count = 7;

Any thoughts/comments?

UPDATE:

This should handle any number of logins at the day level:

SELECT DISTINCT user_id, count(1) AS total 
  FROM (SELECT DISTINCT user_id, logged_in_date 
         FROM users 
           WHERE logged_in_date >= '2015-05-01' 
             AND logged_in_date < '2015-05-08' 
               ORDER BY logged_in_date) sub 
   GROUP BY user_id 
   HAVING total = 7;

As well as @Gordon's answer:

SELECT u.user_id
  FROM users u
   WHERE u.LOGGED_IN_DATE >= '2015-05-01' 
      AND u.LOGGED_IN_DATE < '2015-05-08'
       GROUP BY u.user_id
         HAVING COUNT(DISTINCT DATE(u.LOGGED_IN_DATE)) = 7;

I like his better though. Good job.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269633

Aggregation is probably the easiest way:

select u.user_id
from users u
where u.LOGGED_IN_DATE >= '2015-05-01' and u.LOGGED_IN_DATE < '2015-05-08'
group by u.user_id
having count(distinct date(u.LOGGED_IN_DATE)) = 7;

If the field is really a date with no time, then you don't need the date() function in the having clause.

Upvotes: 5

Related Questions