Oracle Error: SELECT list inconsistent with GROUP BY-CodePudding

I'm new to SQL and I'm trying to understand this challenge: SQL Contest

I found this solution in the challenge's discussion thread:

SELECT submission_date, 
(SELECT COUNT(distinct hacker_id)  
    FROM Submissions s2  
    WHERE s2.submission_date = s1.submission_date 
        AND (SELECT COUNT(distinct s3.submission_date) 
                FROM Submissions s3 WHERE s3.hacker_id = s2.hacker_id AND s3.submission_date < s1.submission_date) 
                  = (s1.submission_date - TO_DATE('2016-03-01'))),
(SELECT hacker_id from submissions s2 where s2.submission_date = s1.submission_date 
    GROUP BY hacker_id 
    ORDER BY count(submission_id) desc
    FETCH FIRST 1 ROW ONLY) as shit,
(SELECT hacker_name from hackers where hacker_id = shit)
FROM 
(SELECT distinct submission_date from submissions) s1
group by submission_date;

As the title states, I get the error that "SELECT list inconsistent with GROUP BY" which I don't understand. And if I try to run the script I get this error that I also don't understand:

Error at Command Line : 12 Column : 56

Error report - SQL Error:

ORA-00904: "SHIT": invalid identifier

00000 - "%s: invalid identifier"

*Cause:

*Action:

CodePudding user response：

Without tables, we can't run that code.

Anyway, error you mentioned is raised in this line:

(SELECT hacker_name from hackers where hacker_id = shit)
                                                   ----

because you can't reference a column which is part of the same select statement. You could, for example, use a subquery or a CTE to collect shit and then reuse it afterwards. Something like this (simplified):

with 
-- you need S1 because you use it in A_CTE (see the "s2 join s1" part)
s1 as
  (SELECT distinct submission_date from submissions),
a_cte as
  (SELECT hacker_id as shit
   from submissions s2 join s1 on s2.submission_date = s1.submission_date   --> S1 is used here
   GROUP BY hacker_id 
   ORDER BY count(submission_id) desc
   FETCH FIRST 1 ROW ONLY
  )
-- finally, you can now join HACKERS to A_CTE and use that SHIT
select hacker_name
from hackers h join a_cte on h.hacker_id = a.shit