Title
#users-public
e

ExistentialKev

08/17/2022, 3:12 PM
Hello currently I have been running the following query that would return a table of average prices in a given time interval...
WITH history_range
        AS(SELECT * FROM 'mlb'
        WHERE id = '10002087'
        AND timestamp > to_timestamp('2022-07-10:00:00:00.000000Z', 'yyyy-MM-dd:HH:mm:ss.SSSUUUZ')), 
        avg_book_price
        AS(SELECT id, name, avg(price) price, timestamp FROM history_range SAMPLE BY 1d)
        SELECT * from avg_book_price
        ORDER by timestamp DESC
However as it turns out we needed something more like the final price in a given time interval.... So I tried something like
max(timestamp)
but that didn't work well at all. It seems like I probably don't need
SAMPLE BY
for this use-case but I can't find anything in the documentation that would return a table that would give me the closest price to an interval of time... for instance what is the last price recorded every 2 hours or 1 week or month
Bolek Ziobrowski

Bolek Ziobrowski

08/17/2022, 3:20 PM
Hi @ExistentialKev. last() might be what you're looking for (https://questdb.io/docs/reference/function/aggregation/#firstlast).
e

ExistentialKev

08/17/2022, 3:25 PM
Ahh perfect