title: Postgres count rows where value transitions from : A -> B -> A
body: <p>I need to find the count of records where <code>position</code> of a <code>person_id</code> changes from <code>1 -&gt; 2 -&gt; 1</code><br> The transitions needs to be sorted in ascending order depending on the <code>timestamp</code></p>
  
  <p>Table Structure</p>
  
  <pre><code>timestamp  | person_id | position
  --------------------------------
  2020-01-01 | 5         |  1
  2020-01-03 | 4         |  2
  2020-02-04 | 7         |  1
  2020-02-05 | 5         |  2
  2020-02-06 | 7         |  2
  2020-02-09 | 5         |  1
  </code></pre>
  
  <p>Here, the output will be <code>1</code>, since <code>person_id</code>:<code>5</code> transitions from <code>1 -&gt; 2 -&gt; 1</code></p>
  
  <pre class="lang-sql prettyprint-override"><code>select count(*)
  from table_name t1
  join table_name t2
    on t1.person_id = t2.person_id
   and t1.position = 1 
   and t2.position = 2
   and t1.timestamp &lt; t2.timestamp;
  </code></pre>
  
  <p>Using this query I can find transition from <code>1 -&gt; 2</code>
  But I can't figure out how to again search for the transition to <code>1</code></p>