title: In Postgresql, how can I find 3 consecutive events where only the first one occurs within a time frame?
body: <p>I have the table below with user_id, timestamp and event_id. The column "tag" denotes if this is the desirable row (tag = 1) or not (tag = 0):</p>
  
  <pre><code>user_id | timestamp                 | event_id | tag 
  
      46  | 2018-12-21 08:42:35.000   | 1        | 0
      46  | 2018-12-21 09:58:35.000   | 2        | 1
      46  | 2018-12-22 06:42:35.000   | 3        | 0
      46  | 2018-12-22 07:18:35.000   | 4        | 1
      46  | 2018-12-22 08:30:35.000   | 5        | 1
      46  | 2018-12-23 06:42:35.000   | 6        | 0
      46  | 2018-12-23 06:11:35.000   | 7        | 1
      46  | 2018-12-23 07:51:35.000   | 8        | 1
      46  | 2018-12-23 07:26:35.000   | 9        | 1
      46  | 2018-12-23 07:37:35.000   | 10       | 1
      46  | 2018-12-23 08:05:35.000   | 11       | 1
      46  | 2018-12-23 08:20:35.000   | 12       | 1 
      46  | 2018-12-23 09:10:35.000   | 13       | 1
      46  | 2018-12-23 09:42:35.000   | 14       | 0
      46  | 2018-12-23 10:17:35.000   | 15       | 1   
      46  | 2018-12-24 09:42:35.000   | 16       | 0
      46  | 2018-12-24 10:45:35.000   | 17       | 0
      46  | 2018-12-24 11:12:35.000   | 18       | 0
      46  | 2018-12-24 11:51:35.000   | 19       | 1
      122 | 2018-12-22 08:30:35.000   | 1        | 1
      122 | 2018-12-23 06:42:35.000   | 2        | 0
      122 | 2018-12-23 06:11:35.000   | 3        | 1
      122 | 2018-12-23 07:51:35.000   | 4        | 1
      122 | 2018-12-23 07:26:35.000   | 5        | 1
      122 | 2018-12-23 07:37:35.000   | 6        | 1
      122 | 2018-12-28 06:42:35.000   | 1        | 0
      122 | 2018-12-28 06:38:35.000   | 2        | 1
      122 | 2018-12-28 07:51:35.000   | 3        | 1
      122 | 2018-12-28 07:26:35.000   | 4        | 1
      122 | 2018-12-28 08:42:35.000   | 5        | 0
      122 | 2018-12-28 09:38:35.000   | 6        | 0
      122 | 2018-12-28 10:51:35.000   | 7        | 0
      122 | 2018-12-28 11:26:35.000   | 8        | 0
  </code></pre>
  
  <p>So I would like to find:</p>
  
  <ol>
  <li>The number of times the user had 3 CORRECT (tag = 1) <em>consecutive</em> events (i.e. triplets) in the same date.</li>
  <li>The timestamp of the 1st event for each of those 3 consecutive events.</li>
  </ol>
  
  <p>Ideally the returned table should look like this: </p>
  
  <pre><code>user_id | first_occurrence           |event_id | consecutive_events 
       46 | 2018-12-23 06:11:35.000    | 7       | 2  &lt;-- 2 consecutive triplets 
       46 | 2018-12-23 07:37:35.000    | 10      | 2  &lt;-- this has 4 consecutive events  but I am only interested in triplets of events.
       122| 2018-12-23 06:11:35.000    | 4       | 1
       122| 2018-12-28 06:38:35.000    | 2       | 1  
  </code></pre>
  
  <p>In other words the columns consecutive_events must display all the triplets for a user in each day while the columns first_occurrence &amp; event_id should display the fist timestamp &amp; event_id of each triplet per user_id &amp; date.</p>
  
  <p>NOTE:
  The user_id 46 has a triplet of 0 (tag = 0). These triplet should be excluded.</p>
  
  <pre><code>    46  | 2018-12-24 09:42:35.000   | 16       | 0
      46  | 2018-12-24 10:45:35.000   | 17       | 0
      46  | 2018-12-24 11:12:35.000   | 18       | 0
  </code></pre>
  
  <p>I tried to use the <code>DENSE_RANK()</code> function but the results are far from optimal:</p>
  
  <pre><code>dense_rank() over (partition by user_id, date(timestamp) order by tag,date(timestamp) ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
  </code></pre>
  
  <p><strong>[UPDATE]</strong></p>
  
  <p>The example I'm referring to in the first comment of Gordon's answer is the following. For these consecutive events:</p>
  
  <pre><code>user_id | timestamp                 | event_id | tag 
      46  | 2018-12-23 06:11:35.000   | 7        | 1
      46  | 2018-12-23 07:51:35.000   | 8        | 1
      46  | 2018-12-23 07:26:35.000   | 9        | 1
      46  | 2018-12-23 07:37:35.000   | 10       | 1
      46  | 2018-12-23 08:05:35.000   | 11       | 1
      46  | 2018-12-23 08:20:35.000   | 12       | 1 
      46  | 2018-12-23 09:10:35.000   | 13       | 1
  </code></pre>
  
  <p>the query returns:</p>
  
  <pre><code> user_id | min(timestamp)            | min_event_id | num_consecutive 
       46  | 2018-12-23 06:11:35.000   | 7            | 2
  </code></pre>
  
  <p>It should also return</p>
  
  <pre><code>user_id | min(timestamp)            | min_event_id | num_consecutive 
       46  | 2018-12-23 06:11:35.000   | 7            | 2
       46  | 2018-12-23 07:37:35.000   | 10           | 2
  </code></pre>
  
  <p>Do you think this is possible to fetch as well?</p>