Can I use MAX(COUNT()) in SQL?

I came across an interesting SQL challenge that looked easy first and then proved to be a bit tricker than expected.

And the short answer to the above question is, no. You can't. It is not possible to nest aggregate functions.

So, I have a list with all the guests that appeared on the DailyShow from 1999 - 2015. The dataset includes the date/year of the show, guest name, and their occupation.

The question is:

What was the most popular profession of guest for each year Jon Stewart hosted the Daily Show?

This is the table schema:

First, let's find out the most common occupation over ALL the years:

Easy enough. The result:

So far so good. Now, how do we get this result by year?

To start, let's get the occupation count by year for each profession:

The result is the occupation count for each occupation for each year:

Now how do we limit these results for each year to the highest occupation count?

We'd expect to use MAX on COUNT(occupation) and add the year to the GROUP BY. Something like this:

Uh-oh.

Hm, another try is to use a WHERE clause:

Nope.

So how can we find an alternative to the MAX(COUNT()) that we are dreaming of?

The solution is to use the first table as a subquery. We will create an additional query, an outer query, which uses the first table in its FROM clause. It will be able to use MAX() on the COUNT() result from the first table, thus circumventing the direct use of two layered aggregate functions.

1./ Inner query > Reminder, this is how it looked like:

2./ Outer Query > Now we are creating an ALIAS "occupation_count" for COUNT(occupation) column in the inner query so that we can reference it from the outer query.

And voila, there we have it:

The problem was that it is not allowed to use two aggregate functions in the SELECT statement. The solution is to create a table to get the results from the inner function first, and then use this table for calling the second function on the respective column.

Data can be found on github:

https://github.com/qtotuan/bonus-sql-challenge-web-051517