SQL Server 2019 (Linux)

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

-- Create Orders table
CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    OrderDate DATE,
    TotalAmount DECIMAL(10, 2)
);

-- Insert sample data
INSERT INTO Orders (OrderID, OrderDate, TotalAmount) VALUES
    (1, DATEADD(DAY, -7, GETDATE()), 1200.00),
    (2, DATEADD(DAY, -2, GETDATE()), 480.00),
    (3, DATEADD(DAY, -5, GETDATE()), 550.00),
    (4, DATEADD(DAY, -35, GETDATE()), 750.00),
    (5, DATEADD(DAY, -5, GETDATE()), 1100.00),
    (6, DATEADD(DAY, -6, GETDATE()), 300.00),
    (7, DATEADD(DAY, -7, GETDATE()), 1600.00),
    (8, DATEADD(DAY, -8, GETDATE()), 90.00),
    (9, DATEADD(DAY, -10, GETDATE()), 520.00),
    (10, DATEADD(DAY, -45, GETDATE()), 950.00);
10 rows affected

SELECT *
FROM Orders


OrderID
OrderDate
TotalAmount


1
2023-08-11
1200.00

2
2023-08-16
480.00

3
2023-08-13
550.00

4
2023-07-14
750.00

5
2023-08-13
1100.00

6
2023-08-12
300.00

7
2023-08-11
1600.00

8
2023-08-10
90.00

9
2023-08-08
520.00

10
2023-07-04
950.00


SELECT OrderID
	,OrderDate
	,TotalAmount
	,CASE 
		WHEN TotalAmount >= 1000
			THEN 1 -- High-value orders
		WHEN TotalAmount >= 500
			AND DATEDIFF(DAY, OrderDate, GETDATE()) <= 30
			THEN 2 -- Mid-value orders within 30 days
		ELSE 3 -- Other orders
		END AS Category
FROM Orders
ORDER BY CASE 
		WHEN TotalAmount >= 1000
			THEN 1 -- High-value orders
		WHEN TotalAmount >= 500
			AND DATEDIFF(DAY, OrderDate, GETDATE()) <= 30
			THEN 2 -- Mid-value orders within 30 days
		ELSE 3 -- Other orders
		END
	,TotalAmount DESC;


OrderID
OrderDate
TotalAmount
Category


7
2023-08-11
1600.00
1

1
2023-08-11
1200.00
1

5
2023-08-13
1100.00
1

3
2023-08-13
550.00
2

9
2023-08-08
520.00
2

10
2023-07-04
950.00
3

4
2023-07-14
750.00
3

2
2023-08-16
480.00
3

6
2023-08-12
300.00
3

8
2023-08-10
90.00
3


SELECT OrderID
	,OrderDate
	,TotalAmount
	,CASE 
		WHEN TotalAmount >= 1000
			THEN 1 -- High-value orders
		WHEN TotalAmount >= 500
			AND DATEDIFF(DAY, OrderDate, GETDATE()) <= 30
			THEN 2 -- Mid-value orders within 30 days
		ELSE 3 -- Other orders
		END AS Category
FROM Orders
ORDER BY Category
	,TotalAmount DESC;


OrderID
OrderDate
TotalAmount
Category


7
2023-08-11
1600.00
1

1
2023-08-11
1200.00
1

5
2023-08-13
1100.00
1

3
2023-08-13
550.00
2

9
2023-08-08
520.00
2

10
2023-07-04
950.00
3

4
2023-07-14
750.00
3

2
2023-08-16
480.00
3

6
2023-08-12
300.00
3

8
2023-08-10
90.00
3

OrderID	OrderDate	TotalAmount
1	2023-08-11	1200.00
2	2023-08-16	480.00
3	2023-08-13	550.00
4	2023-07-14	750.00
5	2023-08-13	1100.00
6	2023-08-12	300.00
7	2023-08-11	1600.00
8	2023-08-10	90.00
9	2023-08-08	520.00
10	2023-07-04	950.00