Find All Customers Who Have Ordered a Product That Is Out of Stock

This technical problem involves finding all customers who have ordered a product that is out of stock. The input is three tables: customers, orders, and products. The output is the customers table with the id and name of the customers who have ordered a product that is out of stock.

Problem

Write a SQL query to find all customers who have placed an order for a product that is out of stock.
Input:
customers table:
id | name
1 | John
2 | Jane
3 | Smith
4 | Mark
orders table:
id | customer_id | product_id
1 | 1 | 1
2 | 2 | 1
3 | 3 | 2
4 | 4 | 2
products table:
id | name | stock
1 | Product A | 0
2 | Product B | 10
Output:
customers table:
id | name
1 | John
2 | Jane

Solution

This solution is in SQL. Our A.I. can create solutions in multiple languages.

by

sarastevens

SELECT customers.id, customers.name
FROM customers
JOIN orders ON customers.id = orders.customer_id
JOIN products ON orders.product_id = products.id
WHERE products.stock = 0;

A.I. Evaluation of the Solution

The solution is correct and demonstrates a level of completeness. It solves the problem and uses the correct approach.

Evaluated at: 2022-11-25 14:16:14

Community solutions:

Check these solutions from our community and artificial intelligence:

Find All Customers Who Have Ordered a Product That Is Out of Stock

AskAI

The candidate's solution is incomplete - it does not return the expected output. The candidate has correctly identified that a join is needed to combine the data from the three tables, but has not included all the necessary conditions in the WHERE clause. A better approach would be to use a LEFT JOIN and check for NULL values in the products table, as this would return all customers who have placed an order for a product that is out of stock.