Oloompa

-- COUNT ALL location gaps over a period
WITH AisLocationGaps AS (
    SELECT
        v.name,
        v."imoIdentifier",
        l.dtg,
        COALESCE(
            LAG(l.dtg) OVER (PARTITION BY v."activeMmsiIdentifier" ORDER BY l.dtg),
            NOW() - INTERVAL '4 months'
        ) AS previous_dtg,