title: SQL counting occurrences of conditional statements using values from 2 different roles
body: <p>I have a table with fields including time (UTC) and accountID. </p>
  
  <pre><code>accountID | time | ...
  1         |12:00 |....
  1         |12:01 |...
  1         |13:00 |...
  2         |14:00 |...
  </code></pre>
  
  <p>I need to make an sql query to return the accountID with a new field counting 'category' where 'category' can be 'a' or 'b'. If there is a row entry from the same accountID that has a <em>positive</em> time difference of 1 minute or less, category 'a' needs to be incremented, otherwise 'b'. The results from the above table would be</p>
  
  <pre><code>accountID| cat a count| cat b count
  1        | 1          | 2
  2        | 0          | 1
  </code></pre>
  
  <p>What approaches can I take to compare values between different rows and output occurrences of comparison outcomes?</p>
  
  <p>Thanks</p>