Daily Failed-Delivery Rate

Problem

A food-delivery platform logs every order, plus a flag marking whether each customer and each courier account is suspended.

Table: `orders`

| Column | Type |
|-------------|---------|
| order_id | int |
| customer_id | int |
| courier_id | int |
| outcome | text |
| order_day | varchar |

`order_id` is the primary key. `outcome` is one of `'delivered'`, `'failed_courier'`, `'failed_customer'`. `customer_id` and `courier_id` reference `accounts.account_id`.

Table: `accounts`

| Column | Type |
|-------------|------|
| account_id | int |
| suspended | text |
| account_kind| text |

`account_id` is the primary key. `suspended` is `'yes'` or `'no'`; `account_kind` is `'customer'` or `'courier'`.

An order **counts** only if **both** its customer and its courier accounts are not suspended. The **failed rate** for a day is the number of counted orders that were not delivered divided by the total number of counted orders that day.

Write a query that reports, for each `order_day` from `'2024-02-01'` through `'2024-02-03'` that has at least one counted order, the day and its failed rate rounded to two decimals. Return columns `order_day` and `fail_rate`, in any order.

Tables

Example rows — the live problem includes the full dataset.

Expected output

Your answer should return 3 rows with the columns order_day, fail_rate.

Starter code (SQL)

SELECT *
FROM orders;

Related SQL questions

• 3-Consecutive-Week Marketing StreakHard
• 30-Day Rolling Average Trading VolumeHard
• 7-Day Active User Rolling MetricHard
• 7-Day Revenue Moving AverageHard
• Always Mid-Pack BakersHard
• Annual Energy Output Growth by Panel ModelHard