view: 1: CREATE VIEW FilmPopularitySummary AS SELECT f.film_id… — Database 4031 — TG.ME

CREATE VIEW FilmPopularitySummary AS
SELECT
    f.film_id,
    f.title,
    f.rating,
    COUNT(r.rental_id) AS total_rentals
FROM
    film AS f
LEFT JOIN inventory AS i ON f.film_id = i.film_id
LEFT JOIN rental AS r ON i.inventory_id = r.inventory_id
GROUP BY
    f.film_id, f.title, f.rating;

select * from FilmPopularitySummary order by total_rentals desc limit 20

CREATE VIEW CustomerRentalSummary AS
SELECT
    c.customer_id,
    c.first_name || ' ' || c.last_name AS customer_name,
    COUNT(distinct r.rental_id) AS total_rentals,
    COALESCE(SUM(p.amount), 0) AS total_payments
FROM
    customer AS c
LEFT JOIN rental AS r ON c.customer_id = r.customer_id
LEFT JOIN payment AS p ON p.customer_id = c.customer_id
GROUP BY
    c.customer_id, c.first_name, c.last_name;

select * from CustomerRentalSummary order by total_payments desc limit 20

CREATE VIEW FilmAvailabilityPopularity AS
SELECT
    f.film_id,
    f.title,
    f.rating,
    COUNT(DISTINCT i.inventory_id) AS total_copies,
    COUNT(DISTINCT r.rental_id) AS total_rentals,
    COUNT(DISTINCT CASE WHEN r.return_date IS NULL THEN i.inventory_id END) AS copies_rented_out,
    COUNT(DISTINCT CASE WHEN r.return_date IS NOT NULL THEN i.inventory_id END) AS copies_available
FROM
    film AS f
JOIN inventory AS i ON f.film_id = i.film_id
LEFT JOIN rental AS r ON i.inventory_id = r.inventory_id
GROUP BY
    f.film_id, f.title, f.rating;

select * from FilmAvailabilityPopularity