Find starting value and ending value

Question

Hi, My friend faced an interview question.

 

Input:

CurrencyValue	Value
100	1
200	1
300	1
99	1
123	2
321	2
115	2
34	3
37	3
89	3

 

Output: 

 

Value	Started	Ended
1	100	99
2	123	115
3	34	89

 

I tried the following query.

 

WITH cte

AS (SELECT currencyvalue,

value,

Row_number()

OVER(

partition BY value

ORDER BY value DESC ) rk

FROM table_max_min)

SELECT value,

Min(rk)minimum,

Max(rk)maximum

FROM cte

GROUP BY value