0

I am doing a practice SQL questions Hacker Rank. here is the website https://www.hackerrank.com/challenges/challenges/problem. So, If put the following code, it will give me right answer:

select h.hacker_id,h.name,count(c.challenge_id) num
from Hackers h, Challenges c
where h.hacker_id = c.hacker_id
group by h.hacker_id, h.name
having count(c.challenge_id) in
(8 ,9 ,10 ,14 ,15 ,16 ,18 ,19 ,20 ,21 ,22 ,23 ,24 ,25 ,
26 ,27 ,28 ,29 ,30 ,31 ,32 ,33 ,34 ,35 ,36 ,37 ,40 ,41 ,42 ,50)
order by num desc, h.hacker_id

But the number

(8 ,9 ,10 ,14 ,15 ,16 ,18 ,19 ,20 ,21 ,22 ,23 ,24 ,25 ,
26 ,27 ,28 ,29 ,30 ,31 ,32 ,33 ,34 ,35 ,36 ,37 ,40 ,41 ,42 ,50)

is actually generate by this following code:

select t1.num2  from
(select hacker_id id_1, count(challenge_id) num2 from challenges
group by hacker_id
order by num2 desc) t1
group by t1.num2
having count(t1.num2)=1 or count(t1.num2) = 12

If you replace the number with the code above as a subquery, like:

select h.hacker_id,h.name,count(c.challenge_id) num
from Hackers h, Challenges c
where h.hacker_id = c.hacker_id
group by h.hacker_id, h.name
having count(c.challenge_id) in
(select t1.num2  from
(select hacker_id id_1, count(challenge_id) num2 from challenges
group by hacker_id
order by num2 desc) t1
group by t1.num2
having count(t1.num2)=1 or count(t1.num2) = 12)

It simply will not return any values. Could anyone tell me why is this?

J.Wu
  • 21
  • 2
  • I think this [link](https://stackoverflow.com/questions/1406215/sql-select-where-not-in-subquery-returns-no-results) can help you. – sepideh_ssh Jan 14 '21 at 21:11
  • What was the question in hackerrank? And could you add the sample data given for this particular problem? At a glance, the first query is correct because you're just filtering the final result of the `count()` values in `HAVING`. The main question is how to get the final `count()` without filtering it in `HAVING`. – FanoFN Jan 15 '21 at 01:45
  • https://stackoverflow.com/questions/54857386/meaning-of-this-line-having-c2-hacker-id-c-hacker-id This is the website I found. – J.Wu Jan 15 '21 at 18:04
  • I did this challenge previously and got it solved but I couldn't remember how. I'll check it out and provide some information afterwards – FanoFN Jan 16 '21 at 06:06

1 Answers1

1

I discovered that if you change

...
having count(c.challenge_id) in
(select t1.num2  from ...

TO

...
having count(c.challenge_id) in
(select GROUP_CONCAT(t1.num2) from ...

OR

...
having num in
(select t1.num2 from ...

/*with "having", you're allowed to use aliases that you've assigned in "SELECT". 
In your case, you've already assigned "count(c.challenge_id) num" so you can use "num" in HAVING.*/

This is my first time encountering such thing and I'm still looking if there's any documentation about this behaviour.. which I'm unsure if there's any.

FanoFN
  • 6,815
  • 2
  • 13
  • 33