E-Commerce & Marketplaces
Your customers filled their carts — then vanished. Where did they go?
CartPulse is a fast-growing direct-to-consumer e-commerce platform selling across four product categories — Electronics, Apparel, Home & Garden, and Beauty & Personal Care — with approximately 220,000 monthly active shoppers and $4.8M in monthly GMV. Despite strong add-to-cart behaviour, the retention team has flagged a worrying pattern: a large proportion of sessions end with items sitting in the cart and no purchase completed. The VP of Retention wants to understand whether cart abandonment is uniform across the platform or concentrated in specific device types (mobile, desktop, tablet) and product categories — both of which may point to very different root causes. The analysis window is Q1 2024 (January 1 – March 31).
The analytics database logs each cart interaction as a row in the cart_events table, capturing the device type, product category, and whether the session ultimately ended in a purchase (is_purchased = 1) or abandonment (is_purchased = 0). Your task is to write a SQL aggregation query that groups cart sessions by device type and product category, counts the total number of cart sessions and the number of abandoned sessions, then calculates the abandonment rate as a percentage. This requires filtering on a date range, applying a multi-column GROUP BY, using COUNT and SUM with a CASE WHEN conditional, and computing a derived percentage column.
You are the data analyst on CartPulse's Retention Analytics team. The VP of Retention has asked you to produce a query that outputs one row per device–category combination, showing the total cart sessions, number of abandoned carts, and the abandonment rate (%). The results should be sorted by abandonment rate descending so the highest-risk combinations surface immediately at the top of the report.
No results yet
Write a SQL query and click Run to see results