1174. Immediate Food Delivery II

[QuXiangjie]

1174. Immediate Food Delivery II

My solution

-- Find the first orders of all customers
-- Calculate the immediate orders

WITH cte AS (
    SELECT 
        *,
        DENSE_RANK() OVER (PARTITION BY customer_id ORDER BY order_date) AS dense_ranking
    FROM 
        Delivery
), 
first_cte AS (
    SELECT *
    FROM cte
    WHERE dense_ranking = 1
),
immediate_cte AS (
    SELECT *
    FROM first_cte
    WHERE order_date = customer_pref_delivery_date
)
SELECT 
    ROUND((SELECT COUNT(*) FROM immediate_cte) / (SELECT COUNT(*) FROM first_cte) * 100, 2) AS immediate_percentage;

Just notice that the MySQL doesn't support the numeric function

Other solution

Select 
    round(avg(order_date = customer_pref_delivery_date)*100, 2) as immediate_percentage
from Delivery
where (customer_id, order_date) in (
  Select customer_id, min(order_date) 
  from Delivery
  group by customer_id
);