CODEWITHSUNDEEP
androidsqlsqlite
Nick
Nick

Reputation: 4766

How do you count the number of consecutive dates in a table?

In my Android App I need to keep track of the longest streak, and current streak, of consecutive dates that are saved in a database table. I don't even know where to start to get this to work. The best I can come up with is to query every row in the table and iterate through all of them programmatically to find where there's a gap. Not very efficient. Anyone have any better ideas?

Upvotes: 4

Views: 1515

Answers (2)

Sergey Benner
Sergey Benner

Reputation: 4431

Basically you have a month and days in a month so you just compare the days count to the needed number.

If there's a gap you can easily find it out by substracting the count from days in a month. E.g. you have count(days_visited) where month=1 and it returns you 20 days but January has 31 so there's a gap in 11 days and here're the date functions of sqlite

http://www.sqlite.org/lang_datefunc.html

You can use following functions like SELECT date('now','start of year','+9 months','weekday 2');

EDIT

sorry everyone solution is ugly. it is I know.

create table visits(day_visited timestamp,visited int); You create a record everyday in this table indicating whether a user was online or offline with 'now',1 or 0 (online/offline). Then you run through there records. Your records for month will be an int array with 1s and 0s. called vistedrecordsformonth

pseudo code:

      int online=0;
      int offline=0;     
for(int i=0;i<vistedrecordsformonth.size();i++){    
      boolean flag=false;         
   if(vistedrecordsformonth[i]==1){ //visited
      if(!flag&&i!=0) //check for flag and not 0 index to insert a record
      {
      streaksMap.put(online,offline); //or insert a record into another streakmap or table 
      online=0;
      offline=0;     
      }
      flag=true;
      online++; 
   }
   else{
      flag=false;
      offline++;
   }
} //end of for

The map or table will contain a pair of online=offline days for a month. with usual order by you can see what was the biggest streak in online or offline days.

It is ugly I know I'm sure there should be something more elegant but as quick and dirty it should work.

hope it helps abit.

Upvotes: 1

gangreen
gangreen

Reputation: 909

Here is an SQL only solution that I thought was really cool. Assuming the dates in your table are unique (not that it would be too hard to just group on them) you can use the method adapted from here http://www.sqlteam.com/article/detecting-runs-or-streaks-in-your-data. I ran through the example and there are some syntax errors, so hopefully I didn't repeat them in my answer below. I probably used some reserved keywords, so you may need to adapt that.

First create a table of Dates that is significantly large to cover your needs. I'm not sure what the best method for SQLite is, but in SQL Server you can insert integers into a Datetime field and it will do an implicit conversion of integers to dates. There are many methods to insert integers into tables...

Anyway, once the Dates table is created, do a left join from your Dates table to your Streak table using your min and max dates from your streak table as your range limiter. You will then have the following code. Let’s call it SQL 0

SELECT Dates.Date,
       CASE 
         WHEN StreakTable.DATE IS NULL THEN 0 
         ELSE 1 
       END AS Result 
FROM   Dates 
       LEFT JOIN StreakTable 
         ON Dates.DATE = StreakTable.DATE 
WHERE  Dates.DATE BETWEEN (SELECT MIN(DATE) AS MinDate 
                           FROM   StreakTable) AND (SELECT MAX(DATE) AS MaxDate 
                                                    FROM   StreakTable)

Let’s call the following SQL 1

SELECT Date, 
       Result, 
       (SELECT COUNT(*) 
        FROM   (SQL 0) S 
        WHERE  S.Result <> SV.Result 
               AND S.GameDate <= SV.GameDate) AS RunGroup 
FROM   (SQL 0) SV

Let’s call the following SQL 2

SELECT Result, 
       MIN(Date) AS StartDate, 
       MAX(Date) AS EndDate, 
       COUNT(*)      AS Days 
FROM   (SQL 1) A 
GROUP  BY Result, 
          RunGroup 
ORDER  BY MIN(Date)

At this point you can do some pretty cool stuff like answer: What was the longest streak?

SELECT TOP 1 * 
FROM   (SQL 2) A 
WHERE  Result = 1 
ORDER  BY Games DESC

What is the current streak as of the most recent date?

SELECT *  
FROM (SQL2) A 
WHERE EndDate = (SELECT Max(Date) 
FROM Streak)

How many streaks of 3 or more did we have?

SELECT Result, 
  COUNT(*) as NumberOfStreaks 
FROM (SQL 2) A 
GROUP BY Result 
WHERE Days >= 3

Upvotes: 1

Related Questions