Unknown error when using lag function with SQLITE-CodePudding

I have the following table. When I am missing a value from the num dimension, I would like to replace it with the value of the previous row. The following is an example of what I have tried. I'm using the lag window function, and is not working.

CREATE TABLE test(dt text, num INT);
INSERT INTO test VALUES("2011-11-11", 3);
INSERT INTO test VALUES("2011-11-12", NULL);
INSERT INTO test VALUES("2011-11-13", 5);
INSERT INTO test VALUES("2011-11-14", NULL);
INSERT INTO test VALUES("2011-11-15", NULL);
INSERT INTO test VALUES("2011-11-16", 2);

select dt,
       case when num is not null then num 
       else lag(num,1,0) over (order by dt) end 
from test

The error that I'm getting: OperationalError: near "(": syntax error which I am not even sure what it means. Any help will be appreciated.

CodePudding user response：

Window function support was first added to SQLite with release version 3.25.0 (2018-09-15). You need to upgrade your SQLite binaries, or not use Window functions. See https://www.sqlite.org/windowfunctions.html#history

CodePudding user response：

Window functions in SQLite were only introduced from version 3.25.0. As a workaround, you may use a correlated subquery:

select dt,
    COALESCE(
        num,
        (SELECT num FROM test t2 WHERE t2.dt < t1.dt ORDER BY t2.dt DESC LIMIT 1)
    ) AS num_lag
FROM test t1;