Intermediate SQL Queries

Task 1

Manager asked for an Excel sheet report of the total sum of payments made by each customer in the payments table. Data would be presented in ascending order and four columns to be shown only (customer_id, first_name, last_name, and sum).

Query

SELECT customer.customer_id,first_name,last_name, SUM(payment.amount)
FROM customer
JOIN payment
ON customer.customer_id = payment.customer_id
GROUP BY customer.customer_id
ORDER BY customer_id

Output

---

Task 2

Manager askeed for the SQL syntax and output of customer ids having an average payment amount of less than 3.

Query

select customer.customer_id,first_name,last_name, avg(payment.amount)
from customer
join payment
on customer.customer_id = payment.customer_id
group by customer.customer_id
having avg(amount)<3
order by customer_id 

Output

---

Task 3

A fellow data analyst couldn't understand how the following SQL queries were not working in the database. I was asked to provide a brief explanation and the corrected syntax.

-----Query 1
SELECT first_name,last_name,district FROM customer
INNER JOIN address
ON address_id = address_id

-----Query 2
SELECT customer_id,SUM(amount) FROM payment
GROUP BY customer_id
HAVING amount > 100

Comments & Syntax

When citing the same column from both tables (in this case address_id), we need to include the table names and add a period (.) before the column name (ex. customer.address_id=address.address_id)

-----Corrected Query 1
SELECT first_name,last_name,distict FROM customer
INNER JOIN address
ON customer.address_id=address.address_id

The amount column is only found in the payment table, hence we need to use inner join/join syntax to join the customer and payment table together. After that, we need to specify where the said columns are being extracted from (ex. customer.customer_id and payment.amount). Finally, for the HAVING syntax to work we need to write down SUM() to amount>100, since that was mentioned in the first given line of code.

-----Corrected Query 2
SELECT customer.customer_id,SUM(payment.amount) FROM customer
join payment
on customer.customer_id=payment.customer_id
GROUP BY customer.customer_id
HAVING sum(amount)>100

---

Task 4

One of the criteria for a customer to become part of the customer loyalty program is that they need to have an accumulated payment made to the DVD rental store of at least $200 or more. As a Data Analyst, you need to provide a list of customer IDs with a total accumulated payment of at least 200 or more. As the data analyst onsite, I was tasked to provide the SQL code and the output.

Query

select customer.customer_id,first_name,last_name,sum(payment.amount)
from customer
join payment
on customer.customer_id=payment.customer_id
group by customer.customer_id
having sum(amount)>=200

Output