By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE table_name (person, date_value, day_ranking) AS
SELECT 1, DATE '2022-01-01', 6 FROM DUAL UNION ALL
SELECT 1, DATE '2022-01-02', 7 FROM DUAL UNION ALL
SELECT 1, DATE '2022-01-04', 8 FROM DUAL UNION ALL
SELECT 1, DATE '2022-01-05', 9 FROM DUAL UNION ALL
SELECT 1, DATE '2022-01-07', 10 FROM DUAL UNION ALL
SELECT 1, DATE '2022-01-09', 11 FROM DUAL UNION ALL
SELECT 2, DATE '2022-01-01', 15 FROM DUAL UNION ALL
SELECT 2, DATE '2022-01-02', 16 FROM DUAL UNION ALL
SELECT 2, DATE '2022-01-03', 17 FROM DUAL UNION ALL
SELECT 2, DATE '2022-01-04', 18 FROM DUAL UNION ALL
SELECT 2, DATE '2022-01-05', 19 FROM DUAL UNION ALL
SELECT 3, DATE '2022-01-01', 25 FROM DUAL UNION ALL
SELECT 3, DATE '2022-01-03', 27 FROM DUAL;
13 rows affected
SELECT *
FROM table_name
MATCH_RECOGNIZE(
PARTITION BY person
ORDER BY day_ranking
MEASURES
FIRST(date_value) AS start_date,
LAST(date_value) AS end_date,
COUNT(*) AS count
ONE ROW PER MATCH
PATTERN (successive_ranks* last_rank)
DEFINE successive_ranks AS day_ranking + 1 = NEXT(day_ranking)
)
| PERSON | START_DATE | END_DATE | COUNT |
|---|---|---|---|
| 1 | 01-JAN-22 | 09-JAN-22 | 6 |
| 2 | 01-JAN-22 | 05-JAN-22 | 5 |
| 3 | 01-JAN-22 | 01-JAN-22 | 1 |
| 3 | 03-JAN-22 | 03-JAN-22 | 1 |