Oracle 11g Release 2

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

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
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;
5 rows affected

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


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

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