Time-Series Queries

Missing Days

Compare Expected and Actual

A left join from expected days to actual readings can reveal time-series gaps.

Program

Play the script to choose the first expected day and identify which readings are missing.

missing_day_report.sql 
CREATE TABLE expected_days (day TEXT);
INSERT INTO expected_days VALUES ('2026-04-01'), ('2026-04-02'), ('2026-04-03'), ('2026-04-04');
CREATE TABLE readings (day TEXT, temperature INTEGER);
INSERT INTO readings VALUES ('2026-04-01', 71), ('2026-04-03', 69);
WITH params(start_day) AS (VALUES ()) SELECT e.day, CASE WHEN r.day IS NULL THEN 'missing' ELSE 'present' END AS status FROM expected_days AS e LEFT JOIN readings AS r ON r.day = e.day WHERE e.day >= (SELECT start_day FROM params) ORDER BY e.day;

CREATE TABLE expected_days (day TEXT);
INSERT INTO expected_days VALUES ('2026-04-01'), ('2026-04-02'), ('2026-04-03'), ('2026-04-04');
CREATE TABLE readings (day TEXT, temperature INTEGER);
INSERT INTO readings VALUES ('2026-04-01', 71), ('2026-04-03', 69);
WITH params(start_day) AS (VALUES ()) SELECT e.day, CASE WHEN r.day IS NULL THEN 'missing' ELSE 'present' END AS status FROM expected_days AS e LEFT JOIN readings AS r ON r.day = e.day WHERE e.day >= (SELECT start_day FROM params) ORDER BY e.day;

CREATE TABLE expected_days (day TEXT);
INSERT INTO expected_days VALUES ('2026-04-01'), ('2026-04-02'), ('2026-04-03'), ('2026-04-04');
CREATE TABLE readings (day TEXT, temperature INTEGER);
INSERT INTO readings VALUES ('2026-04-01', 71), ('2026-04-03', 69);
WITH params(start_day) AS (VALUES ()) SELECT e.day, CASE WHEN r.day IS NULL THEN 'missing' ELSE 'present' END AS status FROM expected_days AS e LEFT JOIN readings AS r ON r.day = e.day WHERE e.day >= (SELECT start_day FROM params) ORDER BY e.day;
LEFT JOIN The expected day remains visible even when there is no matching reading.
gap status `CASE` labels missing joined rows as `missing`.
calendar table A small expected-days table gives the query a complete timeline to compare against.