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