syncrow-data/fact_presence_state_timestamp/fact_presence_state_timestamp.sql

/*
 * This model tracks the timestamp when a presence state went from no-presence --> presence detected, per device.
 * This model should be used to display the presence logs Talal requested on the platform
 */

WITH device_logs AS (
    SELECT 
        device.uuid AS device_id,
        device.created_at,
        device.device_tuya_uuid,
        device.space_device_uuid AS space_id,
        "device-status-log".event_id,
        "device-status-log".event_time::timestamp,
        "device-status-log".code,
        "device-status-log".value,
        "device-status-log".log,
        LAG("device-status-log".event_time::timestamp) 
            OVER (PARTITION BY device.uuid 
                  ORDER BY "device-status-log".event_time) AS prev_timestamp,
        LAG("device-status-log".value) 
            OVER (PARTITION BY device.uuid 
                  ORDER BY "device-status-log".event_time) AS prev_value
    FROM device
    LEFT JOIN "device-status-log"
        ON device.uuid = "device-status-log".device_id
    LEFT JOIN product
        ON product.uuid = device.product_device_uuid
    WHERE product.cat_name = 'hps' -- presence sensors
      AND "device-status-log".code = 'presence_state'
    ORDER BY device.uuid, "device-status-log".event_time
)

, presence_detection AS (
    SELECT *, 
        CASE 
            WHEN value IN ('presence', 'motion') AND prev_value = 'none' THEN 1  -- detects a change in status from no presence to presence or motion
            ELSE 0 
        END AS presence_detected
    FROM device_logs
)

SELECT event_time as "time_presence_detected", device_id, space_id
FROM presence_detection
WHERE presence_detected=1