Instant Food Delivery

Use decimal arithmetic such as 100.0 * ... / NULLIF(COUNT(*), 0) when the database might otherwise perform integer division. ROUND controls the numeric value, not necessarily trailing-zero display formatting.

Problem Statement

As a restaurant owner, you want to know how many orders are delivered right away (same day as ordered) versus how many are scheduled for a later day. This helps with planning delivery logistics and understanding what customers actually expect.

Business context If most orders are “immediate,” delivery teams need to be ready to move fast the moment an order comes in. If most orders are “scheduled,” the business has more breathing room to plan routes and staffing in advance.

Goal Calculate what percentage of all orders are immediate orders.

Input A table listing every delivery, along with the date the order was placed and the date the customer prefers to receive it.

Output A single number: the percentage of orders that are immediate, rounded to 2 decimal places.

Table Structure

Table: Delivery

Column Meaning

• delivery_id — a unique ID for each delivery record
• customer_id — identifies which customer placed the order
• order_date — the date the order was placed
• customer_pref_delivery_date — the date the customer wants the order delivered by

An order is called immediate when order_date and customer_pref_delivery_date are the exact same date. Otherwise, it’s called scheduled.

Important Notes

• There is no explicit primary key mentioned, but delivery_id naturally acts as one, since it’s described as a unique identifier.
• Both date columns are expected to always have a value. If either date were NULL, the equality check between them would return UNKNOWN, and that row would simply not count as immediate, similar to how NULL comparisons behaved in the CASE Expression notes.
• The percentage must be rounded to exactly 2 decimal places, so the final ROUND step is not optional, it’s part of the expected output format.
• Watch out for integer division. If both the immediate count and total count are integers, some databases will divide them as whole numbers and lose the decimal part unless one side is explicitly treated as a decimal.

Example

Input: Delivery table

CREATE TABLE Delivery (delivery_id INTEGER, customer_id INTEGER, order_date TEXT, customer_pref_delivery_date TEXT);
INSERT INTO Delivery VALUES
  (1, 1, '2019-08-01', '2019-08-02'),
  (2, 5, '2019-08-02', '2019-08-02'),
  (3, 1, '2019-08-11', '2019-08-11'),
  (4, 3, '2019-08-24', '2019-08-26'),
  (5, 4, '2019-08-21', '2019-08-22'),
  (6, 2, '2019-08-11', '2019-08-13');

Expected Output

immediate_percentage

33.33

Explanation

There are 6 total orders. Out of these, 2 orders have the same order date and preferred delivery date (delivery_id 2 and delivery_id 3), so they are immediate. The rest are scheduled for a later date.

Percentage = (2 / 6) × 100 = 33.33%

Intuition

Picture a food delivery app tracking two dates for every order: the day someone places the order, and the day they actually want it delivered.

Most of the time, these dates are different, someone orders today but asks for delivery tomorrow or later. But sometimes, someone wants it the same day they ordered it, that’s the “immediate” case.

This problem is really just asking: out of every order in the table, what fraction of them are same-day orders, shown as a percentage?

This is a classic pattern of turning a condition into a count, then turning that count into a percentage. You are not filtering rows out of the result, you are counting how many rows satisfy a condition, while still looking at the whole table.

Mental Model

Imagine you have a big pile of 100 receipts from a store. You want to know what percentage of those receipts were paid in cash versus card.

You wouldn’t sort the receipts into two separate piles and count each pile with a different pass. Instead, you’d go through the pile once, and for every receipt, mentally tag it as “cash” or “not cash,” while keeping a running tally. At the end, you divide the cash tally by the total number of receipts, and multiply by 100 to get a percentage.

That’s exactly what this query does. It goes through the Delivery table once, tags each row as immediate or not, counts only the immediate ones, and divides by the total row count.

Thought Process

Before writing SQL, break the problem into smaller questions:

1. How do I know if a single row is “immediate”? By checking if order_date equals customer_pref_delivery_date.
2. Do I need to filter out scheduled orders, or just count immediate ones while still knowing the total? You need both numbers, immediate count and total count, so you cannot use a WHERE clause to filter rows out. WHERE would remove scheduled orders entirely, and then you’d lose the true total.
3. So how do I count only some rows without removing the others? This is where CASE inside COUNT becomes useful. COUNT only counts non-NULL values, so a CASE expression that returns 1 for immediate orders and NULL otherwise gives you exactly the count you need, without touching the rest of the table.
4. How do I turn two counts into a percentage? Divide the immediate count by the total count, multiply by 100, and round to 2 decimal places.
5. Do I need to worry about the division being cut off (integer division)? Yes. This depends on the database, but it’s safest to make sure the division produces a decimal result, not a truncated whole number.

Approach

Step 1: Identify immediate orders The condition order_date = customer_pref_delivery_date decides whether a single row counts as immediate.

Step 2: Count total ordersCOUNT(*) counts every row in the table, regardless of whether it’s immediate or scheduled.

Step 3: Count immediate orders onlyCOUNT(CASE WHEN order_date = customer_pref_delivery_date THEN 1 END) counts only the rows where the condition is true. Since the CASE has no ELSE, every non-matching row returns NULL, and COUNT automatically skips NULLs. This is a clean way to count a subset of rows without a WHERE clause.

Step 4: Calculate the percentage Divide the immediate count by the total count, multiply by 100 to convert it into a percentage, then wrap the whole thing in ROUND(…, 2) to match the expected 2 decimal place format.

SQL Query

SELECT
    ROUND(
        -- Count only immediate orders (CASE returns 1 if immediate, NULL otherwise)
        -- COUNT ignores NULLs, so this effectively counts only immediate rows
        (COUNT(CASE WHEN order_date = customer_pref_delivery_date THEN 1 END)
        -- Divide by total number of orders (COUNT(*) counts every row)
        / COUNT(*)) * 100,
        2
    ) AS immediate_percentage
FROM Delivery;

Query Explanation

• COUNT(CASE WHEN order_date = customer_pref_delivery_date THEN 1 END) — for every row, this checks if the two dates match. If they do, it returns 1. If they don’t, since there is no ELSE, it returns NULL. COUNT only counts values that are not NULL, so this expression ends up counting exactly the immediate orders, and nothing else.
• COUNT(*) — counts every single row in the table, immediate or scheduled, since does not depend on any specific column being NULL or not.
• Division (immediate count / total count) — produces a fraction representing what portion of all orders are immediate.
• 100 — converts that fraction into a percentage.
• ROUND(…, 2) — rounds the final percentage to exactly 2 decimal places, matching the expected output format.
• FROM Delivery — this whole calculation happens over the entire table at once, since there is no GROUP BY, so the query naturally produces a single summary row.

Dry Run

Original table

↓

What SQL does first: evaluate the CASE for every row

↓

Counting step

• COUNT(CASE …) counts only the 1s, ignoring the NULLs → result is 2
• COUNT(*) counts every row, regardless of value → result is 6

↓

Percentage calculation

(2 / 6) × 100 = 33.333… → rounded to 2 decimal places → 33.33

↓

Final output

immediate_percentage

33.33

SQL Execution Flow

FROM Delivery
   ↓
Read all rows
   ↓
Evaluate CASE per row (1 if immediate, NULL otherwise)
   ↓
COUNT non-NULL CASE results → immediate count
   ↓
COUNT(*) → total row count
   ↓
Divide immediate count by total count
   ↓
Multiply by 100
   ↓
ROUND to 2 decimal places
   ↓
Return single summary row

Since there is no WHERE, GROUP BY, or HAVING, the whole table is treated as one single group, and the aggregate functions (COUNT) run over all rows together, producing one final row of output.

Time Complexity

Time Complexity: O(n) Every row needs to be scanned once to evaluate the CASE expression and to count it toward the total. There is no sorting, no joining, and no repeated scanning, so the work grows in a straight line with the number of rows.

Space Complexity: O(1) beyond the final result. No temporary tables, grouping structures, or sorted buffers are needed, since both COUNT expressions can be calculated while scanning the table just once, keeping a small running tally in memory.

Why: This is a single-pass aggregation. The database does not need to look at any row more than once, and it does not need to remember individual row values after counting them, only the running totals.

Common Mistakes to Avoid

Mistake 1: Using WHERE to filter immediate orders

Why it happens: It feels natural to filter for immediate orders first, since that’s the thing being measured.

Wrong:

SELECT
    ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM Delivery), 2) AS immediate_percentage
FROM Delivery
WHERE order_date = customer_pref_delivery_date;

This works, but it needs a separate subquery just to get the total count back, since WHERE already removed the scheduled orders from the main query. It’s more complex than necessary.

Correct: Use CASE inside COUNT instead, so both counts can be computed from a single scan of the table without a subquery, as shown in the main solution.

Mistake 2: Forgetting that division can silently truncate

Why it happens: In some databases, dividing two integers returns another integer, cutting off the decimal part entirely, without any warning or error.

Wrong (in databases where this matters):

SELECT (COUNT(CASE WHEN order_date = customer_pref_delivery_date THEN 1 END) / COUNT(*)) * 100 AS immediate_percentage
FROM Delivery;

If integer division happens here, 2 / 6 could become 0 instead of 0.333…, making the final percentage incorrectly show as 0.

Correct: Make sure at least one side of the division is treated as a decimal, for example by multiplying by 100.0 instead of 100, or by casting one of the counts to a decimal type, depending on the database being used.

Mistake 3: Forgetting ROUND entirely

Why it happens: The raw division might already look like a reasonable number, so it’s easy to forget the formatting requirement.

Wrong:

SELECT (COUNT(CASE WHEN order_date = customer_pref_delivery_date THEN 1 END) * 100.0 / COUNT(*)) AS immediate_percentage
FROM Delivery;

This might return something like 33.333333333, which does not match the expected 2 decimal place format.

Correct: Always wrap the final calculation in ROUND(..., 2) when the expected output specifies a rounding requirement.

Mistake 4: Adding an ELSE 0 inside the CASE unnecessarily

Why it happens: It can feel incomplete to leave out ELSE, since most CASE examples include one.

Unnecessary:

COUNT(CASE WHEN order_date = customer_pref_delivery_date THEN 1 ELSE 0 END)

This actually breaks the logic. COUNT counts non-NULL values, and 0 is not NULL, so this version ends up counting every single row (both immediate and scheduled), not just the immediate ones, since every row now returns either 1 or 0, both of which are non-NULL.

Correct: Leave out ELSE, so non-matching rows return NULL and get correctly ignored by COUNT.

• Mistake 5: Confusing COUNT(column) with COUNT(*)

Why it happens: They look similar, but behave differently around NULLs.

Why it matters: COUNT(*) counts every row no matter what. COUNT(some_column) only counts rows where that specific column is not NULL. Mixing these up in a query like this can silently produce a wrong total, especially if any column used in the CASE expression could ever be NULL.

Correct: Use COUNT(*) specifically when you want the true total row count, and COUNT(CASE WHEN ... THEN 1 END) specifically when you want a conditional count.

Further Questions

• “How would you modify this query to break the percentage down by customer instead of the whole table?”
• “What would happen to this query if some order_date or customer_pref_delivery_date values were NULL?”
• “How would you rewrite this using AVG instead of COUNT and division?”
• “How would you calculate both the immediate percentage and the scheduled percentage in the same query?”
• “What’s the difference between COUNT(*) and COUNT(column_name), and when would that difference actually matter?”

Similar Problems

• Conditional aggregation problems, similar to the “apples vs oranges” example from the CASE Expression concept notes, where SUM(CASE WHEN …) split one metric into multiple totals
• Percentage or ratio calculation problems, such as calculating pass rates, churn rates, or completion rates
• Problems that require combining a conditional count with a total count without removing rows using WHERE

Key SQL Concepts Learned

Pattern Recognition

When should you immediately think of this approach?

• Need to calculate a percentage or ratio of rows matching a condition, out of all rows → COUNT(CASE WHEN …) combined with COUNT(*)
• Need two different counts from the same table without scanning it twice or using a subquery → conditional COUNT inside a single SELECT
• Need to avoid losing rows from the total while still measuring a subset → don’t use WHERE, use CASE inside an aggregate function instead
• Output needs a specific decimal format → always wrap the final number in ROUND(…, decimal_places)
• Dividing two counts and getting an unexpectedly whole number (like 0 or 1) → check for integer division, and force decimal division using 100.0 or an explicit cast