Find missing values in seq

gistfile1.sql

CREATE TABLE seqtest(seqval INT);

INSERT INTO seqtest( 3);
INSERT INTO seqtest( 4);
INSERT INTO seqtest( 5);
INSERT INTO seqtest( 8);
INSERT INTO seqtest( 9);
INSERT INTO seqtest(11);
INSERT INTO seqtest(18);
INSERT INTO seqtest(19);
INSERT INTO seqtest(20);

Find missing values:

 *** Query completed. 4 rows found. 3 columns returned.
 *** Total elapsed time was 1 second.

   GapStart       GapEnd         #IDs
-----------  -----------  -----------
          1            2            2  -- optional
          6            7            2
         10           10            1
         12           17            6

SELECT
  seqval - #IDs AS GapStart,
  seqval - 1 AS GapEnd,
  (COALESCE(seqval
            - MIN(seqval) OVER
                 (order by seqval
                  ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING)
            ,seqval)) - 1 AS #IDs
FROM
  seqtest
QUALIFY
  #IDs > 0;



Find ranges of used values:

 *** Query completed. 4 rows found. 3 columns returned.
 *** Total elapsed time was 1 second.

   StartVal       EndVal          Cnt
-----------  -----------  -----------
          3            5            3
          8            9            2
         11           11            1
         18           20            3

SELECT
  MIN(seqval) AS StartVal,
  MAX(seqval) AS EndVal,
  COUNT(*) AS #IDs
FROM
 (
  SELECT
    seqval - RANK() OVER (ORDER BY seqval ASC) AS grp,
    seqval
  FROM seqtest
 ) dt
GROUP BY grp
ORDER BY 1
;