We use cookies to ensure you have the best browsing experience on our website. Please read our cookie policy for more information about how we use cookies.
  1. Prepare
  2. SQL
  3. Basic Join
  4. Challenges
  5. Discussions

Challenges

Sort 1123 Discussions, By:

  • james98
     + 87 comments

    Boy, I learned a lot doing this. Here is my mySQL code, with comments and similar, but not identical to other posts:

    /* these are the columns we want to output */
select c.hacker_id, h.name ,count(c.hacker_id) as c_count

/* this is the join we want to output them from */
from Hackers as h
    inner join Challenges as c on c.hacker_id = h.hacker_id

/* after they have been grouped by hacker */
group by c.hacker_id

/* but we want to be selective about which hackers we output */
/* having is required (instead of where) for filtering on groups */
having 

    /* output anyone with a count that is equal to... */
    c_count = 
        /* the max count that anyone has */
        (SELECT MAX(temp1.cnt)
        from (SELECT COUNT(hacker_id) as cnt
             from Challenges
             group by hacker_id
             order by hacker_id) temp1)

    /* or anyone who's count is in... */
    or c_count in 
        /* the set of counts... */
        (select t.cnt
         from (select count(*) as cnt 
               from challenges
               group by hacker_id) t
         /* who's group of counts... */
         group by t.cnt
         /* has only one element */
         having count(t.cnt) = 1)

/* finally, the order the rows should be output */
order by c_count DESC, c.hacker_id

/* ;) */
;

  • simple_guitar99
     + 24 comments

    MS SQL using WITH

    /* count total submissions of challenges of each user */
WITH data
AS
(
SELECT c.hacker_id as id, h.name as name, count(c.hacker_id) as counter
FROM Hackers h
JOIN Challenges c on c.hacker_id = h.hacker_id
GROUP BY c.hacker_id, h.name
)
/* select records from above */
SELECT id,name,counter
FROM data
WHERE
counter=(SELECT max(counter) FROM data) /*select user that has max count submission*/
OR
counter in (SELECT counter FROM data
GROUP BY counter
HAVING count(counter)=1 ) /*filter out the submission count which is unique*/
ORDER BY counter desc, id

  • adria_escolano
     + 4 comments

    Posting my thought process (and code) in order to solve this. The problem gets way less complicated if you face it from "inside" to "outside":

    We have two constraints: 1. We have to select counter values that have unique scores 2. We have to select conter values that have maximum

    (I name counter to the total number of challenges created by each student)

    So 1st part is to select the counter values per each hacker_id:

    SELECT hacker_id, COUNT(challenge_id) AS counter 
FROM challenges
GROUP BY hacker_id
ORDER BY counter DESC;

    Once we have this clear we shall focus on obtaining the values according to the two constraits.

    1st: selecting the unique counter values

    SELECT aux_table.counter
FROM(
	SELECT hacker_id, COUNT(challenge_id) AS counter 
	FROM challenges
	GROUP BY hacker_id
	ORDER BY counter DESC
) AS aux_table
GROUP BY aux_table.counter /*Grouping by counter scores*/ 
HAVING COUNT(aux_table.counter) = 1; /*We are only selecting those values whose counter score are unique.*/

    2nd: selecting the max counter value

    SELECT MAX(aux_table.counter)
FROM(
	SELECT hacker_id, COUNT(challenge_id) AS counter
	FROM challenges
	GROUP BY hacker_id
	ORDER BY counter DESC
) AS aux_table;

    Once we have these two values we can build the QUERY all together:

    ---/*Building the query all together*/---

/*1st SELECTING THE VALUES requested to be printed*/
SELECT h.hacker_id, h.name, COUNT(c.challenge_id) AS challenge_counter
FROM hackers h
JOIN challenges c
	ON h.hacker_id = c.hacker_id
GROUP BY h.hacker_id, h.name

/*2nd applying the values found before*/

HAVING challenge_counter IN (
	SELECT aux_table.counter
	FROM(
		SELECT hacker_id, COUNT(challenge_id) AS counter 
		FROM challenges
		GROUP BY hacker_id
		ORDER BY counter DESC
	) AS aux_table
	GROUP BY aux_table.counter 
	HAVING COUNT(aux_table.counter) = 1
)
OR
challenge_counter =(
	SELECT MAX(aux_table.counter)
	FROM(
		SELECT hacker_id, COUNT(challenge_id) AS counter
		FROM challenges
		GROUP BY hacker_id
		ORDER BY counter DESC
	) AS aux_table)
/* Finally we order as requested (by counter and hacker_id)*/
ORDER BY challenge_counter DESC, h.hacker_id ASC;

    `

  • maximusx3
     + 9 comments

    It took me an hour to correctly write the query. For people struggling with the logic I advice you to take a paper and pen and analyze how you can seperate the data which is required.

        select a.hacker_id,a.name,count(b.hacker_id)    
    from Hackers a, Challenges b
    WHERE a.hacker_id = b.hacker_id
    GROUP BY a.hacker_id,a.name
    HAVING count(b.hacker_id) not in (select  distinct          count(hacker_id) from Challenges
    WHERE hacker_id <> a.hacker_id
    group by hacker_id
    having count(hacker_id) < (select                           max(x.challenge_count) 
    from (select count(b.challenge_id) as challenge_count                 from Challenges b GROUP BY b.hacker_id) as x ))
    ORDER BY count(b.hacker_id) desc, a.hacker_id

  • sabin774
     + 7 comments

    My solution:

    select c.hacker_id, h.name, count(c.challenge_id) as total from challenges c

inner join hackers h
on h.hacker_id= c.hacker_id

group by c.hacker_id having 

total=
(select  count(c1.challenge_id) from challenges c1
group by c1.hacker_id 
ORDER BY count(c1.challenge_id) desc limit 1)

or 

count(c.challenge_id) in 
(select t1.tot from ((select t.tot, count(t.hacker_id) from (select count(c2.challenge_id) as tot, c2.hacker_id from challenges c2
group by c2.hacker_id) as t
group by t.tot having count(t.hacker_id)=1) as t1))

order by total desc, h.hacker_id;
Load more conversations