Wishlist Fulfillment Rates
Start Timer
0:00:00
A toy marketplace wants to measure how effectively sellers are fulfilling children’s wishlists during the holiday season. Each wishlist contains one or more items, and every item is labeled as either fulfilled or unfulfilled.
Write a SQL query that computes, for each wishlist:
total_items: total number of items on the wishlistfulfilled_items: number of items marked as'fulfilled'fulfillment_rate: percentage of fulfilled items, rounded to two decimal places
Return one row per wishlist, ordered by fulfillment_rate in descending order.
Note:
- Each wishlist is guaranteed to have at least one item in the wishlist_items table.
- If two wishlists have the same rate, order by
wishlist_idascending.
Schema
Input:
wishlists table
| Column | Type |
|---|---|
| wishlist_id | INTEGER |
| child_name | VARCHAR |
wishlist_items table
| Column | Type |
|---|---|
| item_id | INTEGER |
| wishlist_id | INTEGER |
| item_name | VARCHAR |
| status | VARCHAR |
Output:
| Column | Type |
|---|---|
| wishlist_id | INTEGER |
| total_items | INTEGER |
| fulfilled_items | INTEGER |
| fulfillment_rate | DECIMAL |
Example
Input:
wishlists table
| wishlist_id | child_name |
|---|---|
| 1 | Alice |
| 2 | Bob |
| 3 | Carol |
wishlist_items table
| item_id | wishlist_id | item_name | status |
|---|---|---|---|
| 101 | 1 | Toy Car | fulfilled |
| 102 | 1 | Doll | unfulfilled |
| 103 | 1 | Puzzle | fulfilled |
| 104 | 2 | Board Game | unfulfilled |
| 105 | 2 | Blocks | unfulfilled |
| 106 | 3 | Book | fulfilled |
Output:
| wishlist_id | total_items | fulfilled_items | fulfillment_rate |
|---|---|---|---|
| 1 | 3 | 2 | 66.67 |
| 3 | 1 | 1 | 100.00 |
| 2 | 2 | 0 | 0.00 |
Explanation
Wishlist 1 (Alice):
- Total items: 3
- Fulfilled items: 2 (
Toy Car,Puzzle) - Fulfillment rate:
2/3×100=66.67%
Wishlist 2 (Bob):
- Total items: 2
- Fulfilled items: 0
- Fulfillment rate:
0/2×100=0.00%
Wishlist 3 (Carol):
- Total items: 1
- Fulfilled items: 1 (
Book) - Fulfillment rate:
1/1×100=100.00%
.
.
.
.
.
.
.
.
.
Comments