alter session set nls_timestamp_format = 'DD-MON-RR HH.MI.SS.FF AM'

select 78328696 pkid, 12848815 customer_id, to_date('10/19/2022 11:05:38 AM','MM/DD/YYYY HH:MI:SS AM') actdate, 0.5 units, to_timestamp('19-OCT-22 11.05.38.947750000 AM') datetime from dual

union all

select 78328697, 12848815, to_date('10/19/2022 11:05:39 AM','MM/DD/YYYY HH:MI:SS AM'), 0.5, to_timestamp('19-OCT-22 11.05.39.024819000 AM') from dual

union all

select 78328698, 12848815, to_date('10/19/2022 11:05:39 AM','MM/DD/YYYY HH:MI:SS AM'), 0.5, to_timestamp('19-OCT-22 11.05.39.050859000 AM') from dual

union all

select 78321196, 12978419, to_date('10/19/2022 9:13:56 AM','MM/DD/YYYY HH:MI:SS AM'), 1, to_timestamp('19-OCT-22 09.13.56.879037000 AM') from dual

union all

select 78321197, 12978419, to_date('10/19/2022 9:13:56 AM','MM/DD/YYYY HH:MI:SS AM'), 1, to_timestamp('19-OCT-22 09.13.56.909837000 AM') from dual

union all

select 78321199, 12978419, to_date('10/19/2022 9:13:56 AM','MM/DD/YYYY HH:MI:SS AM'), 1, to_timestamp('19-OCT-22 09.13.56.931040000 AM') from dual

union all

select 78321200, 12978419, to_date('10/19/2022 9:13:56 AM','MM/DD/YYYY HH:MI:SS AM'), 1, to_timestamp('19-OCT-22 09.13.56.952084000 AM') from dual

union all

select 78321201, 12978419, to_date('10/19/2022 9:13:56 AM','MM/DD/YYYY HH:MI:SS AM'), 1, to_timestamp('19-OCT-22 09.13.56.971703000 AM') from dual

union all

select 78321202, 12978419, to_date('10/19/2022 9:13:56 AM','MM/DD/YYYY HH:MI:SS AM'), 1, to_timestamp('19-OCT-22 09.13.56.993092000 AM') from dual

union all

select 78321203, 12978419, to_date('10/19/2022 9:13:57 AM','MM/DD/YYYY HH:MI:SS AM'), 1, to_timestamp('19-OCT-22 09.13.57.014174000 AM') from dual

union all

select 78330838, 13710675, to_date('10/19/2022 11:44:29 AM','MM/DD/YYYY HH:MI:SS AM'), 0.5, to_timestamp('19-OCT-22 11.44.29.465212000 AM') from dual

union all

select 78330839, 13710675, to_date('10/19/2022 11:44:29 AM','MM/DD/YYYY HH:MI:SS AM'), 0.5, to_timestamp('19-OCT-22 11.44.29.498326000 AM') from dual

union all

select 78330840, 13710675, to_date('10/19/2022 11:44:29 AM','MM/DD/YYYY HH:MI:SS AM'), 0.5, to_timestamp('19-OCT-22 11.44.29.527076000 AM') from dual

union all

select 78331625, 13710675, to_date('10/19/2022 11:56:28 AM','MM/DD/YYYY HH:MI:SS AM'), 0.5, to_timestamp('19-OCT-22 11.56.28.726815000 AM') from dual

customer_id,

trunc(max(actdate)) as accdate,

sum(units) as units

from sampledata

match_recognize (

partition by customer_id

order by actdate

measures match_number() as match_num

all rows per match

pattern (start_tran same_tran*)

define same_tran as (actdate <= prev(actdate) + interval '2' second)

)

group by customer_id, match_num