-- In the data below, the days Tuesday, Wednesday, Friday and 
-- Saturday are not in linear order with respect to the day index. 
-- Using the analytic functions LEAD/LAG, the non-linear data setup
-- can be easily captured as shown below. 

WITH q_days AS
(SELECT 1 idx, 'MON' wk_day, 1 day_idx FROM DUAL UNION 
 SELECT 2 idx, 'TUE' wk_day, 3 day_idx FROM DUAL UNION
 SELECT 3 idx, 'WED' wk_day, 2 day_idx FROM DUAL UNION
 SELECT 4 idx, 'THU' wk_day, 4 day_idx FROM DUAL UNION
 SELECT 5 idx, 'FRI' wk_day, 6 day_idx FROM DUAL UNION
 SELECT 6 idx, 'SAT' wk_day, 5 day_idx FROM DUAL UNION
 SELECT 7 idx, 'SUN' wk_day, 7 day_idx FROM DUAL
), q_lag AS
(SELECT
   idx, wk_day, day_idx,
   LAG(day_idx) OVER (ORDER BY idx) day_lag,
   LEAD(day_idx) OVER (ORDER BY idx) day_lead
FROM q_days
), q_gaps AS
(SELECT
   idx, wk_day, day_idx,
   day_idx - NVL(day_lag,day_idx)   as lag_day,
   NVL(day_lead,day_idx) - day_idx  as lead_day
FROM  q_lag)
SELECT 
  idx, wk_day, day_idx, lag_day, lead_day
FROM  q_gaps
WHERE lag_day < 0  OR  lead_day < 0;



-- The data below should have been in the linear order 
-- 1, 5, 7, 14, 17, 23, 31
-- The values 7, 5 and 23, 17 are the outliers.  The same logic of
-- using LEAD/LAG function can capture the outlier data.

WITH q_days AS
(SELECT 1 idx, 'MON' wk_day,  1 day_idx FROM DUAL UNION 
 SELECT 2 idx, 'TUE' wk_day,  7 day_idx FROM DUAL UNION
 SELECT 3 idx, 'WED' wk_day,  5 day_idx FROM DUAL UNION
 SELECT 4 idx, 'THU' wk_day, 14 day_idx FROM DUAL UNION
 SELECT 5 idx, 'FRI' wk_day, 23 day_idx FROM DUAL UNION
 SELECT 6 idx, 'SAT' wk_day, 17 day_idx FROM DUAL UNION
 SELECT 7 idx, 'SUN' wk_day, 31 day_idx FROM DUAL
), q_lag AS
(SELECT
   idx, wk_day, day_idx,
   LAG(day_idx) OVER (ORDER BY idx) day_lag,
   LEAD(day_idx) OVER (ORDER BY idx) day_lead
FROM q_days
), q_gaps AS
(SELECT
   idx, wk_day, day_idx,
   day_idx - NVL(day_lag,day_idx)   as lag_day,
   NVL(day_lead,day_idx) - day_idx  as lead_day
FROM  q_lag)
SELECT 
  idx, wk_day, day_idx, lag_day, lead_day
FROM  q_gaps
WHERE lag_day < 0  OR  lead_day < 0;