The cleanest query I could think of at this time is this, if there is no previous contract the query should return NULL in that field; you can specify a value DEFAULT for the records not found but it should be of type DATE but you can also use COALESCE and% CAST to change that NULL to other text if necessary.

SELECT DISTINCT EMPLOYEE_ID, CITY_ID, NOMBRE, APELLIDO,
LAG(CONTRACT_ID, 0 ) OVER (PARTITION BY EMPLOYEE_ID ORDER BY contratos.START_DATE ASC),
LAG(CONTRACT_ID, 1 ) OVER (PARTITION BY EMPLOYEE_ID ORDER BY contratos.START_DATE ASC)
FROM empleados
LEFT JOIN ciudades ON empleados.CITY_ID = ciudades.CITY_ID
LEFT JOIN contratos ON empleados.EMPLOYEE_ID = contratos.EMPLOYEE_ID 
ORDER BY contratos.START_DATE

The only drawback is that DISTINCT must be applied to EMPLOYEE_ID and therefore the field does not come out in order you can select the correct order if you surround this query with another query:

SELECT CITY_ID, NOMBRE, (orden correcto)... FROM (...TODO EL QUERY ANTERIOR...) alias

I hope I help you.

Windowing type functions

There are windowing functions to do these tasks: link

The lag function gives you the row above and lead the line below. If you only want to show the last row (with the one above) you have to put one select into another to filter at the end. The example goes so that it is understood:

select * from (
  select empleados.*, ciudades.ciudad, 
        contratos.contract_id, 
        contratos.start_date,
        contratos.end_date,
        lag(contratos.contract_id) over (partition by empleados.employee_id order by contratos.start_date) as contrato_anterior,
        lag(contratos.start_date) over (partition by empleados.employee_id order by contratos.start_date) as contrato_anterior_start_date,
        lag(contratos.end_date) over (partition by empleados.employee_id order by contratos.start_date) as contrato_anterior_end_date
    from contratos 
      inner join empleados on contratos.employee_id=empleados.employee_id
      left join ciudades on ciudades.city_id = empleados.city_id
  ) select_base_con_todo_junto
  where end_date is null;

full version

I put a full version with the insert and create table at: link

I assume that the logic that determines the current and previous contract is the value of the start_date column. If that is the case, using row_number() and conditional aggregations, you can get the information in this way:

with ct as (
  select employee_id,
         max(case when rnk = 1 then contract_id end) as current_contract_id,
         max(case when rnk = 2 then contract_id end) as previous_contract_id
    from (select contract_id,
                 employee_id,
                 row_number() over (partition by employee_id order by start_date desc) as rnk
            from contratos) c
   group by employee_id
)
select c.ciudad,
       e.employee_id,
       e.nombre,
       e.apellido,
       ct.current_contract_id,
       ct.previous_contract_id
  from empleados e
  join ciudades c
    on c.city_id = e.city_id
  join ct
    on ct.employee_id = e.employee_id

The query should work on both Oracle and PostgreSQL.

SELECT CITIES.CITYID, A.EMPLOYEEID, EMPLOYEES.NOMBRE AS NAME, EMPLOYEES.LASTNAME, A.CURRENTCONTRACTID, B.PREVIOUSCONTRACTID
FROM (
  (
    (
      SELECT  EMPLOYEES.EMPLOYEEID, MAX(CONTRACTID) AS CURRENTCONTRACTID FROM 
      EMPLOYEES INNER JOIN CONTRACTS ON EMPLOYEES.EMPLOYEEID = CONTRACTS.EMPLOYEEID
      GROUP BY EMPLOYEES.EMPLOYEEID
      )  AS A 
      LEFT JOIN (SELECT EMPLOYEEID, MAX(PREVIOUSCONTRACTIDG) AS PREVIOUSCONTRACTID FROM
      (
       SELECT C.EMPLOYEEID, C.PREVIOUSCONTRACTIDG FROM
          ( 
            SELECT  EMPLOYEEID, CONTRACTID AS PREVIOUSCONTRACTIDG FROM CONTRACTS ) AS C
          INNER JOIN
          (
            SELECT  EMPLOYEEID, MAX(CONTRACTID) AS PREVIOUSCONTRACTIDG FROM CONTRACTS GROUP BY EMPLOYEEID
           ) AS D
          ON C.EMPLOYEEID = D.EMPLOYEEID 
          WHERE C.PREVIOUSCONTRACTIDG < D.PREVIOUSCONTRACTIDG
          )
          GROUP BY EMPLOYEEID
        )  AS B ON A.EMPLOYEEID = B.EMPLOYEEID
      ) 
   INNER JOIN EMPLOYEES ON A.EMPLOYEEID = EMPLOYEES.EMPLOYEEID
 ) 
INNER JOIN CITIES ON EMPLOYEES.CITYID = CITIES.CITYID

Instead of the 'NAME' field in the 'EMPLOYEE' table, I used 'NAME', so I aliased the field.