Common Table Expressions (CTE)

A. Find the average amount paid by the top 5 customers using CTEs.
```
WITH total_amount_paid_cte AS

(SELECT B.customer_id,B.last_name,B.first_name,D.city,E.country,
       SUM(amount) AS topfive 
FROM payment A
INNER JOIN customer B ON A.customer_id = B.customer_id
INNER JOIN address C ON B.address_id = C.address_id
INNER JOIN city D ON C.city_id = D.city_id
INNER JOIN country E ON D.country_ID = E.country_ID

WHERE D.city IN
('Aurora','Tokat','Tarsus','Atlixco','Emeishan','Pontianak','Shimoga','Aparecida de Goinia','Zalantun','Taguig') 

GROUP BY B.customer_id,B.last_name,B.first_name,D.city,E.country
ORDER BY topfive DESC 
LIMIT 5)

SELECT AVG(topfive) AS average_of_top5

FROM total_amount_paid_cte
```

B. Find out how many of the top 5 customers are based within each country.
```
WITH top_5_customers_cte AS

(SELECT B.customer_id,B.last_name,B.first_name,D.city,E.country,
       SUM(amount) AS total_amount_paid
	   
FROM payment A
INNER JOIN customer B ON A.customer_id = B.customer_id
INNER JOIN address C ON B.address_id = C.address_id
INNER JOIN city D ON C.city_id = D.city_id
INNER JOIN country E ON D.country_ID = E.country_ID

WHERE D.city IN
('Aurora','Tokat','Tarsus','Atlixco','Emeishan','Pontianak','Shimoga','Aparecida de Goinia','Zalantun','Taguig') 

GROUP BY B.customer_id,B.last_name,B.first_name,D.city,E.country
ORDER BY total_amount_paid DESC 
LIMIT 5) 

SELECT E.country, 
       COUNT(DISTINCT B.customer_ID) AS all_customer_count,
       COUNT(DISTINCT top_5_customers_cte) AS top_customer_count      
	   
FROM payment A
INNER JOIN customer B ON A.customer_ID = B.customer_ID
INNER JOIN address C ON B.address_id = C.address_id
INNER JOIN city D ON C.city_id = D.city_id
INNER JOIN country E ON D.country_ID = E.country_ID

LEFT JOIN top_5_customers_cte

ON E.country = top_5_customers_cte.country
GROUP BY E.country
ORDER BY top_customer_count DESC
```