Cart Abandonment Rate by Device — SQL Problem
The product team compares checkout drop-off across the Android app, the iOS app and the website.
- Difficulty: Medium
- Topics: Conditional Logic, Aggregation
- Dialect: MySQL
- Problem: #106
Problem statement
The product team compares checkout drop-off across the Android app, the iOS app and the website. A cart is abandoned when it holds at least one item but never became an order (order_id is NULL). Empty carts (items = 0) say nothing about checkout and are left out entirely — of both the count and the rate.
For every device with at least one non-empty cart, return device, carts (non-empty carts), abandoned, and abandonment_pct = 100 × abandoned ÷ carts rounded to 2 decimals. Order the rows by abandonment_pct descending, then by device ascending.
Tables
Table: carts
| Column | Type |
|---|---|
| cart_id | int |
| customer_id | int |
| device | enum(android, ios, web) |
| created_at | datetime |
| items | int |
| order_id | int |
Primary key: cart_id.
One row per cart session. order_id is the order the cart was checked out into, or NULL if it never was.
Examples
Example 1
carts
| cart_id | customer_id | device | created_at | items | order_id |
|---|---|---|---|---|---|
| 1 | 11 | android | 2024-09-01 10:00:00 | 3 | 5001 |
| 2 | 12 | android | 2024-09-01 10:30:00 | 1 | NULL |
| 3 | 13 | android | 2024-09-01 11:00:00 | 0 | NULL |
| 4 | 14 | ios | 2024-09-01 11:20:00 | 2 | NULL |
| 5 | 15 | ios | 2024-09-01 12:00:00 | 5 | 5002 |
| 6 | 16 | web | 2024-09-01 12:10:00 | 1 | NULL |
| 7 | 17 | web | 2024-09-01 13:45:00 | 2 | NULL |
| 8 | 18 | web | 2024-09-01 14:05:00 | 4 | 5003 |
| 9 | 19 | android | 2024-09-01 15:30:00 | 2 | 5004 |
Output
| device | carts | abandoned | abandonment_pct |
|---|---|---|---|
| web | 3 | 2 | 66.67 |
| ios | 2 | 1 | 50 |
| android | 3 | 1 | 33.33 |
How to solve Cart Abandonment Rate by Device
Group the non-empty carts by device and count two things per group: all of them, and the ones with no order. The empty carts are removed in WHERE, before grouping, so a device whose only carts were empty produces no group at all and is not listed — exactly what the statement asks.
Counting a subset inside a group is conditional aggregation: SUM(CASE WHEN order_id IS NULL THEN 1 ELSE 0 END). COUNT(*) - COUNT(order_id) gives the same number because COUNT(column) skips NULLs. The rate is that count over the group size, times 100 — MySQL's / is decimal division, so no cast is needed — then ROUND(..., 2) so both engines print the same figure.
Sorting by the alias abandonment_pct descending, then by device, makes ties deterministic. The IF version keeps the empty carts in the groups and filters them inside every aggregate instead, which needs a HAVING to drop devices with nothing left. Either way it is one pass over the carts.
Reference solution (MySQL)
SELECT device,
COUNT(*) AS carts,
SUM(CASE WHEN order_id IS NULL THEN 1 ELSE 0 END) AS abandoned,
ROUND(100 * SUM(CASE WHEN order_id IS NULL THEN 1 ELSE 0 END) / COUNT(*), 2) AS abandonment_pct
FROM carts
WHERE items > 0
GROUP BY device
ORDER BY abandonment_pct DESC, deviceAnother way
SELECT device, carts, abandoned, ROUND(100 * abandoned / carts, 2) AS abandonment_pct
FROM (SELECT device, COUNT(*) AS carts, COUNT(*) - COUNT(order_id) AS abandoned FROM carts WHERE items > 0 GROUP BY device) t
ORDER BY abandonment_pct DESC, deviceAnother way
SELECT device, SUM(IF(items > 0, 1, 0)) AS carts, SUM(IF(items > 0 AND order_id IS NULL, 1, 0)) AS abandoned,
ROUND(100 * SUM(IF(items > 0 AND order_id IS NULL, 1, 0)) / SUM(IF(items > 0, 1, 0)), 2) AS abandonment_pct
FROM carts GROUP BY device HAVING SUM(IF(items > 0, 1, 0)) > 0
ORDER BY abandonment_pct DESC, device← Masked Reviewer Names for Top Reviews · Sellers With a High Return Rate →