I would like the given prices to make a ZONE

Question

1834.14
1834.00
1831.72
1828.61
1828.34
1825.70
1814.09
1813.84
1813.74
1803.58
1802.84
1797.87
1797.30
1795.70

I would like to make a ZONE of the above prices where every value closer to each other with difference of <=3.00

sample result:

Answer 1

Here is one way, using the LEAD() analytic function:

WITH cte AS (
    SELECT *, CASE WHEN val - LEAD(val) OVER (ORDER BY val DESC) > 3
                   THEN 1 ELSE 0 END AS label
    FROM yourTable
),
cte2 AS (
    SELECT val, SUM(label) OVER (ORDER BY val DESC) AS zone
    FROM cte
)

SELECT MIN(val) AS min_val, MAX(val) AS max_val, zone
FROM cte2
GROUP BY zone
ORDER BY MAX(val) DESC;