title: Best way to tell 3 or more consecutive records missing
body: <p>I'm implementing a achievement system. One of the "badges" I'm trying to create will determine:</p>
  
  <ol>
  <li>If a user has joined in at least one coding challenge</li>
  <li>Then hasn't joined in 3 consecutive coding challenges</li>
  <li>Then started participating again.</li>
  </ol>
  
  <p>The badge is simply called "I'll be back" ;-)</p>
  
  <p><strong>The tables</strong></p>
  
  <pre><code>users
  ==================
  id    fullname
  1     Gary Green
  
  
  challenge
  ==================================
  id  name         start_date
  1   challenge1   01-AUG-2010
  2   challenge2   03-AUG-2010
  3   challenge3   06-SEP-2010
  4   challenge4   07-SEP-2010
  5   challenge5   30-OCT-2010
  6   challenge6   05-NOV-2010
  
  
  entries
  ====================================================
  id   challengeid    userid    type       code
  1     1             1         1          -
  2     2             1         1          -
  3     6             1         1          -
  4     6             1         2          -
  </code></pre>
  
  <p>The "type" in the entries table refers to if the entry type is either a non-regex based entry or regex based one. A user can submit both a regex and non-regex entry, therefore the above entry for challenge 6 is valid.</p>
  
  <p><strong>Example output</strong></p>
  
  <p>This is the style output of the query I would like (in this case the badge should be awarded):</p>
  
  <pre><code>(for userid 1)
  Challenge 1 --&gt; Joined in
  Challenge 2 --&gt; Joined in
  Challenge 3 --&gt; NULL
  Challenge 4 --&gt; NULL
  Challenge 5 --&gt; NULL
  Challenge 6 --&gt; Joined in
  </code></pre>
  
  <p><strong>How?</strong></p>
  
  <p>Here are my questions</p>
  
  <ol>
  <li>Whats the best way to do this in a query?</li>
  <li>Is there a function I can use in MySQL to SELECT this range without resorting to some PHP?</li>
  </ol>
  
  <hr>
  
  <p><strong>The query so far</strong></p>
  
  <p>I'm doing a LEFT OUTER JOIN to join the challenge table and entries table (LEFT OUTER to make sure to preserve the challenges the user has not joined in), then sort by challenge start_date to see if the user has not joined in for 3 or more consecutive challenges.</p>
  
  <pre><code>SELECT challenge.id AS challenge_id, entries.id AS entry_id
  FROM challenge_entries entries
  LEFT OUTER JOIN challenge_details challenge
   ON entries.challengeid = challenge.id
  WHERE entries.userid = &lt;user_id&gt;
  ORDER BY challenge.start_date
  GROUP BY entries.challengeid
  </code></pre>
  
  <hr>
  
  <p><strong>important edit:</strong> for this badge to make sense the criteria will need to be 3 or more consecutive challenges sandwiched between challenges that were joined in i.e. like the example output above. Otherwise anyone who joins in a challenge for the first time will automatically receive the badge. The user has to be seen to have been "away" from participating in challenges for a while (>=3)</p>