title: Hacker Rank Question SQL Advanced Mysql query Incorrect syntax (only full group by mode)
body: <p>Question:</p>
  <blockquote>
  <p>Julia conducted a 15 days of learning SQL contest. The start date of the contest was March 01, 2016 and the end date was March 15, 2016.</p>
  <p>Write a query to print total number of unique hackers who made at least submission each day (starting on the first day of the contest), and find the <code>hacker_id</code> and name of the hacker who made maximum number of submissions each day. If more than one such hacker has a maximum number of submissions, print the lowest <code>hacker_id</code>. The query should print this information for each day of the contest, sorted by the date.</p>
  <p><strong>Input Format</strong><br />
  The following tables hold contest data:</p>
  <ul>
  <li><code>Hackers</code>: The <code>hacker_id</code> is the id of the hacker, and <code>name</code> is the name of the hacker.</li>
  <li><code>Submissions</code>: The <code>submission_date</code> is the date of the submission, <code>submission_id</code> is the id of the submission, <code>hacker_id</code> is the id of the hacker who made the submission, and <code>score</code> is the score of the submission.</li>
  </ul>
  <p><strong>Sample Input</strong><br />
  For the following sample input, assume that the end date of the contest was March 06, 2016.</p>
  <p><a href="https://i.stack.imgur.com/nDSl9.png" rel="nofollow noreferrer">Hackers Table</a><br />
  <a href="https://i.stack.imgur.com/crpb3.png" rel="nofollow noreferrer">Submissions Table</a></p>
  <p><strong>Sample Output</strong></p>
  <pre class="lang-none prettyprint-override"><code>2016-03-01 4 20703 Angela
  2016-03-02 2 79722 Michael
  2016-03-03 2 20703 Angela
  2016-03-04 2 20703 Angela
  2016-03-05 1 36396 Frank
  2016-03-06 1 20703 Angela
  </code></pre>
  </blockquote>
  <p>My query so far is following:</p>
  <pre class="lang-sql prettyprint-override"><code>SELECT DISTINCT
    @s := submission_date,
    COUNT(DISTINCT hacker_id),
    @h := (SELECT hacker_id
           FROM (SELECT DISTINCT
                   hacker_id,
                   @ch := COUNT(hacker_id)
                 FROM Submissions
                 WHERE submission_date=@s
                 ORDER BY @ch
                 GROUP BY hacker_id DESC) x
           LIMIT 1),
    @n:=(SELECT name FROM Hackers WHERE hacker_id=@h)
  FROM
    Submissions
  WHERE 1;
  </code></pre>
  <p>Well this query fails 'SQL sytax error'{MYSQL}, however without <code>GROUP BY</code> this query is rejected as only-full-group-by mode is activated.</p>
  <p>Error Message is:</p>
  <blockquote>
  <p>ERROR 1064 (42000) at line 5: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GROUP BY hacker_id DESC) x LIMIT 1),@n:=(SELECT name FROM Hackers WHERE hacker_i' at line 1</p>
  </blockquote>