SOE weekly data quality

update_minimum_transit_times.sql

WITH sea_legs AS (
  SELECT
    v.class AS vessel_class,
    pc.id AS arrival_port_call_id,
    pc.un_locode AS arrival_port,
    CASE WHEN pc.eta_is_actual THEN pc.eta ELSE NULL END actual_arrival_time,
    LAG(pc.id) OVER w AS departure_port_call_id,
    LAG(pc.un_locode) OVER w AS departure_port,
    CASE WHEN LAG(pc.etd_is_actual) OVER w THEN LAG(pc.etd) OVER w ELSE NULL END actual_departure_time
  FROM port_calls AS pc
  INNER JOIN vessels AS v ON v.id = pc.vessel_id
  WHERE pc.is_deleted = FALSE
    AND pc.id = pc.root_port_call_id
    AND pc.eta <> pc.etd
    AND v.class IS NOT NULL
  WINDOW w AS (PARTITION BY pc.vessel_id ORDER BY pc.eta ASC)
  ORDER BY 1, 3
)
INSERT INTO minimum_transit_times (departure_port, arrival_port, vessel_class, duration)
(
  SELECT
    departure_port,
    arrival_port,
    vessel_class,
    MIN(actual_arrival_time - actual_departure_time) min_transit_time
  FROM sea_legs
  WHERE
    actual_arrival_time IS NOT NULL
    AND actual_departure_time IS NOT NULL
    AND departure_port != arrival_port
    AND actual_arrival_time - actual_departure_time > INTERVAL '1 hour'
  GROUP BY departure_port, arrival_port, vessel_class
)
ON CONFLICT (departure_port, arrival_port, vessel_class)
DO UPDATE SET duration = EXCLUDED.duration
RETURNING departure_port, arrival_port, vessel_class, duration

upsert_MSC_autofill_terminals.sql

WITH port_to_unique_terminal_mapping AS (
  SELECT * FROM (VALUES 
    ('DOCAU', 'DP WORLD TERMINALS CAUCEDO'),
    ('EGDAM', 'DAMIETTA CONTAINER AND CARGO'),
    ('GBLGP', 'LONDON GATEWAY'),
    ('GBSOU', 'SOUTHAMPTON CONTAINER TERMINAL LTD'),
    ('BRRIO', 'MULTI-RIO OPERACOES PORTUARIAS S.A'),
    ('CAVAN', 'GCT CANADA LIMITED PARTNERSHIP-DEL'),
    ('JMKIN', 'KINGSTON FREEPORT TERMINAL LIMITED'),
    ('MTMAR', 'MALTA FREEPORT TERMINAL LIMITED'),
    ('GRPIR', 'PIRAEUS CONTAINER TERMINAL SA'),
    ('REPDG', 'POINTE DES GALETS TERMINAL')
  ) AS t(un_locode, terminal_name)
), services_to_update AS (
  SELECT
    s.id,
    s.full_name,
    p.un_locode
  FROM port_calls AS pc
  INNER JOIN vessels AS v ON v.id = pc.vessel_id
  INNER JOIN vessels_import_map AS vim ON vim.vessel_id = v.id
  INNER JOIN data_sources AS ds ON ds.id = vim.data_source_id
  INNER JOIN services AS s ON s.id = pc.service_id
  INNER JOIN operators AS op ON op.id = s.operator_id
  INNER JOIN port_to_unique_terminal_mapping AS p ON p.un_locode = pc.un_locode 
  WHERE ds.name = 'msc'
    AND op.name = 'msc'
    AND vim.data_source_authoritative = TRUE
    AND pc.terminal_id IS NULL
    AND pc.etd > NOW()
    AND pc.is_deleted = FALSE
    AND pc.scenario_id = 'root'
  GROUP BY 1,2,3
  ORDER BY 1,2,3
), services_already_setup AS (
  SELECT s.id, spc.un_locode, s.full_name
  FROM service_port_calls AS spc
  INNER JOIN services AS s ON s.id = spc.service_id
  INNER JOIN operators AS o ON o.id = s.operator_id
  INNER JOIN terminals AS t ON t.id = spc.terminal_id
  WHERE o.name = 'msc'
  GROUP BY 1, 2, 3
), inserted_service_port_calls AS (
  INSERT INTO service_port_calls (service_id, un_locode, terminal_id) (
    SELECT 
      s.id,
      s.un_locode,
      t.id
    FROM services_to_update AS s
    INNER JOIN port_to_unique_terminal_mapping AS p ON p.un_locode = s.un_locode
    INNER JOIN terminals AS t ON t.un_locode = p.un_locode AND t.name = p.terminal_name
    LEFT JOIN services_already_setup AS sas ON sas.id = s.id AND sas.un_locode = t.un_locode
    WHERE sas IS NULL
  ) 
  RETURNING service_id, un_locode, terminal_id
) 
SELECT 
  s.id,
  s.short_name,
  s.full_name,
  o.name,
  t.un_locode,
  t.name
FROM inserted_service_port_calls AS spc
INNER JOIN services AS s ON s.id = spc.service_id
INNER JOIN operators AS o ON o.id = s.operator_id
INNER JOIN terminals AS t ON t.id = spc.terminal_id;