Question 78# MAX(store_id)

I have three questions for Question #78: 

1. Why is INNER JOIN used here instead of LEFT JOIN? Customers that do not have any rental history should be counted as part of the ranking as well; the solution only takes into account the customers that had a rental in the past. 

2. If one customer can belong to more than one store, is the only part that should be changed "MAX(C.store_id) As store_id" ?

    Instead of using MAX(), we need to use customer_id, store_id, count(*) as num_rentals ... GROUP BY customer_id, store_id. 

3. There isn't any description in the question that asked us to create QUARTILE within each store, therefore the "PARTITION BY store_id" may not be used. OR the question can add the description to clarify that the QUARTILE is created for each store. 

Solution: 

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
)
SELECT customer_id, store_id, quartile
FROM (
    SELECT 
        customer_id,
        store_id,
        NTILE(4) OVER(PARTITION BY store_id ORDER BY num_rentals) AS quartile
    FROM cust_rentals
) X
WHERE customer_id IN (1,2,3,4,5,6,7,8,9,10);

My answer: 

WITH customer_rental AS (
  SELECT a.customer_id, 
  MAX(a.store_id) as store_id, 
  count(b.rental_id) as num_rental
  FROM customer a
  LEFT JOIN rental b
  ON a.customer_id = b.customer_id
  GROUP BY a.customer_id
  )
SELECT * FROM (
SELECT customer_id, store_id, 
       NTILE(4) OVER (ORDER BY num_rental) as quartile
FROM customer_rental
  ) X
WHERE customer_id IN (1,2,3,4,5,6,7,8,9,10)