#127

As per my understanding, the question is asking to output avg rating for the restaurants excluding the first 10 trips' data for that restaurant . Also it need to exclude the restaurants which have less than 10 trips.
So how am visualizing it, is to remove the restaurants where count of trips is less than 10.
Then get the row number for bookings based on trip_start_ts as one date can have multiple trips for a restaurant. Then finding the avg of rating for those where thaat row number is greater than 10.
My solution is not getting accepted. The solution provided is using a RANK function which I believe shouldn't be used unless am not getting the question right. Also the solution provided using only those bookings which happened on the 10th day and not necessarily 10th trip.
Please help me understand. My solution is below. Thanks !!!
WITH eligible_restaurants AS
(SELECT restaurant_id
FROM trip
GROUP BY restaurant_id
HAVING COUNT(trip_id) >= 10),

each_rest_trip_ranking AS
(SELECT e.restaurant_id,
r.thumb_up,
ROW_NUMBER() OVER (PARTITION BY e.restaurant_id ORDER BY trip_start_ts) AS ranking
FROM eligible_restaurants e
LEFT JOIN trip t ON e.restaurant_id = t.restaurant_id
LEFT JOIN trip_rating r ON t.trip_id = r.trip_id
)

SELECT restaurant_id,
AVG(CASE WHEN thumb_up IS TRUE then 1 ELSE 0 END) AS avg_rating
FROM each_rest_trip_ranking
WHERE ranking > 10
GROUP BY restaurant_id

;