History.sql

WITH way_ranges AS (
    SELECT
        way_id,
        timestamp AS timestamp_from,
        LEAD(timestamp, 1, '3000-01-01') OVER (PARTITION BY way_id ORDER BY TIMESTAMP) AS timestamp_to,
        version,
        MAX(version) FILTER(WHERE visible = true) OVER
             (PARTITION BY way_id 
                ORDER BY TIMESTAMP
                ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS waynodes_available_version
    FROM
        ways
    WHERE
        way_id = 4000290032
    AND redaction_id IS NULL
--        AND version >= 1
--        AND version <= 2
),
waynode_ranges AS (
    SELECT
        wr.way_id,
        wr.timestamp_from,
        wr.timestamp_to,
        wn.node_id
    FROM
        way_ranges AS wr
        INNER JOIN way_nodes AS wn
             ON wr.way_id = wn.way_id
            AND wr.waynodes_available_version = wn.version
),
node_ranges AS (
    SELECT
        node_id,
        TIMESTAMP AS timestamp_from,
        LEAD(TIMESTAMP, 1, '3000-01-01') OVER (PARTITION BY node_id ORDER BY TIMESTAMP) AS timestamp_to,
        version
    FROM
        nodes
    WHERE node_id IN (SELECT DISTINCT node_id FROM waynode_ranges)
      AND redaction_id IS NULL
)
SELECT
    nr.node_id,
    nr.version
FROM
    node_ranges as nr
    INNER JOIN waynode_ranges AS wnr
         ON nr.timestamp_from < wnr.timestamp_to
        AND wnr.timestamp_from < nr.timestamp_to
        AND nr.node_id = wnr.node_id
GROUP BY
    nr.node_id,
    nr.version
ORDER BY
    nr.node_id,
    nr.version;