Skip to content

Instantly share code, notes, and snippets.

@nherment
Last active March 5, 2020 13:15
Show Gist options
  • Save nherment/1e1dc8d115d89eb2e4e57ae654e909a3 to your computer and use it in GitHub Desktop.
Save nherment/1e1dc8d115d89eb2e4e57ae654e909a3 to your computer and use it in GitHub Desktop.
SOE weekly data quality
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
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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment