Snowflake workaround for missing WINDOW FRAME in LAST_VALUE function

sf-workaround.sql

CREATE OR REPLACE TABLE "test" ("id" INTEGER, "text" VARCHAR);
INSERT INTO "test" ("id", "text") VALUES (1, 'first'), (2, 'second'), (3, NULL), 
(4, NULL), (5, 'fifth'), (6, NULL), (7, NULL), (8, NULL), (9, 'ninth');
SELECT * FROM "test" ORDER BY "id";

SELECT "id", 
   FIRST_VALUE("text") OVER (PARTITION BY "grp" ORDER BY "id") AS "text"
FROM 
   ( 
     SELECT
      "id",
      "text",
      SUM(CASE WHEN "text" IS NOT NULL THEN 1 ELSE 0 END) OVER (ORDER BY "id") AS "grp"
     FROM "test"
  ) AS "subq";