#126

Hey Luming,
That is a great question, the business logic behind this question is that we want to have an apple to apple comparison and exclude restaurants with less than 10 trips. (Because there might not be enough samples to really tell us how good the trip experience is).
You solution differs when computing average, the HAVING COUNT(DISTINCT t.trip_id)>=10 filters out restaurants with less than 10 trips in the final reporting, but when computing the average ratings, those restaurants were included.
Query 1:
SELECT city,restaurant_id,
AVG(CASE WHEN thumb_up=True THEN 1 ELSE 0 END) as avg_rating
FROM trip t
JOIN trip_rating tr
ON t.trip_id = tr.trip_id
WHERE is_completed=True
GROUP BY 1,2
HAVING COUNT(DISTINCT t.trip_id)>=10
ORDER BY 2;
Result 1:
city restaurant_id avg_rating
Shanghai 100002 0.92857142857142857143
London 100003 1.00000000000000000000
Portland 100004 0.92857142857142857143
Query 2:
SELECT city,restaurant_id,
AVG(CASE WHEN thumb_up=True THEN 1 ELSE 0 END) as avg_rating
FROM trip t
JOIN trip_rating tr
ON t.trip_id = tr.trip_id
WHERE is_completed=True
GROUP BY 1,2
-- HAVING COUNT(DISTINCT t.trip_id)>=10
ORDER BY 2;
Result 2:
city restaurant_id avg_rating
Berlin 100001 1.00000000000000000000
Shanghai 100002 0.92857142857142857143
London 100003 1.00000000000000000000
Portland 100004 0.92857142857142857143
As you can see, the HAVING clause was used in the last stage to remove unqualified restaurants, but those unqualified restaurants were included when computing average ratings.
Query 3:
WITH qualified_resaturant AS (
SELECT restaurant_id, COUNT(DISTINCT trip_id)
FROM trip
WHERE is_completed = TRUE
GROUP BY restaurant_id
HAVING COUNT(DISTINCT trip_id) >=10
),
avg_rating as (
SELECT city, T.restaurant_id, AVG(CASE WHEN thumb_up IS TRUE THEN 1 ELSE 0 END ) AS avg_rating
FROM trip T
INNER JOIN qualified_resaturant Q
ON Q.restaurant_id = T.restaurant_id
INNER JOIN trip_rating R
ON T.trip_id = R.trip_id
GROUP BY 1,2
)
SELECT * FROM avg_rating
WHERE restaurant_id IN (100001, 100002, 100003, 100004);
Result 3:
city restaurant_id avg_rating
London 100003 1.00000000000000000000
Portland 100004 0.93333333333333333333
Shanghai 100002 0.93333333333333333333
The avg_rating values are different.
Hope it answered your question, please let us know if you have any other questions.
- Leon