fixed an extra case, PostgreSQL

Language: Layout:

--PostgreSQL 9.6
--'\\' is a delimiter

create table ticket_thread(
  thread_id   serial    primary key,
  ticket_id   int       not null, -- references ...
  thread_type text      not null, -- or enum ...
  post_time   timestamp not null, -- or timestamp with time zone
  message     text
);

insert into ticket_thread(ticket_id, thread_type, post_time, message)
values (12, 'c2s', 'today 12:20:20', 'first customer post'),
       (13, 'c2s', 'today 12:20:20', 'new customer question'),
       (12, 'c2s', 'today 12:21:20', 'additional info added to first post, should not appear'),
       (13, 's2c', 'today 12:20:30', 'support responding to new customer question'),
       (12, 's2s', 'today 12:22:00', 'support internal discussion'),
       (12, 's2s', 'today 12:27:00', 'support internal discussion'),
       (12, 's2c', 'today 12:30:20', 'support first response to customer'),
       (12, 's2c', 'today 12:35:20', 'additional info added to first response, should not appear'),
       (12, 'c2s', 'today 12:40:00', 'customer second post'),
       (12, 'c2s', 'today 12:45:00', 'additional info added to second post'),
       (12, 's2s', 'today 12:50:00', 'support internal discussion'),
       (12, 's2c', 'today 12:55:30', 'support second reponse to customer'),
       (12, 'c2s', 'today 13:10:20', 'customer third response'),
       (12, 's2s', 'today 13:15:00', 'support internal discussion');

-- with self-join

select  t.*, row_number() over (partition by t.ticket_id order by t.c2s_time) rank
from    (select    distinct on (coalesce(s2c.thread_id, c2s.thread_id))
                   c2s.ticket_id,
                   c2s.post_time c2s_time,
                   c2s.message c2s_message,
                   s2c.post_time s2c_time,
                   s2c.message s2c_message,
                   s2c.post_time - c2s.post_time time_taken
         from      ticket_thread c2s
         left join ticket_thread s2c on  c2s.ticket_id = s2c.ticket_id
                                     and s2c.thread_type = 's2c'
                                     and c2s.post_time < s2c.post_time
                                     and not exists(select 1
                                                    from   ticket_thread
                                                    where  post_time > c2s.post_time
                                                    and    post_time < s2c.post_time
                                                    and    ticket_id = c2s.ticket_id
                                                    and    thread_type = 's2c')
         where     c2s.thread_type = 'c2s'
         order by  coalesce(s2c.thread_id, c2s.thread_id), c2s.post_time) t
order by t.ticket_id, t.c2s_time;

-- with window functions

select  t.*, row_number() over (partition by t.ticket_id order by t.c2s_time) rank
from    (select    distinct on (coalesce((m).thread_id, (t).thread_id))
                   (t).ticket_id,
                   (t).post_time c2s_time,
                   (t).message c2s_message,
                   (m).post_time s2c_time,
                   (m).message s2c_message,
                   (m).post_time - (t).post_time time_taken
         from      (select t, array_agg(t) filter (where thread_type = 's2c')
                                             over (partition by ticket_id
                                                   order by     post_time
                                                   rows between current row and unbounded following) a
                    from   ticket_thread t) t
         left join lateral  (select   m
                             from     unnest(a) m
                             order by (m).post_time
                             limit    1) m on true
         where     (t).thread_type = 'c2s'
         order by  coalesce((m).thread_id, (t).thread_id), (t).post_time) t
order by t.ticket_id, t.c2s_time;

-- using custom aggregate

create or replace function first_agg_val(anyelement, anyelement)
  returns anyelement
  language sql
  immutable
  strict
  as 'select $1';

create aggregate first_agg(
  sfunc    = first_agg_val,
  basetype = anyelement,
  stype    = anyelement
);

select  t.*, row_number() over (partition by t.ticket_id order by t.c2s_time) rank
from    (select    distinct on (coalesce((s2c).thread_id, (c2s).thread_id))
                   (c2s).ticket_id,
                   (c2s).post_time c2s_time,
                   (c2s).message c2s_message,
                   (s2c).post_time s2c_time,
                   (s2c).message s2c_message,
                   (s2c).post_time - (c2s).post_time time_taken
         from      (select t c2s, first_agg(t) filter (where thread_type = 's2c')
                                                 over (partition by ticket_id
                                                       order by     post_time
                                                       rows between current row and unbounded following) s2c
                    from   ticket_thread t) t
         where     (c2s).thread_type = 'c2s'
         order by  coalesce((s2c).thread_id, (c2s).thread_id), (c2s).post_time) t
order by t.ticket_id, t.c2s_time;


absolute service time: 0,46 sec