First, a quick comment: You're coming up with some great questions between here and the Slack channel - nice work!
In regard to your question, *technically* on an inner join it shouldn't matter which version of the listing_id you're using as it only includes the data present in the appropriate column in both tables (at least with this type of join condition). And whether you select the column from either table, you'll get the same rows back, but they are not necessarily going to be in the *same* order. There's a bunch of things happening to make this be the case:
1) The solution data isn't ordering the initial CTE either so it's returning as whatever order it exists in the initial table. In other words, the order you see is going to depend on how the data is stored and how the database engine is doing the join. The best way to see this is something like:
with august_bookings as
(
select l.country, l.listing_id, count(b.booking_id) as booking_count
from bookings b
inner join listings l
on l.listing_id = b.listing_id
where date >= '2021-08-01' and date <= '2021-08-31'
and l.country in ('AU', 'CA', 'UK')
group by country, l.listing_id
)
select * from august_bookings
Look at the order of the rows in this case. Then do the same thing again, but change the l.listing_id to b.listing_id. If you really want to mess with your head, add both columns (ie, l.listing_id, b.listing_id) to the CTE (and make sure to group by both) and you'll get a third ordering.
The way to work around this to force ordering in the CTE so it will always be the same. Which leads to...
2) There are a lot of listings with the same number of bookings - way more than 10. This means the ordering of the data has an outsized effect on the results.
3) Normally this wouldn't really matter, but because we're using ROW_NUMBER, the order makes a difference when it comes to calculating what comes first. Now you could add the specific ordering in the CTE as above, but we have another (smaller) problem...
4) Because the solution for this example isn't using an explicit ordering, to solve the question we have to follow the same ordering as the solution. I'll chat w/ Leon about this one, but I'm not sure it makes sense to change the solution given they're all somewhat arbitrarily correct.
To reiterate, in another type of query where you weren't using ROW_NUMBER, this wouldn't matter as much as the order isn't crucial to the answer.
Hopefully this makes some sense, and I may have missed something in the explanation / my analysis of the problem - feel free to comment and we can work through this more.
