title: How to select specific rows based on a logic in a grouped-by data?
body: <p>I have the following data in Hive:</p>
  
  <pre><code>id  sequence app    time1                time2                 first_d_seq  last_d_seq
  2456    1      a    10/11/2018 10:25:43  10/11/2018 10:25:47   5            6
  2456    2      b    10/11/2018 10:25:48  10/11/2018 10:25:55   5            6
  2456    3      b    10/11/2018 10:25:58  10/11/2018 10:26:02   5            6
  2456    4      c    10/11/2018 10:26:02  10/11/2018 10:26:08   5            6
  2456    5      d    10/11/2018 10:26:08  10/11/2018 10:26:13   5            6
  2456    6      d    10/11/2018 10:26:15  10/11/2018 10:26:20   5            6
  2456    7      f    10/11/2018 10:26:20  10/11/2018 10:26:28   5            6
  2456    8      f    10/11/2018 10:26:32  10/11/2018 10:26:39   5            6
  9702    1      a    10/11/2018 11:05:14  10/11/2018 11:05:16   3            3
  9702    2      b    10/11/2018 11:05:16  10/11/2018 11:05:20   3            3
  9702    3      d    10/11/2018 11:05:20  10/11/2018 11:05:25   3            3
  9702    4      h    10/11/2018 11:05:25  10/11/2018 11:05:27   3            3
  9702    5      f    10/11/2018 11:05:27  10/11/2018 11:05:36   3            3
  
  </code></pre>
  
  <p>for which I know where app <code>d</code> starts and ends in the sequence for each <code>id</code> group (i.e., for the first group <code>d</code> starts at sequence=5 and ends at sequence=6). </p>
  
  <p>What I'd like to calculate, for each <code>id</code> group, are <strong>1)</strong> time spent from the very beginning (<code>sequence=1</code>) till the first appearance of <code>d</code> (<code>sequence = first_d_seq - 1</code>), and <strong>2)</strong> time spent from right after <code>d</code> (<code>sequence = last_d_seq + 1</code>) till the end of sequence for that id (i.e., <code>8</code> for id = 2456; and <code>5</code> for id = 9702).</p>
  
  <p>Essentially, the output should be like this:</p>
  
  <pre><code>id      before_d    after_d
  2456    25          19
  9702    6           11
  </code></pre>