I have a dataset that looks like this:
user_id day_session rank day_diff
xyz789 2017-11-19 1 1
abc123 2017-11-19 1 1
abc123 2017-11-20 2 1
abc123 2017-11-21 3 1
abc123 2017-11-22 4 1
abc123 2017-11-23 5 1
abc123 2017-11-24 6 1
abc123 2017-11-25 7 1
abc123 2017-11-26 8 1
abc123 2017-11-27 9 1
abc123 2017-11-28 10 1
abc123 2017-11-29 11 1
abc123 2017-11-30 12 1
abc123 2017-12-01 13 1
abc123 2017-12-02 14 1
def456 2017-11-19 1 1
def456 2017-11-20 2 1
def456 2017-11-21 3 1
def456 2017-11-22 4 1
def456 2017-11-23 5 1
def456 2017-11-24 6 1
def456 2017-11-25 7 1
def456 2017-11-26 8 1
def456 2017-11-27 9 1
def456 2017-11-28 10 1
def456 2017-11-29 11 1
def456 2017-11-30 12 1
def456 2017-12-01 13 1
def456 2017-12-02 14 1
def456 2017-12-03 15 1
def456 2017-12-04 16 1
def456 2017-12-05 17 1
def456 2017-12-06 18 1
def456 2017-12-07 19 1
def456 2017-12-08 20 1
def456 2017-12-09 21 1
def456 2017-12-10 22 1
def456 2017-12-11 23 1
def456 2017-12-12 24 1
def456 2017-12-13 25 1
def456 2017-12-14 26 1
def456 2017-12-15 27 1
def456 2017-12-16 28 1
def456 2017-12-17 29 1
def456 2017-12-18 30 1
def456 2017-12-19 31 1
def456 2017-12-20 32 1
def456 2017-12-21 33 1
def456 2017-12-22 34 1
def456 2017-12-23 35 1
def456 2017-12-24 36 1
def456 2017-12-25 37 1
def456 2017-12-26 38 5
def456 2017-12-31 39 1
def456 2018-01-01 40 1
def456 2018-01-02 41 1
def456 2018-01-03 42 1
def456 2018-01-04 43 1
I'd like to calculate the number of consecutive day sessions each user_id has from the earliest day_session in the dataset. So user xyz789 would return 1, abc123 would return 14 and def456 would return 38. TIA
Based on your data, you can just do:
select t.user, count(*)
from t left join
(select user, min(day_session) as minds
from t
where day_diff > 1
group by user
) tt
on t.user = tt.user
where tt.minds is null or t.day_session < tt.minds
group by t.user;