es-stackoverflow-32566, PostgreSQL

Language: Layout:

--PostgreSQL 9.5
--'\\' is a delimiter

create table "ciudades" (
 "city_id" integer,
 "ciudad" character varying(13),
 primary key ("city_id")
);

insert into "ciudades" ("city_id", "ciudad") values
 ( 1, 'BARQUISIMENTO'),
 (25, 'CARACAS'      ),
 (30, 'BOGOTÁ'       );

create table "contratos" (
 "contract_id" integer,
 "employee_id" integer,
 "start_date" character varying(10),
 "end_date" character varying(10),
 primary key ("contract_id")
);

insert into "contratos" ("contract_id", "employee_id", "start_date", "end_date") values
 ( 40, 1234, '2005-06-01', '2009-06-01'),
 ( 45, 1234, '2009-06-01', null        ),
 ( 60, 5678, '2006-03-01', '2010-03-01'),
 (100, 5678, '2010-03-01', null        ),
 (458, 9012, '2015-10-01', '2016-10-01'),
 (500, 9012, '2016-10-01', null        );
 
create table "empleados" (
 "employee_id" integer,
 "nombre" character varying(5),
 "apellido" character varying(8),
 "city_id" integer,
 primary key ("employee_id")
);

insert into "empleados" ("employee_id", "nombre", "apellido", "city_id") values
 (1234, 'José' , 'Pérez'   , 30),
 (5678, 'Juan' , 'González', 25),
 (9012, 'David', 'López'   ,  1);

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;


absolute service time: 0,45 sec