Oracle SQL - How to contrain related records timewise

Question

Given the below listed data, how to select only records, for which:

a) at least 1 previous ticket for the same client_id exists and

b) the maximum time difference of each predecessing ticket may not exceed 14 days. In other words, if a ticket has a successor as described in a) and this successor has been created >14 days later, it must not be considered.

create table tickets (
ticket_id number,
client_id number,
start_time date);

insert into tickets values (1,1,to_date('201601011330','yyyymmddhh24mi'));
insert into tickets values (2,1,to_date('201601021320','yyyymmddhh24mi'));
insert into tickets values (3,1,to_date('201601101330','yyyymmddhh24mi'));
insert into tickets values (4,1,to_date('201603101330','yyyymmddhh24mi'));
insert into tickets values (5,2,to_date('201601011630','yyyymmddhh24mi'));
insert into tickets values (6,2,to_date('201601201330','yyyymmddhh24mi'));
insert into tickets values (7,3,to_date('201602011330','yyyymmddhh24mi'));
insert into tickets values (8,4,to_date('201602290000','yyyymmddhh24mi'));
insert into tickets values (9,4,to_date('201603011630','yyyymmddhh24mi'));
insert into tickets values (10,4,to_date('201604011120','yyyymmddhh24mi'));
insert into tickets values(11,4,to_date('201604101030','yyyymmddhh24mi'));
commit;

Answer 1

You can do what you want with analytic functions. I think this is the logic:

select t.*
from (select t.*,
             row_number() over (partition by client_id order by start_time) as seqnum,
             lag(start_time) over (partition by client_id order by start_time) as prev_st
      from tickets t
     ) t
where (start_time - prev_st) < 14 and seqnum >= 2;

I realize that I don't know what the "it" refers to in (b) -- the successor the the record in question. As written, the seqnum >= 2 is redundant, because the first record for each client fails the first condition (prev_st is NULL).

If this isn't exactly what you need, then some combination of row_number(), lag(), and lead() would seem correct.

Answer 2

Also possible without analytic functions.

select * from tickets t1
  where exists (
    select 1 from tickets t2
      where t1.client_id = t2.client_id
        and t1.start_time>t2.start_time
        and t1.start_time<=t2.start_time+14
   );