Cart Abandonment Rate by Device

Medium
106.

Cart Abandonment Rate by Device

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

ColumnTypeValues
cart_idint
customer_idint
deviceenumandroid, ios, web
created_atdatetime
itemsint
order_idint

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.

Example

The rows must come back in the order shown.

carts

cart_idcustomer_iddevicecreated_atitemsorder_id
111android2024-09-01 10:00:0035001
212android2024-09-01 10:30:001NULL
313android2024-09-01 11:00:000NULL
414ios2024-09-01 11:20:002NULL
515ios2024-09-01 12:00:0055002
616web2024-09-01 12:10:001NULL
717web2024-09-01 13:45:002NULL
818web2024-09-01 14:05:0045003
919android2024-09-01 15:30:0025004

Output

devicecartsabandonedabandonment_pct
web3266.67
ios2150
android3133.33
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
cart_idint
customer_idint
deviceenum(android, ios, web)
created_atdatetime
itemsint
order_idint

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_idcustomer_iddevicecreated_atitemsorder_id
111android2024-09-01 10:00:0035001
212android2024-09-01 10:30:001NULL
313android2024-09-01 11:00:000NULL
414ios2024-09-01 11:20:002NULL
515ios2024-09-01 12:00:0055002
616web2024-09-01 12:10:001NULL
717web2024-09-01 13:45:002NULL
818web2024-09-01 14:05:0045003
919android2024-09-01 15:30:0025004

Output

devicecartsabandonedabandonment_pct
web3266.67
ios2150
android3133.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, device

Another 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, device

Another 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 →