PROJECT TASKS
SQL QUERIES
Sl. Task Query
1 Top Customers: Select
Identify the top 10 customer.customer_id,
customers who rent customer.first_name,
the most movies. customer.last_name,
Include their names, customer.email,
total rentals, and Count(rental.rental_id)
contact details From customer
Join rental On customer.customer_id =
rental.customer_id
Group By customer.customer_id,
customer.first_name, customer.last_name,
customer.email
Order By Count(rental.rental_id) desc
Limit 10
2 Rental Trends: A.
Analyze the monthly Select To_Char(rental_date, 'YYYY-MM') As
rental trends for the rental_month,
past year to identify Count(*) As rental_count
peak rental periods. From rental
Where rental_date Between '2020-01-01' And
'2020-12-31'
Group By To_Char(rental_date, 'YYYY-MM')
Order By rental_count desc
B.
Select to_Char(rental_date, 'yyyy-mm') as
rental_month,
Count (*) as rental_count
From Rental
where rental_date between '2005-01-01' and
'2005-12-31'
Group by to_Char(rental_date, 'yyyy-mm')
Order by rental_count desc
3 Customer Retention: Select
Generate a list of distinct customer.customer_id,
customers who haven’t customer.first_name,
rented any movies in customer.last_name,
the last 6 months. customer.email
From customer
Left Join rental On customer.customer_id =
rental.customer_id and rental.rental_date >
'2005-12-31'
Where rental.rental_id is null
4 Popular Movies: Select film.title, To_Char(rental.rental_date,
Determine the most 'YYYY-MM') As rental_month,
rented movies and how Count(rental.rental_id) As rental_count
often they’ve been From Film
rented Join inventory on film.film_id = inventory.film_id
Join rental On inventory.inventory_id =
rental.inventory_id
Group By film.title, To_Char(rental.rental_date,
'YYYY-MM')
Order By rental_count desc
5 Unrented Movies: Select film.title
Identify movies in the From Film
inventory that have Join inventory on film.film_id = inventory.film_id
never been rented. Join rental On inventory.inventory_id =
rental.inventory_id
Where rental_date is null
6 Availability Check: Select film.title, Count(inventory.inventory_id) As
Generate a report available_copies
showing how many From film
copies of each movie Join inventory On film.film_id = inventory.film_id
are currently available. Left Join rental On inventory.inventory_id =
rental.inventory_id
And rental.return_date Is Null
Where rental.rental_id Is Null
Group By film.title
Order By available_copies desc
7 Monthly Revenue: Select To_Char(rental_date, 'YYYY-MM') As
Calculate the monthly rental_month,
revenue generated Sum(payment.amount) As revenue$
from rentals for the From payment
past year. Join rental On payment.rental_id =
rental.rental_id
Where rental_date Between '2005-01-01' And
'2020-12-31'
Group By To_Char(rental_date, 'YYYY-MM')
Order By rental_month desc
8 Category Select category.name, SUM(payment.amount) As
Performance: Find total_revenue
the total revenue From category
generated by each Join film_category On category.category_id =
movie category. film_category.category_id
Join film On film_category.film_id = film.film_id
Join inventory On film.film_id = inventory.film_id
Join rental On inventory.inventory_id =
rental.inventory_id
Join payment On rental.rental_id =
payment.rental_id
Group By category.name
Order By total_revenue desc
9 Late Fees Analysis: A. To find out total late fees:
Analyze the total late
fees collected and Select Sum(payment.amount - film.rental_rate)
identify customers who As total_late_fees
incurred the most late From rental
fees. If no data is Join inventory On rental.inventory_id =
available, what is your inventory.inventory_id
analysis of the Join film On inventory.film_id = film.film_id
business? Assuming Join payment On rental.rental_id =
that $1 is the late fee, payment.rental_id
can you tell how much Where (payment.amount - film.rental_rate) > 0
are we in loss due to
not returning movies? B. To find out the customers who incurred
the most late fees:
Select customer.customer_id,
customer.first_name, customer.last_name,
film.rental_rate, payment.amount, film.title,
SUM(payment.amount-film.rental_rate) As
late_fees
From customer
Join rental On customer.customer_id =
rental.customer_id
Join inventory On rental.inventory_id =
inventory.inventory_id
Join film on inventory.film_id = film.film_id
Join payment On rental.rental_id =
payment.rental_id
Where payment.amount-film.rental_rate > 0
Group By customer.customer_id,
customer.first_name, customer.last_name,
film.rental_rate, payment.amount, film.title
Order By customer.first_name asc
(we can also use- Order By late_fees desc)
C. To find out total loss amount due to not
returning the movie:
Select
Sum(Extract(Day From Current_Date -
rental.rental_date) * 1) As total_loss
From rental
Where rental.return_date is null
10 Actor Contribution: Select actor.first_name, actor.last_name,
Generate a report Count(film.film_id)
listing actors and the From actor
number of movies Join film_actor On film_actor.actor_id =
they’ve been featured actor.actor_id
in. Join film On film_actor.film_id = film.film_id
Group by actor.first_name, actor.last_name
Order by count desc
11 Filmography: Create Select actor.first_name, actor.last_name,
a detailed filmography film.title, film.description, film.release_year,
for a specific actor, film.length, film.rating
including the titles and From actor
release years of their Join film_actor On film_actor.actor_id =
movies actor.actor_id
Join film On film_actor.film_id = film.film_id
Where actor.first_name = 'EMILY' and
actor.last_name = 'DEE'
Group by actor.first_name, actor.last_name,
film.title, film.description,
film.release_year, film.length, film.rating
Order by film.title asc
12 Staff Performance: Select staff.first_name, staff.last_name,
Generate a report staff.staff_id, Count(rental.rental_id) AS
showing the number of total_rentals
rentals processed by From rental
each staff member. Left Join staff On rental.staff_id = staff.staff_id
Group By staff.first_name, staff.last_name,
staff.staff_id
Order By total_rentals desc
13 Overdue Rentals: Select
Identify rentals that customer.first_name,
are overdue, including customer.last_name,
customer names, film.title,
movie titles, and due film.rental_duration,
dates. rental.rental_date, rental.return_date,
(rental.rental_date + film.rental_duration *
Interval '1 day') As due_date,
Case
When rental.return_date Is Null Then
Date_Part('day', Current_Date -
(rental.rental_date + film.rental_duration *
Interval '1 day'))
When rental.return_date >
(rental.rental_date + film.rental_duration *
Interval '1 day') Then
Date_Part('day', rental.return_date -
(rental.rental_date + film.rental_duration *
Interval '1 day'))
Else 0
End As overdue_days
From customer
Join rental On customer.customer_id =
rental.customer_id
Join inventory On rental.inventory_id =
inventory.inventory_id
Join film On inventory.film_id = film.film_id
Where rental.return_date is null Or
rental.return_date > rental.rental_date
Group By
customer.customer_id,
customer.first_name,
customer.last_name,
film.film_id,
film.title,
film.rental_duration,
rental.rental_date,
rental.return_date
Order By overdue_days desc
14 Frequent Renters: Select
Classify customers into customer.customer_id,
different tiers (e.g., customer.first_name,
Platinum, Gold, Silver) customer.last_name,
based on their rental Count(rental.rental_id) As total_rentals,
activity. Case
When Count(rental.rental_id) >= 50 Then
'Platinum'
When Count(rental.rental_id) Between 20
And 49 Then 'Gold'
When Count(rental.rental_id) Between 5
And 19 Then 'Silver'
Else 'Bronze'
End As customer_tier
From customer
Left Join rental On customer.customer_id =
rental.customer_id
Group By customer.customer_id,
customer.first_name, customer.last_name
Order By total_rentals desc
15 Regional Analysis: If Select city.city, country.country,
customer location data Count(rental.rental_id) As total_rentals,
is available, analyze Count(Distinct customer.customer_id) As
rental patterns by unique_customers
region. From customer
Join rental On customer.customer_id =
rental.customer_id
Join address On customer.address_id =
address.address_id
Join city On address.city_id = city.city_id
Join country On city.country_id =
country.country_id
Group By city.city, country.country
Order By country asc
