Groupy window function help

Below given is my table schema
CREATE TABLE ticks (
instrument_token BIGINT,
lastPrice BIGINT,
lastQuantity BIGINT,
avgPrice BIGINT,
volume BIGINT,
buy_quantity BIGINT,
sell_quantity BIGINT,
o BIGINT,
h BIGINT,
l BIGINT,
c BIGINT,
ltt TIMESTAMP,
oi BIGINT,
oi_day_high BIGINT,
oi_day_low BIGINT,
ts TIMESTAMP
);
for a given instrument_token I want to fetch first and last value min, max value of lastPrice for every 5 minute window for the period defined by where condition
ltt >= ‘2020-10-12 10:16:00’ and ltt < ‘2020-10-12 11:18:00’
llt contains datetime till minute, second is truncated

expected output is
first, last, min, max,timestamp
75,80,10,90,'2020-10-12 10:16:00
75,82,150,290,'2020-10-12 10:17:00
75,85,190,190,'2020-10-12 10:18:00

Hi @arunsoman,

nice to have you in the community forum,

The window functions are a fairly new feature, and it comes with some limitations; one of them is that you can’t use a derived field to create a computed bucket of 5 mins on the partition clause of first, last, min, max, and so on.

To overcome this limitation, you can create a TEMPORARY TABLE using a CTAS and then querying the temporary table.

As an example, using the provided schema:

CREATE TABLE ticks_2021012101699_20201012111800_temp AS
SELECT  instrument_token,
                ts,
                lastPrice,
                FLOOR(EXTRACT(EPOCH FROM ttl) / (5 * 60)) * (5 * 60) as ttl_five_minutes_bin
FROM      ticks
WHERE   ltt BETWEEN ‘2020-10-12 10:16:00’
                    AND ‘2020-10-12 11:18:00’;

SELECT  FIRST_VALUE(depdelay) OVER (PARTITION BY ttl_five_minutes_bin) AS first,
                LAST_VALUE(depdelay) OVER (PARTITION BY ttl_five_minutes_bin) AS last,
                MIN(depdelay) OVER (PARTITION BY ttl_five_minutes_bin) AS min,
                MAX(depdelay) OVER (PARTITION BY ttl_five_minutes_bin) AS  max,
                ts AS timestamp
FROM     ticks_2021012101699_20201012111800_temp;

DROP TABLE ticks_2021012101699_20201012111800_temp;

I’m not sure if I guessed what you asked when you said “for every 5 mins”; I choosed to create a serie of buckets of 5 mins on Time To Live field

Regards