Analytics - LAG and LEAD functions

View All Scripts Login to Run Script

Script Name Analytics - LAG and LEAD functions
Description SQL from the KISS (Keep It Simply SQL) Analytic video series by Developer Advocate Connor McDonald. This script looks at the lag and lead functions. Run this script standalone, or take it as part of the complete Analytics class at https://tinyurl.com/devgym-classes
Area SQL General / Analytics
Contributor Connor Mcdonald (Oracle)
Created Friday March 11, 2016

Statement 1
```
drop table ORDERS purge
```
```
ORA-00942: table or view does not exist
```
More Details: https://docs.oracle.com/error-help/db/ora-00942

Statement 2

create table ORDERS 
( order_id     int,
  status_date  date,
  status       varchar2(20)
)

Table created.

Statement 3

insert into ORDERS values (11700, date '2016-01-03', 'New')

1 row(s) inserted.

Statement 4

insert into ORDERS values (11700, date '2016-01-04', 'Inventory Check')

1 row(s) inserted.

Statement 5

insert into ORDERS values (11700, date '2016-01-05', 'Inventory Check')

1 row(s) inserted.

Statement 6

insert into ORDERS values (11700, date '2016-01-06', 'Inventory Check')

1 row(s) inserted.

Statement 7

insert into ORDERS values (11700, date '2016-01-07', 'Inventory Check')

1 row(s) inserted.

Statement 8

insert into ORDERS values (11700, date '2016-01-08', 'Inventory Check')

1 row(s) inserted.

Statement 9

insert into ORDERS values (11700, date '2016-01-09', 'Awaiting Signoff')

1 row(s) inserted.

Statement 10

insert into ORDERS values (11700, date '2016-01-10', 'Awaiting Signoff')

1 row(s) inserted.

Statement 11

insert into ORDERS values (11700, date '2016-01-11', 'Awaiting Signoff')

1 row(s) inserted.

Statement 12

insert into ORDERS values (11700, date '2016-01-12', 'In Warehouse')

1 row(s) inserted.

Statement 13

insert into ORDERS values (11700, date '2016-01-13', 'In Warehouse')

1 row(s) inserted.

Statement 14

insert into ORDERS values (11700, date '2016-01-14', 'In Warehouse')

1 row(s) inserted.

Statement 15

insert into ORDERS values (11700, date '2016-01-15', 'Awaiting Signoff')

1 row(s) inserted.

Statement 16

insert into ORDERS values (11700, date '2016-01-16', 'Awaiting Signoff')

1 row(s) inserted.

Statement 17

insert into ORDERS values (11700, date '2016-01-17', 'Payment Pending')

1 row(s) inserted.

Statement 18

insert into ORDERS values (11700, date '2016-01-18', 'Payment Pending')

1 row(s) inserted.

Statement 19

insert into ORDERS values (11700, date '2016-01-19', 'Awaiting Signoff')

1 row(s) inserted.

Statement 20

insert into ORDERS values (11700, date '2016-01-20', 'Awaiting Signoff')

1 row(s) inserted.

Statement 21

insert into ORDERS values (11700, date '2016-01-21', 'Delivery')

1 row(s) inserted.

Statement 22

insert into ORDERS values (11700, date '2016-01-22', 'Delivery')

1 row(s) inserted.

Statement 23
```
commit
```
```
Statement processed.
```

Statement 24

select * from orders order by 1,2

 
ORDER_ID STATUS_DATE STATUS
11700 03-JAN-16 New
11700 04-JAN-16 Inventory Check
11700 05-JAN-16 Inventory Check
11700 06-JAN-16 Inventory Check
11700 07-JAN-16 Inventory Check
11700 08-JAN-16 Inventory Check
11700 09-JAN-16 Awaiting Signoff
11700 10-JAN-16 Awaiting Signoff
11700 11-JAN-16 Awaiting Signoff
11700 12-JAN-16 In Warehouse
11700 13-JAN-16 In Warehouse
11700 14-JAN-16 In Warehouse
11700 15-JAN-16 Awaiting Signoff
11700 16-JAN-16 Awaiting Signoff
11700 17-JAN-16 Payment Pending
11700 18-JAN-16 Payment Pending
11700 19-JAN-16 Awaiting Signoff
11700 20-JAN-16 Awaiting Signoff
11700 21-JAN-16 Delivery
11700 22-JAN-16 Delivery

20 rows selected.

ORDER_ID	STATUS_DATE	STATUS
11700	03-JAN-16	New
11700	04-JAN-16	Inventory Check
11700	05-JAN-16	Inventory Check
11700	06-JAN-16	Inventory Check
11700	07-JAN-16	Inventory Check
11700	08-JAN-16	Inventory Check
11700	09-JAN-16	Awaiting Signoff
11700	10-JAN-16	Awaiting Signoff
11700	11-JAN-16	Awaiting Signoff
11700	12-JAN-16	In Warehouse
11700	13-JAN-16	In Warehouse
11700	14-JAN-16	In Warehouse
11700	15-JAN-16	Awaiting Signoff
11700	16-JAN-16	Awaiting Signoff
11700	17-JAN-16	Payment Pending
11700	18-JAN-16	Payment Pending
11700	19-JAN-16	Awaiting Signoff
11700	20-JAN-16	Awaiting Signoff
11700	21-JAN-16	Delivery
11700	22-JAN-16	Delivery

Statement 25

select status, min(status_date) from_date, max(status_date) to_date
from orders
group by status
order by 2

 
STATUS FROM_DATE TO_DATE
New 03-JAN-16 03-JAN-16
Inventory Check 04-JAN-16 08-JAN-16
Awaiting Signoff 09-JAN-16 20-JAN-16
In Warehouse 12-JAN-16 14-JAN-16
Payment Pending 17-JAN-16 18-JAN-16
Delivery 21-JAN-16 22-JAN-16

6 rows selected.

STATUS	FROM_DATE	TO_DATE
New	03-JAN-16	03-JAN-16
Inventory Check	04-JAN-16	08-JAN-16
Awaiting Signoff	09-JAN-16	20-JAN-16
In Warehouse	12-JAN-16	14-JAN-16
Payment Pending	17-JAN-16	18-JAN-16
Delivery	21-JAN-16	22-JAN-16

Statement 26

select   
  order_id,
  status_date,
  status,
  lag(status,1) over 
    (partition by order_id order by status_date) lag_status
from ORDERS
order by 1,2

 
ORDER_ID STATUS_DATE STATUS LAG_STATUS
11700 03-JAN-16 New  - 
11700 04-JAN-16 Inventory Check New
11700 05-JAN-16 Inventory Check Inventory Check
11700 06-JAN-16 Inventory Check Inventory Check
11700 07-JAN-16 Inventory Check Inventory Check
11700 08-JAN-16 Inventory Check Inventory Check
11700 09-JAN-16 Awaiting Signoff Inventory Check
11700 10-JAN-16 Awaiting Signoff Awaiting Signoff
11700 11-JAN-16 Awaiting Signoff Awaiting Signoff
11700 12-JAN-16 In Warehouse Awaiting Signoff
11700 13-JAN-16 In Warehouse In Warehouse
11700 14-JAN-16 In Warehouse In Warehouse
11700 15-JAN-16 Awaiting Signoff In Warehouse
11700 16-JAN-16 Awaiting Signoff Awaiting Signoff
11700 17-JAN-16 Payment Pending Awaiting Signoff
11700 18-JAN-16 Payment Pending Payment Pending
11700 19-JAN-16 Awaiting Signoff Payment Pending
11700 20-JAN-16 Awaiting Signoff Awaiting Signoff
11700 21-JAN-16 Delivery Awaiting Signoff
11700 22-JAN-16 Delivery Delivery

20 rows selected.

ORDER_ID	STATUS_DATE	STATUS	LAG_STATUS
11700	03-JAN-16	New	-
11700	04-JAN-16	Inventory Check	New
11700	05-JAN-16	Inventory Check	Inventory Check
11700	06-JAN-16	Inventory Check	Inventory Check
11700	07-JAN-16	Inventory Check	Inventory Check
11700	08-JAN-16	Inventory Check	Inventory Check
11700	09-JAN-16	Awaiting Signoff	Inventory Check
11700	10-JAN-16	Awaiting Signoff	Awaiting Signoff
11700	11-JAN-16	Awaiting Signoff	Awaiting Signoff
11700	12-JAN-16	In Warehouse	Awaiting Signoff
11700	13-JAN-16	In Warehouse	In Warehouse
11700	14-JAN-16	In Warehouse	In Warehouse
11700	15-JAN-16	Awaiting Signoff	In Warehouse
11700	16-JAN-16	Awaiting Signoff	Awaiting Signoff
11700	17-JAN-16	Payment Pending	Awaiting Signoff
11700	18-JAN-16	Payment Pending	Payment Pending
11700	19-JAN-16	Awaiting Signoff	Payment Pending
11700	20-JAN-16	Awaiting Signoff	Awaiting Signoff
11700	21-JAN-16	Delivery	Awaiting Signoff
11700	22-JAN-16	Delivery	Delivery

Statement 27

select   
  order_id,
  status_date,
  status,
  lag(status,1) over (partition by order_id order by status_date) lag_status,
  lead(status,1) over (partition by order_id order by status_date) lead_status
from ORDERS
order by 1,2

 
ORDER_ID STATUS_DATE STATUS LAG_STATUS LEAD_STATUS
11700 03-JAN-16 New  - Inventory Check
11700 04-JAN-16 Inventory Check New Inventory Check
11700 05-JAN-16 Inventory Check Inventory Check Inventory Check
11700 06-JAN-16 Inventory Check Inventory Check Inventory Check
11700 07-JAN-16 Inventory Check Inventory Check Inventory Check
11700 08-JAN-16 Inventory Check Inventory Check Awaiting Signoff
11700 09-JAN-16 Awaiting Signoff Inventory Check Awaiting Signoff
11700 10-JAN-16 Awaiting Signoff Awaiting Signoff Awaiting Signoff
11700 11-JAN-16 Awaiting Signoff Awaiting Signoff In Warehouse
11700 12-JAN-16 In Warehouse Awaiting Signoff In Warehouse
11700 13-JAN-16 In Warehouse In Warehouse In Warehouse
11700 14-JAN-16 In Warehouse In Warehouse Awaiting Signoff
11700 15-JAN-16 Awaiting Signoff In Warehouse Awaiting Signoff
11700 16-JAN-16 Awaiting Signoff Awaiting Signoff Payment Pending
11700 17-JAN-16 Payment Pending Awaiting Signoff Payment Pending
11700 18-JAN-16 Payment Pending Payment Pending Awaiting Signoff
11700 19-JAN-16 Awaiting Signoff Payment Pending Awaiting Signoff
11700 20-JAN-16 Awaiting Signoff Awaiting Signoff Delivery
11700 21-JAN-16 Delivery Awaiting Signoff Delivery
11700 22-JAN-16 Delivery Delivery  - 

20 rows selected.

ORDER_ID	STATUS_DATE	STATUS	LAG_STATUS	LEAD_STATUS
11700	03-JAN-16	New	-	Inventory Check
11700	04-JAN-16	Inventory Check	New	Inventory Check
11700	05-JAN-16	Inventory Check	Inventory Check	Inventory Check
11700	06-JAN-16	Inventory Check	Inventory Check	Inventory Check
11700	07-JAN-16	Inventory Check	Inventory Check	Inventory Check
11700	08-JAN-16	Inventory Check	Inventory Check	Awaiting Signoff
11700	09-JAN-16	Awaiting Signoff	Inventory Check	Awaiting Signoff
11700	10-JAN-16	Awaiting Signoff	Awaiting Signoff	Awaiting Signoff
11700	11-JAN-16	Awaiting Signoff	Awaiting Signoff	In Warehouse
11700	12-JAN-16	In Warehouse	Awaiting Signoff	In Warehouse
11700	13-JAN-16	In Warehouse	In Warehouse	In Warehouse
11700	14-JAN-16	In Warehouse	In Warehouse	Awaiting Signoff
11700	15-JAN-16	Awaiting Signoff	In Warehouse	Awaiting Signoff
11700	16-JAN-16	Awaiting Signoff	Awaiting Signoff	Payment Pending
11700	17-JAN-16	Payment Pending	Awaiting Signoff	Payment Pending
11700	18-JAN-16	Payment Pending	Payment Pending	Awaiting Signoff
11700	19-JAN-16	Awaiting Signoff	Payment Pending	Awaiting Signoff
11700	20-JAN-16	Awaiting Signoff	Awaiting Signoff	Delivery
11700	21-JAN-16	Delivery	Awaiting Signoff	Delivery
11700	22-JAN-16	Delivery	Delivery	-

Statement 28

select   order_id,
         status_date,
         status,
         lag(status) over (partition by order_id order by status_date) lag_status,
         lead(status) over (partition by order_id order by status_date) lead_status,
         lag(status_date) over (partition by order_id order by status_date) lag_status_date,
         lead(status_date) over (partition by order_id order by status_date) lead_status_date
from ORDERS
order by 1,2

 
ORDER_ID STATUS_DATE STATUS LAG_STATUS LEAD_STATUS LAG_STATUS_DATE LEAD_STATUS_DATE
11700 03-JAN-16 New  - Inventory Check  - 04-JAN-16
11700 04-JAN-16 Inventory Check New Inventory Check 03-JAN-16 05-JAN-16
11700 05-JAN-16 Inventory Check Inventory Check Inventory Check 04-JAN-16 06-JAN-16
11700 06-JAN-16 Inventory Check Inventory Check Inventory Check 05-JAN-16 07-JAN-16
11700 07-JAN-16 Inventory Check Inventory Check Inventory Check 06-JAN-16 08-JAN-16
11700 08-JAN-16 Inventory Check Inventory Check Awaiting Signoff 07-JAN-16 09-JAN-16
11700 09-JAN-16 Awaiting Signoff Inventory Check Awaiting Signoff 08-JAN-16 10-JAN-16
11700 10-JAN-16 Awaiting Signoff Awaiting Signoff Awaiting Signoff 09-JAN-16 11-JAN-16
11700 11-JAN-16 Awaiting Signoff Awaiting Signoff In Warehouse 10-JAN-16 12-JAN-16
11700 12-JAN-16 In Warehouse Awaiting Signoff In Warehouse 11-JAN-16 13-JAN-16
11700 13-JAN-16 In Warehouse In Warehouse In Warehouse 12-JAN-16 14-JAN-16
11700 14-JAN-16 In Warehouse In Warehouse Awaiting Signoff 13-JAN-16 15-JAN-16
11700 15-JAN-16 Awaiting Signoff In Warehouse Awaiting Signoff 14-JAN-16 16-JAN-16
11700 16-JAN-16 Awaiting Signoff Awaiting Signoff Payment Pending 15-JAN-16 17-JAN-16
11700 17-JAN-16 Payment Pending Awaiting Signoff Payment Pending 16-JAN-16 18-JAN-16
11700 18-JAN-16 Payment Pending Payment Pending Awaiting Signoff 17-JAN-16 19-JAN-16
11700 19-JAN-16 Awaiting Signoff Payment Pending Awaiting Signoff 18-JAN-16 20-JAN-16
11700 20-JAN-16 Awaiting Signoff Awaiting Signoff Delivery 19-JAN-16 21-JAN-16
11700 21-JAN-16 Delivery Awaiting Signoff Delivery 20-JAN-16 22-JAN-16
11700 22-JAN-16 Delivery Delivery  - 21-JAN-16  - 

20 rows selected.

ORDER_ID	STATUS_DATE	STATUS	LAG_STATUS	LEAD_STATUS	LAG_STATUS_DATE	LEAD_STATUS_DATE
11700	03-JAN-16	New	-	Inventory Check	-	04-JAN-16
11700	04-JAN-16	Inventory Check	New	Inventory Check	03-JAN-16	05-JAN-16
11700	05-JAN-16	Inventory Check	Inventory Check	Inventory Check	04-JAN-16	06-JAN-16
11700	06-JAN-16	Inventory Check	Inventory Check	Inventory Check	05-JAN-16	07-JAN-16
11700	07-JAN-16	Inventory Check	Inventory Check	Inventory Check	06-JAN-16	08-JAN-16
11700	08-JAN-16	Inventory Check	Inventory Check	Awaiting Signoff	07-JAN-16	09-JAN-16
11700	09-JAN-16	Awaiting Signoff	Inventory Check	Awaiting Signoff	08-JAN-16	10-JAN-16
11700	10-JAN-16	Awaiting Signoff	Awaiting Signoff	Awaiting Signoff	09-JAN-16	11-JAN-16
11700	11-JAN-16	Awaiting Signoff	Awaiting Signoff	In Warehouse	10-JAN-16	12-JAN-16
11700	12-JAN-16	In Warehouse	Awaiting Signoff	In Warehouse	11-JAN-16	13-JAN-16
11700	13-JAN-16	In Warehouse	In Warehouse	In Warehouse	12-JAN-16	14-JAN-16
11700	14-JAN-16	In Warehouse	In Warehouse	Awaiting Signoff	13-JAN-16	15-JAN-16
11700	15-JAN-16	Awaiting Signoff	In Warehouse	Awaiting Signoff	14-JAN-16	16-JAN-16
11700	16-JAN-16	Awaiting Signoff	Awaiting Signoff	Payment Pending	15-JAN-16	17-JAN-16
11700	17-JAN-16	Payment Pending	Awaiting Signoff	Payment Pending	16-JAN-16	18-JAN-16
11700	18-JAN-16	Payment Pending	Payment Pending	Awaiting Signoff	17-JAN-16	19-JAN-16
11700	19-JAN-16	Awaiting Signoff	Payment Pending	Awaiting Signoff	18-JAN-16	20-JAN-16
11700	20-JAN-16	Awaiting Signoff	Awaiting Signoff	Delivery	19-JAN-16	21-JAN-16
11700	21-JAN-16	Delivery	Awaiting Signoff	Delivery	20-JAN-16	22-JAN-16
11700	22-JAN-16	Delivery	Delivery	-	21-JAN-16	-

Statement 29

select 
  order_id, 
  status, 
  lag(status_date) over (partition by order_id order by status_date)  from_date,
  status_date to_date
from (
  select 
    order_id,
    status_date,
    status,
    lag(status) over (partition by order_id order by status_date) lag_status,
    lead(status) over (partition by order_id order by status_date) lead_status
  from ORDERS
  )
where lag_status is null
       or lead_status is null
       or lead_status <> status
order by 1,3 nulls first

 
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
11700 In Warehouse 11-JAN-16 14-JAN-16
11700 Awaiting Signoff 14-JAN-16 16-JAN-16
11700 Payment Pending 16-JAN-16 18-JAN-16
11700 Awaiting Signoff 18-JAN-16 20-JAN-16
11700 Delivery 20-JAN-16 22-JAN-16

8 rows selected.

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
11700	In Warehouse	11-JAN-16	14-JAN-16
11700	Awaiting Signoff	14-JAN-16	16-JAN-16
11700	Payment Pending	16-JAN-16	18-JAN-16
11700	Awaiting Signoff	18-JAN-16	20-JAN-16
11700	Delivery	20-JAN-16	22-JAN-16

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database