To 🔥 celebrate the holiday season, Also check out our NEWLY launched Annual plan (Up to 50% off). Use coupon code THANKSGIVING15 and enjoy additional 15% off all paid plans. Coupon expires at 2020/11/30 .
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.
  • If there are ties, return any one of them.
  • Yours results should have exactly 5 rows.

Table 1: 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

  

Table 2: 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
 last_update | timestamp with time zone
 active      | integer

  

Table 3: address

  col_name   | col_type
-------------+--------------------------
 address_id  | integer
 address     | text
 address2    | text
 district    | text
 city_id     | smallint
 postal_code | text
 phone       | text
 last_update | timestamp with time zone

  

Table 4: city

  col_name   | col_type
-------------+--------------------------
 city_id     | integer
 city        | text
 country_id  | smallint
 last_update | 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

Subscribe for solution