1:
CREATE MATERIALIZED VIEW StoreInventorySummary AS
SELECT
s.store_id,
COUNT(i.inventory_id) AS total_inventory,
COUNT(r.rental_id) AS total_rented
FROM
store AS s
LEFT JOIN inventory AS i ON s.store_id = i.store_id
LEFT JOIN rental AS r ON i.inventory_id = r.inventory_id
GROUP BY
s.store_id;
select * from StoreInventorySummary
2:
CREATE MATERIALIZED VIEW MostExpensiveMoviePerCustomer AS
SELECT
c.customer_id,
c.first_name || ' ' || c.last_name AS customer_name,
f.title AS most_expensive_movie,
f.rental_rate AS movie_price
FROM
customer AS c
JOIN rental AS r ON c.customer_id = r.customer_id
JOIN inventory AS i ON r.inventory_id = i.inventory_id
JOIN film AS f ON i.film_id = f.film_id
WHERE f.rental_rate = (
SELECT MAX(f2.rental_rate)
FROM rental AS r2
JOIN inventory AS i2 ON r2.inventory_id = i2.inventory_id
JOIN film AS f2 ON i2.film_id = f2.film_id
WHERE r2.customer_id = c.customer_id
);
select * from MostExpensiveMoviePerCustomer
