DROP TABLE IF EXISTS rentals_by_genre; CREATE TABLE rentals_by_genre(genre varchar(25), total_rentals integer, percentile double precision, rating varchar(25)); INSERT INTO rentals_by_genre( genre, total_rentals, percentile, rating ) SELECT ct.name AS genre, count(cs.customer_id) AS total_rentals, PERCENT_RANK() OVER(ORDER BY count(cs.customer_id) DESC) AS percentile, (CASE WHEN (PERCENT_RANK() OVER(ORDER BY count(cs.customer_id) DESC ) < 0.33) THEN 'TOP 5' WHEN (PERCENT_RANK() OVER(ORDER BY count(cs.customer_id) DESC ) > 0.90) THEN 'UNPOPULAR' ELSE 'AVERAGE' END ) AS rating FROM category ct JOIN film_category USING(category_id) JOIN film USING(film_id) JOIN inventory USING(film_id) JOIN rental USING(inventory_id) JOIN customer cs USING(customer_id) GROUP BY Genre ORDER BY total_rentals DESC; SELECT * FROM rentals_by_genre DROP TABLE IF EXISTS rentals_ranked; CREATE TABLE rentals_ranked(genre varchar(25), popularity varchar(25)); CREATE FUNCTION update_rentals_ranked_function() RETURNS TRIGGER LANGUAGE plpgsql AS $$ BEGIN DELETE FROM rentals_ranked; INSERT INTO rentals_ranked(SELECT genre, rating AS popularity FROM rentals_by_genre); RETURN NEW; END; $$ CREATE TRIGGER update_rentals_ranked AFTER INSERT ON rentals_by_genre FOR EACH STATEMENT EXECUTE PROCEDURE update_rentals_ranked_function(); CREATE PROCEDURE update_all_popularity() LANGUAGE plpgsql AS $$ BEGIN DELETE FROM rentals_by_genre; INSERT INTO rentals_by_genre( genre, total_rentals, percentile, rating ) SELECT ct.name AS genre, count(cs.customer_id) AS total_rentals, PERCENT_RANK() OVER(ORDER BY count(cs.customer_id) DESC) AS percentile, (CASE WHEN (PERCENT_RANK() OVER(ORDER BY count(cs.customer_id) DESC ) < 0.33) THEN 'TOP 5' WHEN (PERCENT_RANK() OVER(ORDER BY count(cs.customer_id) DESC ) > 0.90) THEN 'UNPOPULAR' ELSE 'AVERAGE' END ) AS rating FROM category ct JOIN film_category USING(category_id) JOIN film USING(film_id) JOIN inventory USING(film_id) JOIN rental USING(inventory_id) JOIN customer cs USING(customer_id) GROUP BY Genre ORDER BY total_rentals DESC; END; $$ -- USE THIS FUNCTION BEFORE MAKING A NEW ORDER - OR ONCE A MONTH DEPENDING ON PREFERENCE CALL update_all_popularity(); SELECT * FROM rentals_by_genre SELECT * FROM rentals_ranked