## Test challenge for data skills

Kevin Ashley

#### 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.
```select * from customers inner join orders on customers.id = orders.customer_id
inner join products orders.product_id = products.id
where products.stock = 0```

A.I. Evaluation of the Solution

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.

Evaluated at: 2022-12-01 00:05:54

#### Community solutions:

Check these solutions from our community and artificial intelligence:
The solution is correct and demonstrates a level of completeness. It solves the problem and uses the correct approach.