CODEWITHSUNDEEP
mysql
Kofi Black
Kofi Black

Reputation: 121

How to get each user's maximum number of consecutive days during which a condition is met

I have the following ranking table: 

CREATE TABLE IF NOT EXISTS ranking(  
user_id int(11) unsigned NOT NULL,  
create_date date NOT NULL,  
score int(8),  
PRIMARY KEY (user_id, create_date)  
);

I want to get each user's maximum number of consecutive days during which the score is greater or equal to 10. For example, if the table contains the following entries, the output (user, max_number) is listed below. My question is how to write the query in MySQL?

user_id | create_date | score  
  1     | 2017-03-08  |    40   
  1     | 2017-03-07  |    50   
  1     | 2017-03-06  |    60   
  1     | 2017-03-05  |     0   
  1     | 2017-03-04  |    70   
  1     | 2017-03-03  |    80   
  1     | 2017-03-02  |     0   
  2     | 2017-03-10  |    20   
  2     | 2017-03-09  |    30   
  2     | 2017-03-08  |    40   
  2     | 2017-03-07  |    50   
  2     | 2017-03-06  |     0   
  2     | 2017-03-05  |    60   
  2     | 2017-03-04  |    70

Output: 

user_id | max_number  
  1     |   3         
  2     |   4

Upvotes: 2

Views: 142

Answers (1)

Gurwinder Singh
Gurwinder Singh

Reputation: 39507

You can use user variables for this task:

select user_id, max(cnt) max_cnt
from (
    select user_id, date_group, count(*) cnt
    from (
        select t.*, date_sub(create_date, interval(@rn := @rn + 1) day) date_group
        from your_table t, (select @rn := 0) x
        where score >= 10
        order by user_id, create_date
        ) t
    group by user_id, date_group
    ) t
group by user_id;

Produces:

user_id max_cnt
1       3
2       4

Demo: Rextester

How it works:

We generate a sequence number in the order of user_id and create_date (both increasing) and then, subtract as many days as this sequence number from the create_date to create groups where the dates are consecutive and then apply required aggregations to get the results.

Upvotes: 1

Related Questions