- *****************************************************************************
- *******************************************************
- *******************************************************************************
- ******************************************
Table 1: trip
Fact table for every uber eats delivery.
col_name | col_type --------------------+------------------- trip_id | bigint date | date trip_start_ts | timestamp delivery_fee_usd | int surge_fee_usd | int is_curbside_dropoff | boolean is_completed | boolean is_cash_trip | boolean user_id | bigint restaurant_id | bigint courier_id | bigint city | text country | varchar(2)
Table 2: trip_rating
Ratings received after a uber eat delivery, either thumb up or thumb down.
col_name | col_type --------------------+------------------- trip_id | bigint reviewer_id | bigint subject | text thumb_up | boolean
Sample results
city | restaurant_id | avg_rating | ranking
---------------+---------------+------------------------+---------
Austin | 100068 | 0.94444444444444444444 | 1
Austin | 100034 | 0.92452830188679245283 | 2
Austin | 100077 | 0.91666666666666666667 | 3
Berlin | 100046 | 0.94594594594594594595 | 1
Berlin | 100063 | 0.93333333333333333333 | 2
Berlin | 100059 | 0.86666666666666666667 | 3
Chicago | 100060 | 0.96551724137931034483 | 1
Solution postgres
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
),
restaurant_ranking AS (
SELECT city, restaurant_id, avg_rating, RANK() OVER(PARTITION BY city ORDER BY avg_rating DESC) AS ranking
FROM avg_rating
)
SELECT city, restaurant_id, avg_rating, ranking
FROM restaurant_ranking
WHERE ranking <= 3;
Explanation
This query selects the top three restaurants by average rating in each city, but only considers restaurants that have had at least 10 completed trips.
To achieve this, the query first creates a CTE (common table expression) called "qualified_restaurant" that selects the restaurant ID and the count of unique completed trips for each restaurant that has had at least 10 completed trips.
Then, another CTE called "avg_rating" is created which calculates the average rating (based on thumbs up or down) for each restaurant in each city, but only considers restaurants that are in the "qualified_restaurant" CTE.
Finally, a third CTE called "restaurant_ranking" is created which assigns a ranking to each restaurant in each city based on its average rating, with the highest-rated restaurant receiving a ranking of 1.
The final SELECT statement then selects the city, restaurant ID, average rating, and ranking from the "restaurant_ranking" CTE, but only includes restaurants with a ranking of 3 or lower (i.e. the top three restaurants in each city).
Expected results
More Uber questions
| ID | Title | Level | FTPR |
|---|---|---|---|
| 124 | Active restaurants by city uber | easy |
|
| 125 | Active restaurants with fewer than 5 trips per city uber | easy |
|
| 127 | Average rating after 10th trip uber | hard |
|
| 128 | Completion rate uber | easy |
|
| 129 | Top 3 and bottom 3 courier uber | medium |
|