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?