Noruj.com
easy

Identify Abandoned Shopping Carts

In the e-commerce industry, cart abandonment is a critical metric. A shopping cart is considered abandoned when a potential customer adds items to their digital cart but leaves the website without completing the checkout process. Recovering even a small percentage of these lost sales can drastically improve a company's bottom line.

The Marketing and CRM (Customer Relationship Management) teams want to launch an automated email campaign. Their goal is to send targeted reminders and small discount codes to users who have left items in their carts, nudging them to complete their purchases.

You have been given access to the shopping_carts table, which tracks the lifecycle of user sessions. The key columns are cart_id (the unique identifier for the shopping session), customer_id (the user's identifier), created_at (the exact timestamp when the cart was initiated), and checkout_date (the timestamp when the purchase was completed).

Your task is to write a SQL query to identify all the abandoned carts so the marketing team can extract the necessary customer IDs. Please ensure your output strictly adheres to the following conditions:

  • Return only the cart_id, customer_id, and created_at columns.

  • Filter the results to include only carts that have not been checked out (meaning the checkout timestamp is missing).

  • Order the final results by the created_at timestamp in ascending order, so the oldest abandoned carts appear at the very top of the list.

Common Pitfall: A very common mistake for beginners is using the standard equals sign to check for empty values (e.g., checkout_date = NULL). Because NULL represents an unknown value in SQL, evaluating equality against it will always result in an unknown state, meaning your query will return zero rows.

Hint: Use the special IS NULL operator in your WHERE clause to correctly identify records where the checkout date has not been populated.

1
2
3
4
5
Preparing...
Identify Abandoned Shopping Carts

In the e-commerce industry, cart abandonment is a critical metric. A shopping cart is considered abandoned when a potential customer adds items to their digital cart but leaves the website without completing the checkout process. Recovering even a small percentage of these lost sales can drastically improve a company's bottom line. The Marketing and CRM (Customer Relationship Management) teams want to launch an automated email campaign. Their goal is to send targeted reminders and small discount codes to users who have left items in their carts, nudging them to complete their purchases. You have been given access to the shopping_carts table, which tracks the lifecycle of user sessions. The key columns are cart_id (the unique identifier for the shopping session), customer_id (the user's identifier), created_at (the exact timestamp when the cart was initiated), and checkout_date (the timestamp when the purchase was completed). Your task is to write a SQL query to identify all the abandoned carts so the marketing team can extract the necessary customer IDs. Please ensure your output strictly adheres to the following conditions: Return only the cart_id , customer_id , and created_at columns. Filter the results to include only carts that have not been checked out (meaning the checkout timestamp is missing). Order the final results by the created_at timestamp in ascending order, so the oldest abandoned carts appear at the very top of the list. Common Pitfall: A very common mistake for beginners is using the standard equals sign to check for empty values (e.g., checkout_date = NULL ). Because NULL represents an unknown value in SQL, evaluating equality against it will always result in an unknown state, meaning your query will return zero rows. Hint: Use the special IS NULL operator in your WHERE clause to correctly identify records where the checkout date has not been populated.

Table Setup (SQL Schema)

CREATE TABLE shopping_carts (cart_id INT PRIMARY KEY, customer_id INT, created_at TIMESTAMP, checkout_date TIMESTAMP); INSERT INTO shopping_carts (cart_id, customer_id, created_at, checkout_date) VALUES (1, 301, '2026-04-10 10:00:00', '2026-04-10 10:15:00'), (2, 302, '2026-04-11 11:30:00', NULL), (3, 303, '2026-04-12 09:00:00', NULL), (4, 304, '2026-04-09 14:20:00', '2026-04-09 15:00:00'), (5, 305, '2026-04-11 16:45:00', NULL), (6, 306, '2026-04-08 08:15:00', NULL);
Home Videos Quiz Blog