Главная

Из таблицы purchase_order_header выведите идентификаторы заказов с максимальным интервалом между текущим и предыдущим заказами, которые были созданы одним сотрудником.

SELECT purchase_order_id
FROM (SELECT  purchase_order_id,
              employee_id,
              previous_order,
              MAX(t1.previous_order) OVER () AS max
     FROM (SELECT  purchase_order_id, 
                   employee_id, 
                   order_date - LAG(order_date, 1, NULL) OVER (PARTITION BY employee_id ORDER BY order_date) AS previous_order
           FROM  adventure.purchase_order_header
           ORDER BY employee_id, purchase_order_id) 
           AS t1
     ) AS t2
WHERE previous_order = max
ORDER BY employee_id;