*************************************************************************************************************
Table 1: merchant
A reference table for any service provider such as a restaurant.
col_name | col_type -----------------+------------------- id | bigint marketplace_fee | float price_bucket | varchar(5) delivery_zone_id | bigint is_active | boolean first_online_dt | timestamp city | text country | varchar(2)
Table 2: 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)
Sample results
city | count
---------------+-------
Austin | 12
Chicago | 15
Portland | 14
Seattle | 22
Solution postgres
SELECT city, COUNT(id) FROM (
SELECT M.city, M.id, COUNT(DISTINCT trip_id) AS trip_cnt
From merchant M
LEFT JOIN trip T on M.id = T.restaurant_id
WHERE is_active IS TRUE
AND is_completed = TRUE
GROUP BY 1, 2
HAVING COUNT(DISTINCT trip_id) <5
) X
GROUP BY 1;
Explanation
This query is used to count the number of restaurants in each city that have had less than 5 completed trips.
The first inner query selects the city, ID, and the number of unique trips for each restaurant. It uses a LEFT JOIN to combine data from the "merchant" and "trip" tables based on the restaurant ID. It only includes active and completed restaurants and groups the results by city and ID.
The HAVING clause filters the results to only include restaurants that have had less than five unique trips.
The outer query groups the results by city and counts the number of restaurants that meet the previous criteria.
In summary, this query helps to identify cities with a limited number of restaurants that have not yet had many completed trips.
Expected results
More Uber questions
| ID | Title | Level | FTPR |
|---|---|---|---|
| 124 | Active restaurants by city uber | easy |
|
| 126 | Top 3 restaurants uber | hard |
|
| 127 | Average rating after 10th trip uber | hard |
|
| 128 | Completion rate uber | easy |
|
| 129 | Top 3 and bottom 3 courier uber | medium |
|