#78 : Why COUNT(rental_id) is giving wrong/different result from COUNT(*) used in the solution?

Hi Yogita,
This is an interesting question, the count(*) and count(rental_id) shouldn't matter, as they generate the same counts.
It's up to how the Postgres engine handles ties. Because customer_id = 9 can belong to either quartile 1 or quartile 2, it randomly assigns the bucket and there is no guarantee which.

I would consider your solution the equally correct one.
-- Query to check rental counts
WITH cnt_1 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
),
cnt_2 AS (
SELECT C.customer_id,
MAX(C.store_id) AS store_id, -- one customer can only belong to one store
COUNT(rental_id) AS num_rentals FROM
rental R
INNER JOIN customer C
ON C.customer_id = R.customer_id
GROUP BY C.customer_id
)
SELECT COUNT(*) FROM cnt_1 C1
INNER JOIN cnt_2 C2
ON C1.customer_id = C2.customer_id
WHERE C1.num_rentals <> C2.num_rentals;
-- 0.
customer_id store_id num_rentals quartile
493 1 23 1
519 2 23 1
547 1 23 1
583 1 23 1
9 2 23 2
85 2 23 2
183 2 23 2
A lot of customers share the same number of rentals (all with 23 rentals), and their quartile buckets are assigned randomly by Postgres.
Thanks,
Leon