CODEWITHSUNDEEP
mysqlsql
toy
toy

Reputation: 12141

Find out if a particular user logs in every day

How do I find out if a particular user log ins every day using SQL in MySQL?

I have a table like this.

+----------+---------------------+---------+
| login_id | login_date          | user_id |
+----------+---------------------+---------+
|     8494 | 2016-03-01 00:00:00 |   20703 |
|    22403 | 2016-03-01 00:00:00 |   53473 |
|    23965 | 2016-03-01 00:00:00 |   79722 |
|    30173 | 2016-03-01 00:00:00 |   36396 |
|    34928 | 2016-03-02 00:00:00 |   20703 |
|    38740 | 2016-03-02 00:00:00 |   15758 |
|    42769 | 2016-03-02 00:00:00 |   79722 |
|    44364 | 2016-03-02 00:00:00 |   79722 |
|    45440 | 2016-03-03 00:00:00 |   20703 |
|    49050 | 2016-03-03 00:00:00 |   36396 |
|    50273 | 2016-03-03 00:00:00 |   79722 |
|    50344 | 2016-03-04 00:00:00 |   20703 |
|    51360 | 2016-03-04 00:00:00 |   20703 |
|    54404 | 2016-03-04 00:00:00 |   53473 |
|    61533 | 2016-03-04 00:00:00 |   79722 |
|    72852 | 2016-03-05 00:00:00 |   20703 |
|    74546 | 2016-03-05 00:00:00 |   38289 |
|    76487 | 2016-03-05 00:00:00 |   62529 |
|    82439 | 2016-03-05 00:00:00 |   36396 |
|    90006 | 2016-03-05 00:00:00 |   36396 |
|    90404 | 2016-03-06 00:00:00 |   20703 |
+----------+---------------------+---------+

And the answer would be something like

user_id, consecutive
20703, 'YES'
53473, 'NO'

20703 logins everyday from 2016-03-01 - 2016-03-06 but 53473 only logged in twice. the range is going to be from the start to finish of the entire table.

Upvotes: 2

Views: 206

Answers (3)

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

I would just simply use group by with case expressions to find consecutive logins

select user_id, 
       case when count(distinct login_date) <> datediff(max(login_date), min(login_date))+1
            then 'No' else 'Yes' end consecutive
from table t
group by user_id

If, you want to find users which has consecutive logins with start date to end date, then you could also do that via cross joins.

select user_id, case when counts <> total_counts then 'No' else 'Yes' end consecutive 
from (select t.user_id, count(distinct login_date) counts
      from table t 
      group by t.user_id )t cross join (
select count(distinct login_date) total_counts from table) tt

Upvotes: 1

jose_bacoy
jose_bacoy

Reputation: 12684

I get the max and min date in tbl then if the count per user id is equal to the datediff between max and min date (+1 day) then it is consecutive login else no.

select t1.user_id, 
    case when count(*)=datediff(t2.maxdt, t2.mindt)+1
    then 'YES' else 'NO'  end as consecutive
from tbl t1
join (select max(login_date) as maxdt,
             min(login_date) as mindt
          from tbl) t2
group by t1.user_id
order by t1.user_id;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269633

Well, you can do:

select user_id,
       ( count(distinct login_date) = cnt_ld ) as everyday_flag
from t cross join
     (select count(distinct login_date) as cnt_ld
      from t
     ) tt
group by user_id, tt.cnt_ld;

Note that this compares a user to all days in the table. If one day has no logins and you still want to include that day, then use the maximum minus the minimum for the comparison.

This also returns a "boolean" flag (really 0 or 1). You can use case to convert this to 'YES'/'NO', but I find the number easier to work with.

Upvotes: 2

Related Questions