How to find rows between two dates per row

I have the following table:

topic_id conversation      logical_date         start_date        end_date        type
1            1           2020-01-01 09:00    2020-01-01 09:00  2020-01-01 09:50 phone call
1            2           2020-01-01 09:14                                         text
1            3           2020-01-01 10:27                                         text
2            1           2020-02-03 08:40                                         text

This table represents support requests. Each support request has topic and topic has 1 or more conversations.

I want to find all text requests that were being done between start_date and end_date of phone call.

So for the above table I want to see:

topic_id conversation_id start_date            end_date        sum
 1            1           2020-01-01 09:00 2020-01-01 09:50     1

The logic is: For each topic_id, type='phone call' take start_date and end_date compare them to type='text' conversations from this topic_id sum those who their logical_date between start_date and end_date

I know i need to do this with window function but I'm not sure how.

This is what I have so far:

select topic_id, conversation_id, start_date, end_date, count(1 ) over partition by () 
from table
where type = 'phone call'

I'm using Presto

Solution

I think you want:

select t.*
from t
where t.type = 'text' and
      exists (select 1
              from t t2
              where t2.conversation_id = t.conversation_id and
                    t.logical_date between t2.start_date and t2.end_date and
                    t2.type = 'phone'
             );

If you actually want information from both records, use join:

select tt.*, tp.*
from t tt join
     t tp
     on tp.conversation_id = tt.conversation_id and
        tt.logical_date between tp.start_date and tp.end_date and
        tp.type = 'text' and
        tp.type = 'phone';