title: Count number of events before and after a event "A" till another event "A" is encountered in Big query?
body: <p>I have a table containing date and events and users. There is event named 'A'. I want to find out how many time a particular event occurred before and after event 'A' in SQL Bigquery. The event A might appear multiple times. But it should count events only till it encounters another event A in both before and after condition.<br>
  for Example,</p>
  
  <pre><code> User           Date             Events
      123          2018-02-14            X.Y.A
      123          2018-02-12            X.Y.B
      134          2018-02-10            Y.Z.A
      123          2018-02-11            A
      123          2018-02-01            X.Y.Z
      134          2018-02-05            X.Y.B
      134          2018-02-04            A
      123          2018-02-13            A
  </code></pre>
  
  <p>The output would be something like this.</p>
  
  <pre><code>User       Event    Before   After
  123          A      1        1
  123          A      0        1
  134          A      0        1
  </code></pre>
  
  <p>The other condition remains same.</p>
  
  <p>This question is an extension of my previous question.</p>
  
  <p>See <a href="https://stackoverflow.com/questions/48794727/how-to-count-number-of-a-particular-event-before-another-event-in-sql-bigquery/48795130?noredirect=1#comment84622873_48795130">How to count number of a particular event before another event in SQL Bigquery?</a> for details.</p>
  
  <p>The event that I have to count contains a particular prefix. Means I have to check events that start with ( X.Y.then some event name). So, X.Y.SomeEvent are the events that I have to set the counter for. Any Suggestions?</p>