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…