summarize_islands

summarize_islands.sql

WITH CTE_CONDITION AS (
SELECT SensorTimestamp AS dtm ,DeviceID FROM IOT_DATA
WHERE 
SensorReading > 0 AND SensorTimestamp is not null
),
CTE_LAGGED AS (
    SELECT
        dtm,DeviceID ,
        LAG(dtm) 
            OVER (PARTITION BY DeviceID ORDER BY dtm) AS previous_datetime,
        LEAD(dtm) 
            OVER (PARTITION BY DeviceID ORDER BY dtm) AS next_datetime,
        ROW_NUMBER() OVER (PARTITION BY DeviceID ORDER BY CTE_CONDITION.dtm) 
        AS island_location 
    FROM CTE_CONDITION),
CTE_ISLAND_START AS (
    SELECT
        ROW_NUMBER() OVER (PARTITION BY DeviceID ORDER BY dtm) AS island_number,DeviceID ,
        dtm AS island_start_datetime,
        island_location AS island_start_location
    FROM CTE_LAGGED
    WHERE (DATEDIFF(day, previous_datetime, dtm) > 3
        OR CTE_LAGGED.previous_datetime IS NULL)),
CTE_ISLAND_END AS (
    SELECT
        ROW_NUMBER() 
            OVER (PARTITION BY DeviceID ORDER BY dtm) AS island_number,DeviceID ,
        dtm AS island_end_datetime,
        island_location AS island_end_location
    FROM CTE_LAGGED
    WHERE DATEDIFF(day, dtm, next_datetime) > 3 OR CTE_LAGGED.next_datetime IS NULL)
SELECT
CTE_ISLAND_START.DeviceID ,CTE_ISLAND_START.island_start_datetime,
    CTE_ISLAND_END.island_end_datetime,
    DATEDIFF(day, CTE_ISLAND_START.island_start_datetime, CTE_ISLAND_END.island_end_datetime) AS ISLAND_DURATION_day,
    (SELECT COUNT(*) 
     FROM CTE_LAGGED 
     WHERE CTE_LAGGED.dtm BETWEEN 
        CTE_ISLAND_START.island_start_datetime AND 
        CTE_ISLAND_END.island_end_datetime
AND CTE_LAGGED.DeviceID = CTE_ISLAND_START.DeviceID AND CTE_LAGGED.DeviceID = CTE_ISLAND_START.DeviceID ) 
    AS island_row_count
FROM CTE_ISLAND_START
INNER JOIN CTE_ISLAND_END ON CTE_ISLAND_END.island_number = CTE_ISLAND_START.island_number
 AND CTE_ISLAND_START.DeviceID = CTE_ISLAND_END.DeviceID