GROUP BY

Question 1

SELECT customer_id, SUM(amount) FROM payment
GROUP BY customer_id
ORDER BY SUM(amount) DESC;

Answer 1

Answers the questions of how much each customer is spending and puts them in descending order by total spent

Question 2

SELECT customer_id, SUM(amount), COUNT(rental_id) FROM payment
GROUP BY customer_id
ORDER BY SUM(amount) DESC;

Answer 2

similiar to previous query, amount spent by customer but also provides # of transactions per customer

Question 3

SELECT DATE(payment_date), SUM(amount) FROM payment
GROUP BY DATE(payment_date)
ORDER BY DATE(payment_date);

Answer 3

Provides a list of total sales by day in descending order of day. One note is the DATE function takes a timestamp column and extracts just the day of the month and drops the time

Question 4

SELECT customer_id, SUM(amount) FROM payment
GROUP BY customer_id
ORDER BY SUM(amount) DESC
LIMIT 5;

Answer 4

Gives the top 5 customer ids per amount spent

Question 5

SELECT ROUND(AVG(replacement_cost),2), rating FROM film
GROUP BY rating;

Answer 5

provides an average replacement cost based off of rating of film

Question 6

SELECT staff_id, COUNT(payment_id) FROM payment
GROUP BY staff_id
ORDER BY COUNT(payment_id) DESC;

Answer 6

used to determine which Staff ID has performed the greatest # of transactions

Question 7

HAVING clause

Answer 7

allows a filtering to take place AFTER an aggregation has taken place. HAVING is generally a WHERE statement but only used for GROUP BY

Question 8

SELECT SUM(amount), customer_id FROM payment
GROUP BY customer_id
HAVING SUM(amount) > 100;

Answer 8

List of customer ids who have spent more than $100. Need to use HAVING instead of WHERE because filtering based off of an aggregate function

Question 9

SELECT COUNT(customer_id), store_id FROM customer
GROUP BY store_id
HAVING COUNT(customer_id) > 300;

Answer 9

providing a list of store_ids with more than 300 customers

Question 10

SELECT customer_id, COUNT(payment_id) FROM payment
GROUP BY customer_id
HAVING COUNT(payment_id) >= 40;

Answer 10

creates a list of customers that have had more than 40 transactions

Question 11

SELECT customer_id, SUM(amount) FROM payment
WHERE staff_id = 2
GROUP BY customer_id
HAVING SUM(amount) > 100;

Answer 11

Provides customer_id and sum of amount spent with staff_id number 2. Where statement comes before the having and is not part of aggregate function

Question 12

SELECT customer_id, SUM(amount) FROM payment
WHERE staff_id = 2
GROUP BY customer_id
HAVING SUM(amount) >= 110;

Answer 12

provides a list of customers spending more than 110, where purchased were made with staff_id number 2