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 |