These are the things need adapting:

• PostgreSQL/RisingWave uses double quotes for identifiers rather than backticks.
• PostgreSQL/RisingWave uses #>> to extract a string from a json value at a certain path rather than JSON_VALUE.
• proctime() shall be defined as a generated column on the source/table rather than being part of select.
• Just write interval '00:03:00' without the suffix hour to second.
• The window definition must be written after each OVER. Defining once and reusing the name w is not supported yet.
• The MATCH_RECOGNIZE syntax is not supported yet. A workaround with window function lag can be used.

Additionally, I noticed that rule_record_time was casted from timestamp_ltz to timestamp without time zone, which may cause problems with daylight saving.

The bug on lag has been fixed and would be available in nightly-20231130. Here's how you can express the MATCH_RECOGNIZE:

SELECT * FROM (
    SELECT
        "source.address",
        r0_rule_record_time AS "window_start",
        r1_rule_record_time AS "window_end"
    FROM (
        SELECT
            "source.address",
            LAG(rule_record_time) OVER (PARTITION BY "source.address" ORDER BY "rule_record_time") AS r0_rule_record_time,
            rule_record_time AS r1_rule_record_time,
            LAG(id_rule) OVER (PARTITION BY "source.address" ORDER BY "rule_record_time") AS r0_id_rule,
            id_rule AS r1_id_rule
        FROM
            temp_0e95725d_7320_415d_80f7_004da920fc20
    ) AS pattern_r0_r1
    WHERE
        r1_rule_record_time - r0_rule_record_time <= interval '00:01:00'
        AND r0_id_rule = 'A'
        AND r1_id_rule = 'B'
) AS MATCH_RECOGNIZE_RESULTS;

Thanks for your input, much appreciated. I will work on this and post back results and workarounds.