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 .
78. Quartiles buckets by number of rentals medium

Instructions:

  • Write a query to return the quartile by the number of rentals for the following customers:
  • customer_id IN (1,2,3,4,5,6,7,8,9,10)
Hint
  • USE NTILE(4) to create quartiles.
  • To save you some time, here is the CTE to create customer rentals by store:
WITH cust_rentals AS (
    SELECT C.customer_id, 
    MAX(C.store_id) AS store_id, -- one customer can only belong to one store
    COUNT(*) AS num_rentals FROM 
    rental R
    INNER JOIN customer C
    ON C.customer_id = R.customer_id
    GROUP BY C.customer_id
)

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

  

Sample results

 customer_id | store_id | quartile
-------------+----------+----------
           1 |        1 |        2
           2 |        1 |        2
           3 |        1 |        1
           4 |        2 |        1

Subscribe for solution