LagInSql.txt

create table FOOLAG (idt int, dat varchar(7), VL NUMBER)

INSERT INTO FOOLAG VALUES (1	,'2018-12',1)

INSERT INTO FOOLAG VALUES (1	,'2019-01',2)

INSERT INTO FOOLAG VALUES (1	,'2019-02',3)

INSERT INTO FOOLAG VALUES (2	,'2018-12',4)

INSERT INTO FOOLAG VALUES (2	,'2019-03',5)

INSERT INTO FOOLAG VALUES (2	,'2019-04',6)

INSERT INTO FOOLAG VALUES (3	,'2019-04',7)

SELECT * from FOOLAG

CREATE TABLE FOOLAG_dt AS
	SELECT 
		IDT, DAT, TO_DATE(dat,'YYYY-MM') AS DAT_AS_DATE 
	FROM FOOLAG

SELECT idt, dat, dat, LAG(dat,2) OVER(PARTITION BY IDT ORDER BY DAT_AS_DATE) AS RECORR FROM FOOLAG_dt

CREATE TABLE FOOLAG_recorrencia_3m_base AS
	SELECT 
		idt, dat, 
		MONTHS_BETWEEN (TO_DATE(dat,'YYYY-MM') , TO_DATE(LAG(dat,2) OVER(PARTITION BY IDT ORDER BY DAT_AS_DATE),'YYYY-MM')) AS RECORR  
	FROM FOOLAG_dt


select * from FOOLAG_recorrencia_3m_base WHERE recorr = 2

INSERT INTO FOOLAG 
SELECT 99	,'2018-01',8 FROM dual
UNION 
SELECT 99	,'2018-02',9 FROM dual
UNION 
SELECT 99	,'2018-03',10 FROM dual
UNION 
SELECT 99	,'2018-04',11 FROM dual
UNION 
SELECT 99	,'2018-05',12 FROM dual
UNION 
SELECT 99	,'2018-06',13 FROM dual
UNION 
SELECT 99	,'2018-07',14 FROM dual
UNION 
SELECT 99	,'2018-08',15 FROM dual
UNION 
SELECT 99	,'2018-09',16 FROM dual
UNION 
SELECT 99	,'2018-10',17 FROM dual
UNION 
SELECT 99	,'2018-12',18 FROM dual

select * from FOOLAG ORDER BY dat

select 
idt, 
dat,
vl,
max(vl) OVER (PARTITION BY idt ORDER BY dat ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) MAX_BEFORE, 
max(vl) OVER (PARTITION BY idt ORDER BY dat ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING) MAX_AFTER 
from FOOLAG
ORDER BY IDT, DAT

SELECT --to understand the results from this query, remember this is selecting ordered by dat *without* ordering by idt  
idt, 
dat,
vl,
max(vl) OVER (ORDER BY dat ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) MAX_BEFORE, 
max(vl) OVER (ORDER BY dat ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING) MAX_AFTER 
from FOOLAG
ORDER BY IDT, DAT