'Check if value exists on one date missing on next

I have one table with below column

Table

I want to find missing nodes Missing node definition: node present on day and missing the next day will be missing node on next day In above example 111 will be missing on 2022-03-03 100 will be missing on 2022-03-03 and 2022-03-04

I have applied rank on snapshot partitioned by region but I m not sure how to proceed further

mysqlsqlself-join


Solution 1:[1]

Missing node definition: node present on day and missing the next day

SELECT *
FROM table AS t1
WHERE NOT EXISTS ( SELECT NULL
                   FROM table AS t2
                   WHERE t2.node = t1.node
                     AND t2.date = t1.date + INTERVAL 1 DAY )

The query returns "present day" row for which "the next day" row is absent.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Akina

Related Questions