CODEWITHSUNDEEP
javaandroidsqlite
SRN
SRN

Reputation: 2455

calculate current streak - sqlite

I hava table with sessions

sessions
-----------------
_id | date 
-----------------
1   | 2015-12-25
2   | 2015-12-24
3   | 2015-12-23
4   | 2015-12-22
5   | 2015-12-22
6   | 2015-12-09
7   | 2015-11-04
8   | 2015-05-11

Now how to calculate current consecutive streak, here in the above table
current streak is 22 dec- 25 dec -> 4 days

i can remove duplicate dates using distinct command

SELECT DISTINCT(date) FROM sessions ORDER BY date DESC;


  date 
  -----------------
  2015-12-25
  2015-12-24
  2015-12-23
  2015-12-22
  2015-12-09
  2015-11-04
  2015-05-11

Upvotes: 1

Views: 292

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521864

SELECT (SELECT MAX(JULIANDAY(date)) FROM sessions) -
    COALESCE(
        (
            SELECT MAX(JULIANDAY(t.date))
            FROM
            (
                SELECT DISTINCT s1.date AS date, s1.date -
                    (SELECT MAX(s2.date)
                     FROM sessions s2
                     WHERE s2.date < s1.date) AS diff
                FROM sessions s1
            ) t
            WHERE t.diff > 1
        ),
        (SELECT MIN(JULIANDAY(date)) FROM sessions)
    )

Upvotes: 1

Related Questions