Drill 2 of 12 in E-commerce questions
0

Interview Question Bank · COUNT(*) FILTER · IS NULL · conditional ratio

2. Cart abandonment rate

EasyE-commerceQuestion #86

Concept: COUNT(*) FILTER · IS NULL · conditional ratioshow

An abandonment rate is the share of started carts that never reached checkout. A NULL foreign key (no order_id) usually marks 'didn't happen', so count those rows with COUNT(*) FILTER (WHERE order_id IS NULL) and divide by COUNT(*). Decide up front which rows belong in the denominator at all, and multiply by 100.0 so the division isn't integer division.

Example (different data):

SELECT to_char(started_on, 'YYYY-MM') AS month,
  ROUND(100.0 * COUNT(*) FILTER (WHERE finished_on IS NULL) / COUNT(*), 2) AS dropout_pct
FROM course_enrolments
WHERE lessons_opened > 0
GROUP BY 1;

Your task

Quillmere's checkout team wants the monthly cart abandonment rate for Q1 2025. A cart with nothing in it doesn't count as a real cart.

Ignoring carts with item_count = 0, return month (YYYY-MM of created_on), carts (number of carts), abandoned (carts with no order_id) and abandonment_pct (abandoned as a percentage of carts, rounded to 2 decimals). A cart with an order_id counts as checked out even if that order was later cancelled. Sort by month.

Starting Postgres in your browser…

Checking your access…