SQL Advent Calendar Day 23

Today’s question will help us analyze top gingerbread house builders.

Photo by Stephanie Klepacki on Unsplash

Here is today’s problem: The Gingerbread House Competition wants to feature the top 3 builders who used the most distinct candy types in their designs. How would you find the builders with the highest count of unique candies, and return only the top three?

For this question, we are given the gingerbread designs datasets and its first few rows look like this:

gingerbread_designs data with the fields builder_id, candy_type, and builder_name

We want to rank 3 distinct builders where the count of candy type is highest. We need to do a distinct count of candy types as we want not only who used the most candy (someone could have used gumdrops twice like Clara Claus did above) but the highest number of different candy types. Then we need to group them by builder and rank the top 3. And we also need to make sure, as per the question, that these builders used at least 4 different types of candy.

Get S the Data Nerd’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

Here is what my query looked like for this problem:

SELECT builder_name, COUNT(DISTINCT candy_type) as candy_count
FROM gingerbread_designs
GROUP BY builder_name
HAVING candy_count >= 4
ORDER BY candy_count DESC
LIMIT 3;

Notice how I used HAVING instead of WHERE, it is important for queries where you are aggregating. Here is what the query returned:

We can see that the query would have returned these top 3 individuals without the HAVING clause, but it was still important to add it to show that we fully understood what the question entailed.