- ******************************************************************>) $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 (
SELECT
EXTRACT(YEAR FROM created_at) AS year,
EXTRACT(MON FROM created_at) AS mon,
O.restaurant_id,
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
GROUP BY 1,2;
Explanation
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