CREATE MATERIALIZED VIEW memorin.outdated_devices AS
    SELECT
        serial_number,
        version_id,
        released_at,
        eol_timestamp,
        (
            SELECT COUNT(*) AS versions_behind
            FROM memorin.device_versions AS vs
            WHERE vs.released_at > v.released_at
        ) AS versions_behind
    FROM
        memorin.devices AS d,
        memorin.device_versions AS v
    WHERE eol_timestamp IS NOT NULL
    AND v.id = version_id
    AND serial_number IN (
        SELECT serial_number
        FROM memorin.devices
        WHERE deactivated_at IS NULL
    );