mikeizbicki
sql/17.sql
Why wouldn't we be able to join payment directly with customer (bypassing rentals) and then with address,city, and so on?
Use a JOIN to order the countries by most profitable,
where the profit is the total amount that all customers from that country have payed.
Use tables payment, rental, customer, address, city, and country.
Order by country alphabetically.
Is the idea here that a payment might be associated with a customer but not with a rental, and therefore such payments should not be factored into the profitability of each country?
This is a good question (that is about the semantics of the pagila database itself and not about the semantics of how SQL commands behave). It is also a great illustration about how English language problem descriptions are inherently ambiguous.
The answer to your question hinges on what it means for "profit to be associated with a country", which is not formally defined anywhere. The customer_id of the rentals table is the customer_id of the person who placed the original order, but this is not necessarily the same as the customer_id of the payments table who payed for the order. (A simple real world example would be a child rents a movie and charges the results to the parent's credit card---a situation that most of you are in with your college payments.) By joining with the rental, we are saying we care more about who initiated the demand than who is actually paying the tab.
Making these types of subtle differences explicit is important. Because it is also hard, most programmers these days do not fear LLMs will replace them. This famous (if slightly old) comic gets that point across:
