Problem

Table: Delivery

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

+-----------------------------+---------+
| 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’s 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.

Examples

Example 1

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

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.

Solution

Method 1 – Window Functions and Aggregation

Intuition

We want to find, for each customer, their first order and check if it was immediate (order date equals preferred delivery date). Then, we calculate the percentage of such immediate first orders among all customers.

Approach

  1. Use a window function (ROW_NUMBER) to identify the first order for each customer by ordering their orders by order_date.
  2. Filter to only the first order per customer.
  3. Count how many of these first orders are immediate (order_date = customer_pref_delivery_date).
  4. Calculate the percentage and round to 2 decimal places.

Code

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

SELECT
  ROUND(
    100.0 * SUM(order_date = customer_pref_delivery_date) / COUNT(*),
    2
  ) AS immediate_percentage
FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) AS rn
  FROM Delivery
) t
WHERE rn = 1;
 1
 2
 3
 4
 5
 6
 7
 8
 9
10

SELECT
  ROUND(
    100.0 * SUM(CASE WHEN order_date = customer_pref_delivery_date THEN 1 ELSE 0 END) / COUNT(*),
    2
  ) AS immediate_percentage
FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) AS rn
  FROM Delivery
) t
WHERE rn = 1;
1
2
3
4
5
6

def immediate_percentage(df: 'pd.DataFrame') -> float:
    df1 = df.sort_values(['customer_id', 'order_date'])
    first_orders = df1.groupby('customer_id', as_index=False).first()
    cnt = (first_orders['order_date'] == first_orders['customer_pref_delivery_date']).sum()
    ans = round(100.0 * cnt / len(first_orders), 2)
    return ans

Complexity

  • ⏰ Time complexity: O(n) — We scan all rows once to find first orders and count immediate ones.
  • 🧺 Space complexity: O(n) — We may need to store all rows for grouping and windowing.