contiguous.sql

WITH q AS (
    SELECT id,
        root_id,
        version,
        lag(version) OVER (PARTITION BY root_id ORDER BY root_id, version) as prev_version,
        lead(version) OVER (PARTITION BY root_id ORDER BY root_id, version) as next_version,
        latest
    FROM trades
    ORDER BY root_id, version
)
SELECT id, root_id, version, latest
FROM q
WHERE (next_version IS NOT NULL AND next_version - version <> 1)
   OR (prev_version IS NULL AND version <> 1);