Snowflake lateral subquery fails

Question

If you run the SQL below in Snowflake you'll see that it returns SQL compilation error: Unsupported subquery type cannot be evaluated. For the life of me I cannot figure out how to rewrite it so that it does what I'm trying to do. Any suggestions?

CREATE OR REPLACE TABLE TEST1 
(
    ROWID INT IDENTITY(1,1),
    DATESTAMP TIMESTAMP_NTZ(9),
    STATE_ID INT,
    IS_TEST BOOLEAN,
    USER_ID INT,
    GROUP_ID INT,
    SECONDARY_USER_ID INT
);

INSERT INTO TEST1 (DATESTAMP, STATE_ID, IS_TEST, USER_ID, GROUP_ID) SELECT '01/01/2020', 31, TRUE, 1, 1;
INSERT INTO TEST1 (DATESTAMP, STATE_ID, IS_TEST, USER_ID, GROUP_ID) SELECT '02/01/2020', 31, TRUE, 1, 1;
INSERT INTO TEST1 (DATESTAMP, STATE_ID, IS_TEST, USER_ID, GROUP_ID) SELECT '05/01/2020', 29, NULL, 5, 1;
INSERT INTO TEST1 (DATESTAMP, STATE_ID, IS_TEST, USER_ID, GROUP_ID) SELECT '06/01/2020', 32, TRUE, 6, 1;

UPDATE TEST1 T1
SET SECONDARY_USER_ID = TSU.USER_ID
FROM TEST1 TS, 
    LATERAL (SELECT TOP 1 USER_ID                 
             FROM TEST1 X
             WHERE USER_ID <> 3
             AND X.DATESTAMP < TS.DATESTAMP
             AND TS.GROUP_ID = X.GROUP_ID
             AND (
                (X.STATE_ID = 29 and TS.IS_TEST IS NULL)
                  OR
                (X.STATE_ID = 32 and TS.IS_TEST = TRUE)
              )
             ORDER BY X.DATESTAMP DESC
            ) TSU
WHERE TS.STATE_ID = 31 
AND TS.ROWID = T1.ROWID;

Answer 1

The query will work if you replace:

SELECT TOP 1 [...] ORDER BY X.DATESTAMP DESC

with

ARRAY_AGG(X.USER_ID) WITHIN GROUP(ORDER BY X.DATESTAMP DESC)[0]

Full query:

UPDATE TEST1 T1
SET SECONDARY_USER_ID = TSU.USER_ID
FROM TEST1 TS, 
    LATERAL (SELECT ARRAY_AGG(X.USER_ID) WITHIN GROUP(ORDER BY DATESTAMP DESC)[0] USER_ID
             FROM TEST1 X
             WHERE USER_ID <> 3
             AND X.DATESTAMP < TS.DATESTAMP
             AND TS.GROUP_ID = X.GROUP_ID
             AND (
                (X.STATE_ID = 29 and TS.IS_TEST IS NULL)
                  OR
                (X.STATE_ID = 32 and TS.IS_TEST = TRUE)
              )
            ) TSU
WHERE TS.STATE_ID = 31 
AND TS.ROWID = T1.ROWID;