30 Days Of LeetCode Database Day 18 - 1174. Immediate Food Delivery II

1Example 1:
2
3Input: 
4Delivery table:
5+-------------+-------------+------------+-----------------------------+
6| delivery_id | customer_id | order_date | customer_pref_delivery_date |
7+-------------+-------------+------------+-----------------------------+
8| 1           | 1           | 2019-08-01 | 2019-08-02                  |
9| 2           | 2           | 2019-08-02 | 2019-08-02                  |
10| 3           | 1           | 2019-08-11 | 2019-08-12                  |
11| 4           | 3           | 2019-08-24 | 2019-08-24                  |
12| 5           | 3           | 2019-08-21 | 2019-08-22                  |
13| 6           | 2           | 2019-08-11 | 2019-08-13                  |
14| 7           | 4           | 2019-08-09 | 2019-08-09                  |
15+-------------+-------------+------------+-----------------------------+
16Output: 
17+----------------------+
18| immediate_percentage |
19+----------------------+
20| 50.00                |
21+----------------------+
22Explanation: 
23The customer id 1 has a first order with delivery id 1 and it is scheduled.
24The customer id 2 has a first order with delivery id 2 and it is immediate.
25The customer id 3 has a first order with delivery id 5 and it is scheduled.
26The customer id 4 has a first order with delivery id 7 and it is immediate.
27Hence, half the customers have immediate first orders.
28 

1*Formula*
2
3immediate_percentage = number of mmediate order of customer / total number of customer's order * 100

1SELECT ROUND(
2    SUM(CASE WHEN order_date = customer_pref_delivery_date THEN 1 ELSE 0 END)
3    / COUNT(customer_id) * 100, 
4    2
5) AS 'immediate_percentage' FROM (
6    SELECT 
7        customer_id, 
8        MIN(order_date) AS 'order_date', 
9        MIN(customer_pref_delivery_date) AS 'customer_pref_delivery_date'
10        FROM Delivery GROUP BY customer_id
11) AS immediate_order;