{c}

* <SQLite>: https://www.sqlite.org/windowfunctions.html#exprrange

``
rm -f tmp.sqlite
sqlite3 tmp.sqlite "create table t (id integer, val integer)"
sqlite3 tmp.sqlite <<EOF
insert into t values
  (0, 0),
  (1, 5),
  (2, 10),
  (3, 14),
  (4, 15),
  (5, 16),
  (6, 20),
  (7, 25),
  (8, 29),
  (9, 30),
  (10, 30),
  (11, 31),
  (12, 35),
  (13, 40)
EOF
``

Show how many neighbours each column has with `val` between `val - 2` and `val + 2` inclusive:
``
sqlite3 tmp.sqlite <<EOF
SELECT id, val, COUNT(*) OVER (
  ORDER BY val RANGE BETWEEN 2 PRECEDING AND 2 FOLLOWING
) FROM t;
EOF
``
Output:
``
0|0|1
1|5|1
2|10|1
3|14|3
4|15|3
5|16|3
6|20|1
7|25|1
8|29|4
9|30|4
10|30|4
11|31|4
12|35|1
13|40|1
``

`val - 1` and `val + 1` inclusive instead:
``
sqlite3 tmp.sqlite <<EOF
SELECT id, val, COUNT(*) OVER (
  ORDER BY val RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING
) FROM t;
EOF
``
Output:
``
0|0|1
1|5|1
2|10|1
3|14|2
4|15|3
5|16|2
6|20|1
7|25|1
8|29|3
9|30|4
10|30|4
11|31|3
12|35|1
13|40|1
``

There seems to be no analogue to <HAVING (SQL)> for window functions, so we can just settle for a subquery for once, e.g.:
``
sqlite3 tmp.sqlite <<EOF
SELECT * FROM (
  SELECT id, val, COUNT(*) OVER (
    ORDER BY val RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING
  ) as c FROM t
) WHERE c > 2
EOF
``
which outputs:
``
4|15|3
8|29|3
9|30|4
10|30|4
11|31|3
``


 Discussion on "SQL window RANGE" by Ciro Santilli (@cirosantilli, 34)

 Discussion on "SQL window `RANGE`" by Ciro Santilli (@cirosantilli, 34)