E-commerce: Customers With No Orders (NOT IN)

SQL coding challenge · Difficulty: easy · +50 XP

Problem

The growth team wants to send a "First Purchase" discount to customers who have never bought anything. Find them using a NOT IN subquery.

Tables

Table: Customer

| customer_id | name | email |
| --- | --- | --- |
| 1 | Prince | prince@gmail.com |
| 2 | Mohit | mohit@gmail.com |
| 3 | Rahul | rahul@gmail.com |
| 4 | Krishna | krishna@gmail.com |

Table: Orders

| order_id | customer_id | order_date | total_amount |
| --- | --- | --- | --- |
| 101 | 1 | 2024-05-01 | 1500 |
| 102 | 2 | 2024-05-02 | 2300 |
| 103 | 1 | 2024-05-05 | 1100 |

Expected Output

| customer_id | name |
| --- | --- |
| 3 | Rahul |
| 4 | Krishna |

Return: customer_id, name, email
Must use a NOT IN subquery (not a JOIN)
Sort by customer_id ascending