SQLITE NULL UNIQUE

-- ==================================================
-- Use ctrl-enter or shift-enter to execute sqlite3
-- shell commands and SQL.
-- If a subset of the text is currently selected,
-- only that part is executed.
-- ==================================================
.nullvalue NULL
.headers ON

DROP TABLE IF EXISTS test;

CREATE TABLE IF NOT EXISTS test (
    id INTEGER NOT NULL PRIMARY KEY,
    date_i TEXT NOT NULL,
    numb INTEGER,
    sub_n TEXT NOT NULL ON CONFLICT REPLACE DEFAULT "",
    year_n INTEGER NOT NULL,
    UNIQUE(numb, sub_n, year_n)
);

--INSERT INTO test VALUES(NULL, '2023-12-09', NULL, NULL, 2023);
--INSERT INTO test VALUES(NULL, '2023-12-09', NULL, NULL, 2023);
--INSERT INTO test VALUES(NULL, '2023-12-09', 3, NULL, 2023);
INSERT INTO test VALUES(NULL, '2023-12-08', 1, NULL, 2023);
INSERT INTO test VALUES(NULL, '2023-12-08', 2, NULL, 2023);
INSERT INTO test VALUES(NULL, '2023-12-09', 3, NULL, 2023);
INSERT INTO test VALUES(NULL, '2023-12-10', 5, NULL, 2023);
INSERT INTO test VALUES(NULL, '2023-12-11', 6, NULL, 2023);
INSERT INTO test VALUES(NULL, '2023-12-12', 11, NULL, 2023);
INSERT INTO test VALUES(NULL, '2023-12-14', 14, NULL, 2023);

SELECT * FROM test;

-- select min(numb) from test;

--SELECT value FROM generate_series(1, 5, 1) WHERE value NOT IN (SELECT numb FROM test);

--SELECT value FROM generate_series((select min(numb) from test), (select max(numb) from test), 1) WHERE value NOT IN (SELECT numb FROM test);

SELECT COUNT(*) AS is_free WHERE 3 IN (
WITH bound(MIN,MAX) AS (SELECT MIN(numb),MAX(numb) FROM test)
SELECT VALUE FROM generate_series JOIN bound ON START=MIN AND stop=MAX
EXCEPT SELECT numb FROM test
);

--SELECT numb FROM test WHERE numb < 4 ORDER BY numb DESC LIMIT 1;

--SELECT numb FROM test WHERE numb > 4 ORDER BY numb ASC LIMIT 1;

WITH lowBorder AS (
SELECT numb FROM test WHERE numb < 4 ORDER BY numb DESC LIMIT 1
),
upBorder AS (
SELECT numb FROM test WHERE numb > 4 ORDER BY numb ASC LIMIT 1
)
SELECT * FROM test WHERE numb IN (SELECT * FROM lowBorder UNION SELECT * FROM upBorder)
AND (date_i >= '2023-12-10' OR date_i <= '2023-12-10');

WITH lb(d1, n1) AS (
SELECT date_i, numb FROM test WHERE numb < 4 ORDER BY numb DESC LIMIT 1
),
ub(d2, n2) AS (
SELECT date_i, numb FROM test WHERE numb > 4 ORDER BY numb ASC LIMIT 1
)
SELECT COUNT(*) FROM lb,ub WHERE lb.d1 <= '2023-12-11' AND ub.d2 >= '2023-12-11';

WITH lb(d1, n1) AS (
SELECT date_i, numb FROM test WHERE numb < 12 ORDER BY numb DESC LIMIT 1
),
ub(d2, n2) AS (
SELECT date_i, numb FROM test WHERE numb > 12 ORDER BY numb ASC LIMIT 1
)
SELECT * FROM lb,ub ;

WITH lb(d1, n1) AS (
SELECT date_i, numb FROM test WHERE numb < 12 ORDER BY numb DESC LIMIT 1
),
ub(d2, n2) AS (
SELECT date_i, numb FROM test WHERE numb > 12 ORDER BY numb ASC LIMIT 1
)
SELECT COUNT(*) FROM lb,ub WHERE lb.d1 <= '2023-12-15' AND ub.d2 >= '2023-12-15';