Oloompa

-- This query counts the number of distinct navigations for each vessel (identified by IMO)
-- A navigation is defined as a movement from one port to another (different UN/LOCODE)
-- The query uses window functions to pair departure and arrival events for each vessel
-- and filters out movements that do not involve a change in port.
SELECT subquery.imo, COUNT(*) as navigation_count
FROM
  (
    SELECT
      p.imo,
      p.id as departure_port_event_id,