How can I use lag(offset) window function on QuestDB?

Question

I know QuestDB has some window functions, but lag is still not supported. I have been using LT JOINS to get the value from the previous row. This is not ideal (query more verbose than needed and join performance) but it works.

However, I need now to get the value from the previous row and the one before. With lag I could use the offset argument, but without it I am at lost.

In other databases I can write this:

SELECT timestamp, 
price, 
Lag(price, 1) OVER(ORDER BY timestamp) AS price1, 
Lag(price, 2) OVER(ORDER BY timestamp) AS price2
FROM table

So every row would show the price, plus the price from rows 1 and 2 before.

Any ideas to get this on QuestDB? Thanks

Answer 1

lag is not one of the currently implemented window functions on QuestDB, but you can use the window function first_value. By playing with the row offset, you can get the same result you would get with lag.

For example, if you go to the questdb playground, you can execute

SELECT *, 
FIRST_VALUE(price) OVER (PARTITION BY symbol ORDER BY timestamp rows BETWEEN 1 PRECEDING AND 1 PRECEDING) AS price_lag_1,
FIRST_VALUE(price) OVER (PARTITION BY symbol ORDER BY timestamp rows BETWEEN 2 PRECEDING AND 2 PRECEDING) AS price_lag_2
FROM 
trades 
WHERE timestamp IN '2023-11-30T10'
AND symbol = 'BTC-USD'

And you should get the prices for the 2 rows before and current price on each row.