SELECT 
     H.Hacker_id,H.name
FROM 
    Hackers H 
JOIN 
    Submissions S ON H.Hacker_id = S.Hacker_id 
JOIN 
    Challenges C ON S.challenge_id = C.challenge_id 
JOIN
    Difficulty D ON C.difficulty_level = D.difficulty_level 
WHERE 
    S.score = D.score
GROUP BY 
    H.hacker_id, H.name
HAVING  
    COUNT(DISTINCT S.challenge_id) > 1
ORDER BY 
    COUNT (DISTINCT S.challenge_id) DESC,
    H.hacker_id ASC;

WITH max_scores AS (
    -- Get the maximum score achieved by each hacker for each challenge
    SELECT hacker_id, challenge_id, MAX(score) AS max_score
    FROM submissions
    GROUP BY hacker_id, challenge_id
),
full_scores AS (
    -- Join with difficulty table to check if the hacker achieved full score
    SELECT ms.hacker_id, h.name, ms.challenge_id
    FROM max_scores ms
    JOIN hackers h ON ms.hacker_id = h.hacker_id
    JOIN challenges c ON ms.challenge_id = c.challenge_id
    JOIN difficulty d ON c.difficulty_level = d.difficulty_level
    WHERE ms.max_score = d.score  -- Full score condition
),
hacker_counts AS (
    -- Count challenges where hackers got full scores
    SELECT hacker_id, name, COUNT(challenge_id) AS challenge_count
    FROM full_scores
    GROUP BY hacker_id, name
)
-- Filter hackers with full scores in more than one challenge
SELECT hacker_id, name
FROM hacker_counts
WHERE challenge_count > 1
ORDER BY challenge_count DESC, hacker_id ASC;