15. Fast movie watchers vs slow watchers hard


Instruction

  • Write a query to return the number of fast movie watchers vs slow movie watchers.
  • fast movie watcher: by average return their rentals within 5 days.
  • slow movie watcher: takes an average of >5 days to return their rentals.
  • Most customers have multiple rentals over time, you need to first compute the number of days for each rental transaction, then compute the average on the rounded up days. e.g., if the rental period is 1 day and 10 hours, count it as 2 days.
  • Skip the rentals that have not been returned yet, e.g., return_ts IS NULL.
  • The orders of your results doesn't matter.
  • A customer can only rent one movie per transaction.

Table: rental


   col_name   | col_type
--------------+--------------------------
 rental_id    | integer
 rental_ts    | timestamp with time zone
 inventory_id | integer
 customer_id  | smallint
 return_ts    | timestamp with time zone
 staff_id     | smallint

Sample results

watcher_category | count
------------------+-------
 fast_watcher     |   112
 slow_watcher     |   487