BLACK FRIDAY EXTRA SAVINGS EVENT - EXTENDED
START FREE

Another incorrect example

Page 3/8 in section 3 shows the result of count(*) as 91 and the result of the sub-select count as 173. A basic “does this pass the sniff test” would have revealed that a count of all records could not be less than a count of a sub-set of records within the same table.

Please update this lesson.

https://app.dataquest.io/m/255/subqueries/3/subquery-in-select

2 Likes

Hey.

The count of all records is 173. It’s the result of the subquery SELECT COUNT(*) FROM recent_grads.

The count of all records whose share of women is greater than average is 91 (which, obviously, is smaller than 173).

So either you misunderstood this screen, or I misunderstood your question. Can you please clarify?

1 Like

Hi Bruno,

thanks for the quick response - I honestly didn’t expect one.I found this particular page difficult to follow.

I agree, the total count of all records in recent_grads is 173, I thought that is what the first select value should have returned.

However, I think I’ve realized that the WHERE criteria of the outer query is providing the calculated result of 91 to the first select, and the second select value is its own sub-query just to count the total, and isn’t impacted by the where of the outer query.

I my solution was the reverse… I put the WHERE criteria the first select’s sub-query, and used the outer query to provide the total;

SELECT
(SELECT CAST(COUNT(*) AS FLOAT)
FROM recent_grads
WHERE ShareWomen > (SELECT AVG(ShareWomen) FROM recent_grads)) / CAST(COUNT(*) AS FLOAT) proportion_abv_avg
FROM recent_grads

Both solutions provided the correct result (0.5260115606936416)

Thanks for responding - it made me look at the ‘official’ solution more closely.

Stay safe!

Rob Russell

3 Likes

Hello, guys!

I had the same doubt. Maybe it would be interesting to clarify the detail metioned by Rob in the mission instructions, or in the first lines of the next mission:

I think I’ve realized that the WHERE criteria of the outer query is providing the calculated result of 91 to the first select, and the second select value is its own sub-query just to count the total, and isn’t impacted by the where of the outer query.

BR
Paulo

1 Like