rows between 1 preceding and preceding 1

Question

I am new to SQL and I want to know what exactly the function ( rows between 1 preceding and 1 preceding )do in teradata ,I want a simple clarification please ,I am trying to use this function as a testcase to get the time gaps in history table between start and end date,can anyone help please or provide any useful links.

SELECT DISTINCT CUST_ID
FROM
(
SELECT 
CUST_ID,
STRT_dt - 
MIN(END_dt) OVER (PARTITION BY CUST_ID ORDER BY END_dt
ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS diff
FROM table
QUALIFY diff > 1
) dt

Answer 1

This returns the same result as Standard SQL's LAG(END_dt) OVER (PARTITION BY CUST_ID ORDER BY END_dt, i.e. the previous row's END_dt (or NULL for the 1st row per CUST_ID).

When you switch to FOLLOWING instead of PRECEDING it's the next row, LEAD in Standard SQL.

Both LAG and LEAD are finally implemented in TD16.10.

As you simply want to find gaps and you don't access the actual difference you can also simplify it to:

SELECT DISTINCT CUST_ID
FROM table
QUALIFY
   STRT_dt - 
   MIN(END_dt)
   OVER (PARTITION BY CUST_ID
         ORDER BY END_dt
         ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) > 1