CIA Factbook different outputs

Screen Link:
Guided Project: Analyzing CIA Factbook Data Using SQL Screen #6
https://app.dataquest.io/m/257/guided-project%3A-analyzing-cia-factbook-data-using-sql/6/exploring-average-population-and-area

My Code:

%%sql
SELECT
    AVG(population),
    AVG(area)
FROM facts
WHERE population != (SELECT MAX(population)
                     FROM facts
                    );

My Output:

DQ Solutions code:

%%sql
SELECT AVG(population) AS avg_population, AVG(area) AS avg_area
  FROM facts
 WHERE name <> 'World';

DQ output:

As you can see the output for average population is similar, but average area is a bit off. I noticed that the WHERE clause in my code was different compared to the solution notebook’s. Is there something wrong with what I tried to accomplish?

Hi @rabidracoon93,

a possible cause may be the MIN(population) included in your query. (This needs to be excluded, just like MAX(population).

Thanks for answering. The mission only asked for the row for the whole World to be removed. Just to double check I updated my query to exclude the MIN(population) also. Now both the average population and area is different compared to the solution notebook.

For a reason I cannot pinpoint at the moment, the DQ query has 12 recods more for the area:
Screenshot 2020-07-02 at 00.26.46

UPDATE:
Here’s a full list of facts rows not included in the query with population != MAX(population) ...:

When these areas are taken into account, the result matches that of DQ:
Screenshot 2020-07-02 at 02.53.38

My guess is that by using:

WHERE population != (SELECT MAX(population)
                     FROM facts
                    );

additional rows are excluded.

2 Likes

Ah I see. For whatever reason my query excluded extra rows.
Your answer was helpful and detailed , I really appreciate you taking the time to help!

1 Like