Set new field priority in SELECT SQL

I have a table of bills with the following structure:

id | store_name | sum | payment_date
1  | Amazon     | 10  | 11.05.2022
2  | Amazon     | 20  | 11.05.2022
3  | Ebay       | 15  | 11.05.2022
4  | AppleStore | 13  | 11.05.2022
5  | Google Play| 6   | 11.05.2022

What I need is to select all data from table and set additional field “Priority” based on a sum of bill. First 2 rows get priority 1, next 2 rows get priority 2, others get 0:

id | store_name | sum | payment_date | priority
2  | Amazon     | 20  | 11.05.2022   | 1
3  | Ebay       | 15  | 11.05.2022   | 1
4  | AppleStore | 13  | 11.05.2022   | 2
1  | Amazon     | 10  | 11.05.2022   | 2
5  | Google Play| 6   | 11.05.2022   | 0

In addition table contains data about bills from various days (field payment_date) and this priority should be set based on data inside each single day.

Answer

Order the rows for each day and then assign priority based on the row number:

SELECT t.*,
       CASE ROW_NUMBER()
              OVER (PARTITION BY TRUNC(payment_date) ORDER BY sum DESC)
       WHEN 1 THEN 1
       WHEN 2 THEN 1
       WHEN 3 THEN 2
       WHEN 4 THEN 2
       ELSE 0
       END AS priority
FROM   table_name t

Which, for the sample data:

CREATE TABLE table_name (id, store_name, sum, payment_date) AS
SELECT 1, 'Amazon',      10, DATE '2022-05-11' FROM DUAL UNION ALL
SELECT 2, 'Amazon',      20, DATE '2022-05-11' FROM DUAL UNION ALL
SELECT 3, 'Ebay',        15, DATE '2022-05-11' FROM DUAL UNION ALL
SELECT 4, 'Apple Store', 13, DATE '2022-05-11' FROM DUAL UNION ALL
SELECT 5, 'Google Play',  6, DATE '2022-05-11' FROM DUAL;

Outputs:

ID	STORE_NAME	SUM	PAYMENT_DATE	PRIORITY
2	Amazon	20	2022-05-11 00:00:00	1
3	Ebay	15	2022-05-11 00:00:00	1
4	Apple Store	13	2022-05-11 00:00:00	2
1	Amazon	10	2022-05-11 00:00:00	2
5	Google Play	6	2022-05-11 00:00:00	0

db<>fiddle here