title: Select partitions based on matches in other table
body: <p>Having the following table (<code>conversations</code>):</p>
  
  <pre><code> id | record_id  |  is_response  |         text         |
   ---+------------+---------------+----------------------+
   1  |     1      |      false    | in text 1            |
   2  |     1      |      true     | response text 3      |
   3  |     1      |      false    | in text 2            |
   4  |     1      |      true     | response text 2      |
   5  |     1      |      true     | response text 3      |
   6  |     2      |      false    | in text 1            |
   7  |     2      |      true     | response text 1      |
   8  |     2      |      false    | in text 2            |
   9  |     2      |      true     | response text 3      |
   10 |     2      |      true     | response text 4      |
  </code></pre>
  
  <p>And another help table (<code>responses</code>):</p>
  
  <pre><code> id |         text         |
   ---+----------------------+
   1  | response text 1      |
   2  | response text 2      |
   3  | response text 4      |
  </code></pre>
  
  <p>I'm looking for an SQL query to output the following:</p>
  
  <pre><code>  record_id |       context
    ----------+-----------------------+---------------------
         1    | in text 1 response text 3 in text 2 response text 2
    ----------+-----------------------+---------------------
         2    | in text 1 response text 1
    ----------+-----------------------+---------------------
         2    | in text 2 response text 3 response text 4
  </code></pre>
  
  <p>So each time <code>is_response</code> is <code>true</code> and the <code>text</code> is <strong>in</strong> the responses table, <strong>aggregate</strong> the conversation context up to this point, ignoring conversation part that does not end with a response in the pool.</p>
  
  <p>In the example above living <em>response text 3</em> in <code>record_id</code> 1.</p>
  
  <p>I've tried the following complex SQL but it breaks sometimes aggregating the text wrong:</p>
  
  <pre><code>with context as(
      with answers as (
  
         SELECT record_id, is_response, id as ans_id
          , max(id)
            OVER (PARTITION BY record_id ORDER BY id
            ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS previous_ans_id
         FROM (select * from conversations where text in (select text from responses)) ans
         ),
       lines as (
        select answers.record_id, con.id, COALESCE(previous_ans_id || ',' || ans_id, '0') as block, con.text as text from answers, conversations con where con.engagement_id = answers.record_id and ((previous_ans_id is null and con.id &lt;= ans_id) OR (con.id &gt; previous_ans_id and con.id &lt;= ans_id)) order by engagement_id, id asc
        )
  
        select record_id, block,replace(trim(both ' ' from string_agg(text, E' ')) ,'  ',' ') ctx from lines group by record_id, block order by record_id,block
        )
  
  select * from context
  </code></pre>
  
  <p>I'm sure there is a better way.</p>