Cacher is the code snippet organizer for pro developers

/* Find the full name and email address of all customers that have rented an action movie. */
SELECT CONCAT(first_name, " ", last_name) AS name, email 
FROM customer WHERE customer_id IN 
(SELECT customer_id FROM rental WHERE inventory_id IN 
 (SELECT inventory_id FROM inventory WHERE film_id IN 
  (SELECT film_id FROM film_category JOIN category USING (category_id) WHERE category.name="Action")));
  
/* Find the name of each film, its category, and the aggregate number of films that fall within that category*/
SELECT film.title, category.name, `count`.count_of_category FROM film 
JOIN film_category USING(film_id) 
JOIN category USING (category_id) 
JOIN (SELECT count(*) AS count_of_category, category_id FROM film_category fc GROUP BY category_id) `count` USING (category_id);

/* Average payment for each customer */
SELECT customer_id, first_name, last_name, AVG(amount) FROM customer JOIN payment USING(customer_id) GROUP BY customer_id;

/* All payments that exceed the average for each customer along with the total count of payments exceeding the average. */
SELECT customer_id, CONCAT(first_name, " ", last_name) AS name, amount, COUNT(amount) FROM customer 
JOIN payment p1 USING(customer_id) WHERE amount > 
(SELECT AVG(amount) FROM payment p2 WHERE p2.customer_id=p1.customer_id) 
GROUP BY customer_id;

/* Create a new column classifying existing columns as either or high value transactions based on the amount of payment. */
SELECT payment_id, amount, 
CASE WHEN amount >= 0 AND amount < 5 THEN 'low' 
WHEN amount >= 5 THEN 'high' 
END AS category 
FROM payment;