I got a job on sql at the interview:

Get the name and the number of times it was repeated, but display only those names that were repeated more than 2 times - sorting by the number of repetitions, from major to minor.

My decision was:

select name, count(name) from table group by name having count(name) > 2 order by count(name) desc;

The interviewer's feedback was: the request is "almost" correct .

Can someone suggest how to change or improve it?

  • Everything is correct in the answer. "Improvements" that are described in the responses are not significant. If your potential employer finds fault with such things - I do not advise you to go under it. - pegoopik

2 answers 2

ORDER BY is executed after SELECT , therefore count(name) in it is IMHO unnecessary:

 select name, count(name) AS num from table group by name having count(name) > 2 order by num desc;

In addition, the count(name) field count(name) does not look very nice. Could this also be “almost” correct ?

  • The phrase is almost correctly strange - the result will be correct, the rest is beautiful. - Denis
  • @Denis, this is from the question, who knows what the interviewer considers "almost" correct - Mirdin
  • @Mirdin Thanks for the reply. - Aleksei
  • Yeah, and you can also add upper (name) in group by, suddenly collation is case sensitive, cutting off the initial spaces, removing the “noise” from the name before grouping, and much more. Another formatting might not like it or the fact that the keywords are in lower case. All this is irrelevant. - pegoopik

I would write so as not to calculate the count three times

 select name, count(name) from table group by name having count(name) > 2 order by 2 desc;

Or through aliases

 select name, count(name) cnt from table group by name having count(name) > 2 order by cnt desc;
  • then having count (name)> 2 can be replaced by having count (cnt)> 2 - koks_rs
  • @koks_rs, no, it’s impossible, having executed before select - Mirdin
  • @Mirdin, can not be because the performance will decrease or an error? - koks_rs
  • @koks_rs, the error - cnt does not yet exist at this moment - Mirdin
  • @Mirdin, strange for me in mysql everything works, and for half a million lines the difference in time is within the margin of error - koks_rs