Do you have a complex SQL query you need to write, but have no idea how to do it or how
to get started?
This is the video for you.
In this video, we’ll write a query to solve an SQL problem. We start with the description
of the problem, see the sample data, and slowly write the query until we get to the correct
result.
You can use this process to write your own complex SQL queries.
Welcome to the Database Star YouTube channel - the place for developers looking to improve
their database and SQL skills.
For this video, we’ll use an SQL problem on the Hacker Rank website, which is a website
that has all kinds of coding challenges.
I’ve put the link to this specific challenge in the description below, if you want to follow
along.
This challenge is called Top Competitors.
We can see a description at the top here. It says that Julia has finished a coding contest
and needs to construct a leaderboard. We need to write a query that shows the hacker ID
and the name of the hackers who achieved full scores for more than one challenge.
There’s a sentence here about how to order the data, and what happens if there is more
than one match.
Further down the page we have information on each of the tables, including the table
name, column names, and data types.
In this challenge there are four tables: hackers, difficulty, challenges, and submissions.
Just below this on the page is some sample data. This is very useful, and it’s what
we will be using for our queries. We’ve got sample data for all of the four tables.
Next, we have the sample output, which is what our expected output should show.
The explanation shows why we have this expected output. We’ll refer to this sample output
and explanation as we work through our query.
So, where do we start?
The first step I would suggest is to create the tables and add the sample data, so we
have something to work with.
We’ll do this in MySQL, but the data and concepts will work in other databases such
as Oracle, SQL Server, and Postgres.
I’ll also add a link in the description to the SQL script to create the tables and
insert the sample data, so you don’t have to type all of this out by hand.
Let’s create our tables.
First, we create the hackers table. We use a Create Table statement, and add two columns.
One is the hacker ID, which according to the challenge page is an integer. In MySQL we’ll
use the INT data type.
The second column is the name, which is a string according to the challenge page. We’ll
use the VARCHAR data type for this.
We could add a primary key to this table, but for the purposes of this challenge I don’t
think it’s needed.
We can run this statement and the table is created.
We’ll do the same thing for the next three tables. I’ve added the SQL for the three
tables here, which are difficulty, challenges, and submissions. Run all of these statements
and the tables are created.
Next, we can insert the sample data. The good news is that there is some sample data on
the Hacker Rank challenge page. The bad news is that it’s in an image format, which means
I can’t copy and paste it into this script.
However, I’ll type it out manually from this page, and then include this in the SQL
script linked in the description. So, you don’t need to type it out. You can just
get the script.
We insert the sample data using a series of Insert statements. I’ve written the statements
here for all four tables.
We can run the statements and the sample data is inserted.
Now we have our data, what do we do first?
Let’s have a look at our challenge again.
The challenge says, “Write a query to print the respective *hacker_id* and *name* of
hackers who achieved full scores for *more than one* challenge”.
So, it looks like we need to show information about hackers that meet certain criteria.
It says hacker ID and name, both of which are in the hacker table.
Let’s start there.
We start with a simple Select query on the hackers table. We want to see the hacker ID
and the name. We won’t add any filters to the query at the moment.
We can run the query and see the results here. This shows all records in the table.
What can we do next? We want to find out the scores that hackers received for challenges,
so let’s look at how we can show the scores that hackers have received.
If we look at the problem again, we want to find tables that we can link to the hacker
table. The difficulty table doesn’t have any reference to hackers, so let’s move
on.
The challenges table has a hacker ID. Based on the description earlier on the page, this
is the hacker that created the challenge. We don’t want to see hackers that created
challenges at the moment.
The submissions table shows the submissions for a challenge. It has a hacker ID, which
is good. It has the score, and the challenge ID of the submission. This is what we want.
Let’s join to this table from our query, and show all of the fields. I think it’s
helpful to show all fields at the start, then remove those that we don’t need.
Back in our query, we can add an INNER JOIN to the query, and join to this submissions
table. We’ll use an inner join because we don’t need to see hackers that don’t have
any submissions.
We join on the hacker ID column.
Now that we have two tables, I’ll add table aliases. I find these useful when writing
queries as its easier to write and understand the queries.
So, we have an alias for each table. Now we’ll add in all of the columns from the submissions
table.
We can run the query, and see the results here.
What do we do next?
We need to find out the top score available for each challenge.
Based on our tables, this information is stored in the score column in the difficulty table.
It looks like each challenge has a difficulty rating, and the difficulty rating determines
the top score.
So, we need to join to the challenge table first, because a submission is related to
a challenge. We can then join to the difficulty table to get the score.
How do we join to challenges?
The challenges table has a hacker ID column and a challenge ID column. The hacker ID is
the person that created the challenge, which we are not interested in.
The challenge ID is the unique ID of the challenge. We have a challenge ID in the submissions
table, in our query, that determines the challenge the submission is related to.
So let’s join to the challenges table based on that.
We can update our query to join to the challenges table, joining on the challenge ID in both
the submission and challenge tables.
We also add the challenge ID and the difficulty level from the challenge table into the Select
clause.
Let’s run this query. We can see the results here. We have the challenge ID, and the difficulty
level of the challenge.
Now that we have joined to the challenges table, the next step is to join to the difficulty
table, so we can get the top score for a challenge.
We can do this based on the difficulty level value. We join to the difficulty table, and
join on the difficulty_level column in both the challenges and difficulty table.
In the Select clause, we want to see the difficulty level and the score from the difficulty table.
We can run the query and see these results.
The results show the difficulty level on the right, as well as the score.
But they both have the heading of score. How can we tell the difference?
We can add a column alias to our query. Back in our query, we add the word AS after s.score,
and specify a new heading of submission_score. We do the same thing for the second score
column, and we will call this one challenge_score.
Now we can run the query and see the same results. The only difference is that the headings
are easier to understand.
How do we find the submissions that have the top score? We check that both of these score
values are the same.
We can have a look through the results and see that there are some that match but most
do not.
Let’s add a WHERE clause to our query. We want to say where s.score is equal to d.score.
This should only show us rows where the submission score matches the challenge score, therefore
making the submission a top score.
We can run the query and see the results.
We have three rows here. One for a hacker name of Todd and two for a hacker name of
Joe.
Back in our query, you might be wondering why I added a WHERE clause with the two fields
being in different tables. If we are matching on two fields in different tables, shouldn’t
they go in the FROM clause as a join condition?
You could do that. You could add this in the FROM clause, and you would get the same results.
However, the reason I added it to the WHERE clause comes down to the intent of these two
clauses. The FROM clause and the joins are used to relate two records in two tables based
on some kind of identifying value. We can see this for the hacker ID, challenge ID,
and difficulty level columns. They all are used to identify a row.
The WHERE clause, however, is used to filter data once the table has been related. In this
example, we want to see records where the submissions’s score matches the maximum
possible score. I see this as a criteria on what rows to show, which is what the WHERE
clause does. I don’t think this is a way to uniquely identify rows, so I didn’t put
it in the FROM clause.
So we’ve got three rows here, which show information about the hackers, submission,
challenges, and difficulty. We added all of those columns to help us see information about
the rows we want to show.
Now we have found the correct rows, we need to update the query based on our problem and
what we need to see.
Our problem says we should show the hacker ID and the name, and only show those where
a full score was received on two or more challenges.
How can we do that with our query?
We can use the COUNT function to count the number of records. However, we don’t want
to count all of the rows. We want to count the number of submissions for each hacker.
We want to see a count of 1 for Todd and a count of 2 for Joe.
So, we update our Select clause to remove most of the columns. We leave the hacker ID
and the name.
After the name, we add a COUNT(*) function. It doesn’t matter for this query whether
we count * or count the hacker ID, as they are both counting a single row.
Once we add in the COUNT function, we have a mix of aggregate functions and regular columns.
This means we need to add a group by, to group the data on all of the columns that are not
aggregate functions.
So, add a Group By clause, and then the hacker ID and the name.
We can run this query and see the results. We have the hacker ID, the name, and a count
of 1 for Todd and 2 for Joe.
We’re getting close!
Our problem says we only want to see hackers who have two or more submissions with a full
score. We can see here that Todd has 1 and Joe has 2. We need to filter Todd’s record
out of the results as he only has one.
Let’s do that now.
To filter data, we can use a WHERE clause.
Add a WHERE clause to filter data where the count is greater than 1. We can add the AND
keyword because we already have the WHERE keyword.
Let’s run this query.
We can see no results are shown, and in MySQL Workbench we have an error at the bottom of
the screen.
It says: Error code 1111, invalid use of group function. You’ll see similar errors in other
databases.
This error happens because we can’t use a WHERE clause on an aggregate function that
we are grouping on. This is because the WHERE is processed before the GROUP BY, so the query
can’t filter on something it doesn’t know about yet.
Fortunately, there is a way to do this. We use the HAVING keyword. The Having keyword
is specifically used to filter on the results of a query after a group by is applied.
So, we can change our query to use HAVING instead of the AND keyword or the WHERE keyword.
Now we run the query, and it works. We can see the results here, which only shows one
row.
This looks good so far!
Let’s go back to our problem to see if we need to do anything else.
The second half of the problem says how we need to order our results. Even though we
only have one result, we should still order it. The problem says we need to order by the
total number of challenges that the hacker got a full score in descending order, then
order by hacker ID in ascending order.
In our query, we can implement this by adding an Order By clause. We specify Order By Count
in descending order, then order by hacker ID in ascending order.
We can run the query and see the same results. This means we didn’t break the query.
Finally, our expected output shows a single row. It shows the hacker ID and the name,
but it doesn’t show the count. We have the count in our query, so we should remove it.
Remove the COUNT function from the Select clause as we don’t need to see it.
The rest of the query should still include the Count function. And the query will still
work. We don't need to display the count function in order to use it in the Having clause or
the Order By clause.
Let’s run this query to confirm.
We can see the result here. The count function is not shown, and our result matches the expected
output.
We’re all done! We got the correct result using the sample data, and our query works.
To get to this query, we started small and simple. That’s the best piece of advice
I can give you if you need to write your own complex query. We started with a single table,
then joined to other tables one at a time.
We added columns into the Select clause, even though we didn’t need to, just to see the
data we were working with.
Once we had all of the columns and tables, we started adding filters to the Where clause
to exclude rows we didn’t need.
Once we felt we had the right rows to work on, we then used the count function and group
by to collapse them into the format that we needed, which was one row per hacker and not
one row per submission.
We added some more filtering on the grouped rows using Having, then added the Order By
at the end.
So, if you need to write your own complex query, start small, check your query and results
each time you run it, and add more complexity and requirements as you go.
I hope you liked this video and found it useful. It’s the first of a new style of video I’ve
created, so let me know in the comments what you think of it and if you want more examples
of me writing a complex query.
I’ve created another video recently on how to start with a complex SQL query and understand
what it is doing, and you can watch that video here.
If you want to learn more about database design and SQL, visit my website at databasestar.com
If you liked this video, consider subscribing to my channel.
Thanks for watching.