Average Population of Each Continent | SQL Basic Join

Lesson 40

Courses / HackerRank SQL Problem Solving Solution Challenges

Average Population of Each Continent | SQL Basic Join | HackerRank Solution

01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46

Summary

## Summary

In this exercise, the presenter demonstrates how to calculate the average population of cities for each continent using SQL.

### Steps Taken:

1. **Combine Tables**: The presenter discusses combining the `city` and `country` tables to access continent names.
2. **Select Statement**: They start writing a SQL query using `SELECT` and define the `INNER JOIN` between `city` and `country` tables based on matching country codes.
3. **Select Columns**: They specify `country.continent` to get the continent's name and `city.population` for city populations.
4. **Initial Query Result**: The initial query results in multiple rows for each continent due to different cities.
5. **Group By Clause**: To aggregate the data, a `GROUP BY` clause is added to group the results by continent.
6. **Calculate Average**: The presenter uses the `AVG` function to calculate average city populations and applies a flooring function to round down the results.
7. **Final Output**: The final output displays the average city population for each continent.

### Key Points:

- Use `INNER JOIN` to combine tables based on matching columns.
- Group results by continent to avoid duplicate entries.
- Use `AVG()` function and round down the result with `FLOOR()` to meet the requirements.

Video Transcript

Hey everybody, I will do average population of each continent, an exercise of basic joint section of Sequel and Hacker Rank. We're given these tables city and country and we're asked to query the names of all the continents and their respective average city populations, rounding down to the nearest integer, rounding down the integer meaning floor. Okay, now first we're going to combine these two tables because we need the name of the continent. The name of the continent is in country.continent and then we have to group them by the continent so everything that's all the rows for a certain continent will be collapsed to a single row and then we'll get the average of the population of each city of every city. Right, mind you we're going to use city.population not country.population. Okay, so don't confuse these so let's get started here. We're going to say select. Let me just start with star from city. Now we need to combine these two tables so we need to do a join. Which join I'm going to use? Here's the diagram. We're going to do this one in the center called the inner join where we have table A and table B. We're going to find the records that are in the intersection that is they both are in A and in B based on the country code. So let's get back. We're going to do from city inner join country that's the name of the other table on country.code that's the column has to match equals the city.country.code column. Okay, that's nice. Now we need to get the name of all the continents. Now name is in the column continent in the country table. So here instead of star I'm going to say country.continent. So we got a list of all the continent names. Okay, and then we need to get the average city population. Now let's just do comma population here but we have to say population from where? City not from country city.population. Let's see what we get just with this query. So as you can see we have multiple rows for the same continent and this is because each of these is a different city. So what we have to do is take these three and combine them group them by the name of the continent and then we got to take the average of these values for the population. So to do that let's go here and add group by at the end group by country.continent. Okay now for population I want to take the average so I'm going to use the AVG function. Now it wants us to round down to the nearest integer that if you do down that means floor. Right? If you have 2.5 it becomes 2 for example. Now we got that let's run the code and there you go the average city population by continent.