Question

Write a query to find the city which generated the maximum revenue for the organisation.



Sample Output

city

Abu Dhabi

Answer 1

Answer:

the most revenue

mysql

I have to determine which customer generated the most total revenue. I've managed to create a query that lists all customers and how much revenue they generated.

SELECT P.FirstName, P.LastName, SUM(BookingFee) AS Revenue

FROM Person P, Customer C, Reservation R

WHERE C.Id = P.Id AND R.AccountNo = C.AccountNo

GROUP BY C.AccountNo

ORDER BY Revenue DESC

By putting this in descending order the customer who generated the most revenue is on top. But I have to have the query produce only one row that contains the highest revenue generating customer. I'm not quite sure how to do this or if I'm on the right track with what I have now.

Answer 2

Answer:

select City

from city

inner join address

using (city_id)

inner join customer

using (address_id)

inner join payment

using (customer_id)

group by city

order by sum(amount) desc

limit 1

Explanation: