Hello AskTOM,
this is referring to "KISS series on Analytics: 17 The LAG / LEAD clauses": https://youtu.be/r7AM-1qX7Vs
The testcase for that can be found in https://livesql.oracle.com/apex/livesql/file/content_CZUCT0MCOQZMJM7TI553HC8S9.html

I have sent a tweet to @connor_mc_d while AskTOM was closed for new questions, but got no response there.

The final query in the livesql includes does not meet expectatation. The FROM_DATE and TO_DATE there don't return the correct interval for each status.
I assume that in the base table ORDERS the column STATUS_DATE is getting filled with the timestamp, when the current status actually was recognized in the workflow or order processing. A status then should be existing until its indicated in ORDERS to be replaced first by another new or already previously existing STATUS.

So the first status "New" started 03-JAN-16 and was changed to status "Inventory Check" on 04-JAN-16, which got replaced by "Awaiting Signoff" on 09-JAN-16. This status gets changed on 12-JAN-16 into status "In Warehouse".

Therefore I expect the first three rows of the result to be:

ORDER_ID  STATUS            FROM_DATE       TO_DATE
11700     New               03-JAN-16       04-JAN-16
11700     Inventory Check   04-JAN-16       09-JAN-16
11700     Awaiting Signoff  09-JAN-16       12-JAN-16

Instead however the final query in the livesql returns as first three rows:

ORDER_ID  STATUS            FROM_DATE       TO_DATE
11700     New               -               03-JAN-16
11700     Inventory Check   03-JAN-16       08-JAN-16
11700     Awaiting Signoff  08-JAN-16       11-JAN-16

So I wonder what would be the best query to get the expected and in my point of view correct result also for FROM_DATE and TO_DATE?

Regards,
Bernhard

SQL> select
  2    order_id,
  3    status,
  4    nvl(1+lag(status_date) over (partition by order_id order by status_date),status_date)  from_date,
  5    1+status_date to_date
  6  from (
  7    select
  8      order_id,
  9      status_date,
 10      status,
 11      lag(status) over (partition by order_id order by status_date) lag_status,
 12      lead(status) over (partition by order_id order by status_date) lead_status
 13    from ORDERS
 14    )
 15  where lag_status is null
 16         or lead_status is null
 17         or lead_status <> status
 18  order by 1,3 nulls first;

  ORDER_ID STATUS               FROM_DATE TO_DATE
---------- -------------------- --------- ---------
     11700 New                  03-JAN-16 04-JAN-16
     11700 Inventory Check      04-JAN-16 09-JAN-16
     11700 Awaiting Signoff     09-JAN-16 12-JAN-16
     11700 In Warehouse         12-JAN-16 15-JAN-16
     11700 Awaiting Signoff     15-JAN-16 17-JAN-16
     11700 Payment Pending      17-JAN-16 19-JAN-16
     11700 Awaiting Signoff     19-JAN-16 21-JAN-16
     11700 Delivery             21-JAN-16 23-JAN-16