Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE sumprice (
  id NUMBER(5),
  name VARCHAR(4),
  price NUMBER(5),
  email VARCHAR(9),
  user_id NUMBER(5)
);

INSERT INTO sumprice
  (id, name, price, email, user_id)
VALUES
  ('1', 'Mike', '100', '@mike.com', '5');





1 rows affected

INSERT INTO sumprice
  (id, name, price, email, user_id)
VALUES('2', 'John', '50', '@john.com', '6');
1 rows affected

INSERT INTO sumprice
  (id, name, price, email, user_id)
VALUES  ('3', 'Mike', '100', '@mike.com', '5');
1 rows affected

INSERT INTO sumprice
  (id, name, price, email, user_id)
VALUES  ('4', 'John', '50', '@john.com', '1');
1 rows affected

INSERT INTO sumprice
  (id, name, price, email, user_id)
VALUES  ('5', 'Mark', '50', '@mark.com', '3');
1 rows affected

INSERT INTO sumprice
  (id, name, price, email, user_id)
VALUES  ('6', 'Mike', '100', '@mike.com', '5');
1 rows affected

SELECT     ROW_NUMBER() OVER(
        ORDER BY SUM(price) DESC
    ) id ,name, SUM(price) price , email, MAX(user_id)
FROM sumprice
GROUP BY email, name
ORDER BY price DESC


ID
NAME
PRICE
EMAIL
MAX(USER_ID)


1
Mike
300
@mike.com
5

2
John
100
@john.com
6

3
Mark
50
@mark.com
3