Joining Tables

A. Find top 10 countries for Rockbuster in terms of customer numbers.
```
SELECT D.country, COUNT(customer_id) AS total_customers
	   
FROM customer A
INNER JOIN address B ON A.address_id = B.address_id
INNER JOIN city C ON B.city_id = C.city_id
INNER JOIN country D ON C.country_ID = D.country_ID

GROUP BY country
ORDER BY COUNT(customer_id) DESC 
LIMIT 10
```

B. Find top 10 cities within the top 10 countries identified in step 1
```

SELECT C.city,COUNT(customer_id) AS total_customers
	   
FROM customer A
INNER JOIN address B ON A.address_id = B.address_id
INNER JOIN city C ON B.city_id = C.city_id
INNER JOIN country D ON C.country_ID = D.country_ID

WHERE D.country IN('India','China','United States','Japan','Mexico','Brazil','Russian Federation','Philippines','Turkey','Indonesia') 

GROUP BY C.city
ORDER BY total_customers DESC 
LIMIT 10
```

C. Find top 5 customers in the top 10 cities who have paid the highest total amounts to Rockbuster.
```
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
```