You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
54 lines
1.3 KiB
54 lines
1.3 KiB
-- 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;
|