Updated on General

Interesting problem concerned with updating a value in Presto sql

I am trying to solve the below problem for quite a while now in Presto sql but no approach seems to work.

Suppose I have below table with two columns. X and Y. Output is what I want.

X       Y   Output
dummy   1   1
dummy   2   1
dummy   3   3
dummy   4   3
dummy   5   5

The logic is, I set the Y value in the first row as start and look ahead, if all the subsequent rows are within start + 2 then I assign the first row value as output, otherwise I update the start value with the Y value of the current row.

For example, our initial start value is 1 since first and second row has value less than 3 (start + 2), so we assign the output the value 1, but since 3 is not within the criteria start value gets updated to 3.

Is it even possible in presto sql? Is there any scalable workaround for this problem?

Recommended Articles