Count an island and its start and end

Question

I have a table that looks like this:

What I would like is to count the islands of data based of the Day_Ranking column, so the output would look like something similar to this:

Person           Start Date           End Date           Count
 45286            2021-08-26           2021-09-07         8
 13914            2021-09-22           2021-10-01         7
 32914            2021-01-10           2021-01-10         1
 32914            2021-01-12           2021-01-13         2

Any suggestions on how I can do this? I am doing this is with Oracle SQL

Answer 1

From Oracle 12, you can use MATCH_RECOGNIZE to perform a row-by-row comparison:

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)
)

Which, for the sample data:

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;

Outputs:

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

db<>fiddle here