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

Expected results

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.



More INNER JOIN questions

ID Title Level FTPR
179 Members who ever moved from Microsoft to Google linkedin medium
35%
162 Number of orders per brand walmart easy
40%
150 Countries with above average customers social easy
10%
99 Page recommendation social medium
12%
91 same day friend request acceptance rate spotify easy
11%
49 Top 3 money making movie categories medium
19%
48 Movie and TV actors easy
43%
40 Top 2 most rented movie in June 2020 medium
15%
39 Most productive actor with inner join easy
23%
38 Most popular movie category (name and id) medium
18%
37 Most popular movie category easy
17%
36 Actors from film 'AFRICAN EGG' easy
36%