WITH t AS ( SELECT 1 seq, 'A' cd, 'X' xy FROM dual UNION ALL SELECT 2, 'B', 'Y' FROM dual UNION ALL SELECT 3, 'A', 'Y' FROM dual UNION ALL SELECT 4, 'C', 'Y' FROM dual UNION ALL SELECT 5, 'D', 'X' FROM dual UNION ALL SELECT 6, 'D', 'X' FROM dual UNION ALL SELECT 7, 'B', 'Y' FROM dual UNION ALL SELECT 8, 'C', 'Y' FROM dual UNION ALL SELECT 9, 'E', 'X' FROM dual UNION ALL SELECT 10, 'E', 'X' FROM dual ) SELECT MAX(gap) max_gap , AVG(gap) avg_gap , COUNT(flag) / COUNT(*) rat_continue FROM (SELECT seq, cd, xy , seq - LAG(seq) OVER(PARTITION BY cd ORDER BY seq) - 1 gap , DECODE(LAG(xy) OVER(ORDER BY seq), xy, 1) flag FROM t ) ;