Skip to content

Instantly share code, notes, and snippets.

@ashen007

ashen007/ex_3.py

Created April 26, 2025 06:45
Show Gist options
  • Save ashen007/af8072201282eef02cf74e6ac8a542df to your computer and use it in GitHub Desktop.
Save ashen007/af8072201282eef02cf74e6ac8a542df to your computer and use it in GitHub Desktop.
Download ZIP
Raw
ex_3.py
"""
Question: which film has the most rentals?
SQL Query:
select title,
count(rental_id) as rental_count
from film
join (select i.film_id,
r.rental_id
from inventory as i
join rental as r on i.inventory_id = r.inventory_id) as t
on film.film_id = t.film_id
group by title
order by rental_count desc;
Question: which film has the most revenue?
SQL Query:
select title,
sum(amount * rental_period) as revenue
from film
join (select i.film_id,
r.*
from inventory as i
join (select rental.inventory_id,
rental.rental_id,
amount,
datediff(return_date, rental_date) as rental_period
from rental
join payment on rental.rental_id = payment.rental_id) as r
on i.inventory_id = r.inventory_id) as t
on film.film_id = t.film_id
group by title
order by revenue desc
limit 1;
Question: which film has the highest rental period?
SQL Query:
select title,
max(datediff(return_date, rental_date)) as rental_period
from film
join (select i.film_id,
r.rental_id,
rental_date,
return_date
from inventory as i
join rental as r
on i.inventory_id = r.inventory_id) as t
on film.film_id = t.film_id
group by title;
"""
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment