SELECT
    m.id,
    m.age,
    m.coins_needed,
    m.power
FROM (SELECT a.id, b.age, a.coins_needed, a.power, b.is_evil
    FROM wands a
    JOIN wands_property b
    ON a.code=b.code) m
JOIN (
    SELECT min(a1.coins_needed) as min_coin, a1.power, b1.age
    FROM wands a1
    JOIN wands_property b1
    ON a1.code=b1.code
    WHERE b1.is_evil = 0
    GROUP BY a1.power, b1.age
) n
WHERE n.power=m.power
AND n.age=m.age
AND n.min_coin=m.coins_needed
ORDER BY m.power DESC, m.age DESC;

SELECT m.hacker_id, m.name, m.challenges_created FROM (
    SELECT 
    h.hacker_id,
    h.name,
    count(*) as challenges_created
FROM hackers h
    JOIN challenges c
    ON h.hacker_id=c.hacker_id
    GROUP BY h.hacker_id, h.name
) m
WHERE m.challenges_created = (
    SELECT max(tmp.cnt) FROM (
        (
        SELECT 
            count(*) as cnt
        FROM hackers h1
            JOIN challenges c1
            ON h1.hacker_id=c1.hacker_id
        GROUP BY h1.hacker_id
        ) tmp
    )
) OR m.challenges_created IN (
    SELECT c_compare AS c_unique
     FROM (SELECT h2.hacker_id, 
                  h2.name, 
                  COUNT(challenge_id) AS c_compare
           FROM Hackers h2
           JOIN Challenges c3 ON c3.hacker_id = h2.hacker_id
           GROUP BY h2.hacker_id, h2.name) counts
     GROUP BY c_compare
     HAVING COUNT(c_compare) = 1)
ORDER BY m.challenges_created DESC, m.hacker_id;

SELECT h.hacker_id, 
       h.name, 
       COUNT(c.challenge_id) AS c_count
FROM Hackers h
JOIN Challenges c ON c.hacker_id = h.hacker_id
GROUP BY h.hacker_id, h.name
HAVING c_count = 
    (SELECT COUNT(c2.challenge_id) AS c_max
     FROM challenges as c2 
     GROUP BY c2.hacker_id 
     ORDER BY c_max DESC limit 1)
OR c_count IN 
    (SELECT c_compare AS c_unique
     FROM (SELECT h2.hacker_id, 
                  h2.name, 
                  COUNT(challenge_id) AS c_compare
           FROM Hackers h2
           JOIN Challenges c3 ON c3.hacker_id = h2.hacker_id
           GROUP BY h2.hacker_id, h2.name) counts
     GROUP BY c_compare
     HAVING COUNT(c_compare) = 1)
ORDER BY c_count DESC, h.hacker_id;

SELECT hackers.hacker_id, hackers.name, total_score FROM hackers
JOIN (SELECT hacker_id, SUM(max_score) as total_score FROM (
    SELECT hacker_id, challenge_id, MAX(score) as max_score FROM submissions s1
    GROUP BY hacker_id, challenge_id
) tmp
GROUP BY hacker_id) tmp2
ON tmp2.hacker_id=hackers.hacker_id
WHERE total_score > 0
ORDER BY total_score DESC, hacker_id;

SELECT m.name FROM (
    SELECT s.id, s.name, p.salary from students s
    JOIN packages p
    ON s.id=p.id
    JOIN (
        SELECT f1.id, p1.salary as friends_salary from packages p1
        JOIN friends f1
        ON f1.friend_id=p1.id
    ) tmp
    ON s.id=tmp.id
    WHERE tmp.friends_salary > p.salary
    ORDER BY friends_salary
) m

SELECT 
    n.contest_id,
    n.hacker_id,
    n.name,
    SUM(m.sum_total_views,)
    SUM(m.sum_unique_views,)
    SUM(m.sum_submissions,)
    SUM(m.sum_accepted_submissions)
FROM (
    SELECT 
        college_id,
        SUM(total_views) as sum_total_views,
        SUM(total_unique_views) as sum_unique_views,
        SUM(total_submissions) as sum_submissions,
        SUM(total_accepted_submissions) as sum_accepted_submissions
    FROM challenges cha
    JOIN view_stats vs
    ON vs.challenge_id=cha.challenge_id
    JOIN submission_stats ss
    ON ss.challenge_id=cha.challenge_id
    GROUP BY cha.college_id
    HAVING
        sum_total_views > 0
        OR sum_unique_views >0
        OR sum_submissions > 0
        OR sum_accepted_submissions > 0
) m
JOIN (
    SELECT 
        cont.contest_id,
        cont.hacker_id,
        cont.name,
        coll.college_id
    FROM contests cont
    JOIN colleges coll
    ON coll.contest_id=cont.contest_id
) n
ON m.college_id=n.college_id
GROUP BY contest_id
ORDER BY contest_id
;