MediumJoins

Abandoned Cart Sessions & Lost GMV

Frequently Asked By:AmazonShopifyUber

Interactive In-Browser Workspace

Practice this problem on live schema tables in PostgreSQL 16 WASM with instant row verification and visual EXPLAIN plans.

Solve in Workbench

Problem Statement

Join `checkout_sessions` with `completed_purchases` using a `LEFT JOIN` on `session_id`. Find all abandoned sessions where: - `p.order_id IS NULL` - `s.cart_total_usd >= 100.00` Return: 1. `s.session_id` 2. `s.user_email` 3. `s.cart_total_usd` 4. `s.cart_item_count` Order results by `s.cart_total_usd` descending.

Why Technical Interviewers Ask This

Growth marketers want to run email retargeting sequences for high-intent shoppers who added items to their shopping cart but never completed checkout.

Common Pitfalls & Interview Traps

Forgetting required sorting and deterministic tie-breaking.

Not accounting for NULL or zero-value denominators during calculations.

Using inefficient quadratic subqueries instead of single-pass joins constructs.

Canonical Modern SQL Solution

PostgreSQL 16 / ANSI SQL

SELECT 
        s.session_id,
        s.user_email,
        s.cart_total_usd,
        s.cart_item_count
      FROM checkout_sessions s
      LEFT JOIN completed_purchases p ON s.session_id = p.session_id
      WHERE p.order_id IS NULL
        AND s.cart_total_usd >= 100.00
      ORDER BY s.cart_total_usd DESC;

Approach Breakdown

This query addresses the problem requirements by filtering and structuring data in Joins. It meets the target criteria: High-value abandoned cart sessions with cart value >= $100.

Edge Cases to Verbally Clarify with Your Interviewer

The dataset contains zero matching records (must return valid empty result without crashing).

Multiple records tie on sorting criteria (must evaluate tie-breaking order deterministically).

Foreign keys or secondary attributes have NULL values.

Frequently Asked Follow-Up Questions

What makes this question a favorite among Amazon and Shopify interviewers?

Interviewers use this problem to test whether candidates write defensive, scalable Joins queries that run efficiently on multi-million row production databases without excessive memory usage.

How does the query optimizer execute this query?

Using single-pass table scans with index lookups or sort buffers. Running EXPLAIN (ANALYZE, BUFFERS) in the DB Exercises workbench visualizes the execution nodes and buffer hit cache.