-- Create the HACKERS table
CREATE TABLE HACKERS
(
    hacker_id INT PRIMARY KEY,
    name      VARCHAR(255) NOT NULL
);

-- Create the SUBMISSIONS table
CREATE TABLE SUBMISSIONS
(
    submission_id INT PRIMARY KEY,
    hacker_id     INT,
    challenge_id  INT,
    score         INT,
    FOREIGN KEY (hacker_id) REFERENCES HACKERS (hacker_id)
);

-- Optional: Insert some sample data into HACKERS
INSERT INTO HACKERS (hacker_id, name)
VALUES (1, 'Alice'),
       (2, 'Bob'),
       (3, 'Charlie');

-- Optional: Insert some sample data into SUBMISSIONS
INSERT INTO SUBMISSIONS (submission_id, hacker_id, challenge_id, score)
VALUES (101, 1, 1001, 95),
       (102, 1, 1002, 90),
       (103, 2, 1001, 85),
       (104, 3, 1003, 80);

-- Select data to verify the setup
SELECT *
FROM HACKERS;
SELECT *
FROM SUBMISSIONS;


SELECT H.hacker_id,
       H.name,
       HTS.total_score
FROM HACKERS H
         JOIN
     (SELECT MaxScores.hacker_id,
             SUM(MaxScores.max_score) AS total_score
      FROM (SELECT S.hacker_id,
                   MAX(S.score) AS max_score
            FROM SUBMISSIONS S
            GROUP BY S.hacker_id,
                     S.challenge_id) AS MaxScores
      GROUP BY MaxScores.hacker_id
      HAVING SUM(MaxScores.max_score) > 0) AS HTS ON H.hacker_id = HTS.hacker_id
ORDER BY HTS.total_score DESC,
         H.hacker_id;