Instruction
- Write a query to return the names of the top 5 cities with the most rental revenues in 2020.
- Include each city's revenue in the second column.
- The order of your results doesn't matter.
- Your results should have exactly 5 rows.
Table 1: address
col_name | col_type -------------+-------------------------- address_id | integer address | text address2 | text district | text city_id | smallint postal_code | text phone | text
Table 2: city
col_name | col_type -------------+-------------------------- city_id | integer city | text country_id | smallint
Table 3: customer
col_name | col_type -------------+-------------------------- customer_id | integer store_id | smallint first_name | text last_name | text email | text address_id | smallint activebool | boolean create_date | date active | integer
Table 4: payment
Movie rental payment transactions table
col_name | col_type --------------+-------------------------- payment_id | integer customer_id | smallint staff_id | smallint rental_id | integer amount | numeric payment_ts | timestamp with time zone
Sample results
city | sum
----------------------------+--------
Cape Coral | 221.55
Saint-Denis | 216.54
Aurora | 198.50
City 4 | 12.34
City 5 | 1.23
Solution postgres
SELECT
T.city,
SUM(P.amount)
FROM payment P
INNER JOIN customer C
ON C.customer_id = P.customer_id
INNER JOIN address A
ON A.address_id = C.address_id
INNER JOIN city T
ON T.city_id = A.city_id
WHERE DATE(P.payment_ts) >= '2020-01-01'
AND DATE(P.payment_ts) <= '2020-12-31'
GROUP BY T.city
ORDER BY SUM(P.amount) DESC
LIMIT 5;
Explanation
This query retrieves data from several tables to show the top 5 cities in terms of payment amounts made by customers between January 1st, 2020 and December 31st, 2020.
The SELECT statement specifies the fields to be returned, which are the city and the total payment amount.
The FROM clause references the payment table.
The INNER JOIN clauses are used to connect the payment table to the customer, address, and city tables using their respective id fields.
The WHERE clause filters the payment data to only include transactions made within the specified date range.
The GROUP BY clause groups the payment data by city and the SUM function calculates the total payment amount for each city.
The ORDER BY clause sorts the results in descending order based on the total payment amount.
Finally, the LIMIT 5 statement limits the results to the top 5 cities with the highest payment amounts.
Expected results