138. Happy restaurants

easy doordash

  • ******************************************************************>) $1000*******************************

Table 1: food_delivery

  col_name     | col_type
order_id       | bigint
courier_id     | bigint
delivered_at   | timestamp
rating         | int (1,...,5)

Table 2: food_order

Food orders that are placed on Doordash.

  col_name     | col_type
order_id       | bigint
restaurant_id  | bigint
customer_id    | bigint
created_at     | timestamp
total_amt      | float

Sample results

 year | mon |       percent
 2021 |   8 | 17.9104477611940299
 2021 |   7 | 19.2307692307692308

Solution postgres

WITH monthly_revenue AS (
        EXTRACT(YEAR FROM created_at) AS year,
        EXTRACT(MON FROM created_at) AS mon,
        SUM(O.total_amt) AS rev
    FROM food_order O
    INNER JOIN food_delivery D
    ON D.order_id = O.order_id
    GROUP BY 1,2,3
SELECT year, mon, COUNT(CASE WHEN rev > 1000 THEN restaurant_id ELSE NULL END) * 100.0 / COUNT(*) AS percent
FROM monthly_revenue


This query calculates the percentage of restaurants that have a monthly revenue of over $1000.

First, it creates a temporary table called "monthly_revenue" by joining the "food_order" and "food_delivery" tables and grouping the results by year, month, and restaurant ID. It calculates the total revenue for each restaurant in each month.

Then, the main query selects the year, month, and the percentage of restaurants that have a monthly revenue over $1000. It does this by counting the number of restaurant IDs that have a monthly revenue over $1000 and dividing it by the total number of restaurants in that month. It multiplies this result by 100 to get the percentage.

The results are grouped by year and month, so the output will show the percentage of restaurants with monthly revenue over $1000 for each month and year in the dataset.

Expected results

More Doordash questions

ID Title Level FTPR
136 Extremely late orders doordash easy
137 Extremely late first orders doordash medium
139 Poor first delivery experience doordash medium
140 Daily sales of restaurant 100011 doordash hard
141 Cumulative sales of restaurant 100011 doordash hard