Write a query to find the film which grossed the highest revenue for the video renting organisation

select title from film where film_id in (“highest revenue”)

select film_id from inventory where inventory_id in ( select inventory_id from rental group by inventory_id order by count(inventory_id) desc ))

limit (highest revenue);

where im wrong?

Answer

SELECT title
FROM film
WHERE film_id in (SELECT film_id
FROM inventory
WHERE inventory_id in (
SELECT inventory_id
FROM rental
GROUP BY inventory_id
ORDER BY count(inventory_id) DESC
        )) limit 1;

even this code with joins will work

select Title
from film
inner join inventory
using (film_id)
inner join rental
using (inventory_id)
inner join payment
using (rental_id)
group by title
order by sum(amount) desc
limit 1;