DROP TABLE IF EXISTS #sales;

CREATE TABLE #sales (id INT, region VARCHAR(50), amount DECIMAL(10, 2));

INSERT INTO #sales (id, region, amount) VALUES (1, 'North', 100.0);

INSERT INTO #sales (id, region, amount) VALUES (2, 'North', 200.0);

INSERT INTO #sales (id, region, amount) VALUES (3, 'South', 150.0);

INSERT INTO #sales (id, region, amount) VALUES (4, 'South', 150.0);

INSERT INTO #sales (id, region, amount) VALUES (5, 'South', 300.0);

SELECT

id,

region,

amount,

ROW_NUMBER() OVER(PARTITION BY region ORDER BY amount) AS rn,

RANK() OVER(PARTITION BY region ORDER BY amount) AS rnk,

LAG(amount) OVER(PARTITION BY region ORDER BY amount) AS prev_amount,

LEAD(amount) OVER(PARTITION BY region ORDER BY amount) AS next_amount,

FIRST_VALUE(amount) OVER(PARTITION BY region ORDER BY amount) AS first_amt,

LAST_VALUE(amount) OVER(PARTITION BY region ORDER BY amount) AS last_amt

FROM #sales;