SQL Interview Questions – Immediate Food Delivery

Problem Description –

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 an SQL query to find the percentage of immediate orders in the first orders of all customers, rounded to 2 decimal places.

The query result format is in the following example.

Difficulty Level – Medium

Problem Link – Food Delivery

Solution –

WITH result as (
SELECT *
FROM (
SELECT
    delivery_id,
    customer_id,
    order_date,
    customer_pref_delivery_date,
    (CASE WHEN order_date = customer_pref_delivery_date THEN 1 ELSE 0 END) as immediate,
    RANK() OVER(PARTITION BY customer_id ORDER BY order_date) as first_order
FROM Delivery
) x
WHERE first_order = 1
)

SELECT
ROUND(SUM(immediate) *100 / COUNT(first_order),2) as immediate_percentage
FROM result

In this problem, we need to figure out two pieces of information. First whether or not an order is an immediate order or a scheduled order. And second the first order of a customer.

To find whether an order is an immediate order we used the CASE statement and to find the first order we used RANK window functions. Then in the outer query we only selected the data related to the first order. The first part of the solution looks like this.

WITH result as (
SELECT *
FROM (
SELECT
    delivery_id,
    customer_id,
    order_date,
    customer_pref_delivery_date,
    (CASE WHEN order_date = customer_pref_delivery_date THEN 1 ELSE 0 END) as immediate,
    RANK() OVER(PARTITION BY customer_id ORDER BY order_date) as first_order
FROM Delivery
) x
WHERE first_order = 1
)

SELECT * FROM result;

We need to simple calculate the immediate percentage by diving the total number of immediate order by total number of first orders using the following query.

SELECT
ROUND(SUM(immediate) *100 / COUNT(first_order),2) as immediate_percentage
FROM result