recursive.sql

--Generate all aplphabets in SQL
WITH ALPHA AS
(
  SELECT CHAR(ASCII('A')) LETTER  ---ANCHOR QUERY 
  UNION ALL
  SELECT CHAR(ASCII(LETTER)+1)  ---RECURSIVE QUERY 
  FROM ALPHA
  WHERE LETTER<>'Z'
)
 --FINAL RESULT
 SELECT * FROM ALPHA;


---GENERATE PRIME NUMBER IN GIVEN RANGE 

WITH PRIME_TABLE AS
(
SELECT 2 AS NUM    ---ANCHOR QUERY  REPLACE 2 WITH LOWER BOUND NUMBER
UNION ALL
SELECT P.NUM +1 AS NUM --RECURSIVE QUERY 
 FROM PRIME_TABLE P WHERE P.NUM<20   --REPALCE 20 WITH UPPER BOUND NUMBER LIKE(30,40)
 )
 SELECT NUM
 FROM PRIME_TABLE P1
 WHERE  NOT EXISTS(SELECT 1 FROM PRIME_TABLE P2
                   WHERE P1.NUM%P2.NUM=0
                   AND P1.NUM<>P2.NUM);

--GENRATE FIBBONACI SERIES
WITH CTE AS 
(
  SELECT 0 AS NUM1,1 AS NUM2,1 AS INX ---ANCHOR QUERY
  UNION ALL
  SELECT NUM2 AS NUM1,NUM2+NUM1 AS NUM2,  --RECURSIVE
  INX+1 AS INX
   FROM CTE  WHERE INX+1<=10 
)
SELECT * FROM CTE ;
  
---FIND SATART_dATE,
CREATE OR REPLACE TABLE TEMP
(BAL INTEGER,
 DATES DATE);

INSERT INTO TEMP
VALUES 
(26000,'2020-01-01'),
(26000,'2020-01-02'),
(26000,'2020-01-03'),
(30000,'2020-01-04'),
(30000,'2020-01-05'),
(26000,'2020-01-06'),
(26000,'2020-01-07'), 
(31000,'2020-01-08'),
(32000,'2020-01-09');


WITH CTE1 AS 
(
  SELECT BAL,DATES,LAG(BAL) OVER(ORDER BY DATES) AS LAG1,
  CASE 
  WHEN  LAG(BAL) OVER(ORDER BY DATES)  = BAL THEN 0 ELSE 1 END AS SEQ
  FROM TEMP ),
SEQ_CTE AS 
(
  SELECT BAL,DATES,
  SUM(SEQ) OVER (ORDER BY DATES) AS SEQ
   FROM CTE1
  )
  SELECT MIN(DATES),MAX(DATES),BAL FROM SEQ_CTE GROUP BY SEQ,BAL;


--FIND UNIQUE ENTRY FOR EACH SOURCE & DESTINATION

CREATE TABLE TRAVEL
(SOURCE VARCHAR(100),
DESTINATION VARCHAR(100),
 DISTANCE INTEGER,
);

INSERT INTO TRAVEL 
VALUES 
('DEL','PNE' ,1400),
('PNE','DEL',1400),
('BAN','CHN',350),
('MUB','AHM',500),
('CHN','BAN',350),
('PNT','RNC',300);

WITH CTE AS (SELECT T1.SOURCE AS SRC,
             T1.destination AS DEST,
             T1.distance AS DIST,
             ROW_NUMBER() OVER(ORDER BY T1.SOURCE) AS id
             FROM TRAVEL T1
 			JOIN TRAVEL T2 
      ON T1.DESTINATION=T2.SOURCE AND T2.DESTINATION = T1.SOURCE),
ONE_PATH AS (
  SELECT  SRC FROM CTE  WHERE ID%2=1
)  
SELECT * FROM TRAVEL WHERE source NOT IN(SELECT SRC  FROM ONE_PATH);


--MISSING  ID FROM TABLE

create table ID_TBL 
(ID INTEGER);

INSERT INTO ID_TBL
VALUES
(1),(4),(7),(9),
(12),(16),(17),(20);

WITH 
CTE_MAX AS
(SELECT MAX(ID) ID FROM ID_TBL),
CTE_MIN AS 
(
  SELECT MIN(ID) AS ID FROM ID_TBL
  UNION ALL
  SELECT MN.ID+1 AS ID  FROM CTE_MAX MX INNER JOIN CTE_MIN MN
  ON MN.ID<MX.ID
)
SELECT M.ID  FROM CTE_MIN M  RIGHT JOIN ID_TBL T
ON M.ID = T.ID;
 
---find all number between given range from table
---CREATE RANGE
create table RANGE 
( START_R INTEGER,
  END_R INTEGER);
  
INSERT INTO RANGE
VALUES
(1,4),(6,6),(8,9),(11,13);


WITH CTE AS 
(SELECT MAX(END_R) AS END_VAL FROM RANGE),
 RANGE_TEMP AS
 (
   SELECT MIN(START_R) AS ID   FROM RANGE
   UNION ALL
   SELECT ID+1 AS ID FROM RANGE_TEMP 
   WHERE ID <(SELECT  END_VAL FROM CTE)
   )
SELECT *  FROM RANGE_TEMP , RANGE  
WHERE ID <= END_r AND ID >= start_r;