Table: Delivery
+-----------------------------+---------+
| Column Name | Type |
+-----------------------------+---------+
| delivery_id | int |
| customer_id | int |
| order_date | date |
| customer_pref_delivery_date | date |
+-----------------------------+---------+
delivery_id is the column of unique values of this table.
The table holds information about food delivery to customers that make orders at some date
and specify a preferred delivery date (on the same order date or after it).
If the customer preferred delivery date is the same as the order date,
then the order is called immediate; otherwise, it is called scheduled.
The first order of a customer is the order with the earliest order date
that the customer made. It is guaranteed that a customer has precisely one first order.
Write a solution to find the percentage of immediate orders in the first orders of
all customers, rounded to 2 decimal places.
The result format is in the following example.
Example 1:
Input:
Delivery table:
+-------------+-------------+------------+-----------------------------+
| delivery_id | customer_id | order_date | customer_pref_delivery_date |
+-------------+-------------+------------+-----------------------------+
| 1 | 1 | 2019-08-01 | 2019-08-02 |
| 2 | 2 | 2019-08-02 | 2019-08-02 |
| 3 | 1 | 2019-08-11 | 2019-08-12 |
| 4 | 3 | 2019-08-24 | 2019-08-24 |
| 5 | 3 | 2019-08-21 | 2019-08-22 |
| 6 | 2 | 2019-08-11 | 2019-08-13 |
| 7 | 4 | 2019-08-09 | 2019-08-09 |
+-------------+-------------+------------+-----------------------------+
Output:
+----------------------+
| immediate_percentage |
+----------------------+
| 50.00 |
+----------------------+
Explanation:
The customer id 1 has a first order with delivery id 1 and it is scheduled.
The customer id 2 has a first order with delivery id 2 and it is immediate.
The customer id 3 has a first order with delivery id 5 and it is scheduled.
The customer id 4 has a first order with delivery id 7 and it is immediate.
Hence, half the customers have immediate first orders.
# customer 별 전체 first order 중 immediate order의 비율
# 모든 customer는 하나의 first order를 갖는다.
# immediate order는 order date와 pref delivery date가 같은 order
# 풀이
with base_table as (
select customer_id
, min(order_date) as first_order
, case when min(order_date) = min(customer_pref_delivery_date) then customer_pref_delivery_date
else null
end as immediate_order
from Delivery
group by customer_id
)
select round(100*count(immediate_order)/count(*), 2) as immediate_percentage
from base_table;
