This message was deleted.

I don’t see

LAST_VALUE

as a Druid SQL function - are you looking for

LATEST

? https://druid.apache.org/docs/latest/querying/sql-functions

Thanks Kyle. Yes, I am looking for LATEST. I revised the query to the following: SELECT DATE_TRUNC('PT5M',"__time") as time_interval, LASTEST("lasttrade") as price, SUM("lasttradesize") as volume FROM "stocks_intervals" WHERE "symbol" = 'TSLA' AND "__time" >= TIMESTAMP '2024-01-24 093000' AND "__time" < TIMESTAMP '2024-01-24 163000' GROUP BY DATE_TRUNC('PT5M',"__time") But when I use that I get another error: Error: INVALID_INPUT No match found for function signature LASTEST(<NUMERIC>) (line [3], column [13])

just check your spelling - it should be

LATEST

and you have

LASTEST

Hey @Kyle Hoondert I converted John's query to use Wikipedia... 🤠

SELECT
            DATE_TRUNC('PT5M',"__time") as time_interval,
            LATEST("added") as added,
            SUM("commentLength") as commentLength
        FROM
            "wikipedia"
        WHERE
            "channel" = '#sv.wikipedia'
            AND "__time" >= TIMESTAMP '2016-01-01 09:30:00'
            AND "__time" < TIMESTAMP '2017-01-24 16:30:00'
        GROUP BY
            DATE_TRUNC('PT5M',"__time")

And I get the UNCATEGORIZED (ADMIN) error. Let me play around with that query, at least we have a use case that can be run by anyone with the Wikipedia dataset...

LOL - I did the same

SELECT
            TIME_FLOOR("__time",'PT5M') as time_interval,
            LATEST("comment") as price,
            SUM("commentLength") as volume
        FROM
            "wikipedia"
        WHERE
            "channel" = '#en.wikipedia'
            AND "__time" >= TIMESTAMP '2015-01-24 09:30:00'
            AND "__time" < TIMESTAMP '2024-01-24 16:30:00'
        GROUP BY 1

As I always say, "great minds think like Kyle"... 🤗

yours looks better - you actually changed your aggregation names 😉

Started doing the usual things by making changes, I can get the query to run if I change:

DATE_TRUNC('PT5M',"__time") as time_interval

to

__time as time_interval

and add __time to the GROUP BY

I don’t usually do date stuff with

DATE_TRUNC

so I switched mine to a

TIME_FLOOR

🤷

Thanks for the prompt responses guys. So what's the final query that runs for the 5 minute intervals?

SELECT
            TIME_FLOOR(__time,'PT5M') as time_interval,
            LATEST("lasttrade") as price,
            SUM("lasttradesize") as volume
        FROM
            "stocks_intervals"
        WHERE
            "symbol" = 'TSLA'
            AND "__time" >= TIMESTAMP '2024-01-24 09:30:00'
            AND "__time" < TIMESTAMP '2024-01-24 16:30:00'
        GROUP BY 1

should work?

Absolutely superb. Thanks gentlemen.

Hey @John Masvongo do you have the wikipedia dataset loaded into your Druid instance? Would be good to have, because then you can send us a query that we can run and reproduce things more quickly and easily...

Also raised a PR https://github.com/apache/druid/pull/15759 so that folks get a nicer error message in future. Thanks for reporting the issue.

I was wondering about that @Abhishek Agarwal I didn't look at the code to see where that exception is thrown. Thanks much for fixing the issue and creating a PR, that was fast!!