create table records ( seq number(5) , payload varchar2(200) ) / insert into records values (1, 'red'); insert into records values (2, 'blue'); insert into records values (3, 'yellow'); insert into records values (4, 'red'); insert into records values (5, 'white'); insert into records values (6, 'blue'); insert into records values (7, 'red'); insert into records values (8, 'yellow'); insert into records values (9, 'blue'); insert into records values (29, 'red'); insert into records values (11, 'white'); insert into records values (12, 'red'); insert into records values (13, 'blue'); insert into records values (14, 'blue'); insert into records values (15, 'green'); insert into records values (16, 'blue'); insert into records values (17, 'yellow'); insert into records values (18, 'red'); insert into records values (19, 'yellow'); insert into records values (20, 'red'); insert into records values (10, 'blue'); insert into records values (119, 'white'); insert into records values (120, 'blue'); insert into records values (121, 'red'); -- distinct 3-step sequences with sequences as ( select seq , payload ||'|'||lead(payload) over (order by seq) ||'|'||lead(payload,2) over (order by seq) color_sequence , row_number() over (order by seq desc) rows_to_come from records ) select distinct color_sequence from sequences where rows_to_come > 2 / -- frequency of occurrence of the color_sequences with sequences as ( select seq , payload ||'|'||lead(payload) over (order by seq) ||'|'||lead(payload,2) over (order by seq) color_sequence , row_number() over (order by seq desc) rows_to_come from records ) select color_sequence , count(seq) occurrence_count from sequences where rows_to_come > 2 group by color_sequence order by occurrence_count desc -- top 3 with sequences as ( select seq , payload ||'|'||lead(payload) over (order by seq) ||'|'||lead(payload,2) over (order by seq) color_sequence , row_number() over (order by seq desc) rows_to_come from records ) select * from ( select color_sequence , count(seq) occurrence_count from sequences where rows_to_come > 2 group by color_sequence order by occurrence_count desc ) where rownum < 4 -- all possible combinations that could have happened given the color available in the records table with colors as (select distinct payload color from records ) select c1.color||'|'||c2.color||'|'||c3.color from colors c1 cross join colors c2 cross join colors c3 / -- list of combinations that could have been there - but are not there at all