Julia asked her students to create some coding challenges. Write a query to print the hacker_id, name, and the total number of challenges created by each student. Sort your results by the total number of challenges in descending order. If more than one student created the same number of challenges, then sort the result by hacker_id. If more than one student created the same number of challenges and the count is less than the maximum number of challenges created, then exclude those students from the result.Input FormatThe following tables contain challenge data:Hackers: The hacker_id is the id of the hacker, and name is the name of the hacker. Challenges: The challenge_id is the id of the challenge, and hacker_id is the id of the student who created the challenge.
Question
Julia asked her students to create some coding challenges. Write a query to print the hacker_id, name, and the total number of challenges created by each student. Sort your results by the total number of challenges in descending order. If more than one student created the same number of challenges, then sort the result by hacker_id. If more than one student created the same number of challenges and the count is less than the maximum number of challenges created, then exclude those students from the result.Input FormatThe following tables contain challenge data:Hackers: The hacker_id is the id of the hacker, and name is the name of the hacker. Challenges: The challenge_id is the id of the challenge, and hacker_id is the id of the student who created the challenge.
Solution
Sure, here is the SQL query that you need:
SELECT h.hacker_id, h.name, COUNT(c.challenge_id) as num_challenges
FROM Hackers h
JOIN Challenges c ON h.hacker_id = c.hacker_id
GROUP BY h.hacker_id, h.name
HAVING num_challenges = (
SELECT MAX(challenge_count)
FROM (
SELECT hacker_id, COUNT(challenge_id) as challenge_count
FROM Challenges
GROUP BY hacker_id
) sub_query
) OR num_challenges NOT IN (
SELECT challenge_count
FROM (
SELECT hacker_id, COUNT(challenge_id) as challenge_count
FROM Challenges
GROUP BY hacker_id
) sub_query
GROUP BY challenge_count
HAVING COUNT(hacker_id) > 1
)
ORDER BY num_challenges DESC, h.hacker_id;
This query first joins the Hackers and Challenges tables on the hacker_id field. It then groups the results by hacker_id and name, and counts the number of challenges for each hacker. The HAVING clause is used to filter out hackers who have created a number of challenges that is either equal to the maximum number of challenges created by any hacker, or not a count that is shared by multiple hackers. The results are then ordered by the number of challenges in descending order, and by hacker_id in ascending order.
Similar Questions
You did such a great job helping Julia with her last coding contest challenge that she wants you to work on this one, too!The total score of a hacker is the sum of their maximum scores for all of the challenges. Write a query to print the hacker_id, name, and total score of the hackers ordered by the descending score. If more than one hacker achieved the same total score, then sort the result by ascending hacker_id. Exclude all hackers with a total score of from your result.Input FormatThe following tables contain contest data:Hackers: The hacker_id is the id of the hacker, and name is the name of the hacker. Submissions: The submission_id is the id of the submission, hacker_id is the id of the hacker who made the submission, challenge_id is the id of the challenge for which the submission belongs to, and score is the score of the submission. Sample InputHackers Table: Submissions Table: Sample Output4071 Rose 19174842 Lisa 17484072 Bonnie 1004806 Angela 8926071 Frank 8580305 Kimberly 6749438 Patrick 43ExplanationHacker 4071 submitted solutions for challenges 19797 and 49593, so the total score .Hacker 74842 submitted solutions for challenges 19797 and 63132, so the total score Hacker 84072 submitted solutions for challenges 49593 and 63132, so the total score .The total scores for hackers 4806, 26071, 80305, and 49438 can be similarly calculated.
Julia just finished conducting a coding contest, and she needs your help assembling the leaderboard! Write a query to print the respective hacker_id and name of hackers who achieved full scores for more than one challenge. Order your output in descending order by the total number of challenges in which the hacker earned a full score. If more than one hacker received full scores in same number of challenges, then sort them by ascending hacker_id.Input FormatThe following tables contain contest data:Hackers: The hacker_id is the id of the hacker, and name is the name of the hacker. Difficulty: The difficult_level is the level of difficulty of the challenge, and score is the score of the challenge for the difficulty level. Challenges: The challenge_id is the id of the challenge, the hacker_id is the id of the hacker who created the challenge, and difficulty_level is the level of difficulty of the challenge. Submissions: The submission_id is the id of the submission, hacker_id is the id of the hacker who made the submission, challenge_id is the id of the challenge that the submission belongs to, and score is the score of the submission. Sample InputHackers Table: Difficulty Table: Challenges Table: Submissions Table: Sample Output
ou are given two tables: Students and Grades. Students contains three columns ID, Name and Marks.Grades contains the following data:Ketty gives Eve a task to generate a report containing three columns: Name, Grade and Mark. Ketty doesn't want the NAMES of those students who received a grade lower than 8. The report must be in descending order by grade -- i.e. higher grades are entered first. If there is more than one student with the same grade (8-10) assigned to them, order those particular students by their name alphabetically. Finally, if the grade is lower than 8, use "NULL" as their name and list them by their grades in descending order. If there is more than one student with the same grade (1-7) assigned to them, order those particular students by their marks in ascending order.Write a query to help Eve.Sample InputSample OutputMaria 10 99Jane 9 81Julia 9 88 Scarlet 8 78NULL 7 63NULL 7 68
select h.hacker_id,h.name,sum(max(s.score)) from hackers h JOIN submissions s ON h.hacker_id=s.hacker_idgroup by h.hacker_idhaving sum(max(s.score)>0 order by sum(max(s.score) desc,h.hacker_id;
Write a query to display the student qualification(squal) and the total number of students based on their qualifications. Give an alias name as ‘totalStud’. Display only the squal having the totalStud count less than 8. Display the records sorted in ascending order based on squal.
Upgrade your grade with Knowee
Get personalized homework help. Review tough concepts in more detail, or go deeper into your topic by exploring other relevant questions.