Snowflake LAST_VALUE window frame replacement

snowflake.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", 
   COALESCE("text", FIRST_VALUE("leadText") OVER (PARTITION BY "grp" ORDER BY "id")) AS "text"
FROM 
   ( 
     SELECT *, SUM(CASE WHEN "leadText" IS NOT NULL AND "text" IS NULL OR
         "leadText" IS NULL AND "text" IS NOT NULL THEN 1 ELSE 0 END)
         OVER (ORDER BY "id") AS "grp"
     FROM ( 
        SELECT "id", "text", LEAD("text", -1) OVER (ORDER BY "id") AS "leadText"
         FROM "test"
         ) AS "subq1"
  ) AS "subq2";
  

Ahoj,
dneska jsem to testoval a uspesne vyuzil v praxi. Btw. funkce LEAD/LAG tam nehraje zadnou roli, lze to zkratit na jedine subquery, ktere vytvari podskupiny radku, ktere vzdy zacinaji nenulovou hodnotou a jsou nasledovany nulovymi hodnotami. Viz: https://gist.github.com/alespouzar/214e645e4630f214f0c61aee56515496
Ales