8. Challenge: Complex Query with Joins and Subqueries

Question:

I have a different code for this problem, but same result. I will include both my code and solution code below

So questions are:

  1. Why don’t we need to SUM(F.POPULATION) in the outer select?
  2. (related to #1) Why don’t we need to GROUP BY COUNTRY to aggregate?
  3. If we’re doing joins, do we always have to bring in the column we’re looking to join on (in this case, FACTS.ID)?

My code:
select
f.name country,
c.urban_pop,
sum(f.population) total_pop,
(c.urban_pop / CAST(f.population as float)) urban_pct
from facts f
inner join (
select
facts_id,
sum(population) urban_pop
from cities
group by facts_id
) c on c.facts_id = f.id
where urban_pct > .5
group by country
order by urban_pct asc;

Solution code:
SELECT
f.name country,
c.urban_pop,
f.population total_pop,
(c.urban_pop / CAST(f.population AS FLOAT)) urban_pct
FROM facts f
INNER JOIN (
SELECT
facts_id,
SUM(population) urban_pop
FROM cities
GROUP BY 1
) c ON c.facts_id = f.id
WHERE urban_pct > .5
ORDER BY 4 ASC;

  1. f.population is the total population of the country, since facts is a country-level database. You aren’t summing the populations of the cities within the country. You are simply capturing the total country population per the prompt.
  2. You are performing an inner join between the facts.id value, which is a database of country-level data, and the cities.facts_id value, which is assigning each city the appropriate “parent” country. In this particular case, the join is doing the same thing that a GROUP BY country option would because you’re joining FROM the country level database. If you flipped the order and did a join FROM cities, you would need the GROUP BY country to get the right response (and a different SQL argument)
  3. Yes, when doing joins, you need to include a join on column. If you don’t, the computer will have no idea what to compare against. Without specifying what column to join on, it could try to compare country to population, urban population, or urban percentage, and it would have no idea which is the right option.
1 Like