Never-ordered customers via NOT EXISTS

SQL data engineering interview problem. Difficulty: advanced. Pattern: Subqueries. About 18 minutes. Part of the Pro drill bank.

This is the same question as problem 13, solved a different way. Find the customers who have never placed an order, but this time use NOT EXISTS and not a LEFT JOIN. Using customers and lb_orders, return customer_id and name for every customer that has no row in lb_orders. Sort by customer_id.

Requirements

  • Return customers that have no matching row in lb_orders.
  • Use WHERE NOT EXISTS (SELECT 1 FROM lb_orders ...).
  • Do not use LEFT JOIN.
  • Return only customer_id and name, sorted by customer_id.

Constraints

  • The tables are customers and lb_orders.
  • The subquery must be correlated: it compares o.customer_id = c.customer_id with the outer customer.
  • Return only these 2 columns, in this order: customer_id, name.

Examples

Input: customers customer_id | name C03 | Cam C04 | Dee C05 | Eli lb_orders (matching customer_ids) customer_id | order_id C03 | 6 Output: customer_id | name C04 | Dee C05 | Eli Why this passes: NOT EXISTS returns true when the correlated subquery finds zero rows.

Topics: lakebench, sql, not exists, anti-join.

More SQL interview questions · All interview problems · Learn data engineering

advanced

Never-ordered customers via NOT EXISTS

Interview-style drill: Rewrite the anti-join with NOT EXISTS.

Same result as problem 13: customers with no `lb_orders` rows. Implement it with NOT EXISTS (not LEFT JOIN). Return `customer_id`, `name` ordered by `customer_id`.