How to query Android Room for current streak and longest Streak

Question

Currently have a a table in android room that saves the day in a column in the format yyyy-MM-dd. I want to be query the longest streak of consecutive days and the current streak. How would I accomplish that in Room?

Here is some sample data to help explain what I want:

     action      | date
------------------------------
sample action 1  | 2021-03-01
sample action 2  | 2021-03-02
sample action 3  | 2021-03-03
sample action 4  | 2021-03-04
sample action 5  | 2021-03-05
sample action 6  | 2021-03-06
sample action 7  | 2021-03-09
sample action 8  | 2021-03-09
sample action 9  | 2021-03-10
sample action 10 | 2021-03-11
sample action 11 | 2021-03-12

So the longest streak would be the 6 days from 2021-03-01 through 2021-03-06, and the current streak would be 4 days (2021-03-09 through 2021-03-12).

Answer 1

Use window functions LAG() and SUM() to create 1 group for each streak and then aggregate to count the rows of each group:

WITH cte AS (
  SELECT SUM(COALESCE(flag, 1)) OVER (ORDER BY date) grp
  FROM (
    SELECT *, DATE(date, '-1 day') <> LAG(date) OVER (ORDER BY date) flag
    FROM (SELECT DISTINCT date FROM tablename)
  )
)  
SELECT MAX(COUNT(*)) OVER () longest_streak, 
       COUNT(*) current_streak 
FROM cte
GROUP BY grp
ORDER BY grp DESC LIMIT 1

See the demo.
Results:

longest_streak	current_streak
6	4