Group by rows which are in sequence

Question

Consider I have a table like this

PASSENGER  CITY      DATE
43         NEW YORK  1-Jan-21
44         CHICAGO   4-Jan-21
43         NEW YORK  2-Jan-21
43         NEW YORK  3-Jan-21
44         ROME      5-Jan-21
43         LONDON    4-Jan-21
44         CHICAGO   6-Jan-21
44         CHICAGO   7-Jan-21

How would I group Passenger and City column in sequence to get a result like below?

PASSENGER  CITY      COUNT
43         NEW YORK  3
44         CHICAGO   1
44         ROME      1
43         LONDON    1
44         CHICAGO   2

Answer 1

One way to deal with such a gaps-and-islands problem is to calculate a ranking for the gaps.

Then group also on that ranking.

SELECT PASSENGER, CITY
, COUNT(*) AS "Count" 
-- , MIN("DATE") AS StartDate
-- , MAX("DATE") AS EndDate
FROM (
  SELECT q1.*
  , SUM(gap) OVER (PARTITION BY PASSENGER ORDER BY "DATE") as Rnk
  FROM (
    SELECT PASSENGER, CITY, "DATE"
    , CASE
      WHEN 1 = TRUNC("DATE")
             - TRUNC(LAG("DATE") 
                     OVER (PARTITION BY PASSENGER, CITY ORDER BY "DATE")) 
      THEN 0 ELSE 1 END as gap
    FROM table_name t
  ) q1
) q2
GROUP BY PASSENGER, CITY, Rnk
ORDER BY MIN("DATE"), PASSENGER

PASSENGER	CITY	Count
43	NEW YORK	3
43	LONDON	1
44	CHICAGO	1
44	ROME	1
44	CHICAGO	2

db<>fiddle here