Customers who never ordered

SQL data engineering interview problem. Difficulty: beginner. Pattern: Joins. About 8 minutes. Free to practice.

Marketing wants to email customers who signed up but never bought anything. You have 2 tables: customers (1 row for each customer) and lb_orders (1 row for each order). Find the customers who have never placed an order. Return customer_id and name, sorted by customer_id. A good way to do it is a LEFT JOIN from customers to lb_orders, and then keeping only the rows where no order was found (order_id IS NULL). Problem 48 shows another way, using NOT EXISTS.

Requirements

  • Return every customer who has no row in lb_orders.
  • Do not return customers who have at least one order.
  • Return only customer_id and name, sorted by customer_id.
  • Each such customer appears once.

Constraints

  • The tables are customers and lb_orders. Note that the order table here is called lb_orders, not orders.
  • Join on customer_id.
  • Return only these 2 columns, in this order: customer_id, name.

Examples

Input: customers lb_orders customer_id | name customer_id C03 | Cam C03 C04 | Dee (no rows) C05 | Eli (no rows) Output: customer_id | name C04 | Dee C05 | Eli Why this passes: Cam has orders. Dee and Eli have none.

Topics: lakebench, sql, anti-join, left join.

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

beginner

Customers who never ordered

Interview-style drill: Anti-join customers with no rows in lb_orders.

Using `customers` and `lb_orders`, find customers who have never placed an order. Columns: `customer_id`, `name`. Order by `customer_id`. Prefer LEFT JOIN ... WHERE order_id IS NULL (problem 48 revisits NOT EXISTS).