MAX() latest dates, Invalid objects

Question

i am trying to create a table that has the latest date of a movie rental for each customer.

currently i have a view that covers most of the required data except for the return date of each movie per customer, this information is located in a table called rentals which i am unable to reference in the FROM section because it conflicts with the VIEW.

this is currently what i have done but i keep getting an invalid object error on some tables. such as rentals and rental_view.

SELECT  customer_name
  , COUNT(DISTINCT rental_id) AS Number_of_Rentals
  , latest_rental
FROM Rental_view,
(SELECT MAX(return_date) AS latest_rental FROM rentals) 
 latest_rental
GROUP BY customer_id, customer_name
ORDER BY customer_id, customer_name

Not sure what is wrong here, have a got the Second embedded SELECT statement in the correct place and am i using it properly?

here is the question for context.

"Produce a query that shows the customer name, number of rentals, latest rental date and average rental duration of all customers."

Updated Code.

SELECT  customer_name
  , COUNT(DISTINCT r.rental_id) AS Number_of_Rentals
  , MAX(r.return_date) AS latest_rental 
  , AVG(rv.rental_duration) AS AVG_Rental_duration
FROM  Rental_View AS rv JOIN rentals AS r
    ON r.rental_id = rv.rental_ID
GROUP BY rv.customer_id, customer_name
ORDER BY rv.customer_id, customer_name  

Answer 1

DO a JOIN between rentals and Rental_View using customer_id.
If you only want to include results that have a customer_id in both tables, use an INNER JOIN.

SELECT  customer_name
  , COUNT(DISTINCT r.rental_id) AS Number_of_Rentals
  , MAX(r.return_date) AS latest_rental 
  , AVG(rv.rental_duration) AS AVG_Rental_duration
FROM  Rental_View AS rv 
INNER JOIN rentals AS r
    ON r.rental_id = rv.rental_ID
GROUP BY rv.customer_id, customer_name
ORDER BY rv.customer_id, customer_name  

Please post your View' script as well.