Is there any way to do calculations against the first row of a query result, using a subquery or something like that? I've been trying but haven't been able to make it work. Basically I'm trying to compute a delta column to determine the price difference between each subsequent row and the first row of the query result. Here's my query without the delta column:

SELECT
  time,
  sym,
  first(price) as price
FROM trade
WHERE
  time BETWEEN '2023-03-08 12:00:00' AND '2023-03-08 12:02:00' AND
  sym='BTC.USD'
SAMPLE BY 1T
ORDER BY time ASC, sym ASC

and I'd love to have an additional column something along the lines of "price-first(price) as delta" but nothing I've tried works. if I use a sub-query WITH beforehand and try to query that, doesnt seem to work

@Bolek Ziobrowski will be able to help you tomorrow morning

@John M. You've to use fp in FROM clause, simply declaring it in WITH does nothing .

WITH fp AS (select price from trade where time>'2023-03-08 12:00:00' and sym='BTC.USD' LIMIT -1)
SELECT
  time,
  sym,
  first(price) AS price,
  first(price) - fp.price AS delta
FROM fp 
CROSS JOIN trade
WHERE
  time BETWEEN '2023-03-08 12:00:00' AND '2023-03-08 12:02:00' AND
  sym='BTC.USD'
SAMPLE BY 1T
ORDER BY time ASC, sym ASC

Thank you @Bolek Ziobrowski this is very helpful!

@John M. You might run into an exception with earlier example so try this one :

WITH fp AS (select price as first_price from trades where timestamp>'2023-03-08 12:00:00' and symbol='BTC-USD' LIMIT 1)
select timestamp, symbol, price, price - fp.first_price AS delta
from (
SELECT
  timestamp,
  symbol,
  first(price) AS price
FROM trades
WHERE
  timestamp BETWEEN '2023-03-08 12:00:00' AND '2023-03-08 12:02:00' AND
  symbol='BTC-USD'
SAMPLE BY 1T
) CROSS JOIN fp
ORDER BY timestamp ASC, symbol ASC

This seems to be popular question around market data querying, we may want to add some syntax simplification for it. What are you building with QuestDB John?

@Alex Pelagenko right now I have nothing actually built, but trying to put together a backtesting/research platform as a hobby project. Step 1 has been figuring out if QuestDB will fit my needs. I had initially been building it against ClickHouse, which I love in general, however, QuestDB seems to be more performant than ClickHouse which is very nice. CH has more features, documentation, and is perhaps a little bit easier to use at the moment though, but I'm leaning towards QuestDB. I'm mostly trying to figure out "can I even do what I need to do with QuestDB?" at this stage though... very early stages, hence all the questions (thank you for answering all of them! extremely helpful!). I'm so far very impressed with QuestDB!

Thanks, good to hear, we also appreciate your feedback