How to find anomaly in sequence

Assume we have table with integer column. How we can find is all values of this column are sequence without gaps or duplicates.

For achieve this we can use window function LAG that returns value of column from previous row. Let's start.

Create table and fill it by sequence:

create table tbl (id int);

insert into tbl values (3),(4),(5);  

Now write query:

select count(*) anomaly from (
  select id - (lag(id) over (order by id)) diff from tbl
) t where diff is not null and diff <> 1;

+=========+
| anomaly |
+=========+
| 0       |
+---------+

We got zero anomalies found.

Now we create an anomaly and check our query again.

insert into tbl values (5),(7),(8);


select count(*) anomaly from (
  select id - (lag(id) over (order by id)) diff from tbl
) t where diff is not null and diff <> 1; 

+=========+
| anomaly |
+=========+
| 2       |
+---------+

So we can see two anomalies found

SQL editor online