50. Top 5 cities for movie rentals

easy

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



More INNER JOIN questions

ID Title Level FTPR
213 Interchange revenue by issuer and merchant category visa easy
70%
179 Members who ever moved from Microsoft to Google linkedin medium
31%
162 Number of orders per brand walmart easy
41%
150 Countries with above average customers google easy
15%
99 Page recommendation facebook medium
13%
91 same day friend request acceptance rate spotify easy
13%
49 Top 3 money making movie categories medium
18%
48 Movie and TV actors easy
38%
40 Top 2 most rented movie in June 2020 medium
14%
39 Most productive actor with inner join easy
22%
38 Most popular movie category (name and id) medium
19%
37 Most popular movie category easy
15%
36 Actors from film 'AFRICAN EGG' easy
32%